C # Synchronizing SQL Server database schema

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.