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 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."); } } } }
public int AddOrUpdate(string CollectionName, string Key, object Value) { int RowsAffected = -1; if (!TableExistsOrCreated) { OpenConnectionAndCreateTableIfNotExists(); } if ((string.IsNullOrWhiteSpace(CollectionName)) || (string.IsNullOrWhiteSpace(Key))) { throw new Exception("CollectionName and Key are required."); } byte[] b = new byte[0]; if (Value != null) { if (!(Value is byte[])) { b = ObjectToByteArray(Value); } else { b = (byte[])Value; } } using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn)) { cmd.CommandText = "SELECT RowId FROM KeyValueStore WHERE CollectionName = @CollectionName AND Key = @Key LIMIT 1;"; cmd.Parameters.AddWithValue("@CollectionName", CollectionName); cmd.Parameters.AddWithValue("@Key", Key); cmd.Parameters.AddWithValue("@Value", b); object ObjRowId = cmd.ExecuteScalar(); lock (LockObject) { if (ObjRowId != null) { int Id = int.Parse(ObjRowId.ToString()); cmd.CommandText = "UPDATE KeyValueStore SET Value = @Value WHERE RowId = (SELECT RowId FROM KeyValueStore WHERE CollectionName = @CollectionName AND Key = @Key LIMIT 1);"; RowsAffected = cmd.ExecuteNonQuery(); } else { cmd.CommandText = "INSERT INTO KeyValueStore(CollectionName, Key, Value) VALUES(@CollectionName, @Key, @Value);"; RowsAffected = cmd.ExecuteNonQuery(); } } } return(RowsAffected); }
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(); } } }
public static void AddIndexesToTables(IImportExportFile importExportFile, SqlDatabaseConnection conn) { // get all tables var schemaTables = importExportFile.GetAllTableNames(); foreach (var schemaTable in schemaTables) { // find index var tableIndexes = Indexes?.Where(i => i.TableName.ToLower() == schemaTable.TableName.ToLower()).ToList(); if (tableIndexes == null || tableIndexes?.Count == 0) { return; } // add all found indexes foreach (var index in tableIndexes) { // create index command var indexId = $"{index.TableName}_{Indexes.FindIndex(i => i == index)}"; var querySb = new StringBuilder($"CREATE INDEX IF NOT EXISTS {indexId} ON {schemaTable.TableName} ("); // add the columns foreach (var column in index.IndexColumns) { querySb.Append( $"[{column}],"); } querySb.Length--; querySb.Append(");"); var query = querySb.ToString(); Logger.Debug($"Create index query: {query}"); var cmd = new SqlDatabaseCommand { Connection = conn, CommandText = query }; // execute create index command cmd.ExecuteNonQuery(); // rebuild the index query = $"ReIndex {indexId}"; cmd = new SqlDatabaseCommand { Connection = conn, CommandText = query }; cmd.ExecuteNonQuery(); Logger.Info($"Added index {indexId} to {index.TableName} for columns {JsonConvert.SerializeObject(index.IndexColumns)}"); } } }
/// <summary> /// Execute and return rows affected /// </summary> /// <param name="commandText"></param> private long Execute(string commandText) { using (var cmd = new SqlDatabaseCommand()) { cmd.Connection = Connection; SQLDatabaseResultSet[] reader; cmd.CommandText = commandText; reader = cmd.ExecuteNonQuery(ExtendedResultSet); if (reader != null) { if (string.IsNullOrWhiteSpace(reader[0].ErrorMessage)) { return(reader[0].RowsAffected); } else { throw new Exception(reader[0].ErrorMessage); } } else { return(-1); } } }
public static void DeleteDirectoryFilesFromDb(SqlDatabaseConnection conn, string tableName, string schemaName, IImportExportFactory factory, RootPathObject rootPath, List <string> paths) { try { var tableNames = factory.MakeImportExportFile(conn, rootPath, tableName, schemaName) .GetAllTableNames(); foreach (var table in tableNames) { var tableNameId = $"[{table.SchemaName}].[{table.TableName}]"; Logger.Info($"Purging table: {tableNameId}"); var cmd = new SqlDatabaseCommand { Connection = conn, CommandText = $@"DROP TABLE IF EXISTS {tableNameId}" }; cmd.ExecuteNonQuery(); } } catch (Exception e) { Logger.Error(e, e.Message); Logger.Error(e, "Skipping delete"); } }
public static async Task DeleteRecordAsync(SqlDatabaseConnection conn, ReplicationTable table, string primaryKeyValue) { await conn.OpenAsync(); //TODO: first find record line number and delete it from the replication file var query = string.Format(DeleteRecordQuery, Utility.Utility.GetSafeName(table.SchemaName), Utility.Utility.GetSafeName(table.TableName), Utility.Utility.GetSafeName(table.Columns.Find(c => c.PrimaryKey == true) .ColumnName), primaryKeyValue ); var cmd = new SqlDatabaseCommand { Connection = conn, CommandText = query }; cmd.ExecuteNonQuery(); // check if table exists await cmd.ExecuteNonQueryAsync(); await conn.CloseAsync(); }
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 _dataGridView1_UserDeletingRow(object sender, DataGridViewRowCancelEventArgs e) { var sqlDelete = "DELETE FROM " + table.TableName + " WHERE\n"; var cols = new List <string>(); using (var deleteCmd = new SqlDatabaseCommand(connection)) { foreach (DataColumn c in table.Columns) { var val = e.Row.Cells[c.ColumnName].Value; if (val != DBNull.Value) { cols.Add(string.Format("{0} = @{0}\n", c.ColumnName)); deleteCmd.Parameters.Add("@" + c.ColumnName, val); } else { cols.Add(string.Format("{0} is null\n", c.ColumnName)); } } sqlDelete += string.Join("AND ", cols); deleteCmd.CommandText = sqlDelete; deleteCmd.ExecuteNonQuery(); } }
public static void RestoreTableFromBackup(DataTable backupTable, SqlDatabaseConnection connection, SqlDatabaseTransaction transaction) { var fields = new List <string>(); var parameters = new List <string>(); using (var cmd = new SqlDatabaseCommand(connection)) { foreach (DataRow row in backupTable.Rows) { cmd.Parameters.Clear(); foreach (DataColumn col in row.Table.Columns) { var paramName = "@" + col.ColumnName; fields.Add(col.ColumnName); cmd.Parameters.Add(paramName, row[col]); parameters.Add(paramName); } var insertSql = "INSERT INTO " + backupTable.TableName; insertSql += "(" + string.Join(",", fields) + ")"; insertSql += "\nVALUES\n"; insertSql += "(" + string.Join(",", parameters) + ")"; cmd.CommandText = insertSql; cmd.Transaction = transaction; cmd.ExecuteNonQuery(); } } }
public static void Delete(SqlDatabaseConnection connection, DbSettingItem item) { const string sql = "DELETE FROM CONNECTIONS WHERE Id=@Id"; using (var cmd = new SqlDatabaseCommand(sql, connection)) { cmd.Parameters.Add("@Id", item.Id); cmd.ExecuteNonQuery(); } }
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 static void CreateTableFromDefinition(ISqlDataObject templateTable, SqlDatabaseConnection connection) { var createSql = GetCreateSql(templateTable); if (connection != null) { using (var cmd = new SqlDatabaseCommand(createSql, connection)) { cmd.ExecuteNonQuery(); } } }
private static void CreateMetaDataTable(SqlDatabaseConnection conn) { var cmd = new SqlDatabaseCommand { Connection = conn, CommandText = $@"CREATE TABLE IF NOT EXISTS [{Constants.SchemaName}].[{Constants.ImportMetaDataTableName}] ( {Constants.ImportMetaDataPathColumn} varchar(1024), {Constants.ImportMetaDataLastModifiedDate} datetime );" }; cmd.ExecuteNonQuery(); // Create table }
/// <summary> /// Execute Only with optional return or No return /// </summary> /// <param name="commandText"></param> private long Execute(string commandText, bool returnIdentity = false) { using (var cmd = new SqlDatabaseCommand()) { cmd.Connection = Connection; cmd.CommandText = commandText; try { if (returnIdentity) { cmd.ExecuteNonQuery(); if (!cmd.GetLastError().Equals("SQLDatabase_OK")) { throw new Exception(cmd.GetLastError()); } else { return(cmd.Connection.LastInsertRowId); } } else { cmd.ExecuteNonQuery(); if (!cmd.GetLastError().Equals("SQLDatabase_OK")) { throw new Exception(cmd.GetLastError()); } } } catch (SqlDatabaseException e) { throw e; } } return(0); }
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 toolStripButton1_Click(object sender, EventArgs e) { if (cbConnections.SelectedItem == null) { return; } var conItem = cbConnections.SelectedItem as DbSettingItem; var sql = Sql; if (string.IsNullOrEmpty(sql)) { return; } using (var con = conItem.GetConnection()) { con.Open(); var cmd = new SqlDatabaseCommand(sql, con); try { if (sql.ToUpper().StartsWith("SELECT ")) { dgvResult.Visible = true; lblResult.Visible = false; var dt = new DataTable("Result"); using (var da = new SqlDatabaseDataAdapter(cmd)) { da.Fill(dt); } dgvResult.DataSource = dt; } else { dgvResult.Visible = false; lblResult.Visible = true; var i = cmd.ExecuteNonQuery(); lblResult.Text = string.Format("{0} Rows affected", i); } } catch (Exception ex) { dgvResult.Visible = false; lblResult.Visible = true; lblResult.Text = ex.Message + "\n\nQuery:\n" + sql; } } }
private void CreateSettingsTables(SqlDatabaseConnection con) { const string sql = "CREATE TABLE IF NOT EXISTS CONNECTIONS ( " + "Id TEXT NOT NULL,\n" + "Name TEXT NOT NULL,\n" + "DbFile TEXT NOT NULL,\n" + "Schema TEXT NOT NULL DEFAULT 'dbo',\n" + "Key TEXT\n" + ")"; using (var cmd = new SqlDatabaseCommand(sql, con)) { cmd.ExecuteNonQuery(); } }
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(); } } }
public static void Insert(SqlDatabaseConnection connection, DbSettingItem item) { const string sql = "INSERT INTO CONNECTIONS(Id, Name, DbFile, Key, Schema)\n" + "VALUES \n" + "(@Id, @Name, @DbFile, @Key, @Schema)"; using (var cmd = new SqlDatabaseCommand(sql, connection)) { cmd.Parameters.Add("@Id", item.Id); cmd.Parameters.Add("@Name", item.Name); cmd.Parameters.Add("@DbFile", item.DbFile); cmd.Parameters.Add("@Key", item.Key); cmd.Parameters.Add("@Schema", item.Schema); cmd.ExecuteNonQuery(); } }
public static void Update(SqlDatabaseConnection connection, DbSettingItem item) { const string sql = "UPDATE CONNECTIONS SET \n" + "Name=@Name, DbFile=@DbFile, Key=@Key, Schema=@Schema)\n" + "Where Id=@Id"; using (var cmd = new SqlDatabaseCommand(sql, connection)) { cmd.Parameters.Add("@Id", item.Id); cmd.Parameters.Add("@Name", item.Name); cmd.Parameters.Add("@DbFile", item.DbFile); cmd.Parameters.Add("@Key", item.Key); cmd.Parameters.Add("@Schema", item.Schema); cmd.ExecuteNonQuery(); } }
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 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 CreateTable(string schemaName, string tableName, List <Column> columns) { // setup db table var querySb = new StringBuilder($"CREATE TABLE IF NOT EXISTS [{schemaName}].[{tableName}] ("); var primaryKeySb = new StringBuilder("PRIMARY KEY ("); var hasPrimaryKey = false; foreach (var column in columns) { querySb.Append( $"[{column.ColumnName}] VARCHAR({int.MaxValue}){(column.IsKey ? " NOT NULL" : "")},"); if (column.IsKey) { primaryKeySb.Append($"[{column.ColumnName}],"); hasPrimaryKey = true; } } if (hasPrimaryKey) { primaryKeySb.Length--; primaryKeySb.Append(")"); querySb.Append($"{primaryKeySb});"); } else { querySb.Length--; querySb.Append(");"); } var query = querySb.ToString(); Logger.Debug($"Create table query: {query}"); var cmd = new SqlDatabaseCommand { Connection = _conn, CommandText = query }; cmd.ExecuteNonQuery(); }
public int Delete(string CollectionName, string Key) { if (!TableExistsOrCreated) { OpenConnectionAndCreateTableIfNotExists(); } if ((string.IsNullOrWhiteSpace(CollectionName)) || (string.IsNullOrWhiteSpace(Key))) { throw new Exception("CollectionName and Key are required."); } using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn)) { cmd.CommandText = "DELETE FROM KeyValueStore WHERE RowId = (SELECT RowId FROM KeyValueStore WHERE CollectionName = @CollectionName AND Key = @Key LIMIT 1);"; cmd.Parameters.AddWithValue("@CollectionName", CollectionName); cmd.Parameters.AddWithValue("@Key", Key); return(cmd.ExecuteNonQuery()); } }
/// <summary> /// Add a new trace of podcast view in PodcastStatistic Table's DB /// </summary> /// <param name="podcastID">ID of podcast to trace</param> /// <param name="fileExtension">Extension of file read</param> public void WriteStatisticItem(int podcastID, string fileExtension) { using (SqlDatabaseCommand cmd = this.GetDatabaseCommand()) { cmd.CommandText.AppendLine(" IF EXISTS(SELECT * FROM Podcast WHERE PodcastID = @PodcastID) ") .AppendLine(" INSERT INTO PodcastStatistic (PodcastID, FileType, UserAgent, UserIP, UserCountry) ") .AppendLine(" VALUES (@PodcastID, @FileType, @UserAgent, @UserIP, @UserCountry) "); cmd.Parameters.AddValues(new { PodcastID = podcastID, FileType = fileExtension, UserAgent = HttpContext.Current.Request.UserAgent, UserIP = HttpContext.Current.Request.UserHostAddress, UserCountry = string.Empty // To retrieve later }); cmd.ExecuteNonQuery(); } }
public static async Task DropTableAsync(SqlDatabaseConnection conn, ReplicationTable table) { await conn.OpenAsync(); // TODO: delete file from disk var query = string.Format(DropTableQuery, Utility.Utility.GetSafeName(table.SchemaName), Utility.Utility.GetSafeName(table.TableName) ); var cmd = new SqlDatabaseCommand { Connection = conn, CommandText = query }; cmd.ExecuteNonQuery(); await conn.CloseAsync(); }
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(); } } } }