public static void AddColumns(this SQLiteGateWay SQLiteGateWay, string TableName, IEnumerable <string> ColumnNames, string type = "varchar(1000)")
{
foreach (var ColumnName in ColumnNames)
{
SQLiteGateWay.AddColumn(TableName, ColumnName, type);
}
}
private static IEnumerable <string> GetObjectOfType(this SQLiteGateWay SQLiteGateWay, string ObjectType, string regexp = "%") =>
SQLiteGateWay
.GetDt("SELECT name FROM sqlite_master WHERE type =:ObjectType AND name like :name order by name",
new SQLiteParameter("name", "%" + regexp + "%"),
new SQLiteParameter("ObjectType", ObjectType))
.Rows
.Cast <DataRow>()
.Select(x => x[0].ToString());
public static int InsertDataTableIntoTable(this SQLiteGateWay SQLiteGateWay, System.Data.DataTable DataTable, bool CreateColumns = false)
{
SQLiteGateWay.EnsureColumnsExists(DataTable.GetColumnNames(), DataTable.TableName, CreateColumns);
return(DataTable
.Rows
.Cast <DataRow>()
.ToList()
.Select(x => SQLiteGateWay.InsertDataRowIntoTable(x))
.Sum());
}
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 ExcelPackage WriteDataBaseToExcel(this SQLiteGateWay SQLiteGateWay, IEnumerable <string> TableNames)
{
var ExcelPackage = new ExcelPackage();
foreach (var TableName in TableNames.ToList())
{
var worksheet = ExcelPackage.Workbook.Worksheets.Add(TableName);
var DataTable = SQLiteGateWay.GetDt(string.Format("select * from '{0}'", TableName));
if (DataTable.Columns.Count > 0)
{
worksheet.Cells["A1"].LoadFromDataTable(DataTable, true);
}
}
return(ExcelPackage);
}
public static void EnsureColumnsExists(this SQLiteGateWay SQLiteGateWay, IEnumerable <string> WantedColumns, string TableName, bool CreateMissingColumns = false)
{
var AvailableColumns = SQLiteGateWay.GetColumnNames(TableName);
var MissingColumns = WantedColumns.Except(AvailableColumns).ToArray();
if (!MissingColumns.Any())
{
return;
}
if (CreateMissingColumns)
{
SQLiteGateWay.AddColumns(TableName, MissingColumns);
}
else
{
throw new NotAllColumnsFoundException(MissingColumns);
}
}
public static int InsertDataRowIntoTable(this SQLiteGateWay SQLiteGateWay, DataRow DataRow, bool CreateColumns = false)
{
var ColumnNames = DataRow.Table.GetColumnNames();
SQLiteGateWay.EnsureColumnsExists(ColumnNames, DataRow.Table.TableName, CreateColumns);
var Fields = ColumnNames
.ToJoinedString();
var ParameterNames = ColumnNames
.Select(ColumnName => ":" + ColumnName)
.ToJoinedString();
var ColumnNamesParameter = ColumnNames
.Select(ColumnName => new SQLiteParameter(":" + ColumnName, DataRow[ColumnName]))
.ToArray();
return(SQLiteGateWay.Insert($"insert into {DataRow.Table.TableName} ({Fields}) values ({ParameterNames})", ColumnNamesParameter));
}
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 ExcelPackage WriteDataBaseToExcel(this SQLiteGateWay SQLiteGateWay, string RegExpTables = "%", string RegExpViews = "%") => SQLiteGateWay.WriteDataBaseToExcel(SQLiteGateWay.GetTablesAndViews(RegExpTables, RegExpViews));
public static IEnumerable <string> GetTablesAndViews(this SQLiteGateWay SQLiteGateWay, string RegExpTables = "%", string RegExpViews = "%") =>
SQLiteGateWay
.GetObjectOfType("view", RegExpViews)
.Concat(SQLiteGateWay.GetObjectOfType("table", RegExpTables));
public static bool ViewExists(this SQLiteGateWay SQLiteGateWay, string TableName) =>
SQLiteGateWay
.GetDt("SELECT name FROM sqlite_master WHERE type='view' AND name=:TableName", new SQLiteParameter("TableName", TableName))
.Rows
.Count == 1;
public static IEnumerable <string> GetTableNames(this SQLiteGateWay SQLiteGateWay, string regexp = "%") =>
SQLiteGateWay.GetObjectOfType("table", regexp);
public static IEnumerable <string> GetViews(this SQLiteGateWay SQLiteGateWay, string regexp = "%") =>
SQLiteGateWay.GetObjectOfType("view", regexp);
public static void DropView(this SQLiteGateWay SQLiteGateWay, string TableName) =>
SQLiteGateWay.Update($"drop view {TableName}");
public static IEnumerable <string> GetColumnNames(this SQLiteGateWay SQLiteGateWay, string TableName) =>
SQLiteGateWay
.GetDt($"select * from '{TableName}' where 1=0")
.GetColumnNames();
public static int DropTable(this SQLiteGateWay SQLiteGateWay, string TableName) =>
SQLiteGateWay.Update($"drop table {TableName}");
public static void BeginTransaction(this SQLiteGateWay SQLiteGateWay) =>
SQLiteGateWay.Update("BEGIN TRANSACTION");
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 void EndTransaction(this SQLiteGateWay SQLiteGateWay) =>
SQLiteGateWay.Update("COMMIT TRANSACTION");
public static int AddColumn(this SQLiteGateWay SQLiteGateWay, string TableName, string ColumnName, string type = "varchar(1000)") =>
SQLiteGateWay.Update($"alter table {TableName} add column {ColumnName} {type}");
