public static void CreateView(this SQLiteGateWay SQLiteGateWay, string ViewName, string sql, bool replace = false)
{
if (SQLiteGateWay.ViewExists(ViewName))
{
if (!replace)
{
return;
}
SQLiteGateWay.DropView(ViewName);
}
SQLiteGateWay.Update($"create view {ViewName} as {sql}");
}
public static int InsertExcelWorksheetIntoTable(this SQLiteGateWay SQLiteGateWay, ExcelWorksheet ws, string TableName, Func <ExcelRangeBase, string> Format, bool CreateColumns = false)
{
var RowsAdded = 0;
var WantedColumns = ws.GetColumns();
SQLiteGateWay.EnsureColumnsExists(WantedColumns, TableName, CreateColumns);
var insertStatement = $"insert into {TableName} ({WantedColumns.ToJoinedString()}) values({WantedColumns.Select(x => ":" + x).ToJoinedString()})";
var SQLiteParameters = WantedColumns
.Select(x => new SQLiteParameter(x))
.ToArray();
for (var rowNum = 2; rowNum <= ws.Dimension.End.Row; rowNum++)
{
for (var colNum = 1; colNum <= ws.Dimension.End.Column; colNum++)
{
var val = Format(ws.Cells[rowNum, colNum]);
if (string.IsNullOrEmpty(val))
{
SQLiteParameters[colNum - 1].Value = DBNull.Value;
}
else
{
SQLiteParameters[colNum - 1].Value = val;
}
}
try
{
RowsAdded += SQLiteGateWay.Update(insertStatement, SQLiteParameters);
}
catch { }
}
return(RowsAdded);
}
public static void DropView(this SQLiteGateWay SQLiteGateWay, string TableName) =>
SQLiteGateWay.Update($"drop view {TableName}");
public static int DropTable(this SQLiteGateWay SQLiteGateWay, string TableName) =>
SQLiteGateWay.Update($"drop table {TableName}");
public static int BackupTable(this SQLiteGateWay SQLiteGateWay, string TableName, string prefix = "", string suffix_format = "yyyyMMddHHmmssffff") =>
SQLiteGateWay.Update($"create table {DateTime.Now.ToString(suffix_format)}{TableName}{2} as select * from {TableName}");
public static int AddColumn(this SQLiteGateWay SQLiteGateWay, string TableName, string ColumnName, string type = "varchar(1000)") =>
SQLiteGateWay.Update($"alter table {TableName} add column {ColumnName} {type}");
public static void EndTransaction(this SQLiteGateWay SQLiteGateWay) =>
SQLiteGateWay.Update("COMMIT TRANSACTION");
public static void BeginTransaction(this SQLiteGateWay SQLiteGateWay) =>
SQLiteGateWay.Update("BEGIN TRANSACTION");