wizardform.cs

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

                        {
                            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
                    finishCtrl1.SetProgressText("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;
                        }

                    }
                    tblCount++;
                    finishCtrl1.UpdateProgressBar(tblCount, tableCtrl1.SelectedTableNames.Count);
                }

                if (!copiedFailed)
                {
                    //Now copy the data
                    if (optionsCtrl1.CopyData)
                    {
                        finishCtrl1.SetStageText("Copying database data.");

                        foreach (string tblName in tableCtrl1.SelectedTableNames)
                        {
                            Table tbl = sourceDb.Tables[tblName];
                            if (tbl.IsSystemObject)
                                continue;

                            finishCtrl1.SetProgressText("Copying " + tbl.RowCount.ToString() + " rows from " + tbl.Name);
                            finishCtrl1.UpdateProgressBar(0, (int)tbl.RowCount);
                            bool hasIdentity = false;
                            string alterSql = "ALTER TABLE [{0}] ALTER COLUMN [{1}] IDENTITY({2},{3})";
                            string IDColName = "";
                            long increment = 1;
                            //If the table has an Identity column then we need to re-set the seed and increment
                            //This is a hack since SQL Server Compact Edition does not support SET IDENTITY_INSERT <columnname> ON
                            foreach (Column col in tbl.Columns)
                            {
                                if (col.Identity)
                                {
                                    hasIdentity = true;
                                    IDColName = col.Name;
                                    alterSql = String.Format(alterSql, tbl.Name, col.Name, "{0}", "{1}");
                                }
                            }


                            //Select SQL
                            string sql = "SELECT * FROM [{0}]";

                            //Insert Sql
                            string insertSql = "INSERT INTO [{0}] ({1}) VALUES ({2})";
                            StringBuilder sbColums = new StringBuilder();
                            StringBuilder sbValues = new StringBuilder();
                            int idx1 = 0;
                            foreach (Column col in tbl.Columns)
                            {
                                if (col.Name != IDColName)
                                {
                                    if (idx1 > 0)
                                    {
                                        sbColums.Append(",");
                                        sbValues.Append(",");
                                    }

                                    sbColums.Append("[").Append(col.Name).Append("]");
                                    sbValues.Append("?");
                                    idx1++;
                                }
                            }

                            insertSql = String.Format(insertSql, tbl.Name, sbColums.ToString(), sbValues.ToString());

                            sql = String.Format(sql, tbl.Name);
                            DataSet ds = sourceDb.ExecuteWithResults(sql);
                            if (ds.Tables.Count > 0)
                            {
                                if (ds.Tables[0].Rows.Count > 0)
                                {
                                    int rowCnt = 0;
                                    foreach (DataRow row in ds.Tables[0].Rows)
                                    {
                                        rowCnt++;
                                        finishCtrl1.UpdateProgressBar(rowCnt, (int)tbl.RowCount);

                                        if (hasIdentity)
                                        {
                                            long seed = long.Parse(row[IDColName].ToString());
                                            seed--;
                                            string alterTableForIDColumn = String.Format(alterSql, seed.ToString(), increment.ToString());
                                            cmd.CommandText = alterTableForIDColumn;
                                            try
                                            {
                                                cmd.ExecuteNonQuery();
                                            }
                                            catch (Exception ex)
                                            {
                                                MessageBox.Show(this, ex.Message, "Failed altering the Table for IDENTITY insert.", MessageBoxButtons.OK, MessageBoxIcon.Error);
                                                copiedFailed = true;
                                                break;
                                            }
                                        }

                                        sbValues = new StringBuilder();
                                        cmd.Parameters.Clear();
                                        cmd.CommandText = insertSql;
                                        for (int i = 0; i < tbl.Columns.Count; i++)
                                        {
                                            if (tbl.Columns[i].Name != IDColName)
                                            {
                                                Type type1 = asm.GetType("System.Data.SqlServerCe.SqlCeParameter");
                                                object[] objArray1 = new object[2];
                                                objArray1[0] = tbl.Columns[i].Name;
                                                objArray1[1] = row[tbl.Columns[i].Name];
                                                object p = Activator.CreateInstance(type1, objArray1);
                                                cmd.Parameters.Add(p);
                                            }
                                        }
                                        cmd.CommandText = String.Format(insertSql, sbValues.ToString());
                                        try
                                        {
                                            cmd.ExecuteNonQuery();
                                        }
                                        catch (Exception ex)
                                        {
                                            MessageBox.Show(this, ex.Message, "Copy table data failed!", MessageBoxButtons.OK, MessageBoxIcon.Error);
                                            copiedFailed = true;
                                            break;
                                        }
                                    }
                                }
                            }
                        }




                        //Now add the FK relationships
                        if (!copiedFailed)
                        {
                            finishCtrl1.SetStageText("Adding ForeignKeys.");
                            string fkSql = "ALTER TABLE [{0}] ADD CONSTRAINT [{1}] FOREIGN KEY([{2}]) REFERENCES [{3}] ([{4}])";
                            foreach (string tblName in tableCtrl1.SelectedTableNames)
                            {
                                Table tbl = sourceDb.Tables[tblName];
                                if (tbl.IsSystemObject)
                                    continue;

                                int fkCnt = tbl.ForeignKeys.Count;
                                int fxIdx = 0;
                                foreach(ForeignKey fk in tbl.ForeignKeys)
                                {
                                    if(!tableCtrl1.SelectedTableNames.Contains(fk.ReferencedTable))
                                        continue;

                                    fxIdx++;
                                    finishCtrl1.UpdateProgressBar(fxIdx, fkCnt);
                                    finishCtrl1.SetProgressText(tbl.Name + ": " + fk.Name);
                                    string createFKSql = String.Format(fkSql, tbl.Name, fk.Name, "{0}", fk.ReferencedTable, sourceDb.Tables[fk.ReferencedTable].Indexes[fk.ReferencedKey].IndexedColumns[0].Name);
                                    StringBuilder sbFk = new StringBuilder();
                                    foreach (ForeignKeyColumn col in fk.Columns)
                                    {
                                        if (sbFk.Length > 0)
                                            sbFk.Append(",");

                                        sbFk.Append(col.Name);
                                    }
                                    createFKSql = String.Format(createFKSql, sbFk.ToString());
                                    cmd.CommandText = createFKSql;
                                    try
                                    {
                                        cmd.ExecuteNonQuery();
                                    }
                                    catch (Exception ex)
                                    {
                                        MessageBox.Show(this, ex.Message, "Creating ForeignKeys failed!", MessageBoxButtons.OK, MessageBoxIcon.Error);
                                        copiedFailed = true;
                                        break;
                                    }
                                }
                                
                            }
                            
                        }

                        finishCtrl1.SetProgressText("Closing the connection to the SQL Server Compact Edition Database...");
                        conn.Close();
                        conn.Dispose();

                        if (!copiedFailed)
                        {
                            finishCtrl1.SetStageText("");
                            finishCtrl1.SetProgressText("Complete!");
                            finishCtrl1.UpdateProgressBar(100, 100);
                        }
                        else
                        {
                            finishCtrl1.SetStageText("");
                            finishCtrl1.SetProgressText("Copy failed!");
                            finishCtrl1.UpdateProgressBar(0, 0);
                        }
                    }
                    else
                    {
                        finishCtrl1.SetStageText("");
                        finishCtrl1.SetProgressText("Finished!");
                        finishCtrl1.UpdateProgressBar(100, 100);
                    }
                }
                else
                {
                    finishCtrl1.SetStageText("");
                    finishCtrl1.SetProgressText("Copy failed!");
                    finishCtrl1.UpdateProgressBar(0, 0);
                    btnBack.Enabled = true;
                }
                btnNext.Enabled = true;
                this.Cursor = Cursors.Default;
            }
        }

        private void linkLabel1_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
        {
            System.Diagnostics.Process.Start("http://www.johnnycantcode.com");
        }

        private void btnHelp_Click(object sender, EventArgs e)
        {
            AboutForm frm = new AboutForm();
            frm.ShowDialog(this);
        }
    }
}