C # Synchronizing SQL Server database schema

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 run 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 also a lot of places to keep intact, for example, constraints. Dual master key. foreign keys, etc...

Copyright notice: This article blog original article. Blogs, without consent, may not be reproduced.

C # Synchronizing SQL Server database schema