static void SimpleTransaction()
{
using (SqlDatabaseConnection cnn = new SqlDatabaseConnection("schemaname=db;uri=file://@memory;"))
{
cnn.Open();
using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn))
{
cmd.CommandText = "Create Table If not exists temptable(Id Integer, TextValue Text) ; ";
cmd.ExecuteNonQuery();
SqlDatabaseTransaction trans = cnn.BeginTransaction();
cmd.Transaction = trans;
try
{
for (int i = 0; i < 1000; i++)
{
cmd.CommandText = "INSERT INTO temptable VALUES (" + i + ", 'A Value" + i + "');";
cmd.ExecuteNonQuery();
}
trans.Commit(); //Commit since all inserts are completed.
//Test if transaction is open
if (trans.IsOpen)
{
trans.Commit();
}
}
catch (SqlDatabaseException sqlex)
{
//Rollback as their was an error.
if (trans.IsOpen)
{
trans.Rollback();
}
Console.WriteLine(sqlex.Message);
}
cmd.CommandText = "SELECT COUNT(*) FROM temptable;";
Console.WriteLine("Table Record Count using COUNT(*) : {0}", cmd.ExecuteScalar());
// Pure SQL Way of starting and committing transaction.
cmd.CommandText = "BEGIN";
cmd.ExecuteNonQuery();
// Your SQL Statements and commands can reside here to run with-in a transaction.
// INSERT UPDATE and DELETE
cmd.CommandText = "COMMIT"; //ROLLBACK
cmd.ExecuteNonQuery();
}
}
}
private void SimpleTransaction_Click(object sender, EventArgs e)
{
using (SqlDatabaseConnection cnn = new SqlDatabaseConnection("schemaname=db;uri=file://@memory;"))
{
cnn.Open();
using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn))
{
cmd.CommandText = "Create Table If not exists temptable(Id Integer, TextValue Text) ; ";
cmd.ExecuteNonQuery();
SqlDatabaseTransaction trans = cnn.BeginTransaction();
cmd.Transaction = trans;
try
{
for (int i = 0; i < 1000; i++)
{
cmd.CommandText = "INSERT INTO temptable VALUES (" + i + ", 'AValue" + i + "');";
cmd.ExecuteNonQuery();
}
} catch (SqlDatabaseException sqlex)
{
trans.Rollback();
Debug.WriteLine(sqlex.Message);
}
finally
{
trans.Commit();
}
cmd.CommandText = "SELECT COUNT(*) FROM temptable;";
Debug.WriteLine(cmd.ExecuteScalar());
}
}
}
static void MultipleCommandTransaction()
{
using (SqlDatabaseConnection cnn = new SqlDatabaseConnection("schemaname=db;uri=file://@memory;"))
{
cnn.Open();
// Create a table
using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn))
{
cmd.CommandText = "Create Table If not exists temptable(Id Integer, TextValue Text) ; ";
cmd.ExecuteNonQuery();
cmd.CommandText = "Create Table If not exists temptable2(Id Integer, TextValue Text) ; ";
cmd.ExecuteNonQuery();
}
// Start a transaction on this connection
SqlDatabaseTransaction trans = cnn.BeginTransaction();
try
{
using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn))
{
cmd.Transaction = trans; // attach this Command object to transaction.
for (int i = 0; i < 10; i++)
{
cmd.CommandText = "INSERT INTO temptable VALUES (" + i + ", 'AValue" + i + "');";
cmd.ExecuteNonQuery();
}
}
// Other processes can run here.
// Transaction stays active even after command object is closed and can be attached to other objects.
//Create another command object and insert in temptable2 using same transaction.
using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn))
{
cmd.Transaction = trans; // attach this Command object to transaction.
for (int i = 0; i < 10; i++)
{
cmd.CommandText = "INSERT INTO temptable2 VALUES (" + i + ", 'AValue" + i + "');";
cmd.ExecuteNonQuery();
}
}
trans.Commit();
}
catch (SqlDatabaseException sqlex)
{
trans.Rollback();
Console.WriteLine(sqlex.Message);
}
// Let's check the record count.
using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn))
{
cmd.CommandText = "SELECT COUNT(*) FROM temptable;";
Console.WriteLine("Record Count temptable : {0}", cmd.ExecuteScalar());
cmd.CommandText = "SELECT COUNT(*) FROM temptable2;";
Console.WriteLine("Record Count temptable2 : {0}", cmd.ExecuteScalar());
}
}
}
static void SavePoint()
{
using (SqlDatabaseConnection cnn = new SqlDatabaseConnection("schemaname=db;uri=file://@memory;"))
{
cnn.Open();
SqlDatabaseTransaction trans = cnn.BeginTransaction();
using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn))
{
cmd.Transaction = trans;
cmd.CommandText = "CREATE TABLE SavePointExample (id Integer); ";
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO SavePointExample VALUES (1); ";
cmd.ExecuteNonQuery();
cmd.CommandText = "SAVEPOINT a; ";
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO SavePointExample VALUES (2); ";
cmd.ExecuteNonQuery();
cmd.CommandText = "SAVEPOINT b; ";
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO SavePointExample VALUES (3); ";
cmd.ExecuteNonQuery();
cmd.CommandText = "SAVEPOINT c; ";
cmd.ExecuteNonQuery();
//should return 1, 2, 3 since no rollback or released has occured.
cmd.CommandText = "SELECT * FROM SavePointExample; ";
SqlDatabaseDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
for (int c = 0; c < dr.VisibleFieldCount; c++)
{
Console.Write(dr.GetValue(c) + "\t");
}
Console.WriteLine("");
}
//rollback save point to b without committing transaction. The value 3 and savepoint c will be gone.
cmd.CommandText = "ROLLBACK TO b"; //b
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT * FROM SavePointExample; ";
dr = cmd.ExecuteReader();
while (dr.Read())
{
for (int c = 0; c < dr.VisibleFieldCount; c++)
{
Console.Write(dr.GetValue(c) + "\t");
}
Console.WriteLine(""); // line break.
}
//if we uncomment and release c it wil produce logical error as savepoint c does not exists due to rollback to b.
//cmd.CommandText = "RELEASE c"; //c
//cmd.ExecuteNonQuery();
cmd.CommandText = "RELEASE b;"; //release b means commit the deffered transaction.
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT * FROM SavePointExample; ";
dr = cmd.ExecuteReader();
while (dr.Read())
{
for (int c = 0; c < dr.VisibleFieldCount; c++)
{
Console.Write(dr.GetValue(c) + "\t");
}
Console.WriteLine(""); // line break.
}
//We can still rollback entire transaction
//trans.Rollback();
//commit an entire transaction
trans.Commit();
//If we rollback transaction above regardless of release savepoint (i.e. saving)
//following will produce an error that SavePointExample table not found.
cmd.CommandText = "SELECT * FROM SavePointExample; ";
dr = cmd.ExecuteReader();
while (dr.Read())
{
for (int c = 0; c < dr.VisibleFieldCount; c++)
{
Console.Write(dr.GetValue(c) + "\t");
}
Console.WriteLine(""); // line break.
}
}
}
}
private void ParallelInsertFile_Click(object sender, EventArgs e)
{
DialogResult dlgresult = MessageBox.Show("This example will copy file names from MyDocuments folder, continue ?", "Important Question", MessageBoxButtons.YesNo);
if (dlgresult.ToString() == "No")
{
return;
}
string[] files = System.IO.Directory.GetFiles(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments), "*.*");
string dbfilepath = Path.Combine(Path.GetDirectoryName(Application.ExecutablePath), "files.db");
try
{
if (File.Exists(dbfilepath))
{
File.Delete(dbfilepath);
}
} catch (IOException ioe)
{
MessageBox.Show(ioe.Message);
return;
}
using (SqlDatabaseConnection cnn = new SqlDatabaseConnection("schemaname=db;uri=file://" + dbfilepath + ";"))
{
cnn.Open();
using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn))
{
cmd.CommandText = "CREATE TABLE FileNames (Id Integer primary key autoincrement, InsertDateTime Text, ThreadId Integer, FileName Text); ";
cmd.ExecuteNonQuery();
}
SqlDatabaseTransaction trans = cnn.BeginTransaction();
try
{
Parallel.ForEach(files, (currentFile) =>
{
using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn))
{
cmd.Transaction = trans;
cmd.CommandText = "INSERT INTO FileNames VALUES (null, GetDate(), @ThreadId, @FileName); ";
cmd.Parameters.Add("@ThreadId", Thread.CurrentThread.ManagedThreadId);
cmd.Parameters.Add("@FileName", currentFile);
cmd.ExecuteNonQuery();
}
});
}
catch
{
trans.Rollback();
}
trans.Commit();
//Now try reading first 100 rows by using LIMIT 100..
using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn))
{
cmd.CommandText = "SELECT * FROM FileNames LIMIT 100; ";
SqlDatabaseDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
for (int c = 0; c < dr.VisibleFieldCount; c++)
{
Debug.Write(dr.GetValue(c) + "\t");
}
Debug.WriteLine("");
}
}
}
//Delete the database file since we don't need it.
try
{
if (File.Exists(dbfilepath))
{
File.Delete(dbfilepath);
}
}
catch (IOException ioe)
{
MessageBox.Show(ioe.Message);
return;
}
}
