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