public static void UpgradeDataBase()
{
string connStringCI = "Data Source= SmartCA.sdf; LCID= 1033";
// Set "Case Sensitive" to true to change the collation from CI to CS.
string connStringCS = "Data Source= SmartCA.sdf; LCID= 1033; Case Sensitive=true";
SqlCeEngine engine = new SqlCeEngine(connStringCI);
// The collation of the database will be case sensitive because of
// the new connection string used by the Upgrade method.
engine.Upgrade(connStringCS);
SqlCeConnection conn = null;
conn = new SqlCeConnection(connStringCI);
conn.Open();
//Retrieve the connection string information - notice the 'Case Sensitive' value.
List <KeyValuePair <string, string> > dbinfo = conn.GetDatabaseInfo();
Console.WriteLine("\nGetDatabaseInfo() results:");
foreach (KeyValuePair <string, string> kvp in dbinfo)
{
Console.WriteLine(kvp);
}
}
/// <summary>
/// Demonstrates how to upgrade a database with case sensitivity.
/// </summary>
public static void UpgradeDatabasewithCaseSensitive(string filePath)
{
// <Snippet2>
// Default case-insentive connection string.
// Note that Northwind.sdf is an old 3.1 version database.
string connStringCI = $"Data Source= {filePath}; LCID= 1033";
// Set "Case Sensitive" to true to change the collation from CI to CS.
string connStringCS = $"Data Source= {filePath}; LCID= 1033; Case Sensitive=true";
SqlCeEngine engine = new SqlCeEngine(connStringCI);
// The collation of the database will be case sensitive because of
// the new connection string used by the Upgrade method.
engine.Upgrade(connStringCS);
SqlCeConnection conn = null;
conn = new SqlCeConnection(connStringCI);
conn.Open();
//Retrieve the connection string information - notice the 'Case Sensitive' value.
List <KeyValuePair <string, string> > dbinfo = conn.GetDatabaseInfo();
Console.WriteLine("\nGetDatabaseInfo() results:");
foreach (KeyValuePair <string, string> kvp in dbinfo)
{
Console.WriteLine(kvp);
}
// </Snippet2>
}
internal static string UpgradeDB()
{
string connStringCI = "Data Source= Shop.sdf; LCID= 1049";
// Set "Case Sensitive" to true to change the collation from CI to CS.
string connStringCS = "Data Source= Shop.sdf; LCID= 1049; Case Sensitive=true";
SqlCeEngine engine = new SqlCeEngine(connStringCI);
// The collation of the database will be case sensitive because of
// the new connection string used by the Upgrade method.
engine.Upgrade(connStringCS);
SqlCeConnection conn = null;
conn = new SqlCeConnection(connStringCI);
conn.Open();
//Retrieve the connection string information - notice the 'Case Sensitive' value.
List <KeyValuePair <string, string> > dbinfo = conn.GetDatabaseInfo();
Console.WriteLine("\nGetDatabaseInfo() results:");
foreach (KeyValuePair <string, string> kvp in dbinfo)
{
Console.WriteLine(kvp);
}
return("Shop.sdf has been upgraded to Ver. 4.0. Please rerun the program.");
}
private List <KeyValuePair <string, string> > GetSqlCeInfo()
{
List <KeyValuePair <string, string> > valueList = new List <KeyValuePair <string, string> >();
#if V31
#else
valueList = cn.GetDatabaseInfo();
#endif
valueList.Add(new KeyValuePair <string, string>("Database", cn.Database));
valueList.Add(new KeyValuePair <string, string>("ServerVersion", cn.ServerVersion));
if (System.IO.File.Exists(cn.Database))
{
System.IO.FileInfo fi = new System.IO.FileInfo(cn.Database);
valueList.Add(new KeyValuePair <string, string>("DatabaseSize", fi.Length.ToString(System.Globalization.CultureInfo.InvariantCulture)));
valueList.Add(new KeyValuePair <string, string>("Created", fi.CreationTime.ToShortDateString() + " " + fi.CreationTime.ToShortTimeString()));
}
return(valueList);
}
private List <KeyValuePair <string, string> > GetSqlCeInfo()
{
List <KeyValuePair <string, string> > valueList = new List <KeyValuePair <string, string> >();
#if V31
#else
valueList = cn.GetDatabaseInfo();
#endif
valueList.Add(new KeyValuePair <string, string>("Database", cn.Database));
valueList.Add(new KeyValuePair <string, string>("ServerVersion", cn.ServerVersion));
if (System.IO.File.Exists(cn.Database))
{
System.IO.FileInfo fi = new System.IO.FileInfo(cn.Database);
valueList.Add(new KeyValuePair <string, string>("DatabaseSize", RepositoryHelper.GetSizeReadable(fi.Length)));
valueList.Add(new KeyValuePair <string, string>("Created", fi.CreationTime.ToShortDateString() + " " + fi.CreationTime.ToShortTimeString()));
}
return(valueList);
}
internal void Execute(EngineAction action, string newConnectionString)
{
// Specify connection string for new database options; The following
// tokens are valid:
// - Password
// - LCID
// - Encryption Mode
// - Case Sensitive
//
// All other SqlCeConnection.ConnectionString tokens are ignored
//
// engine.Compact("Data Source=; Password =a@3!7f$dQ;");
if (action != EngineAction.GetInfo)
{
using (SqlCeEngine engine = new SqlCeEngine(connectionString))
{
switch (action)
{
case EngineAction.Undefined:
break;
case EngineAction.Shrink:
engine.Shrink();
Console.WriteLine("Database successfully shrunk");
break;
case EngineAction.Verify:
Console.WriteLine(engine.Verify(VerifyOption.Enhanced)
? "Database successfully verified"
: "Database verification failed");
break;
case EngineAction.Compact:
engine.Compact(null);
Console.WriteLine("Database successfully compacted");
break;
case EngineAction.Upgrade:
engine.Upgrade();
Console.WriteLine("Database successfully upgraded");
break;
case EngineAction.Create:
engine.CreateDatabase();
Console.WriteLine("Database successfully created");
break;
case EngineAction.RepairDelete:
engine.Repair(null, RepairOption.DeleteCorruptedRows);
Console.WriteLine("Database successfully repaired");
break;
case EngineAction.RepairRecover:
engine.Repair(null, RepairOption.RecoverAllOrFail);
Console.WriteLine("Database successfully repaired");
break;
case EngineAction.SetOption:
engine.Compact(newConnectionString);
Console.WriteLine("Database option(s) successfully changed");
break;
default:
break;
}
}
}
else if (action == EngineAction.GetInfo)
{
using (SqlCeConnection cn = new SqlCeConnection(connectionString))
{
cn.Open();
List <KeyValuePair <string, string> > valueList = new List <KeyValuePair <string, string> >();
// 3.5 or later only API
valueList = cn.GetDatabaseInfo();
valueList.Add(new KeyValuePair <string, string>("Database", cn.Database));
valueList.Add(new KeyValuePair <string, string>("ServerVersion", cn.ServerVersion));
if (System.IO.File.Exists(cn.Database))
{
System.IO.FileInfo fi = new System.IO.FileInfo(cn.Database);
valueList.Add(new KeyValuePair <string, string>("DatabaseSize", fi.Length.ToString(CultureInfo.InvariantCulture)));
valueList.Add(new KeyValuePair <string, string>("Created", fi.CreationTime.ToShortDateString() + " " + fi.CreationTime.ToShortTimeString()));
}
valueList.Add(new KeyValuePair <string, string>(string.Empty, string.Empty));
valueList.Insert(0, new KeyValuePair <string, string>("SqlCeCmd", "Database Information"));
foreach (KeyValuePair <string, string> pair in valueList)
{
Console.WriteLine(string.Format(CultureInfo.InvariantCulture, "{0}: {1}", pair.Key, pair.Value));
}
}
}
return;
}
