C#同步SQL Server数据库Schema
1. 先写个sql处理类：
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
namespace PinkDatabaseSync
{
class DBUtility : IDisposable
{
private string Server;
private string Database;
private string Uid;
private string Password;
private string connectionStr;
private SqlConnection sqlConn;
public void EnsureConnectionIsOpen()
{
if (sqlConn == null)
{
sqlConn = new SqlConnection(this.connectionStr);
sqlConn.Open();
}
else if (sqlConn.State == ConnectionState.Closed)
{
sqlConn.Open();
}
}
public DBUtility(string server, string database, string uid, string password)
{
this.Server = server;
this.Database = database;
this.Uid = uid;
this.Password = password;
this.connectionStr = "Server=" + this.Server + ";Database=" + this.Database + ";User Id=" + this.Uid + ";Password=" + this.Password;
}
public int ExecuteNonQueryForMultipleScripts(string sqlStr)
{
EnsureConnectionIsOpen();
SqlCommand cmd = sqlConn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = sqlStr;
return cmd.ExecuteNonQuery();
}
public int ExecuteNonQuery(string sqlStr)
{
EnsureConnectionIsOpen();
SqlCommand cmd = new SqlCommand(sqlStr, sqlConn);
cmd.CommandType = CommandType.Text;
return cmd.ExecuteNonQuery();
}
public object ExecuteScalar(string sqlStr)
{
EnsureConnectionIsOpen();
SqlCommand cmd = new SqlCommand(sqlStr, sqlConn);
cmd.CommandType = CommandType.Text;
return cmd.ExecuteScalar();
}
public DataSet ExecuteDS(string sqlStr)
{
DataSet ds = new DataSet();
EnsureConnectionIsOpen();
SqlDataAdapter sda= new SqlDataAdapter(sqlStr,sqlConn);
sda.Fill(ds);
return ds;
}
public void Dispose()
{
if (sqlConn != null)
sqlConn.Close();
}
}
}
2. 再写个数据库类型类：
using System;
using System.Collections.Generic;
using System.Text;
namespace PinkDatabaseSync
{
public class SQLDBSystemType
{
public static Dictionary<string, string> systemTypeDict
{
get{
var systemTypeDict = new Dictionary<string, string>();
systemTypeDict.Add("34", "image");
systemTypeDict.Add("35", "text");
systemTypeDict.Add("36", "uniqueidentifier");
systemTypeDict.Add("40", "date");
systemTypeDict.Add("41", "time");
systemTypeDict.Add("42", "datetime2");
systemTypeDict.Add("43", "datetimeoffset");
systemTypeDict.Add("48", "tinyint");
systemTypeDict.Add("52", "smallint");
systemTypeDict.Add("56", "int");
systemTypeDict.Add("58", "smalldatetime");
systemTypeDict.Add("59", "real");
systemTypeDict.Add("60", "money");
systemTypeDict.Add("61", "datetime");
systemTypeDict.Add("62", "float");
systemTypeDict.Add("98", "sql_variant");
systemTypeDict.Add("99", "ntext");
systemTypeDict.Add("104", "bit");
systemTypeDict.Add("106", "decimal");
systemTypeDict.Add("108", "numeric");
systemTypeDict.Add("122", "smallmoney");
systemTypeDict.Add("127", "bigint");
systemTypeDict.Add("240-128", "hierarchyid");
systemTypeDict.Add("240-129", "geometry");
systemTypeDict.Add("240-130", "geography");
systemTypeDict.Add("165", "varbinary");
systemTypeDict.Add("167", "varchar");
systemTypeDict.Add("173", "binary");
systemTypeDict.Add("175", "char");
systemTypeDict.Add("189", "timestamp");
systemTypeDict.Add("231", "nvarchar");
systemTypeDict.Add("239", "nchar");
systemTypeDict.Add("241", "xml");
systemTypeDict.Add("231-256", "sysname");
return systemTypeDict;
}
}
}
}
3. 写个同步数据库表结构schema：
public void SyncDBSchema(string server, string dbname, string uid, string password,
string server2, string dbname2, string uid2, string password2)
{
DBUtility db = new DBUtility(server, dbname, uid, password);
DataSet ds = db.ExecuteDS("SELECT sobjects.name FROM sysobjects sobjects WHERE sobjects.xtype = 'U'");
DataRowCollection drc = ds.Tables[0].Rows;
string test = string.Empty;
string newLine = " ";
foreach (DataRow dr in drc)
{
string tableName = dr[0].ToString();
test += "if NOT exists (select * from sys.objects where name = '" + tableName + "' and type = 'u')";
test += "CREATE TABLE [dbo].[" + tableName + "](" + newLine;
DataSet ds2 = db.ExecuteDS("SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('dbo." + tableName + "')");
DataRowCollection drc2 = ds2.Tables[0].Rows;
foreach (DataRow dr2 in drc2)
{
test += "[" + dr2["name"].ToString() + "] ";
string typeName = SQLDBSystemType.systemTypeDict[dr2["system_type_id"].ToString()];
test += "[" + typeName + "]";
string charLength = string.Empty;
if (typeName.Contains("char"))
{
charLength = (Convert.ToInt32(dr2["max_length"].ToString()) / 2).ToString();
test += "(" + charLength + ")" + newLine;
}
bool isIdentity = bool.Parse(dr2["is_identity"].ToString());
test += isIdentity ? " IDENTITY(1,1)" : string.Empty;
bool isNullAble = bool.Parse(dr2["is_nullable"].ToString());
test += (isNullAble ? " NULL," : " NOT NULL,") + newLine;
}
test += "CONSTRAINT [PK_" + tableName + "] PRIMARY KEY CLUSTERED ";
string primaryKeyName = drc2[0]["name"].ToString();
test += @"(
[" + primaryKeyName + @"] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]" + newLine;
}
test = "use [" + dbname2 + "]" + newLine + test;
DBUtility db2 = new DBUtility(server2, dbname2, uid2, password2);
db2.ExecuteNonQueryForMultipleScripts(test);
}
4. 最后执行同步函数：
private void SyncDB_Click(object sender, EventArgs e)
{
string server = "localhost";
string dbname = "testdb1";
string uid = "sa";
string password = "password1";
string server2 = "servername2";
string dbname2 = "testdb2";
string uid2 = "sa";
string password2 = "password2";
try
{
SyncDBSchema(server, dbname, uid, password, server2, dbname2, uid2, password2);
MessageBox.Show("Done sync db schema successfully!");
}
catch (Exception exc)
{
MessageBox.Show(exc.ToString());
}
}注： 这只是做个简单的DB schema同步，还可以很多地方可以继续完善，比如约束，双主键，外键等等。
2332
