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 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();
}
}
}
public static void RestoreTableFromBackup(DataTable backupTable, SqlDatabaseConnection connection, SqlDatabaseTransaction transaction)
{
var fields = new List <string>();
var parameters = new List <string>();
using (var cmd = new SqlDatabaseCommand(connection))
{
foreach (DataRow row in backupTable.Rows)
{
cmd.Parameters.Clear();
foreach (DataColumn col in row.Table.Columns)
{
var paramName = "@" + col.ColumnName;
fields.Add(col.ColumnName);
cmd.Parameters.Add(paramName, row[col]);
parameters.Add(paramName);
}
var insertSql = "INSERT INTO " + backupTable.TableName;
insertSql += "(" + string.Join(",", fields) + ")";
insertSql += "\nVALUES\n";
insertSql += "(" + string.Join(",", parameters) + ")";
cmd.CommandText = insertSql;
cmd.Transaction = transaction;
cmd.ExecuteNonQuery();
}
}
}
static void MixedLanguagesUTF8()
{
//Uncomment if your console window does not show all utf8 characters.
//Console.OutputEncoding = Encoding.UTF8;
using (SqlDatabaseConnection cnn = new SqlDatabaseConnection("schemaname=db;uri=file://@memory;"))
{
cnn.Open();
SqlDatabaseTransaction trans = cnn.BeginTransaction();
using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn))
{
cmd.CommandText = "CREATE TABLE Languages (Id Integer Primary Key AutoIncrement, LanguageName Text, LangText Text);";
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO Languages VALUES (null, @Language, @LangText);";
cmd.Parameters.Add(new SqlDatabaseParameter {
ParameterName = "@Language"
});
cmd.Parameters.Add(new SqlDatabaseParameter {
ParameterName = "@LangText"
});
cmd.Parameters["@Language"].Value = "English";
cmd.Parameters["@LangText"].Value = "Hello World";
cmd.ExecuteNonQuery();
//Languages written right to left must use parameters intead of string concatenation of sql text.
cmd.Parameters["@Language"].Value = "Urdu";
cmd.Parameters["@LangText"].Value = "ہیلو ورلڈ";
cmd.ExecuteNonQuery();
cmd.Parameters["@Language"].Value = "Arabic";
cmd.Parameters["@LangText"].Value = "مرحبا بالعالم";
cmd.ExecuteNonQuery();
cmd.Parameters["@Language"].Value = "Chinese Traditional";
cmd.Parameters["@LangText"].Value = "你好，世界";
cmd.ExecuteNonQuery();
cmd.Parameters["@Language"].Value = "Japanese";
cmd.Parameters["@LangText"].Value = "こんにちは世界";
cmd.ExecuteNonQuery();
cmd.Parameters["@Language"].Value = "Russian";
cmd.Parameters["@LangText"].Value = "Привет мир";
cmd.ExecuteNonQuery();
cmd.Parameters["@Language"].Value = "Hindi";
cmd.Parameters["@LangText"].Value = "नमस्ते दुनिया";
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT * FROM Languages; ";
SqlDatabaseDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
for (int c = 0; c < dr.VisibleFieldCount; c++)
{
Console.Write(dr.GetValue(c) + "\t");
}
Console.WriteLine("");
}
Console.WriteLine("---- Search -----");
//Urdu and Arabic should return when searching like ر which is R character in english.
cmd.CommandText = "SELECT * FROM Languages WHERE LangText LIKE @LikeSearch;"; //note no single quotes around @LikeSearch parameter LIKE '%w%'
cmd.Parameters.Add(new SqlDatabaseParameter {
ParameterName = "@LikeSearch", Value = "%ر%"
});
dr = cmd.ExecuteReader();
while (dr.Read())
{
for (int c = 0; c < dr.VisibleFieldCount; c++)
{
Console.Write(dr.GetValue(c) + "\t");
}
Console.WriteLine("");
}
Console.WriteLine("---- Search With OR operator -----");
//Now it should return English, Urdu, Arabic and Russian due to OR operator
cmd.CommandText = "SELECT * FROM Languages WHERE (LangText LIKE '%W%') OR (LangText LIKE @LikeSearch) OR (LangText = @LangText);"; //note no single quotes around @LikeSearch parameter LIKE '%w%'
//Parameters can be cleared using : cmd.Parameters.Clear(); //however we are reusing existing parameter names.
cmd.Parameters["@LikeSearch"].Value = "%ر%"; //since parameter @LikeSearch already exist assign new value.
cmd.Parameters["@LangText"].Value = "Привет мир"; //parameter @LangText already exist in this Command object.
dr = cmd.ExecuteReader();
while (dr.Read())
{
for (int c = 0; c < dr.VisibleFieldCount; c++)
{
Console.Write(dr.GetValue(c) + "\t");
}
Console.WriteLine("");
}
}
}
}
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;
}
}
