/// <summary>
/// Execute Only with optional return or No return
/// </summary>
/// <param name="commandText"></param>
private long Execute(string commandText, bool returnIdentity = false)
{
using (var cmd = new SqlDatabaseCommand())
{
cmd.Connection = Connection;
cmd.CommandText = commandText;
try
{
if (returnIdentity)
{
cmd.ExecuteNonQuery();
if (!cmd.GetLastError().Equals("SQLDatabase_OK"))
{
throw new Exception(cmd.GetLastError());
}
else
{
return(cmd.Connection.LastInsertRowId);
}
}
else
{
cmd.ExecuteNonQuery();
if (!cmd.GetLastError().Equals("SQLDatabase_OK"))
{
throw new Exception(cmd.GetLastError());
}
}
}
catch (SqlDatabaseException e)
{
throw e;
}
}
return(0);
}
private void ToInMemoryDatabase_Click(object sender, EventArgs e)
{
//Connection to physical database file
using (SqlDatabaseConnection cnn = new SqlDatabaseConnection("SchemaName=db;uri=file://" + ExampleDatabaseFile))
{
cnn.Open();
using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn))
{
cmd.CommandText = "SELECT COUNT(*) FROM db.SYS_OBJECTS;"; // db.SYS_OBJECTS is SchemaName.ObjectName
Debug.WriteLine("Object Count in db: " + cmd.ExecuteScalar());
cmd.CommandText = "ATTACH Database '@memory' AS 'memdb1' ; "; //Attach new database schema with name memdb1
cmd.ExecuteNonQuery();
cmd.CommandText = "BACKUP Database 'db' AS 'memdb1' ;";
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT COUNT(*) FROM memdb1.SYS_OBJECTS;";
Debug.WriteLine("Object Count in memdb1: " + cmd.ExecuteScalar());
// To Save In memory database to file take backup to disk.
string dbfilepath = Path.Combine(Path.GetDirectoryName(Application.ExecutablePath), "ExampleBackup.db");
if (File.Exists(dbfilepath))
{
File.Delete(dbfilepath);
}
cmd.CommandText = "BACKUP Database 'memdb1' AS '" + dbfilepath + "' ;";
cmd.ExecuteNonQuery();
if (File.Exists(dbfilepath))
{
Debug.WriteLine(string.Format("Backup file created at {0}", dbfilepath));
}
else
{
Debug.WriteLine(cmd.GetLastError());
}
}
}
}