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; } }