/// <summary>
/// Creates a new sql connection
/// </summary>
/// <param name="dbFilePrefix"></param>
/// <param name="onDisk"></param>
/// <param name="dbFolder"></param>
/// <returns>An open sql connection</returns>
public static SqlDatabaseConnection GetSqlConnection(string dbFilePrefix, bool onDisk = false, string dbFolder = null)
{
if (_connection != null)
{
if ((_connection.State & ConnectionState.Open) != 0)
{
return(_connection);
}
}
if (string.IsNullOrWhiteSpace(dbFolder))
{
dbFolder = Constants.DbFolder;
}
Directory.CreateDirectory(dbFolder);
var connBuilder = new SqlDatabaseConnectionStringBuilder
{
DatabaseFileMode = DatabaseFileMode.OpenOrCreate,
DatabaseMode = DatabaseMode.ReadWrite,
SchemaName = Constants.SchemaName,
Uri = onDisk ? $"file://{Path.Join(dbFolder, $"{dbFilePrefix}_{Constants.DbFile}")}" : "file://@memory"
};
_connection = new SqlDatabaseConnection(connBuilder.ConnectionString);
_connection.Open();
return(_connection);
}
private void cbTable_SelectedValueChanged(object sender, EventArgs e)
{
listBox1.DataSource = null;
var table = (cbTable.SelectedItem as ISqlDataObject);
if (table == null)
{
return;
}
if (table.Columns.Count == 0)
{
using (var con = new SqlDatabaseConnection(table.ConnectionString)) {
con.Open();
table.GetColumns(con);
}
}
aviableColumns = new BindingList <SqlDataColumn>(table.Columns.ToList());
listBox1.DataSource = aviableColumns;
listBox1.DisplayMember = "Name";
selectedColumns.Clear();
listBox2.DataSource = selectedColumns;
listBox2.DisplayMember = "Name";
}
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 MetaCollections()
{
using (SqlDatabaseConnection cnn = new SqlDatabaseConnection("uri=@memory"))
{
cnn.Open();
//Available Meta Data Collections in this connection.
// Let's use foreach loop to get all the meta collections
foreach (DataRow r in cnn.GetSchema("METADATACOLLECTIONS").Rows)
{
if (r["NumberOfRestrictions"].Equals(0))
{
Console.WriteLine();
Console.WriteLine("Starting... {0}", r["CollectionName"].ToString());
Console.WriteLine();
//Load another datatable CollectionDT with values
DataTable CollectionDT = cnn.GetSchema(r["CollectionName"].ToString());
foreach (DataColumn column in CollectionDT.Columns)
{
Console.Write(column.ColumnName + "\t");
}
Console.WriteLine(Environment.NewLine);
foreach (DataRow row in CollectionDT.Rows)
{
foreach (DataColumn column in CollectionDT.Columns)
{
Console.Write(row[column.ColumnName] + "\t");
}
Console.WriteLine();
}
}
}
}
}
static void ImportExportCSV()
{
using (SqlDatabaseConnection cnn = new SqlDatabaseConnection("uri=file://" + ExampleDatabaseFile))
{
cnn.DatabaseFileMode = DatabaseFileMode.OpenIfExists;
cnn.Open();
if (File.Exists("Products.csv"))
{
File.Delete("Products.csv");
}
CSVFile.CsvImportExport importExport = new CSVFile.CsvImportExport(cnn, "Products", "");
//Export Example
int rowcount = importExport.ExportTable("Products.csv", false);
Console.WriteLine("Number of Rows Imported : {0}", rowcount);
//Import Example
importExport.TableName = "Products1";
rowcount = importExport.ImportTable("Products.csv", true);
Console.WriteLine("Number of Rows Exported : {0}", rowcount);
cnn.Close();
}
}
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("");
}
;
}
}
}
void OpenConnectionAndCreateTableIfNotExists()
{
if (string.IsNullOrWhiteSpace(DatabaseFile))
{
throw new Exception("DatabaseFile property must be set");
}
if (cnn.State == System.Data.ConnectionState.Closed)
{
cnn.ConnectionString = "SchemaName=db;uri=" + (DatabaseFile == "@memory" ? "@memory;" : "file://" + DatabaseFile + ";");
cnn.Open();
}
if (cnn.State == System.Data.ConnectionState.Open)
{
using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn))
{
cmd.CommandText = "Create Table If Not Exists KeyValueStore(CollectionName Text, Key Text, Value BLOB);";
cmd.CommandText += "Create Index If Not Exists IdxKeyValueStore ON KeyValueStore(CollectionName, Key);";
cmd.ExecuteNonQuery();
TableExistsOrCreated = true;
}
}
else
{
throw new Exception("Unable to open connection");
}
}
private void tsiDropTable_Click(object sender, EventArgs e)
{
var node = tvMain.SelectedNode;
if (node == null || node.Tag == null)
{
return;
}
var newNode = node.PrevVisibleNode;
if (node.Tag.GetType() == typeof(SqlDataTable))
{
var table = node.Tag as SqlDataTable;
if (table == null)
{
return;
}
if (MessageBox.Show("Are you sure to drop table '" + table.Name + "'?", "Confirm", MessageBoxButtons.YesNoCancel, MessageBoxIcon.Question) == DialogResult.Yes)
{
var sql = "DROP TABLE " + table.Name;
using (var con = new SqlDatabaseConnection(table.ConnectionString))
{
con.Open();
new SqlDatabaseCommand(sql, con).ExecuteNonQuery();
con.Close();
tvMain.Nodes.Remove(node);
tvMain.SelectedNode = newNode;
}
}
}
}
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 EnsureColumns()
{
if (sourceTable != null && (sourceTable.Columns == null || sourceTable.Columns.Count == 0))
{
using (var con = new SqlDatabaseConnection(sourceTable.ConnectionString))
{
try
{
con.Open();
sourceTable.GetColumns(con);
}
finally
{
con.Close();
}
}
}
if (this.table.Columns.Count == 0)
{
this.table.BeginInit();
// happens when there is no data in the table
foreach (var c in sourceTable.Columns)
{
var dt = SqlDataColumn.SqlTypeToType(c.Type);
var col = new DataColumn
{
AllowDBNull = c.Nullable,
AutoIncrement = c.AutoInc,
Caption = c.Name,
DataType = dt,
ColumnName = c.Name
// DefaultValue = c.DefaultValue
};
var defaultString = Convert.ToString(c.DefaultValue);
if (defaultString.StartsWith("'") && defaultString.EndsWith("'"))
{
defaultString = defaultString.Substring(1, defaultString.Length - 2);
}
if (!string.IsNullOrEmpty(defaultString))
{
var defVal = Convert.ChangeType(defaultString, dt);
col.DefaultValue = defVal;
}
table.Columns.Add(col);
}
this.table.AcceptChanges();
this.table.EndInit();
}
}
/// <summary>
/// default constructor, opens connection to in memory database using @memory.
/// </summary>
public SqlDatabaseOrmClient()
{
Connection = new SqlDatabaseConnection
{
DatabaseFileMode = DatabaseFileMode.OpenOrCreate,
DatabaseMode = DatabaseMode.ReadWrite,
MultipleActiveResultSets = true,
ConnectionString = "SchemaName=sdbn;uri=@memory"
};
Connection.Open();
_DatabaseObjectT = new T();
}
/// <summary>
/// Pass the schema name and Sqldatabase file path
/// </summary>
/// <param name="SchemaName"></param>
/// <param name="DatabaseFilePath"></param>
public SqlDatabaseOrmClient(string SchemaName, string DatabaseFilePath)
{
Connection = new SqlDatabaseConnection
{
DatabaseFileMode = DatabaseFileMode.OpenOrCreate,
DatabaseMode = DatabaseMode.ReadWrite,
MultipleActiveResultSets = true,
ConnectionString = "SchemaName=" + SchemaName + "; uri=file://" + DatabaseFilePath
};
Connection.Open();
_DatabaseObjectT = new T();
}
static void SIUDOperations()
{
using (SqlDatabaseConnection cnn = new SqlDatabaseConnection("schemaname=db;uri=file://@memory;")) // In Memory database.
{
cnn.Open();
using (SqlDatabaseCommand cmd = new SqlDatabaseCommand())
{
cmd.Connection = cnn;
cmd.CommandText = "CREATE TABLE IF NOT EXISTS TestTable (Username TEXT PRIMARY KEY, FirstName TEXT, LastName TEXT);";
cmd.ExecuteNonQuery();
// INSERT
cmd.CommandText = "INSERT INTO TestTable VALUES ('jdoe', 'John' , 'DOE');";
cmd.ExecuteNonQuery();
// SELECT - Load DataTable
DataTable dt = new DataTable();
cmd.CommandText = "SELECT Username, FirstName, LastName FROM TestTable;";
using (SqlDatabaseDataAdapter da = new SqlDatabaseDataAdapter())
{
da.SelectCommand = cmd;
da.Fill(dt);
}
if (dt.Rows.Count > 0)
{
Console.WriteLine(string.Format("Total Rows {0}", dt.Rows.Count));
}
// UPDATE
cmd.CommandText = "UPDATE TestTable SET LastName = 'Doe' WHERE Username = '******'; ";
cmd.ExecuteNonQuery();
// DELETE
cmd.CommandText = "DELETE FROM TestTable WHERE Username = '******'; ";
cmd.ExecuteNonQuery();
// TRUNCATE - Library does not support truncate but it can be achived by recreating the table
cmd.CommandText = "SELECT sqltext FROM SYS_OBJECTS Where type = 'table' AND tablename = 'TestTable' LIMIT 1;";
object TableSQLText = cmd.ExecuteScalar();
if (!string.IsNullOrWhiteSpace(TableSQLText.ToString()))
{
cmd.CommandText = "DROP TABLE IF EXISTS TestTable;";
cmd.ExecuteNonQuery();
// Now recreate the table....
cmd.CommandText = TableSQLText.ToString();
cmd.ExecuteNonQuery();
}
}
}
}
static void IdentityColumnValues()
{
using (SqlDatabaseConnection sqlcnn = new SqlDatabaseConnection("schemaName=db;uri=@memory"))
{
sqlcnn.Open();
using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(sqlcnn))
{
cmd.CommandText = "CREATE TABLE IF NOT EXISTS TestTable(Id Integer Primary Key AutoIncrement, Name Text);";
cmd.ExecuteNonQuery();
// Id should be one (1) after first insert
cmd.CommandText = "INSERT INTO TestTable VALUES(null, 'Hello');";
cmd.ExecuteNonQuery();
//LastSequenceNumber requires table name
Console.WriteLine(string.Format("via cmd.LastSequenceNumber: {0}", cmd.LastSequenceNumber("TestTable")));
//LastInsertRowId is tracked on connection.
Console.WriteLine(string.Format("via sqlcnn.LastInsertRowId: {0}", sqlcnn.LastInsertRowId));
//last_insert_rowid() is tracked on connection and returns Int64
cmd.CommandText = "SELECT last_insert_rowid()";
Int64 LastID = (Int64)cmd.ExecuteScalar();
Console.WriteLine(string.Format("via SQL: {0}", LastID));
// Id should be two (2) after following insert.
cmd.CommandText = "INSERT INTO TestTable(Name) VALUES('World');";
cmd.ExecuteNonQuery();
//LastSequenceNumber requires table name
Console.WriteLine(string.Format("via cmd.LastSequenceNumber: {0}", cmd.LastSequenceNumber("TestTable")));
//LastInsertRowId is tracked on connection.
Console.WriteLine(string.Format("via sqlcnn.LastInsertRowId: {0}", sqlcnn.LastInsertRowId));
//last_insert_rowid is tracked on connection SQL Statement
cmd.CommandText = "SELECT last_insert_rowid()";
LastID = (Int64)cmd.ExecuteScalar();
Console.WriteLine(string.Format("via SQL: {0}", LastID));
//Attach another database file to same connection
cmd.CommandText = "ATTACH DATABASE '@memory' AS 'db1'";
cmd.ExecuteNonQuery();
// Create table on schema db1
cmd.CommandText = "CREATE TABLE IF NOT EXISTS db1.TestTable(Id Integer Primary Key AutoIncrement, Name Text);";
cmd.ExecuteNonQuery();
// Id should be one (1)
cmd.CommandText = "INSERT INTO db1.TestTable VALUES(null, 'Hello');";
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT last_insert_rowid()";
LastID = (Int64)cmd.ExecuteScalar();
Console.WriteLine(string.Format("via SQL from db1: {0}", LastID));
cmd.CommandText = "SELECT * FROM db1.TestTable WHERE Id = last_insert_rowid()";
SqlDatabaseDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
Console.WriteLine(dr["Name"]);
}
}
}
}
private SqlDatabaseConnection GetSettingsConnection()
{
var file = SettingsFile;
var createTables = !File.Exists(file);
var csb = string.Format("Schema=dbo;DataSource=file://{0}", SettingsFile);
var con = new SqlDatabaseConnection(csb);
con.Open();
CreateSettingsTables(con);
return(con);
}
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 btnCreateTable_Click(object sender, EventArgs e)
{
if (string.IsNullOrEmpty(tbTableName.Text))
{
MessageBox.Show("No name for the new table entered.", "Information", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return;
}
Table.Name = tbTableName.Text;
Table.TableName = Table.Name;
if (!Utils.IsValidName(Table.TableName))
{
MessageBox.Show("Tablename '" + Table.TableName + "' is not valid", "Information", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return;
}
foreach (var col in Table.Columns)
{
if (string.IsNullOrEmpty(col.Name))
{
MessageBox.Show("At least one column has no name.", "Information", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return;
}
if (!Utils.IsValidName(col.Name))
{
MessageBox.Show("Invalid column name '" + col.Name + "'.", "Information", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return;
}
}
using (var con = new SqlDatabaseConnection(ConnectionString))
{
con.Open();
try
{
Utils.CreateTableFromDefinition(Table, con);
this.DialogResult = DialogResult.OK;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
con.Close();
}
}
}
static void DatabaseViews()
{
Console.WriteLine("\nLoading data from SQL based database views....");
Console.WriteLine();
string ExampleDatabaseFile = System.IO.Path.Combine(
System.IO.Directory.GetParent(
System.IO.Path.GetDirectoryName(
AppDomain.CurrentDomain.BaseDirectory)).Parent.Parent.FullName, "Orders.db"
);
if (!System.IO.File.Exists(ExampleDatabaseFile))
{
Console.WriteLine("Unable to find Orders.db exiting..");
return;
}
string constr = "uri=file://" + ExampleDatabaseFile;
using (SqlDatabaseConnection cnn = new SqlDatabaseConnection(constr))
{
cnn.Open();
//Initialize the ORM client
SqlDatabaseOrmClient <vw_Top10CustomersByOrders> Top10CustomersByOrders = new SqlDatabaseOrmClient <vw_Top10CustomersByOrders>(cnn);
foreach (vw_Top10CustomersByOrders o in Top10CustomersByOrders.GetAll())
{
Console.Write(string.Format("FirstName: {0} \t LastName: {1} \t OrderCount: {2} \t OrderTotal: {3}", o.FirstName, o.LastName, o.TotalOrders, o.OrdersTotal));
Console.WriteLine();
}
Console.WriteLine(); //Empty line
Console.WriteLine("Since views are readonly following will produce an error when inserting, updating or deleting from view.");
try
{
vw_Top10CustomersByOrders v10 = new vw_Top10CustomersByOrders();
v10.CustomerId = 10001;
v10.FirstName = "John";
v10.LastName = "Doe";
v10.TotalOrders = 100;
v10.OrdersTotal = "$1";
Top10CustomersByOrders.Add(v10);
} catch (Exception e)
{
Console.WriteLine(e.Message);
}
Console.WriteLine(); //Empty line
}
}
private void btnCreate_Click(object sender, EventArgs e)
{
var table = cbTable.SelectedItem as SqlDataTable;
if (table == null)
{
MessageBox.Show("No table selected.", "Information", MessageBoxButtons.OK);
return;
}
var cols = new List <string>();
foreach (SqlDataColumn col in selectedColumns)
{
cols.Add(col.Name);
}
if (cols.Count == 0)
{
MessageBox.Show("No columns selected.", "Information", MessageBoxButtons.OK);
return;
}
const string Sql = "CREATE {0} INDEX IF NOT EXISTS {1} ON {2} ({3});";
string sql = string.Format(Sql, (Unique ? "UNIQUE" : ""), IndexName, table.Name, string.Join(",", cols));
using (var con = new SqlDatabaseConnection(table.ConnectionString))
{
try
{
con.Open();
using (var cmd = new SqlDatabaseCommand(sql, con))
{
cmd.ExecuteNonQuery();
}
this.DialogResult = DialogResult.OK;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
con.Close();
}
}
}
private void button1_Click(object sender, EventArgs e)
{
BackupData = new DataTable(Table.Name);
for (var i = 0; i < cbColumns.Items.Count; i++)
{
var col = this.Table.Columns.First(o => o == cbColumns.Items[i]);
col.IsPKey = cbColumns.GetItemChecked(i);
}
using (var con = new SqlDatabaseConnection(Table.ConnectionString))
{
con.Open();
using (var cmd = new SqlDatabaseCommand("SELECT * FROM " + Table.Name, con))
{
using (var da = new SqlDatabaseDataAdapter(cmd))
{
da.Fill(BackupData);
}
}
var transaction = con.BeginTransaction();
try
{
using (var cmd = new SqlDatabaseCommand("DROP TABLE " + Table.Name, con))
{
cmd.ExecuteNonQuery();
}
Utils.CreateTableFromDefinition(Table, con);
Utils.RestoreTableFromBackup(BackupData, con, transaction);
transaction.Commit();
this.DialogResult = DialogResult.OK;
}
catch (Exception ex)
{
transaction.Rollback();
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
con.Close();
}
}
}
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);
}
});
}
}
private void ParallelRead_Click(object sender, EventArgs e)
{
if (string.IsNullOrWhiteSpace(ExampleDatabaseFile))
{
return;
}
//build connection
cb.Clear(); //clear any previous settings
cb.Uri = ExampleDatabaseFile;
cb.MultipleActiveResultSets = true;
cb.ExtendedResultSets = false;
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) + ";";
SQLDatabaseResultSet[] cmdrs = command.ExecuteReader(true);
if ((cmdrs != null) && (cmdrs.Length > 0))
{
foreach (SQLDatabaseResultSet rs in cmdrs)
{
Debug.WriteLine("RowCount {0}", rs.RowCount);
}
}
}
}
catch (Exception ex)
{
Debug.WriteLine(ex.Message);
}
});
}
}
private void IndexAndVacuum_Click(object sender, EventArgs e)
{
using (SqlDatabaseConnection cnn = new SqlDatabaseConnection("schemaname=db;uri=file://" + ExampleDatabaseFile))
{
cnn.Open();
//CREATE INDEX IndexName ON TableName (Columns...)
// Also see online documentation
// http://www.sqldatabase.net/docs/create-index.aspx
using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn))
{
cmd.CommandText = "ReIndex ; "; // Rebuild all indexes on all tables.
cmd.ExecuteNonQuery();
}
// After large delete or dropping of large table Vacuum will rearrange space.
using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn))
{
cmd.CommandText = "VACUUM ; "; // Rearrange database pages
cmd.ExecuteNonQuery();
}
// Integrity Check in case something else write to file or any other issues.
// If integrity check is not equals to SQLDATABASE_OK then it can be fixed by rebuilding indexes.
using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn))
{
cmd.CommandText = "SYSCMD Integrity_Check ; ";
if (!cmd.ExecuteScalar().Equals("SQLDATABASE_OK"))
{
cmd.CommandText = "ReIndex ; VACUUM ;";
cmd.ExecuteNonQuery();
}
}
// Not required since dispose also closes the connection
if (cnn.State != ConnectionState.Closed)
{
cnn.Close();
}
}
}
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());
}
}
}
}
public void InitializeData(ISqlDataObject table)
{
try
{
var sql = "SELECT * FROM " + table.Name + " LIMIT " + pageSize + " OFFSET " + start;
var sqlCount = "SELECT COUNT(*) FROM " + table.Name;
this.Text = table.Name;
connection = new SqlDatabaseConnection(table.ConnectionString);
connection.Open();
this.table = new DataTable(table.Name);
command = new SqlDatabaseCommand(sql, connection);
adapter = new SqlDatabaseDataAdapter(command);
adapter.Fill(this.table);
total = Convert.ToInt32(new SqlDatabaseCommand(sqlCount, connection).ExecuteScalar());
sourceTable = table;
hasPrimaryKey = table.Columns.Any(o => o.IsPKey);
btnCreatePKey.Enabled = !hasPrimaryKey;
btnCreatePKey.ToolTipText = hasPrimaryKey ? "Table has existent primary key" : "Recreate Table with primary Key";
btnCreatePKey.Visible = !hasPrimaryKey;
dataGridView1.AllowUserToResizeRows = false;
dataGridView1.DataSource = this.table;
if (table.GetType() == typeof(SqlDataView))
{
dataGridView1.ReadOnly = true;
dataGridView1.AllowUserToAddRows = false;
dataGridView1.AllowUserToDeleteRows = false;
}
UpdateRecordsInfo();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error loading Data", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public CsvImportExport(SqlDatabaseConnection sqlDatabaseConnection, string TableName, string SchemaName)
{
if (string.IsNullOrWhiteSpace(TableName))
{
throw new Exception("TableName parameter is required.");
}
if (sqlDatabaseConnection.State == System.Data.ConnectionState.Closed)
{
sqlDatabaseConnection.Open();
}
if (string.IsNullOrWhiteSpace(SchemaName))
{
SchemaName = "sdbn";
}
SQLDatabaseConnection = sqlDatabaseConnection;
this.TableName = TableName;
this.SchemaName = SchemaName;
}
public static void StartExamples()
{
if (InMemoryConnection.State != System.Data.ConnectionState.Open)
{
InMemoryConnection.Open();
}
LoadDepartments();
LoadEmployees();
EmployeesWithDepartments();
DatabaseViews();
if (InMemoryConnection.State != System.Data.ConnectionState.Open)
{
InMemoryConnection.Close();
}
InMemoryConnection.Dispose();
Console.WriteLine();
}
private void dropIndexToolStripMenuItem_Click(object sender, EventArgs e)
{
var node = tvMain.SelectedNode;
if (node == null || node.Tag == null || node.Tag.GetType() != typeof(SqlDataIndex))
{
return;
}
var idx = node.Tag as SqlDataIndex;
if (idx == null)
{
return;
}
if (MessageBox.Show("Really drop Index '" + idx.Name + "'?", "Confirm", MessageBoxButtons.YesNoCancel, MessageBoxIcon.Question) == DialogResult.Yes)
{
string sql = "DROP INDEX " + idx.Name;
using (var con = new SqlDatabaseConnection(idx.ConnectionString))
{
try
{
con.Open();
using (var cmd = new SqlDatabaseCommand(sql, con))
{
cmd.ExecuteNonQuery();
tvMain.Nodes.Remove(node);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
con.Close();
}
}
}
}
private void dropViewToolStripMenuItem_Click(object sender, EventArgs e)
{
var node = tvMain.SelectedNode;
if (node == null || node.Tag == null || node.Tag.GetType() != typeof(SqlDataView))
{
return;
}
var view = node.Tag as SqlDataView;
if (view == null)
{
return;
}
var newNode = node.PrevVisibleNode;
if (MessageBox.Show("Are you sure to drop view '" + view.Name + "'?", "Confirm", MessageBoxButtons.YesNoCancel, MessageBoxIcon.Question) == DialogResult.Yes)
{
var sql = "DROP VIEW " + view.Name;
using (var con = new SqlDatabaseConnection(view.ConnectionString))
{
try
{
con.Open();
new SqlDatabaseCommand(sql, con).ExecuteNonQuery();
tvMain.Nodes.Remove(node);
tvMain.SelectedNode = newNode;
}
catch
{
throw;
}
finally
{
con.Close();
}
}
}
}
public DelimitedImportExport(SqlDatabaseConnection sqlDatabaseConnection, string tableName, string schemaName, RootPathObject rootPath)
{
if (string.IsNullOrWhiteSpace(tableName))
{
throw new Exception("TableName parameter is required.");
}
if (string.IsNullOrWhiteSpace(schemaName))
{
throw new Exception("SchemaName parameter is required.");
}
if (sqlDatabaseConnection.State == System.Data.ConnectionState.Closed)
{
sqlDatabaseConnection.Open();
}
SQLDatabaseConnection = sqlDatabaseConnection;
TableName = tableName;
SchemaName = schemaName;
Delimiter = rootPath.ModeSettings.DelimitedSettings.GetDelimiter();
}
