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