wizardform.cs

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

            }
            else if (_currentStep.GetType() == typeof(TableCtrl))
            {
                if (tableCtrl1.SelectedTableNames.Count < 1)
                {
                    MessageBox.Show(this, "You must select at least 1 table to copy before you continue.", "Select at least 1 table.", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return;
                }
                _currentStep = outputCtrl1;
            }
            else if (_currentStep.GetType() == typeof(OutputCtrl))
            {
                _currentStep = optionsCtrl1;
            }
            else if (_currentStep.GetType() == typeof(OptionsCtrl))
            {
                _currentStep = summaryCtrl1;
                StringBuilder sb = new StringBuilder();
                sb.AppendLine("Source Server:").AppendLine(sourceCtrl1.SourceServer.Name).AppendLine(" ");

                sb.AppendLine("Tables to be copied:");
                for (int i = 0; i < tableCtrl1.SelectedTableNames.Count; i++)
                {
                    if (i > 0)
                        sb.Append(", ");

                    sb.Append(tableCtrl1.SelectedTableNames[i]);
                }

                sb.AppendLine(" ").AppendLine("").AppendLine("SQL Server Compact Edition Connection String:").AppendLine(outputCtrl1.OutPutConnectionString).AppendLine(" ");

                sb.AppendLine("Options:").Append("Copy table data?  ").Append(optionsCtrl1.CopyData.ToString());

                summaryCtrl1.SetSummaryText(sb.ToString());
            }
            else if (_currentStep.GetType() == typeof(SummaryCtrl))
            {
                _currentStep = finishCtrl1;
            }
            else if (_currentStep.GetType() == typeof(FinishCtrl))
            {
                Application.Exit();
                return;
            }

            UpdateWizard();
        }

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

        private void DoCopy()
        {
            this.Cursor = Cursors.WaitCursor;

            //Get ref to the source database being copied
            Database sourceDb = sourceCtrl1.SourceServer.Databases[sourceCtrl1.DatabaseName];


            bool doCopy = true;
            //Create the Output SDF file
            finishCtrl1.SetStageText("Creating Database");
            finishCtrl1.SetProgressText("Checking output path...");

            if (File.Exists(outputCtrl1.DestinationPath))
            {
                if (MessageBox.Show(this, "A file with that name already exist! Are you sure you want to overwrite this file?", "File already exist!", MessageBoxButtons.YesNoCancel, MessageBoxIcon.Question) == DialogResult.Yes)
                {
                    try
                    {
                        File.Delete(outputCtrl1.DestinationPath);
                    }
                    catch (IOException ex)
                    {
                        MessageBox.Show(this, ex.Message, "Unable to continue!", MessageBoxButtons.OK, MessageBoxIcon.Error);
                        _currentStep = outputCtrl1;
                        UpdateWizard();
                        this.Cursor = Cursors.Default;
                        return;
                    }
                }
                else
                    doCopy = false;
            }

            if (doCopy)
            {
                bool copiedFailed = false;

                string mobileConnStr = outputCtrl1.OutPutConnectionString;

                string assemblyPath = "";
                if (outputCtrl1.EnableVer31)
                    assemblyPath = GLT.SqlCopy.Properties.Settings.Default.SQLMobile30;
                else
                    assemblyPath = GLT.SqlCopy.Properties.Settings.Default.SQLMobile35;


                //Test Assembly version
                finishCtrl1.SetProgressText("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);

                //Create the database. 
                MethodInfo mi = type.GetMethod("CreateDatabase");
                finishCtrl1.SetProgressText("Creating the SQL Server Compact Edition Database...");
                try
                {
                    mi.Invoke(engine, null);
                }
                catch (TargetInvocationException ex)
                {
                    MessageBox.Show(this, "You do not have permissions to save the file to " + outputCtrl1.DestinationPath + ". Please select a different destination path and try again.", "Create database failed!", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    _currentStep = outputCtrl1;
                    UpdateWizard();
                    this.Cursor = Cursors.Default;
                    return;
                }
                finishCtrl1.SetProgressText("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
                finishCtrl1.UpdateProgressBar(0, tableCtrl1.SelectedTableNames.Count);
                int tblCount = 0;

                Type cmdType = asm.GetType("System.Data.SqlServerCe.SqlCeCommand");
                System.Data.IDbCommand cmd = (System.Data.IDbCommand)Activator.CreateInstance(cmdType);
                foreach (string tblName in tableCtrl1.SelectedTableNames)
                {
                    Table tbl = sourceDb.Tables[tblName, tableCtrl1.SchemaName];
                    if(tbl==null)
                    {
                        MessageBox.Show(this, "Table '" + tblName + "' was not found in the selected schema.", "Create table failed!", MessageBoxButtons.OK, MessageBoxIcon.Error);
                        copiedFailed = true;
                        _currentStep = outputCtrl1;
                        break;
                    }
                    if (tbl.IsSystemObject)
                        continue;

                    
                    finishCtrl1.SetProgressText("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(" ");
                        int max = 0;
                        switch (col.DataType.SqlDataType)
                        {
                            case SqlDataType.VarChar:
                                max = col.DataType.MaximumLength;
                                col.DataType = new DataType(SqlDataType.NVarChar);
                                col.DataType.MaximumLength = max;
                                break;

                            case SqlDataType.Char:
                                max = col.DataType.MaximumLength;
                                col.DataType = new DataType(SqlDataType.NChar);
                                col.DataType.MaximumLength = max;
                                break;

                            case SqlDataType.Text:
                                col.DataType = new DataType(SqlDataType.NText);
                                break;

                            case SqlDataType.Decimal:
                                int scale = col.DataType.NumericScale;
                                int precision = col.DataType.NumericPrecision;
                                col.DataType = new DataType(SqlDataType.Numeric);
                                col.DataType.NumericPrecision = precision;
                                col.DataType.NumericScale = scale;
                                break;


                        }

                        sb.Append(col.DataType.SqlDataType.ToString());

                        SqlDataType datatype = col.DataType.SqlDataType;
                        if (datatype == SqlDataType.NVarChar || datatype == SqlDataType.NChar)
                            sb.Append(" (").Append(col.DataType.MaximumLength.ToString()).Append(") ");
                        else if (datatype == SqlDataType.Numeric)
                            sb.Append(" (").Append(col.DataType.NumericPrecision).Append(",").Append(col.DataType.NumericScale).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(")");


                    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