static void EXECUDFExample()
{
using (SqlDatabaseConnection cnn = new SqlDatabaseConnection("schemaname=db;uri=file://" + ExampleDatabaseFile + "; "))
{
cnn.Open();
// Available from version 2.7
using (SqlDatabaseCommand cmd = new SqlDatabaseCommand())
{
cmd.Connection = cnn;
// SELECT - ExecScalarUDF
// ExecScalarUDF is called once for each row for each call.
// Following will call the event handler twice as ExecScalarUDF is called twice.
DataTable dt = new DataTable();
cmd.CommandText = "SELECT *, ExecScalarUDF('Exec1', Phone) AS ANumber, ExecScalarUDF('Exec2', 1, 2, @Param1, FirstName ) AS ExampleText FROM Customers LIMIT 10;";
cmd.Parameters.AddWithValue("@Param1", 3);
SqlDatabaseDataReader dr = cmd.ExecuteReader();
for (int c = 0; c < dr.VisibleFieldCount; c++)
{
Console.Write(dr.GetName(c) + "\t");
}
Console.WriteLine(Environment.NewLine + "----------------------");
while (dr.Read())
{
for (int c = 0; c < dr.VisibleFieldCount; c++)
{
Console.Write(dr.GetValue(c) + "\t");
}
Console.WriteLine("");
}
;
}
}
}
static void AddImage()
{
// Following example code inserts and retrive files/images from database
Console.WriteLine("Example: How to add images or files in database.");
using (SqlDatabaseConnection cnn = new SqlDatabaseConnection("SchemaName=db;Uri=@memory")) //We will strore in memory for the example
{
cnn.Open();
using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn))
{
cmd.CommandText = "CREATE TABLE Images (Id Integer Primary Key, FileName Text, ActualImage BLOB);";
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO Images VALUES(@Id, @FileName, @Image);";
cmd.Parameters.Add("@Id");
cmd.Parameters.Add("@FileName");
cmd.Parameters.Add("@Image");
//Read the file
if (File.Exists("ImageFile.png"))
{
byte[] byteArray = File.ReadAllBytes("ImageFile.png");
// Assign values to parameters.
cmd.Parameters["@Id"].Value = 1;
cmd.Parameters["@FileName"].Value = "ImageFile.png";
cmd.Parameters["@Image"].Value = byteArray;
cmd.ExecuteNonQuery(); // Execute insert query
Console.WriteLine("Image / File example read..");
cmd.CommandText = "SELECT FileName , ActualImage FROM Images WHERE Id = 1 LIMIT 1;";
SqlDatabaseDataReader dr = cmd.ExecuteReader();
while (dr.HasRows)
{
while (dr.Read())
{
// Create Random file name so we won't overwrite the original actual file.
string NewFileName = Path.GetRandomFileName() + dr["FileName"].ToString();
byte[] FileBytes = (byte[])dr.GetValue(dr.GetOrdinal("ActualImage"));
File.WriteAllBytes(NewFileName, FileBytes);
if (File.Exists(NewFileName))
{
Console.WriteLine("File {0} created successfully.", NewFileName);
}
else
{
Console.WriteLine("Unable to create file {0}.", NewFileName);
}
}
}
}
else
{
Console.WriteLine("File ImageFile.png not found.");
}
}
}
}
private void MultiRead_Click(object sender, EventArgs e)
{
if (string.IsNullOrWhiteSpace(ExampleDatabaseFile))
{
return;
}
//build connection string
cb.Clear(); //clear any existing settings.
cb.Uri = ExampleDatabaseFile;
cb.SchemaName = "db";
using (SqlDatabaseConnection cnn = new SqlDatabaseConnection(cb.ConnectionString))
{
cnn.Open();
Parallel.For(0, Environment.ProcessorCount, new ParallelOptions {
MaxDegreeOfParallelism = Environment.ProcessorCount
}, i => {
try
{
using (SqlDatabaseCommand command = new SqlDatabaseCommand())
{
command.Connection = cnn;
command.CommandText = "SELECT ProductId, ProductName FROM Products ORDER BY ProductId LIMIT 10 OFFSET " + (10 * i) + ";";
SqlDatabaseDataReader rd = command.ExecuteReader();
while (rd.Read())
{
Debug.Write(Thread.CurrentThread.ManagedThreadId + "\t");
for (int c = 0; c < rd.VisibleFieldCount; c++)
{
Debug.Write(rd.GetValue(c) + "\t");
}
Debug.WriteLine("");
}
}
}
catch (Exception ex)
{
Debug.WriteLine(ex.Message);
}
});
}
}
static void EncryptionDecryption()
{
Console.WriteLine("*************** Encrypted File Example *******************");
using (SqlDatabaseConnection cnn = new SqlDatabaseConnection("SchemaName=db;uri=file://Encrypted.db;"))
{
cnn.Open();
using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn))
{
//Entire Database File will be encrypted using AES 256
cmd.CommandText = "SYSCMD Key='SecretPassword';";
cmd.ExecuteNonQuery();
cmd.CommandText = "Create table if not exists Users(id integer primary key autoincrement, Username Text, Password Text); ";
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO Users values(NULL, @username, @password);";
cmd.Parameters.AddWithValue("@username", "sysdba");
cmd.Parameters.AddWithValue("@password", "SecretPassword");
cmd.ExecuteNonQuery();
}
}
using (SqlDatabaseConnection cnn = new SqlDatabaseConnection("SchemaName=db;uri=file://Encrypted.db;"))
{
cnn.Open();
using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn))
{
//Entire Database File will be encrypted using AES 256
cmd.CommandText = "SYSCMD Key = 'SecretPassword'; "; //If incorrect password library will not respond.
cmd.ExecuteNonQuery();
// COLLATE BINARY performs case sensitive search for password
// see http://www.sqldatabase.net/docs/syscmd.aspx for available collation sequences.
cmd.CommandText = "SELECT Id FROM Users WHERE Username = @username AND Password = @password COLLATE BINARY;";
cmd.Parameters.AddWithValue("@username", "sysdba");
cmd.Parameters.AddWithValue("@password", "SecretPassword");
Console.WriteLine("User Found {0}", cmd.ExecuteScalar() == null ? "No" : "Yes");
}
}
Console.Write(string.Empty);
Console.WriteLine("*************** Encrypted Column Example *******************");
string RandomUserName = "******" + System.IO.Path.GetRandomFileName();
using (SqlDatabaseConnection cnn = new SqlDatabaseConnection("SchemaName=db;uri=file://EncryptedColumn.db;"))
{
cnn.Open();
using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn))
{
cmd.CommandText = "CREATE TABLE IF NOT EXISTS UsersCreditCards(Name Text Primary Key, CreditCardNumber Text); ";
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO UsersCreditCards values(@Name, EncryptText(@CreditCardNumber , 'SecretPassword'));";
cmd.Parameters.AddWithValue("@Name", RandomUserName);
cmd.Parameters.AddWithValue("@CreditCardNumber", "1234-5678");
cmd.ExecuteNonQuery();
}
}
using (SqlDatabaseConnection cnn = new SqlDatabaseConnection("SchemaName=db;uri=file://EncryptedColumn.db;"))
{
cnn.Open();
using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn))
{
cmd.CommandText = "SELECT DecryptText(CreditCardNumber , 'SecretPassword') AS [CreditCardNumber] FROM UsersCreditCards WHERE Name = @Name LIMIT 1;";
cmd.Parameters.AddWithValue("@Name", RandomUserName);
Console.WriteLine("User {0} Credit Card Number is : {1}", RandomUserName, cmd.ExecuteScalar());
Console.WriteLine("*************** All Users *******************");
cmd.CommandText = "SELECT Name, DecryptText(CreditCardNumber , 'SecretPassword') AS CreditCardNumber FROM UsersCreditCards;";
SqlDatabaseDataReader dr = cmd.ExecuteReader();
for (int c = 0; c < dr.VisibleFieldCount; c++)
{
Console.Write(dr.GetName(c) + "\t");
}
Console.WriteLine(Environment.NewLine + "----------------------");
while (dr.Read())
{
for (int c = 0; c < dr.VisibleFieldCount; c++)
{
Console.Write(dr.GetValue(c) + "\t");
}
Console.WriteLine("");
}
;
}
}
if (File.Exists("Encrypted.db"))
{
File.Delete("Encrypted.db");
}
if (File.Exists("EncryptedColumn.db"))
{
File.Delete("EncryptedColumn.db");
}
}
static void MultiThreading()
{
using (SqlDatabaseConnection cnn = new SqlDatabaseConnection("schemaname=db;uri=file://@memory;")) // In Memory database.
{
try
{
cnn.Open();
if (cnn.State != ConnectionState.Open)
{
Console.WriteLine("Unable to open connection.");
return;
}
}
catch (SqlDatabaseException e)
{
Console.WriteLine("Error: {0}", e.Message);
return;
}
using (SqlDatabaseCommand cmd = new SqlDatabaseCommand())
{
cmd.Connection = cnn;
cmd.CommandText = "CREATE TABLE IF NOT EXISTS TestTable (ThreadId Integer, Id Integer, RandomText Text, ByteArray Blob);";
cmd.ExecuteNonQuery();
}
Random rnd = new Random();
Parallel.For(0, Environment.ProcessorCount,
new ParallelOptions
{
MaxDegreeOfParallelism = Environment.ProcessorCount
},
i => {
using (SqlDatabaseCommand cmd = new SqlDatabaseCommand())
{
cmd.Connection = cnn;
string RandomPathForText = System.IO.Path.GetRandomFileName();
cmd.CommandText = "INSERT INTO TestTable VALUES (@ThreadId, @Id, @RandomText, @ByteArray);";
if (!cmd.Parameters.Contains("@ThreadId"))
{
cmd.Parameters.AddWithValue("@ThreadId", System.Threading.Thread.CurrentThread.ManagedThreadId);
}
else
{
cmd.Parameters["@ThreadId"].Value = System.Threading.Thread.CurrentThread.ManagedThreadId;
}
if (!cmd.Parameters.Contains("@Id"))
{
cmd.Parameters.AddWithValue("@Id", rnd.Next(1, 100));
}
else
{
cmd.Parameters["@Id"].Value = rnd.Next(1, 100);
}
if (!cmd.Parameters.Contains("@RandomText"))
{
cmd.Parameters.AddWithValue("@RandomText", RandomPathForText);
}
else
{
cmd.Parameters["@RandomText"].Value = RandomPathForText;
}
if (!cmd.Parameters.Contains("@ByteArray"))
{
cmd.Parameters.AddWithValue("@ByteArray", Encoding.UTF8.GetBytes(RandomPathForText));
}
else
{
cmd.Parameters["@ByteArray"].Value = Encoding.UTF8.GetBytes(RandomPathForText);
}
cmd.ExecuteNonQuery();
}
});
using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn))
{
cmd.CommandText = "SELECT * FROM TestTable; ";
cmd.ExecuteNonQuery();
SqlDatabaseDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
for (int c = 0; c < dr.VisibleFieldCount; c++)
{
//Console.Write(Encoding.UTF8.GetString(dr.GetFieldValue<byte[]>(c)) + "\t");
//byte[] byteArray = (byte[])dr.GetValue(c);
if (dr.GetName(c).Equals("ByteArray"))
{
Console.Write(Encoding.UTF8.GetString(dr.GetFieldValue <byte[]>(c)) + "\t");
}
else
{
Console.Write(dr.GetValue(c) + "\t");
}
}
Console.WriteLine("");
}
}
}
}
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 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 CreateDropTable_Click(object sender, EventArgs e)
{
//Create file name
string dbfilepath = Path.Combine(Path.GetDirectoryName(Application.ExecutablePath), "tempdb.db");
//Connection string
string strCon = "schemaname=db;uri=file://" + dbfilepath + ";";
using (SqlDatabaseConnection cnn = new SqlDatabaseConnection(strCon))
{
//Either open the existing database file or create new.
//Other option is DatabaseFileMode.OpenIfExists in which new file is not created.
cnn.DatabaseFileMode = DatabaseFileMode.OpenOrCreate;
//Since we are creating new table, database must be opened in ReadWrite mode.
cnn.DatabaseMode = DatabaseMode.ReadWrite;
try
{
cnn.Open();
} catch (SqlDatabaseException dbe)
{
Debug.WriteLine(dbe.Message);
return;
}
// Check if database connection is open before we create command object to query.
if (cnn.State == ConnectionState.Open)
{
using (SqlDatabaseCommand cmd = new SqlDatabaseCommand())
{
// Assign the connection to this command object.
cmd.Connection = cnn;
cmd.CommandText = "DROP TABLE IF EXISTS ATableName; ";
cmd.ExecuteNonQuery();
StringBuilder sb = new StringBuilder();
sb.AppendLine("CREATE TABLE IF NOT EXISTS ATableName ");
sb.AppendLine(" ( ");
sb.AppendLine(" Id INTEGER PRIMARY KEY AUTOINCREMENT "); // Column with integer data types
sb.AppendLine(" , ProductName TEXT "); // Column with Text data type with no max number of characters same as varchar or string
sb.AppendLine(" , Price REAL "); // double, float or decimal datatype used for money.
sb.AppendLine(" , Picture BLOB "); // BLOB data type for bytes.
sb.AppendLine(" , MoreInfo NONE "); // Not sure and no preference
sb.AppendLine(" ) ");
cmd.CommandText = sb.ToString();
cmd.ExecuteNonQuery();
//SYS_OBJECTS Stores the table , you can verify that table exists or get the original sql from sqltext column.
sb.Clear();
sb.AppendLine("SELECT type [Object Type], crdatetime AS [DateTime Created], tablename [Table Name] FROM SYS_OBJECTS WHERE type = 'table' AND Name = 'ATableName';");
cmd.CommandText = sb.ToString();
SqlDatabaseDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
// Column Names using GetName function
for (int c = 0; c < dr.VisibleFieldCount; c++)
{
Debug.Write(dr.GetName(c).ToString() + "\t");
}
Debug.WriteLine(Environment.NewLine + "-------------------------------------------");
// Row values
for (int c = 0; c < dr.VisibleFieldCount; c++)
{
Debug.Write(dr.GetValue(c).ToString() + "\t");
}
}
// Drop the table
sb.Clear();
sb.Append("DROP TABLE IF EXISTS ATableName ; ");
cmd.CommandText = sb.ToString();
cmd.ExecuteNonQuery();
}
}
}
try
{
if (File.Exists(dbfilepath))
{
File.Delete(dbfilepath);
}
} catch (IOException ex)
{
throw ex;
}
}
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;
}
}