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("");
}
;
}
}
}
public Dictionary <string, object> GetAll(string CollectionName)
{
Dictionary <string, object> KeyValuePairs = new Dictionary <string, object>();
if (!TableExistsOrCreated)
{
OpenConnectionAndCreateTableIfNotExists();
}
if (string.IsNullOrWhiteSpace(CollectionName))
{
throw new Exception("CollectionName is required.");
}
using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn))
{
cmd.CommandText = "SELECT Key, Value FROM KeyValueStore WHERE CollectionName = @CollectionName;";
cmd.Parameters.AddWithValue("@CollectionName", CollectionName);
SqlDatabaseDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
byte[] b = (byte[])dr["Value"];
if (b != null && b.Length > 1)
{
KeyValuePairs[dr["Key"].ToString()] = ObjectFromByteArray(b);
}
else
{
KeyValuePairs[dr["Key"].ToString()] = null;
}
}
}
return(KeyValuePairs);
}
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.");
}
}
}
}
/// <summary>
/// Execute and get records as native T type
/// </summary>
/// <param name="commandText"></param>
/// <returns></returns>
private IList <T> ExecuteGet(string commandText)
{
using (var cmd = new SqlDatabaseCommand())
{
cmd.Connection = Connection;
cmd.CommandText = commandText;
SQLDatabaseResultSet[] reader = cmd.ExecuteReader(ExtendedResultSet);
return(new EntityMapper().Map <T>(reader[0]));
}
}
/// <summary>
/// Pass filter to get records in entity format
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <param name="filter"></param>
/// <returns></returns>
private IList <TEntity> ExecuteGet <TEntity>(IFilter <TEntity> filter)
where TEntity : class, new()
{
using (var cmd = new SqlDatabaseCommand())
{
cmd.Connection = Connection;
cmd.CommandText = filter.Query;
SQLDatabaseResultSet[] reader = cmd.ExecuteReader(ExtendedResultSet);
return(new EntityMapper().Map <TEntity>(reader[0]));
}
}
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 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 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);
}
});
}
}
public int ExportTable(string FilePathAndName, bool AppendToFile = false)
{
int _row_count = 0;
using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(SQLDatabaseConnection))
{
if (SQLDatabaseTransaction != null)
{
cmd.Transaction = SQLDatabaseTransaction;
}
cmd.CommandText = string.Format("SELECT * FROM [{0}].[{1}]", SchemaName, TableName);
using (CsvWriter = new CsvFileWriter(FilePathAndName, AppendToFile, Encoding.UTF8))
{
SqlDatabaseDataReader dataReader = cmd.ExecuteReader();
List <string> ColumnNames = new List <string>();
// Write header i.e. column names
for (int i = 0; i < dataReader.VisibleFieldCount; i++)
{
if (dataReader.GetFieldType(i) != Type.GetType("byte[]")) // BLOB will not be written
{
ColumnNames.Add(dataReader.GetName(i)); //maintain columns in the same order as the header line.
CsvWriter.AddField(dataReader.GetName(i));
}
}
CsvWriter.SaveAndCommitLine();
// Write data i.e. rows.
while (dataReader.Read())
{
foreach (string ColumnName in ColumnNames)
{
CsvWriter.AddField(dataReader.GetString(dataReader.GetOrdinal(ColumnName))); //dataReader.GetOrdinal(ColumnName) provides the position.
}
CsvWriter.SaveAndCommitLine();
_row_count++; //Increase row count to track number of rows written.
}
}
}
return(_row_count);
}
private static bool ShouldImportFile(SqlDatabaseConnection conn, string path)
{
var cmd = new SqlDatabaseCommand
{
Connection = conn,
CommandText = $@"SELECT * FROM [{Constants.SchemaName}].[{Constants.ImportMetaDataTableName}]
WHERE {Constants.ImportMetaDataPathColumn} = @path",
Parameters = { new SqlDatabaseParameter
{
ParameterName = "path",
Value = path
} }
};
var reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
var lastModifiedMeta = DateTime.Parse(reader[Constants.ImportMetaDataLastModifiedDate].ToString());
var lastModifiedFile = File.GetLastWriteTimeUtc(path);
if (DateTime.Compare(lastModifiedMeta, lastModifiedFile) < 0)
{
// file is newer than metadata date
return(true);
}
return(false);
}
}
// file not imported before
return(true);
}
public static Count GetCountOfRecords(Schema schema, string dbFilePrefix)
{
var query = schema.Query;
if (string.IsNullOrWhiteSpace(query))
{
query = Utility.Utility.GetDefaultQuery(schema);
}
var conn = Utility.Utility.GetSqlConnection(dbFilePrefix);
var cmd = new SqlDatabaseCommand
{
Connection = conn,
CommandText = $"SELECT COUNT(*) AS count FROM ({query}) AS Q"
};
var reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
return(new Count
{
Kind = Count.Types.Kind.Exact,
Value = reader.GetInt32(0)
});
}
}
return(new Count
{
Kind = Count.Types.Kind.Unavailable
});
}
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.
}
}
}
}
static void LoadDepartments()
{
Console.WriteLine("Loading Departments example..");
Console.WriteLine();
//Initalize ORM client with Departments object
SqlDatabaseOrmClient <Departments> depts = new SqlDatabaseOrmClient <Departments>(InMemoryConnection);
depts.CreateTable();//Create table departments if it does not exists.
// Add department one by one
// We do not need to provide departmentid as it is autogenerated.
Departments dept = new Departments(); //Create new instance of Departments class
dept.DepartmentName = "Administration";
depts.Add(dept);
dept = new Departments
{
DepartmentName = "Sales & Marketing"
};
depts.Add(dept);
//Following will produce error due to DepartmentName may not be null.
try
{
dept = new Departments
{
DepartmentName = null
};
depts.Add(dept);
}
catch (Exception e)
{
Console.WriteLine("Error due to null: {0}", e.Message);
Console.WriteLine();
}
// End of Add department one by one
// Adding multiple departments using list
List <Departments> departmentslist = new List <Departments>();
departmentslist.Add(new Departments()
{
DepartmentName = "HR"
});
departmentslist.Add(new Departments()
{
DepartmentName = "Information Technology"
});
depts.AddRange(departmentslist);
// Get all records.
Console.WriteLine("Example to get all records for departments..");
foreach (Departments d in depts.GetAll())
{
Console.Write(string.Format("Id: {0} \t Name: {1}", d.DepartmentId, d.DepartmentName));
Console.WriteLine();
}
Console.WriteLine(); //Empty line
// Optional for testing, get records using sql
Console.WriteLine("Example to fetch records using SQL statement....");
using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(InMemoryConnection))
{
cmd.CommandText = "SELECT * FROM Departments; ";
SqlDatabaseDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
Console.Write(string.Format("Id: {0} \t Name: {1}", dr["DepartmentId"], dr["DepartmentName"]));
Console.WriteLine();
}
}
Console.WriteLine(); //Empty line
//Updating existing records
Departments DeptToUpdate = new Departments
{
DepartmentId = 4,
DepartmentName = "IT"
};
depts.Update(DeptToUpdate);
//Filtering records
SqlDatabaseOrmClient <Departments> .Filter <Departments> DeptFilter = new SqlDatabaseOrmClient <Departments> .Filter <Departments>();
DeptFilter.WhereWithOR(item => item.DepartmentName == "HR");
DeptFilter.WhereWithOR(item => item.DepartmentId == 4);
DeptFilter.LimitAndOffSet(2, 0);
DeptFilter.OrderByDescending(item => item.DepartmentId);
// Get records based on filter.
Console.WriteLine("Filtered Records example....");
foreach (Departments d in depts.Find(DeptFilter))
{
Console.Write(string.Format("Id: {0} \t Name: {1}", d.DepartmentId, d.DepartmentName));
Console.WriteLine();
}
//Drop table
//depts.DropTable(new Departments());
}
public long ExportTable(string filePathAndName, bool appendToFile = false)
{
SQLDatabaseConnection.Open();
long rowCount = 0;
using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(SQLDatabaseConnection))
{
if (SQLDatabaseTransaction != null)
{
cmd.Transaction = SQLDatabaseTransaction;
}
cmd.CommandText = $@"SELECT * FROM [{SchemaName}].[{TableName}]";
using (DelimitedWriter = new DelimitedFileWriter(filePathAndName, appendToFile, Encoding.UTF8))
{
// set variables
DelimitedWriter.Delimiter = Delimiter;
DelimitedWriter.QuoteWrap = ReplicationFormData.QuoteWrap;
DelimitedWriter.NullValue = ReplicationFormData.NullValue;
// write custom header to file if not empty
if (!string.IsNullOrWhiteSpace(ReplicationFormData.CustomHeader))
{
DelimitedWriter.WriteLineToFile(ReplicationFormData.CustomHeader);
}
SqlDatabaseDataReader dataReader = cmd.ExecuteReader();
List <string> columnNames = new List <string>();
// Write header i.e. column names
for (int i = 0; i < dataReader.VisibleFieldCount; i++)
{
var name = dataReader.GetName(i);
if (dataReader.GetFieldType(i) != Type.GetType("byte[]") &&
name != Constants.ReplicationRecordId &&
name != Constants.ReplicationVersionIds &&
name != Constants.ReplicationVersionRecordId) // BLOB will not be written
{
columnNames.Add(name); //maintain columns in the same order as the header line.
DelimitedWriter.AddField(name);
}
}
DelimitedWriter.SaveAndCommitLine();
// Write data i.e. rows.
while (dataReader.Read())
{
foreach (string columnName in columnNames)
{
DelimitedWriter.AddField(
dataReader.GetString(
dataReader.GetOrdinal(
columnName))); //dataReader.GetOrdinal(ColumnName) provides the position.
}
DelimitedWriter.SaveAndCommitLine();
rowCount++; //Increase row count to track number of rows written.
}
}
}
return(rowCount);
}
static void TrackDataChanges()
{
//Change tracking allow users to track data changes in user defined tables.
// Tracking is done for all schema's and all user tables for INSERT, UPDATE and DELETE queries
using (SqlDatabaseConnection cnn = new SqlDatabaseConnection())
{
cnn.ConnectionString = "SchemaName=db;uri=file://" + ExampleDatabaseFile + ";";
//Enable Tracking
cnn.TrackDataChanges = true;
//Set buffer size, default is to track last 1000 changes.
// for this example we will change it to 10
cnn.TrackedChangesMaxCount = 10;
cnn.Open();
using (SqlDatabaseCommand cmd = new SqlDatabaseCommand())
{
cmd.Connection = cnn;
cmd.CommandText = "CREATE TABLE IF NOT EXISTS UsersTestTable (Username TEXT PRIMARY KEY, FirstName TEXT, LastName TEXT);";
cmd.ExecuteNonQuery();
// INSERT
cmd.CommandText = "INSERT INTO UsersTestTable VALUES ('johndoe', 'John' , 'DOE');";
cmd.CommandText += "INSERT INTO UsersTestTable VALUES ('janedoe', 'Jane' , 'DOE');";
cmd.ExecuteNonQuery();
// UPDATE
cmd.CommandText = "UPDATE UsersTestTable SET LastName = 'Doe' WHERE Username = '******'; ";
cmd.ExecuteNonQuery();
// DELETE - The actual row is not recoverable after the delete
// Only RowId is stored without deleted data.
cmd.CommandText = "DELETE FROM UsersTestTable WHERE Username = '******'; ";
cmd.ExecuteNonQuery();
// INSERT again to show new RowId has been generated
cmd.CommandText = "INSERT INTO UsersTestTable VALUES ('johndoe', 'John' , 'DOE');";
cmd.ExecuteNonQuery();
// To view changes we will call GetTrackedDataChanges() function which will yeild
foreach (SqlDatabaseDataChanges tdc in cnn.GetTrackedDataChanges())
{
string DMLType = string.Empty;
switch (tdc.ChangeType)
{
case 1:
DMLType = "INSERT";
break;
case 2:
DMLType = "UPDATE";
break;
case 3:
DMLType = "DELETE";
break;
default:
DMLType = "UNKNOWN";
break;
}
Console.WriteLine("ChangeType {0} \t SchemaName: {1} \t TableName: {2} \t RowId: {3} \t DateTime: {4}"
, DMLType
, tdc.SchemaName
, tdc.TableName
, tdc.RowId
, new DateTime(tdc.NowTicks));
}
// To view changed row simply query using SELECT with where clause
// By default each row is given unique RowId
// Using Linq with changeType = 2 for updated rows only.
Int64 RowId = cnn.GetTrackedDataChanges().Where(item => item.ChangeType == 2).FirstOrDefault().RowId;
cnn.MultipleActiveResultSets = true;
cmd.CommandText = "SELECT * FROM UsersTestTable WHERE RowId = " + RowId;
SQLDatabaseResultSet[] rs = cmd.ExecuteReader(false);
if (rs != null)
{
if (!string.IsNullOrWhiteSpace(rs[0].ErrorMessage))
{
Console.WriteLine(rs[0].ErrorMessage);
return;
}
foreach (object[] row in rs[0].Rows)
{
foreach (object column in row)
{
Console.WriteLine(column);
}
}
}
}
}
}
/// <summary>
/// Reads records for schema
/// </summary>
/// <param name="context"></param>
/// <param name="schema"></param>
/// <param name="dbFilePrefix"></param>
/// <returns>Records from the file</returns>
public static IEnumerable <Record> ReadRecords(ServerCallContext context, Schema schema, string dbFilePrefix)
{
var query = schema.Query;
if (string.IsNullOrWhiteSpace(query))
{
query = Utility.Utility.GetDefaultQuery(schema);
}
var conn = Utility.Utility.GetSqlConnection(dbFilePrefix);
var cmd = new SqlDatabaseCommand
{
Connection = conn,
CommandText = query
};
SqlDatabaseDataReader reader;
try
{
Logger.Info($"Executing query");
Logger.Debug(query);
reader = cmd.ExecuteReader();
}
catch (Exception e)
{
Logger.Error(e, $"Failed to execute query");
Logger.Debug(query);
Logger.Error(e, e.Message, context);
throw;
}
Logger.Info("Executed query successfully");
if (reader.HasRows)
{
Logger.Info("Results set has rows. Reading...");
while (reader.Read())
{
var recordMap = new Dictionary <string, object>();
foreach (var property in schema.Properties)
{
try
{
switch (property.Type)
{
case PropertyType.String:
recordMap[property.Id] = reader[property.Id].ToString();
break;
default:
recordMap[property.Id] = reader[property.Id];
break;
}
}
catch (Exception e)
{
Logger.Debug($"No column with property Id: {property.Id}\n{e.Message}\n{e.StackTrace}");
recordMap[property.Id] = "";
}
}
var record = new Record
{
Action = Record.Types.Action.Upsert,
DataJson = JsonConvert.SerializeObject(recordMap)
};
yield return(record);
}
}
}
static void MarsEnabled()
{
// MARS (MultipleActiveResultSets) can decrease read time when there are multiple queries
// It results in better performance since queries can be combined.
// Very useful for large forms and web pages which require data from multiple tables.
// Instead of running each query and processing results get all results at once.
if (string.IsNullOrWhiteSpace(ExampleDatabaseFile))
{
return;
}
//build connection string
cb.Clear(); //clear any previous settings
cb.Uri = "file://" + ExampleDatabaseFile; //Set the database file
cb.MultipleActiveResultSets = true; //We need multiple result sets
cb.ExtendedResultSets = false; //extended result set is false but can be set during command execution e.g. command.ExecuteReader(true)
cb.SchemaName = "db"; //schema name
//"SchemaName=db;uri=file://" + ExampleDatabaseFile + ";MultipleActiveResultSets=true;"
using (SqlDatabaseConnection cnn = new SqlDatabaseConnection(cb.ConnectionString))
{
cnn.Open();
try
{
using (SqlDatabaseCommand command = new SqlDatabaseCommand())
{
command.Connection = cnn;
//execute two queries against two different tables.
//command.CommandText = "SELECT ProductId, ProductName FROM Products; SELECT CustomerId, LastName || ' , ' || FirstName FROM Customers LIMIT 10;";
// For easy to read above command can also be written as following:
command.CommandText = "SELECT ProductId, ProductName FROM Products LIMIT 10 OFFSET 10 ; "; // Query index 0
command.CommandText += "SELECT CustomerId, LastName || ',' || FirstName FROM Customers LIMIT 10 ; "; //Query index 1
SQLDatabaseResultSet[] cmdrs = command.ExecuteReader(false);// parameter bool type is for ExtendedResultSet
if ((cmdrs != null) && (cmdrs.Length > 0))
{
if (!string.IsNullOrWhiteSpace(cmdrs[0].ErrorMessage))
{
Console.WriteLine(cmdrs[0].ErrorMessage);
return;
}
//this loop is just an example how to loop through all rows and columns.
for (int r = 0; r < cmdrs[0].RowCount; r++) //loop through each row of result set query index zero ( 0 )
{
for (int c = 0; c < cmdrs[0].ColumnCount; c++)
{
//Console.WriteLine(cmdrs[0].Rows[r][c]);
}
}
//Loading data from products table which is at index 0 : cmdrs[0]
for (int r = 0; r < cmdrs[0].RowCount; r++) //loop through each row of result set index zero ( 0 ) which is products table
{
//cmdrs[0].Rows[r][0] : in Rows[r][0] r = row and [0] is column index.
Console.WriteLine(string.Format("{0} - {1}", cmdrs[0].Rows[r][0], cmdrs[0].Rows[r][1]));
}
//Loading data from customers table which is at index 1 cmdrs[1]
for (int r = 0; r < cmdrs[1].RowCount; r++) //loop through each row of result set index one ( 1 ) which is customers table
{
//cmdrs[0].Rows[r][0] : Rows[r][0] r = row and [0] is column index.
Console.WriteLine(string.Format("{0} - {1}", cmdrs[1].Rows[r][0], cmdrs[1].Rows[r][1]));
}
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
}
static void ExtendedResultSet()
{
// Old fashioned connection string example
string ConnectionString = "SchemaName = db;";
ConnectionString += "MultipleActiveResultSets = true;";
ConnectionString += "ExtendedResultSets = true;";
ConnectionString += "Mode = readwrite;";
ConnectionString += "FileMode = OpenIfExists;";
ConnectionString += "uri = file://" + ExampleDatabaseFile + ";";
//ConnectionString += "DatabaseJournalDirectory = " + Path.GetDirectoryName(ExampleDatabaseFile) + ";";
using (SqlDatabaseConnection cnn = new SqlDatabaseConnection(ConnectionString))
{
cnn.Open();
try
{
using (SqlDatabaseCommand cmd = new SqlDatabaseCommand())
{
cmd.Connection = cnn;
////execute two queries against two tables.
////query can have parameters they are not declared here since code is commented.
//command.CommandText = "UPDATE Suppliers SET CompanyName = CompanyName Where SupplierId = 1;SELECT ProductId, ProductName FROM Products; SELECT CustomerId, LastName || ' , ' || FirstName FROM Customers LIMIT 10;";
// For easy to read above command can also be written as following:
cmd.CommandText = "UPDATE db.Suppliers SET CompanyName = CompanyName Where SupplierId = 1 ; "; //First Query will be at index 0
cmd.CommandText += "SELECT ProductId [Product Id], ProductName FROM db.Products LIMIT 10 OFFSET @Limit ; "; //Second Query will be at index 1
cmd.CommandText += "SELECT CustomerId, LastName || ',' || FirstName FROM Customers LIMIT @Limit ; "; //Third Query will be at index 2
cmd.Parameters.AddWithValue("@Limit", 10);
//When SQLDatabaseResultSet is needed a boolean type must be passed to command execution object.
SQLDatabaseResultSet[] cmdrs = cmd.ExecuteReader(true);// true for ExtendedResultSet
if ((cmdrs != null) && (cmdrs.Length > 0))
{
foreach (SQLDatabaseResultSet rs in cmdrs)
{
Console.WriteLine("---------------------------------\n" + rs.SQLText);
Console.WriteLine("Execution time in Milliseconds: {0} ", rs.ProcessingTime);
Console.WriteLine("Rows Affected: {0}", rs.RowsAffected); //RowsAffected is non zero for update or delete only.
if (string.IsNullOrWhiteSpace(rs.ErrorMessage))
{
Console.WriteLine("No error");
}
else
{
Console.WriteLine(rs.ErrorMessage);
}
//All the schemas in the query
foreach (object schema in rs.Schemas)
{
Console.WriteLine("Schema Name: {0} ", schema);
}
//All the tables in the query
foreach (object table in rs.Tables)
{
Console.WriteLine("Table Name: {0} ", table);
}
//parameters if any
foreach (object Parameter in rs.Parameters)
{
Console.WriteLine("Parameter Name: {0} ", Parameter);
}
//data type for returned column, datatype is what is defined during create table statement.
foreach (string datatype in rs.DataTypes)
{
Console.Write(datatype + "\t");
}
Console.WriteLine(""); //add empty line to make it easy to read
//Column names or aliases
foreach (string ColumnName in rs.Columns)
{
Console.Write(ColumnName + "\t");
}
Console.WriteLine("");//add empty line to make it easy to read
//all columns and rows.
foreach (object[] row in rs.Rows)
{
foreach (object column in row)
{
Console.Write(column + "\t"); // \t will add tab
}
Console.WriteLine(""); //break line for each new row.
}
}
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
}
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 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");
}
}
private void MarsEnabled_Click(object sender, EventArgs e)
{
// MARS (MultipleActiveResultSets) can decrease read time when there are multiple queries
// It results in better performance since queries can be combined.
// Very useful for large forms and web pages which require data from multiple tables.
// Instead of running each query and processing results get all results at once.
if (string.IsNullOrWhiteSpace(ExampleDatabaseFile))
{
return;
}
comboBox1.Items.Clear();
comboBox2.Items.Clear();
//build connection string
cb.Clear(); //clear any previous settings
cb.Uri = ExampleDatabaseFile; //Set the database file
cb.MultipleActiveResultSets = true; //We need multiple result sets
cb.ExtendedResultSets = false; //extended result set is false but can be set during command execution e.g. command.ExecuteReader(true)
cb.SchemaName = "db"; //schema name
using (SqlDatabaseConnection cnn = new SqlDatabaseConnection(cb.ConnectionString))
{
cnn.Open();
try
{
using (SqlDatabaseCommand command = new SqlDatabaseCommand())
{
command.Connection = cnn;
//execute two queries against two different tables.
//command.CommandText = "SELECT ProductId, ProductName FROM Products; SELECT CustomerId, LastName || ' , ' || FirstName FROM Customers LIMIT 10;";
// For easy to read above command can also be written as following:
command.CommandText = "SELECT ProductId, ProductName FROM Products ; ";
command.CommandText += "SELECT CustomerId, LastName || ',' || FirstName FROM Customers LIMIT 10 ; ";
SQLDatabaseResultSet[] cmdrs = command.ExecuteReader(false);// parameter bool type is for ExtendedResultSet
if ((cmdrs != null) && (cmdrs.Length > 0))
{
//this loop is just an example how to loop through all rows and columns.
for (int r = 0; r < cmdrs[0].RowCount; r++) //loop through each row of result set index zero ( 0 )
{
for (int c = 0; c < cmdrs[0].ColumnCount; c++)
{
Debug.WriteLine(cmdrs[0].Rows[r][c].ToString());
}
}
//Loading data from products table which is at index 0 : cmdrs[0]
for (int r = 0; r < cmdrs[0].RowCount; r++) //loop through each row of result set index zero ( 0 ) which is products table
{
//cmdrs[0].Rows[r][0] : in Rows[r][0] r = row and [0] is column index.
comboBox1.Items.Add(cmdrs[0].Rows[r][0].ToString() + " - " + cmdrs[0].Rows[r][1].ToString());
}
//Loading data from customers table which is at index 1 cmdrs[1]
for (int r = 0; r < cmdrs[1].RowCount; r++) //loop through each row of result set index zero ( 0 ) which is customers table
{
//cmdrs[0].Rows[r][0] : Rows[r][0] r = row and [0] is column index.
comboBox2.Items.Add(cmdrs[1].Rows[r][0].ToString() + " - " + cmdrs[1].Rows[r][1].ToString());
}
}
}
}
catch (Exception ex)
{
Debug.WriteLine(ex.Message);
}
//combobox1 will close when combobox2 is opened.
comboBox1.Focus();
comboBox1.DroppedDown = true;
//combobox2 open will close combobox1 by behaviour.
comboBox2.Focus();
comboBox2.DroppedDown = true;
}
}
private List <ISqlDataObject> GetObjects(string type)
{
var tableList = new List <ISqlDataObject>();
string sql = string.Format("SELECT * FROM SYS_OBJECTS WHERE type = '{0}';", type);
try
{
using (SqlDatabaseConnection con = new SqlDatabaseConnection(ConnectionString))
{
try
{
con.Open();
}
catch
{
return(tableList);
}
using (var cmd = new SqlDatabaseCommand(sql, con))
{
var rdr = cmd.ExecuteReader();
while (rdr.Read())
{
if (type == "table")
{
var table = new SqlDataTable
{
TableName = rdr.GetString(3),
Name = rdr.GetString(2),
ConnectionString = con.ConnectionString
};
if (table.Name != "sys_sequences")
{
tableList.Add(table);
}
}
else if (type == "view")
{
var view = new SqlDataView
{
TableName = rdr.GetString(3),
Name = rdr.GetString(2),
ConnectionString = con.ConnectionString
};
view.GetColumns(con);
tableList.Add(view);
}
else if (type == "index")
{
var index = new SqlDataIndex
{
TableName = rdr.GetString(3),
Name = rdr.GetString(2),
ConnectionString = con.ConnectionString
};
index.GetColumns(con);
tableList.Add(index);
}
}
}
}
}
catch (Exception ex)
{
SqlDatabaseConnection con = new SqlDatabaseConnection(ConnectionString);
MessageBox.Show("Error reading data from " + con.Database);
}
return(tableList);
}
private void ExtendedResults_Click(object sender, EventArgs e)
{
// Extended result set returns more information about query.
// Processing time is in milliseconds.
if (string.IsNullOrWhiteSpace(ExampleDatabaseFile))
{
return;
}
comboBox1.Items.Clear();
comboBox2.Items.Clear();
//build connection
cb.Clear(); //clear any previous settings
cb.Uri = ExampleDatabaseFile;
cb.MultipleActiveResultSets = true;
cb.ExtendedResultSets = true;
cb.SchemaName = "db";
using (SqlDatabaseConnection cnn = new SqlDatabaseConnection(cb.ConnectionString))
{
cnn.Open();
try
{
using (SqlDatabaseCommand command = new SqlDatabaseCommand())
{
command.Connection = cnn;
////execute two queries against two tables.
////query can have parameters they are not declared here since code is commented.
//command.CommandText = "UPDATE Suppliers SET CompanyName = CompanyName Where SupplierId = 1;SELECT ProductId, ProductName FROM Products; SELECT CustomerId, LastName || ' , ' || FirstName FROM Customers LIMIT 10;";
// For easy to read above command can also be written as following:
command.CommandText = "UPDATE Suppliers SET CompanyName = CompanyName Where SupplierId = 1 ; "; //First Query will be at index 0
command.CommandText += "SELECT ProductId [Product Id], ProductName FROM db.Products LIMIT 10 OFFSET @Limit ; "; //Second Query will be at index 1
command.CommandText += "SELECT CustomerId, LastName || ',' || FirstName FROM Customers LIMIT @Limit ; "; //Third Query will be at index 2
command.Parameters.Add(new SqlDatabaseParameter {
ParameterName = "@Limit", Value = 10
});
//When SQLDatabaseResultSet is needed a boolean type must be passed to command execution object.
SQLDatabaseResultSet[] cmdrs = command.ExecuteReader(true);// parameter bool type is for ExtendedResultSet
if ((cmdrs != null) && (cmdrs.Length > 0))
{
foreach (SQLDatabaseResultSet rs in cmdrs)
{
Debug.WriteLine("---------------------------------\n" + rs.SQLText);
Debug.WriteLine("Execution time in Milliseconds: {0} ", rs.ProcessingTime);
Debug.WriteLine("Rows Affected: {0}", rs.RowsAffected); //RowsAffected is non zero for update or delete only.
if (string.IsNullOrWhiteSpace(rs.ErrorMessage))
{
Debug.WriteLine("No error");
}
else
{
Debug.WriteLine(rs.ErrorMessage);
}
//All the schemas in the query
foreach (object schema in rs.Schemas)
{
Debug.WriteLine("Schema Name: {0} ", schema);
}
//All the tables in the query
foreach (object table in rs.Tables)
{
Debug.WriteLine("Table Name: {0} ", table);
}
//parameters if any
foreach (object Parameter in rs.Parameters)
{
Debug.WriteLine("Parameter Name: {0} ", Parameter);
}
//data type for returned column, datatype is what is defined during create table statement.
foreach (string datatype in rs.DataTypes)
{
Debug.Write(datatype + "\t");
}
Debug.WriteLine(""); //add empty line to make it easy to read
//Column names or aliases
foreach (string ColumnName in rs.Columns)
{
Debug.Write(ColumnName + "\t");
}
Debug.WriteLine("");//add empty line to make it easy to read
//all columns and rows.
foreach (object[] row in rs.Rows)
{
foreach (object column in row)
{
Debug.Write(column + "\t"); // \t will add tab
}
Debug.WriteLine(""); //break line for each new row.
}
}
}
}
}
catch (Exception ex)
{
Debug.WriteLine(ex.Message);
}
}
}
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;
}
}
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("");
}
}
}
}
public static Schema GetSchemaForQuery(ServerCallContext context, Schema schema, int sampleSize = 5, List <Column> columns = null)
{
try
{
Logger.Debug(JsonConvert.SerializeObject(schema, Formatting.Indented));
var query = schema.Query;
if (schema.DataFlowDirection == Schema.Types.DataFlowDirection.Write)
{
Logger.Info("Returning Write schema unchanged");
return(schema);
}
if (string.IsNullOrWhiteSpace(query))
{
if (!string.IsNullOrWhiteSpace(schema.PublisherMetaJson))
{
JsonConvert.DeserializeObject <ConfigureWriteFormData>(schema.PublisherMetaJson);
Logger.Info("Returning Write schema with direction changed");
schema.DataFlowDirection = Schema.Types.DataFlowDirection.Write;
return(schema);
}
query = Utility.Utility.GetDefaultQuery(schema);
}
var conn = Utility.Utility.GetSqlConnection(Constants.DiscoverDbPrefix);
var cmd = new SqlDatabaseCommand
{
Connection = conn,
CommandText = query
};
var reader = cmd.ExecuteReader();
var schemaTable = reader.GetSchemaTable();
var properties = new List <Property>();
if (schemaTable != null)
{
var unnamedColIndex = 0;
// get each column and create a property for the column
foreach (DataRow row in schemaTable.Rows)
{
// get the column name
var colName = row["ColumnName"].ToString();
if (string.IsNullOrWhiteSpace(colName))
{
colName = $"UNKNOWN_{unnamedColIndex}";
unnamedColIndex++;
}
// create property
Property property;
if (columns == null)
{
property = new Property
{
Id = colName,
Name = colName,
Description = "",
Type = GetPropertyType(row),
TypeAtSource = row["DataType"].ToString(),
IsKey = Boolean.Parse(row["IsKey"].ToString()),
IsNullable = Boolean.Parse(row["AllowDBNull"].ToString()),
IsCreateCounter = false,
IsUpdateCounter = false,
PublisherMetaJson = ""
};
}
else
{
var column = columns.FirstOrDefault(c => c.ColumnName == colName);
property = new Property
{
Id = colName,
Name = colName,
Description = "",
Type = GetPropertyType(row),
TypeAtSource = row["DataType"].ToString(),
IsKey = column?.IsKey ?? Boolean.Parse(row["IsKey"].ToString()),
IsNullable = !column?.IsKey ?? Boolean.Parse(row["AllowDBNull"].ToString()),
IsCreateCounter = false,
IsUpdateCounter = false,
PublisherMetaJson = ""
};
}
// add property to properties
properties.Add(property);
}
}
// add only discovered properties to schema
schema.Properties.Clear();
schema.Properties.AddRange(properties);
try
{
var records = Read.Read.ReadRecords(context, schema, Constants.DiscoverDbPrefix).Take(sampleSize);
schema.Sample.AddRange(records);
}
catch
{
Logger.Info("Could not add records");
}
// purge publisher meta json
schema.PublisherMetaJson = "";
return(schema);
}
catch (Exception e)
{
// return schema that existed before but files may not currently exist
if (schema.Properties.Count > 0)
{
return(schema);
}
Logger.Error(e, e.Message);
throw;
}
}