public void TryMakeLocalDB()
{
string strQueryCreateObjectInDb = "CREATE TABLE IF NOT EXISTS 'CarAndOwner' ('Id' INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, Plate TEXT, " +
"Factory TEXT, Model TEXT, ManufactureYear TEXT, BodyNumber TEXT, ChassisNumber TEXT, EngineVolume TEXT, " +
"Type TEXT, DRFO INTEGER, F TEXT, I TEXT, O TEXT, Birthday TEXT, " +
"EDRPOU INTEGER, Name TEXT, City TEXT, District TEXT, Street TEXT, Building TEXT, BuildingBody TEXT, Apartment TEXT, " +
"CodeOperation TEXT, CodeDate TEXT);";
if (!File.Exists(settings.Database))
{
SQLiteConnection.CreateFile(settings.Database);
}
using (SqLiteDbWrapper dbWriter = new SqLiteDbWrapper(sqLiteConnectionString, settings.Database))
{
dbWriter.Execute("begin");
dbWriter.Execute(strQueryCreateObjectInDb);
dbWriter.Execute("end");
if (CheckUpDBStructure())
{
EvntInfoMessage?.Invoke(this, new TextEventArgs("Таблицы в БД созданы"));
}
else
{
EvntInfoMessage?.Invoke(this, new TextEventArgs("Ошибка создания таблиц в БД!"));
}
}
}
public void WriteListInLocalDB(IList <CarAndOwner> list)
{
string query =
"INSERT OR REPLACE INTO 'CarAndOwner' (Plate, Factory, Model, ManufactureYear, BodyNumber, ChassisNumber, EngineVolume, Type, DRFO, F, I, O, Birthday, EDRPOU, " +
"Name, City, District, Street, Building, BuildingBody, Apartment, CodeOperation, CodeDate) " +
"VALUES (@Plate, @Factory, @Model, @ManufactureYear, @BodyNumber, @ChassisNumber, @EngineVolume, @Type, @DRFO, @F, @I, @O, @Birthday, @EDRPOU, " +
"@Name, @City, @District, @Street, @Building, @BuildingBody, @Apartment, @CodeOperation, @CodeDate)";
if (CheckUpDBStructure())
{
using (SqLiteDbWrapper dbWriter = new SqLiteDbWrapper(sqLiteConnectionString, settings.Database))
{
EvntInfoMessage?.Invoke(this, new TextEventArgs($"Запись список в {list.Count} записей в базу список"));
dbWriter.Execute("begin");
foreach (var row in list)
{
using (SQLiteCommand SqlQuery = new SQLiteCommand(query, dbWriter.sqlConnection))
{
SqlQuery.Parameters.Add("@Plate", DbType.String).Value = row?.Plate;
SqlQuery.Parameters.Add("@Factory", DbType.String).Value = row?.Factory;
SqlQuery.Parameters.Add("@Model", DbType.String).Value = row?.Model;
SqlQuery.Parameters.Add("@ManufactureYear", DbType.String).Value = row?.ManufactureYear;
SqlQuery.Parameters.Add("@BodyNumber", DbType.String).Value = row?.BodyNumber;
SqlQuery.Parameters.Add("@ChassisNumber", DbType.String).Value = row?.ChassisNumber;
SqlQuery.Parameters.Add("@EngineVolume", DbType.String).Value = row?.EngineVolume;
SqlQuery.Parameters.Add("@Type", DbType.String).Value = row?.Type;
SqlQuery.Parameters.Add("@DRFO", DbType.Int32).Value = row?.DRFO;
SqlQuery.Parameters.Add("@F", DbType.String).Value = row?.F;
SqlQuery.Parameters.Add("@I", DbType.String).Value = row?.I;
SqlQuery.Parameters.Add("@O", DbType.String).Value = row?.O;
SqlQuery.Parameters.Add("@Birthday", DbType.String).Value = row?.Birthday;
SqlQuery.Parameters.Add("@EDRPOU", DbType.Int32).Value = row?.EDRPOU;
SqlQuery.Parameters.Add("@Name", DbType.String).Value = row?.Name;
SqlQuery.Parameters.Add("@City", DbType.String).Value = row?.City;
SqlQuery.Parameters.Add("@District", DbType.String).Value = row?.District;
SqlQuery.Parameters.Add("@Street", DbType.String).Value = row?.Street;
SqlQuery.Parameters.Add("@Building", DbType.String).Value = row?.Building;
SqlQuery.Parameters.Add("@BuildingBody", DbType.String).Value = row?.BuildingBody;
SqlQuery.Parameters.Add("@Apartment", DbType.String).Value = row?.Apartment;
SqlQuery.Parameters.Add("@CodeOperation", DbType.String).Value = row?.CodeOperation;
SqlQuery.Parameters.Add("@CodeDate", DbType.String).Value = row?.CodeDate;
dbWriter.ExecuteBulk(SqlQuery);
}
}
dbWriter.Execute("end");
EvntInfoMessage?.Invoke(this, new TextEventArgs("Запись списка завершена"));
}
}
else
{
EvntInfoMessage?.Invoke(this, new TextEventArgs("Ошибка записи.\r\nПредварительно нужно настроить базу и подключение к базе!"));
}
}
public void WriteModelInTable(IList <IModels> models)
{
string query = "INSERT OR REPLACE INTO 'MainData' (";
IDictionary <int, IModel> firstModels = models[0].list;
foreach (var k in firstModels)
{
query += $"{k.Value.Name}, ";
}
query = query.TrimEnd(' ').TrimEnd(',');
query += ") VALUES (";
foreach (var k in firstModels)
{
query += $"@{k.Value.Name}, ";
}
query = query.TrimEnd(' ').TrimEnd(',');
query += ");";
if (SQLiteCheckImportedDB.Check(settings.Database))
{
// if (CheckUpDBStructure("MainData"))
//{
using (SqLiteDbWrapper dbWriter = new SqLiteDbWrapper(connString))
{
dbWriter.Execute("begin");
foreach (var row in models)
{
using (SQLiteCommand sqlCommand = new SQLiteCommand(query, dbWriter.sqlConnection))
{
foreach (var c in row.list)
{
sqlCommand.Parameters.Add($"@{c.Value.Name}", DbType.String).Value = c.Value.Alias;
}
dbWriter.Execute(sqlCommand);
}
}
dbWriter.Execute("end");
EvntInfoMessage?.Invoke(this, new TextEventArgs("Подготовленные данные импортированы в БД"));
}
}
else
{
EvntInfoMessage?.Invoke(this, new TextEventArgs($"Ошибка записи.{Environment.NewLine}Предварительно нужно проверить базу и таблицу 'MainData' в ней!"));
}
FileReaderModels.evntWaitHandle.Set();
}
public void PrepareTablesForCommonModel(IModels columnsAndAliases)
{
//create table MainData
string query = "CREATE TABLE 'MainData' (";//'Id' INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
IDictionary <int, IModel> models = columnsAndAliases.list;
foreach (var k in models)
{
query += $"'{k.Value.Name}' TEXT, ";
}
query = query.TrimEnd(' ').TrimEnd(',') + ")"; //remove in the end - , "
DoQuery(query);
//Create table ColumnNameAndAlias
query = "CREATE TABLE 'ColumnNameAndAlias' ('ColumnName' TEXT, 'ColumnAlias' TEXT);"; //'Id' INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
DoQuery(query);
//fill table ColumnNameAndAlias
query = "INSERT INTO 'ColumnNameAndAlias' ('ColumnName', 'ColumnAlias') VALUES (@ColumnName, @ColumnAlias);";
if (SQLiteCheckImportedDB.Check(settings.Database))
{
// if (CheckUpDBStructure("ColumnNameAndAlias"))
// {
using (SqLiteDbWrapper dbWriter = new SqLiteDbWrapper(connString))
{
dbWriter.Execute("begin");
foreach (var row in models)
{
using (SQLiteCommand sqlCommand = new SQLiteCommand(query, dbWriter.sqlConnection))
{
sqlCommand.Parameters.Add("@ColumnName", DbType.String).Value = row.Value.Name;
sqlCommand.Parameters.Add("@ColumnAlias", DbType.String).Value = row.Value.Alias;
dbWriter.Execute(sqlCommand);
}
}
dbWriter.Execute("end");
EvntInfoMessage?.Invoke(this, new TextEventArgs("Запись имен колонок и к ним алиасов завершена"));
}
}
else
{
EvntInfoMessage?.Invoke(this, new TextEventArgs($"Ошибка записи.{Environment.NewLine}Предварительно нужно проверить базу и таблицу 'ColumnNameAndAlias' в ней!"));
}
}
public override void DoQuery(string query, bool isCommit = true)
{
if (!File.Exists(settings.Database))
{
SQLiteConnection.CreateFile(settings.Database);
}
EvntInfoMessage?.Invoke(this, new TextEventArgs($"БД: {settings.Database}{Environment.NewLine}" +
$"sqLiteConnectionString: {connString}{Environment.NewLine}Выполняю запрос: {query}"));
using (SqLiteDbWrapper dbWriter = new SqLiteDbWrapper(connString))
{
if (isCommit)
{
dbWriter.Execute("begin");
}
dbWriter.Execute(query);
if (isCommit)
{
dbWriter.Execute("end");
}
}
}
