C # synchronizes SQL Server database SCHEMA1. Write a SQL processing class first:
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 Sq Lconnection (THIS.CONNECTIONSTR); Sqlconn.open (); } else if (sqlconn.state = = connectionstate.closed) {sqlconn.open (); }} public Dbutility (string server, String database, string uid, string password) {THIS.S erver = server; This. Database = database; This. UID = UID; This. Password = Password; This.connectionstr = "server=" + this. Server + ";D AtabasE= "+ 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. Write a database type class again:
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 ("The", "image"); Systemtypedict.add ("n", "text"); Systemtypedict.add ("A", "uniqueidentifier"); Systemtypedict.add ("Max", "date"); Systemtypedict.add ("A", "time"); Systemtypedict.add ("A", "datetime2"); Systemtypedict.add ("A", "DateTimeOffset"); Systemtypedict.add ("The", "tinyint"); Systemtypedict.add ("smallint"); Systemtypedict.add ("n", "int"); Systemtypedict.add ("smalldatetime"); Systemtypedict.add ("The", "real"); Systemtypedict.add ("A", "money"); Systemtypedict.add ("A", "datetime"); Systemtypedict.aDD ("+", "float"); Systemtypedict.add ("98", "sql_variant"); Systemtypedict.add ("The", "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. Write a synchronization database table structure schema:
public void Syncdbschema (string server, String dbname, String uid, string password, string server2, String Dbna Me2, 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": 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_nor Ecompute = off, Ignore_dup_key = off, Allow_row_locks = on, allow_page_locks = on) on [PRIMARY]) O N [PRIMARY] "+ newLine; } test = "Use [" + Dbname2 + "]" + newLine + test; dbutility DB2 = new Dbutility (Server2, Dbname2, Uid2, Password2); DB2. Executenonqueryformultiplescripts (test); }
4. Finally execute the sync function:
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 ()); } }
Note: This is just a simple DB schema synchronization, there are many places can continue to improve, such as constraints, double primary keys, foreign keys and so on.
C # Synchronizing SQL Server database schema