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!");
}
}
