public async void ConvertToSqlite(string pathToExcelFile)
{
SetPathToParentDirectoryOfDatabaseFile();
if (File.Exists(PathToDatabaseArchiveFile) && !File.Exists(_pathToDatabaseFile))
ZipFile.ExtractToDirectory(PathToDatabaseArchiveFile, _pathToDatabase);
using (
var dbSqLiteConnection =
new SQLiteConnection((WebConfigurationManager.ConnectionStrings["SQLite"].ConnectionString)))
{
//load data from xlsx(excel) file
var ds = await SetDataSet(pathToExcelFile);
await dbSqLiteConnection.OpenAsync();
//Set data from rows
for (var i = 0; i < ds.Tables[0].Rows.Count; i++)
{
var rowsStringBuilder = new StringBuilder();
//load data from row to string
for (var j = 0; j < ds.Tables[0].Rows[i].ItemArray.Length; j++)
{
var row = string.IsNullOrEmpty(ds.Tables[0].Rows[i][j].ToString())
? "NULL"
: ds.Tables[0].Rows[i][j].ToString();
if (j < ds.Tables[0].Rows[i].ItemArray.Length - 1)
rowsStringBuilder.Append(row + ",");
else
rowsStringBuilder.Append(row);
}
//Insert data into table
var sqlQuery = "Insert into " + TableName + "(" + ColumnNames + ") Values(" + rowsStringBuilder + ");";
using (var cmd = new SQLiteCommand(sqlQuery, dbSqLiteConnection))
await cmd.ExecuteNonQueryAsync();
}
dbSqLiteConnection.Shutdown();
dbSqLiteConnection.Close();
}
if (File.Exists(PathToDatabaseArchiveFile))
File.Delete(PathToDatabaseArchiveFile);
ZipFile.CreateFromDirectory(_pathToDatabase, PathToDatabaseArchiveFile);
}
