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