mainform.cs

Souce Code and sample to transfer SQL Server database to SqlServer Compact edition database. C#, d

                if(rbVer3.Checked)
                    assemblyPath = GLT.SqlCopy.Properties.Settings.Default.SQLMobile30;
                else
                    assemblyPath = GLT.SqlCopy.Properties.Settings.Default.SQLMobile35;


                //Test Assembly version
                SetStatus("Loading correct version of System.Data.SqlServerCe.dll...");
                Assembly asm = Assembly.LoadFrom(assemblyPath);
                AssemblyName asmName = asm.GetName();
                Version ver = asmName.Version;

                Type type = asm.GetType("System.Data.SqlServerCe.SqlCeEngine");
                object[] objArray = new object[1];
                objArray[0] = mobileConnStr;
                object engine = Activator.CreateInstance(type, objArray);

                MethodInfo mi = type.GetMethod("CreateDatabase");
                SetStatus("Creating the SQL Server Compact Edition Database...");
                mi.Invoke(engine, null);

                SetStatus("Connecting to the SQL Server Compact Edition Database...");
                Type connType = asm.GetType("System.Data.SqlServerCe.SqlCeConnection");
                System.Data.IDbConnection conn = (System.Data.IDbConnection)Activator.CreateInstance(connType);
                conn.ConnectionString = mobileConnStr;
                conn.Open();
                
                //create all the tables
                foreach (Table tbl in sourceDb.Tables)
                {
                    if (tbl.IsSystemObject)
                        continue;

                    SetStatus("Scripting table: " + tbl.Name);
                    StringBuilder sb = new StringBuilder();
                    sb.Append("CREATE TABLE ").Append(tbl.Name).Append("(");
                    int colIdx = 0;
                    List<string> pKeys = new List<string>();
                    foreach (Column col in tbl.Columns)
                    {
                        if (colIdx > 0)
                            sb.Append(", ");

                        sb.Append("[").Append(col.Name).Append("]").Append(" ");
                        string dtName = col.DataType.Name.ToLower();
                        if (dtName == "varchar" || dtName == "char")
                        {
                            int max = col.DataType.MaximumLength;
                            col.DataType = new DataType(SqlDataType.NVarChar);
                            col.DataType.MaximumLength = max;
                        }
                        else if (dtName == "text")
                        {
                            col.DataType = new DataType(SqlDataType.NVarChar);
                            col.DataType.MaximumLength = 4000;
                        }
                        else if (dtName == "decimal")
                        {
                            col.DataType = new DataType(SqlDataType.Numeric);
                        }
                        sb.Append(col.DataType.SqlDataType.ToString());
                       
                        string datatype = col.DataType.SqlDataType.ToString().ToLower();
                        if(datatype == "nvarchar" || datatype == "char" || datatype == "varchar" || datatype == "nchar")
                            sb.Append(" (").Append(col.DataType.MaximumLength.ToString()).Append(") ");

                        if (col.InPrimaryKey)
                            pKeys.Add(col.Name);

                        //if (col.InPrimaryKey)
                        //    sb.Append(" CONSTRAINT PK").Append(col.Name);

                        if (!col.Nullable)
                            sb.Append(" NOT NULL");

                        if (col.DefaultConstraint!=null && !String.IsNullOrEmpty(col.DefaultConstraint.Text))
                        {
                            string def = col.DefaultConstraint.Text.Replace("((","(").Replace("))",")");
                            
                            sb.Append(" DEFAULT ").Append(col.DefaultConstraint.Text);
                            //sb.Append(" DEFAULT (1) ");
                        }

                        if (col.Identity)
                        {
                            sb.Append(" IDENTITY (").Append(col.IdentitySeed.ToString()).Append(",").Append(col.IdentityIncrement.ToString()).Append(")");
                        }

                        //if (col.InPrimaryKey)
                        //    sb.Append(" PRIMARY KEY");

                        colIdx++;
                    }
                    sb.Append(")");

                    Type cmdType = asm.GetType("System.Data.SqlServerCe.SqlCeCommand");
                    System.Data.IDbCommand cmd = (System.Data.IDbCommand)Activator.CreateInstance(cmdType);
                    cmd.CommandText = sb.ToString();
                    cmd.Connection = conn;
                    try
                    {
                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(this, ex.Message, "Create table failed!", MessageBoxButtons.OK, MessageBoxIcon.Error);
                        copiedFailed = true;
                        break;
                    }

                    //add the PK constraints
                    if (pKeys.Count > 0)
                    {
                        sb = new StringBuilder();
                        sb.Append("ALTER TABLE ").Append(tbl.Name).Append(" ADD CONSTRAINT PK_");
                        //create the constraint name
                        for (int k = 0; k < pKeys.Count; k++)
                        {
                            if (k > 0)
                                sb.Append("_");

                            sb.Append(pKeys[k]);
                        }

                        sb.Append(" PRIMARY KEY(");
                        //add the constraint fields
                        for (int k = 0; k < pKeys.Count; k++)
                        {
                            if (k > 0)
                                sb.Append(", ");

                            sb.Append(pKeys[k]);
                        }
                        sb.Append(")");
                    }
                    cmd.CommandText = sb.ToString();
                    try
                    {
                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(this, ex.Message, "Create table failed! Failed creating the Primary Key(s).", MessageBoxButtons.OK, MessageBoxIcon.Error);
                        copiedFailed = true;
                        break;
                    }

                    //copy the indexes
                    SetStatus("Scripting the indexes for table: " + tbl.Name);
                    foreach (Index idx in tbl.Indexes)
                    {
                        if (idx.IndexKeyType == IndexKeyType.DriPrimaryKey)
                            continue;

                        sb = new StringBuilder();
                        sb.Append("CREATE");
                        if (idx.IsUnique)
                            sb.Append(" UNIQUE");
                        
                        //if (!idx.IsClustered)
                        //    sb.Append(" CLUSTERED");
                        //else
                        //    sb.Append(" NONCLUSTERED");

                        sb.Append(" INDEX ").Append(idx.Name).Append(" ON ").Append(tbl.Name).Append("(");
                        for (int i = 0; i < idx.IndexedColumns.Count;i++ )
                        {
                            if (i > 0)
                                sb.Append(", ");

                            sb.Append(idx.IndexedColumns[i].Name);
                        }
                        sb.Append(")");
                       
                        cmd.CommandText = sb.ToString();
                        try
                        {
                            cmd.ExecuteNonQuery();
                        }
                        catch (Exception ex)
                        {
                            MessageBox.Show(this, ex.Message, "Create table failed! Failed creating the indexes.", MessageBoxButtons.OK, MessageBoxIcon.Error);
                            copiedFailed = true;
                            break;
                        }
                        
                    }
                }
                SetStatus("Closing the connection to the SQL Server Compact Edition Database...");
                conn.Close();
                conn.Dispose();
                SetStatus("Ready...");
                if(!copiedFailed)
                    MessageBox.Show(this, "Copy Complete.", "Complete");
            }
            else
                SetStatus("Ready...");

            this.Cursor = Cursors.Default;
        }

        private void button2_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void exitToolStripMenuItem_Click(object sender, EventArgs e)
        {
            Application.Exit();
        }

        private void aboutSQLToSqlMobileToolStripMenuItem_Click(object sender, EventArgs e)
        {
            AboutForm frm = new AboutForm();
            frm.StartPosition = FormStartPosition.CenterParent;
            frm.ShowDialog(this);
        }
    }
}