首页 > 代码库 > C#同步SQL Server数据库Schema
C#同步SQL Server数据库Schema
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同步,还可以很多地方可以继续完善,比如约束,双主键,外键等等。
C#同步SQL Server数据库Schema
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。