private void Form1_Load(object sender, System.EventArgs e) { try { System.IO.File.Delete("\\My Documents\\prac3.sdf"); System.Data.SqlServerCe.SqlCeEngine SQLEngine = new System.Data.SqlServerCe.SqlCeEngine("data source=\\My Documents\\prac3.sdf"); SQLEngine.CreateDatabase(); // Next, open the database. cn = new System.Data.SqlServerCe.SqlCeConnection("Data Source=\\My Documents\\prac3.sdf"); cn.Open(); //Create the structure of the database using SQL statements. // Create the Titles table. String SQL = "CREATE TABLE Titles (TitleID nchar(5) Primary Key " + "NOT NULL,TitleName nvarchar(40) NOT NULL)"; System.Data.SqlServerCe.SqlCeCommand cmd = new System.Data.SqlServerCe.SqlCeCommand(SQL, cn); cmd.CommandType = CommandType.Text; cmd.ExecuteNonQuery(); SQL = ""; //Insert Data into the table. SQL = "INSERT INTO Titles (TitleID, TitleName) VALUES " + "('MSCF1','Compact Framework')"; cmd.CommandText = SQL; cmd.ExecuteNonQuery(); SQL = ""; SQL = "INSERT INTO Titles (TitleID, TitleName) VALUES " + "('MSCE1','SQLCE DB')"; cmd.CommandText = SQL; cmd.ExecuteNonQuery(); } catch (SqlCeException ex) { ShowErrors(ex); } finally { cn.Close(); } }
public void Convert() { if (ValidateDestination() == false) { return; } Server SourceServer = null; if (Configuration.SqlServerIntegratedSecurity) { SourceServer = new Server(Configuration.SqlServerName); } else { ServerConnection svrConn = new ServerConnection(Configuration.SqlServerName); svrConn.LoginSecure = false; svrConn.Login = Configuration.SqlServerUserName; svrConn.Password = Configuration.SqlServerPassword; SourceServer = new Server(svrConn); } Database sourceDb = SourceServer.Databases[Configuration.SqlServerDatabaseName]; if (sourceDb == null) { Console.WriteLine("Source db '{0}' not found in '{1}'", Configuration.SqlServerDatabaseName, Configuration.SqlServerName); return; } List <string> schemaNames = new List <string>(); foreach (Schema schema in sourceDb.Schemas) { if (schema.Name.Substring(0, 3) != "db_") { schemaNames.Add(schema.Name); } } List <string> tableNames = new List <string>(); foreach (Table tbl in sourceDb.Tables) { if (!tbl.IsSystemObject) { tableNames.Add(tbl.Name); } } string sqlCeConnectionString = string.Empty; if (string.IsNullOrEmpty(Configuration.SqlCePassword)) { sqlCeConnectionString = string.Format("Data Source='{0}';Encrypt={1};SSCE:Max Database Size=4091;", Configuration.SqlCeFileName, Configuration.SqlCeIsEncrypted.ToString().ToUpper()); } { sqlCeConnectionString = string.Format("Data Source='{0}';Password={1};Encrypt={2};SSCE:Max Database Size=4091;", Configuration.SqlCeFileName, Configuration.SqlCePassword, Configuration.SqlCeIsEncrypted.ToString().ToUpper()); } bool copiedFailed = false; sqlCeConnectionString = sqlCeConnectionString.Replace("LCID=idpe;", ""); System.Data.SqlServerCe.SqlCeEngine eng = new System.Data.SqlServerCe.SqlCeEngine(sqlCeConnectionString); object engine = eng; Type type = engine.GetType(); Assembly asm = Assembly.GetAssembly(typeof(System.Data.SqlServerCe.SqlCeEngine)); Console.WriteLine("Sql Ce Version:" + asm.GetName().Version.ToString()); //Create the database. MethodInfo mi = type.GetMethod("CreateDatabase"); Console.WriteLine("Creating the SQL Server Compact Edition Database..."); try { mi.Invoke(engine, null); } catch (TargetInvocationException ex) { Console.WriteLine("You do not have permissions to save the file to " + Configuration.SqlCeFileName + ". Please select a different destination path and try again."); return; } Console.WriteLine("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 = sqlCeConnectionString; conn.Open(); //create all the tables 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 tableNames) { Table tbl = sourceDb.Tables[tblName, Configuration.SqlServerSchemaName]; if (tbl == null) { Console.WriteLine("Table '" + tblName + "' was not found in the selected schema."); copiedFailed = true; break; } if (tbl.IsSystemObject) { continue; } //if (tbl.Name == "IdpeVersion") // Debugger.Break(); Console.WriteLine("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(", "); } //if (col.Name == "Data") // Debugger.Break(); 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: case SqlDataType.VarCharMax: col.DataType = new DataType(SqlDataType.NText); break; case SqlDataType.VarBinaryMax: col.DataType = new DataType(SqlDataType.Image); 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.CommandText = cmd.CommandText.Replace("suser_sname()", "'Manual User'"); cmd.Connection = conn; try { cmd.ExecuteNonQuery(); } catch (Exception ex) { Console.WriteLine("Create table failed! " + ex.Message); 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) { Console.WriteLine("Create table failed! Failed creating the Primary Key(s)."); copiedFailed = true; break; } } //copy the indexes Console.WriteLine("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) { Console.WriteLine("Create table failed! Failed creating the indexes." + ex.Message); copiedFailed = true; break; } } tblCount++; } if (!copiedFailed) { //Now copy the data bool copyData = true; if (copyData) { Console.WriteLine("Copying database data."); foreach (string tblName in tableNames) { Table tbl = sourceDb.Tables[tblName]; if (tbl.IsSystemObject) { continue; } Console.WriteLine("Copying " + tbl.RowCount.ToString() + " rows from " + tbl.Name); 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++; } } //if (tbl.Name.Contains("IdpeVersion")) // Debugger.Break(); 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++; 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) { Console.WriteLine("Failed altering the Table for IDENTITY insert."); 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) { //if (tbl.Columns[i].Name == "Data") // Debugger.Break(); 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) { Console.WriteLine("Copy table data failed!"); copiedFailed = true; break; } } } } } //Now add the FK relationships if (!copiedFailed) { Console.Write("Adding ForeignKeys."); string fkSql = "ALTER TABLE [{0}] ADD CONSTRAINT [{1}] FOREIGN KEY([{2}]) REFERENCES [{3}] ([{4}])"; foreach (string tblName in tableNames) { Table tbl = sourceDb.Tables[tblName]; if (tbl.IsSystemObject) { continue; } int fkCnt = tbl.ForeignKeys.Count; int fxIdx = 0; foreach (ForeignKey fk in tbl.ForeignKeys) { if (!tableNames.Contains(fk.ReferencedTable)) { continue; } fxIdx++; Console.WriteLine(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) { Console.WriteLine("Creating ForeignKeys failed!"); //copiedFailed = true; //break; } } } } Console.WriteLine("Closing the connection to the SQL Server Compact Edition Database..."); conn.Close(); conn.Dispose(); if (!copiedFailed) { Console.WriteLine("Completed!"); } else { Console.WriteLine("Copy failed!"); } } else { Console.WriteLine("Finished!"); } } else { Console.WriteLine("Copy failed!"); } }