public void CreateOrUpdateGr(DataBaseMotion motion, int index, SveraGrFormDataBase sveraGrDataBase) { SQLiteConnection SQLconnect = null; SQLiteCommand command = null; try { string filePath = FunctionUse.GetDataBasePath(); if (File.Exists(filePath) == true) { FunctionUse.CopyDataBase(filePath); SQLconnect = new SQLiteConnection(); SQLconnect.ConnectionString = "Data Source=" + FunctionUse.GetDataBasePath() + ";"; SQLconnect.ParseViaFramework = true; SQLconnect.Open(); using (var transaction = SQLconnect.BeginTransaction()) { string sqlCom = ""; FunctionUse.TestTableAndColumns(SQLconnect); if (motion == DataBaseMotion.Delete) { if (sveraGrDataBase != null && sveraGrDataBase.IdBase > 0) { sqlCom = "DELETE FROM GroupTable " + "WHERE Id = " + sveraGrDataBase.IdBase ; } else { MessageBox.Show("Текущая запись не существует", "Svera DataBase", MessageBoxButtons.OK, MessageBoxIcon.Information); return; } } else { FromDataBaseWide detilizeId = GroupList.FirstOrDefault <FromDataBaseWide>(fr => fr.Id == Detalized[index].Id); if (detilizeId != null && detilizeId.IdBase > 0) { //string insertTest = "WHERE NOT EXISTS (SELECT 1 FROM GroupTable WHERE " + // "naimId = " + detilizeId.IdBase + // ")"; sqlCom = "INSERT OR REPLACE" + " INTO GroupTable (id, naimId, groupId) " + "SELECT " + ((motion == DataBaseMotion.Insert) ? "NULL" : sveraGrDataBase.IdBase.ToString()) + ", " + detilizeId.IdBase + "" + ", '" + HashKolAndId(Detalized[index].GridGroupId) + "'" //+ // " " + // ((motion == DataBaseMotion.Insert) ? insertTest : "") ; } else { MessageBox.Show("Текущей группе не присвоен ID базы", "Svera DataBase", MessageBoxButtons.OK, MessageBoxIcon.Information); return; } } command = new SQLiteCommand(sqlCom, SQLconnect); command.ExecuteNonQuery(); transaction.Commit(); if (motion == DataBaseMotion.Insert) { MessageBox.Show("Запись добавлена в базу", "Svera DataBase", MessageBoxButtons.OK, MessageBoxIcon.Information); } else if (motion == DataBaseMotion.Update) { MessageBox.Show("Запись обновлена в базе", "Svera DataBase", MessageBoxButtons.OK, MessageBoxIcon.Information); } else if (motion == DataBaseMotion.Delete) { MessageBox.Show("Запись удалена из базы", "Svera DataBase", MessageBoxButtons.OK, MessageBoxIcon.Information); } } if (command != null) { command.Dispose(); } if (SQLconnect != null) { SQLconnect.Close(); } } } catch (Exception ex) { if (SQLconnect != null) { SQLconnect.Close(); } MessageBox.Show(ex.Message); MessageBox.Show(ex.StackTrace); if (ex.InnerException != null) { MessageBox.Show(ex.InnerException.ToString()); } } }
public void CreateOrUpdate(DataBaseMotion motion) { SQLiteConnection SQLconnect = null; SQLiteCommand command = null; try { string filePath = FunctionUse.GetDataBasePath(); if (File.Exists(filePath) == true) { FunctionUse.CopyDataBase(filePath); SQLconnect = new SQLiteConnection(); SQLconnect.ConnectionString = "Data Source=" + FunctionUse.GetDataBasePath() + ";"; SQLconnect.ParseViaFramework = true; SQLconnect.Open(); //bool tipPostav = dataBaseList.Any(dt => dt.TipPostav == FromDataBaseWideForSvera.TipPostav); //bool tipOborud = dataBaseList.Any(dt => dt.TipOborud == FromDataBaseWideForSvera.TipOborud); //bool edIzm = dataBaseList.Any(dt => dt.EdIzm == FromDataBaseWideForSvera.EdIzm); //bool edIzmW = dataBaseList.Any(dt => dt.EdIzmW == FromDataBaseWideForSvera.EdIzmW); using (var transaction = SQLconnect.BeginTransaction()) { string sql = ""; string sqlCom = ""; FunctionUse.TestTableAndColumns(SQLconnect); if (motion == DataBaseMotion.Insert || motion == DataBaseMotion.Update) { sqlCom = "INSERT OR IGNORE INTO TipPostav (id, tipPostav) values " + "(NULL " + ", '" + FromDataBaseWideForSvera.TipPostav + "'" + ")"; command = new SQLiteCommand(sqlCom, SQLconnect); command.ExecuteNonQuery(); sqlCom = "INSERT OR IGNORE INTO TipOborud (id, tipOborud) values " + "(NULL " + ", '" + FromDataBaseWideForSvera.TipOborud + "'" + ")"; command = new SQLiteCommand(sqlCom, SQLconnect); command.ExecuteNonQuery(); sqlCom = "INSERT OR IGNORE INTO EdIzm (id, edIzm) values " + "(NULL " + ", '" + FromDataBaseWideForSvera.EdIzm + "'" + ")"; command = new SQLiteCommand(sqlCom, SQLconnect); command.ExecuteNonQuery(); sqlCom = "INSERT OR IGNORE INTO EdIzm (id, edIzm) values " + "(NULL " + ", '" + FromDataBaseWideForSvera.EdIzmW + "'" + ")"; command = new SQLiteCommand(sqlCom, SQLconnect); command.ExecuteNonQuery(); } if (motion == DataBaseMotion.Delete) { sqlCom = "DELETE FROM Production " + "WHERE id = " + FromDataBaseWideForSvera.IdBase ; } else { string insertTest = "WHERE NOT EXISTS (SELECT 1 FROM Production WHERE " + "naim = '" + FromDataBaseWideForSvera.Naim + "' AND " + "markaOborud = '" + FromDataBaseWideForSvera.MarkaOborud + "' AND " + "postav = '" + FromDataBaseWideForSvera.Postav + "'" + ")"; sqlCom = ((motion == DataBaseMotion.Insert) ? "INSERT" : "INSERT OR REPLACE") + " INTO Production (id, naim, naimKrat, elGroup, tipPostav, tipOborud, markaOborud, docObozn, kod, postav, edIzm, edIzmW, koefToWord, massa, vedRabot, vzriv, klimat, otdel, ip, vedTable) " + "SELECT " + ((motion == DataBaseMotion.Insert) ? "NULL" : FromDataBaseWideForSvera.IdBase.ToString()) + ", '" + FromDataBaseWideForSvera.Naim + "'" + ", '" + FromDataBaseWideForSvera.NaimKrat + "'" + ", " + Convert.ToInt32(FromDataBaseWideForSvera.ElGroup) + ", '" + FromDataBaseWideForSvera.TipPostav + "'" + ", '" + FromDataBaseWideForSvera.TipOborud + "'" + ", '" + FromDataBaseWideForSvera.MarkaOborud + "'" + ", '" + FromDataBaseWideForSvera.DocObozn + "'" + ", '" + FromDataBaseWideForSvera.Kod + "'" + ", '" + FromDataBaseWideForSvera.Postav + "'" + ", '" + FromDataBaseWideForSvera.EdIzm + "'" + ", '" + FromDataBaseWideForSvera.EdIzmW + "'" + ", " + FromDataBaseWideForSvera.KoefToWord.ToString().Replace(",", ".") + ", " + FromDataBaseWideForSvera.Massa.ToString().Replace(",", ".") + ", '" + FromDataBaseWideForSvera.VedRabot + "'" + ", '" + FromDataBaseWideForSvera.Vzriv + "'" + ", '" + FromDataBaseWideForSvera.Klimat + "'" + ", '" + FromDataBaseWideForSvera.Otdel + "'" + ", '" + FromDataBaseWideForSvera.Ip + "'" + ", @ved" + " " + ((motion == DataBaseMotion.Insert) ? insertTest : "") ; } command = new SQLiteCommand(sqlCom, SQLconnect); command.Parameters.Add("@ved", DbType.Binary).Value = FromDataBaseWideForSvera.VedRabotToByte; command.ExecuteNonQuery(); if (motion == DataBaseMotion.Delete) { sqlCom = "DELETE FROM TipPostav " + "WHERE TipPostav.tipPostav = '" + FromDataBaseWideForSvera.TipPostav + "' AND " + "NOT EXISTS (SELECT 1 FROM Production WHERE " + "Production.tipPostav = '" + FromDataBaseWideForSvera.TipPostav + "'" + ")" ; command = new SQLiteCommand(sqlCom, SQLconnect); command.ExecuteNonQuery(); sqlCom = "DELETE FROM TipOborud " + "WHERE TipOborud.tipOborud = '" + FromDataBaseWideForSvera.TipOborud + "' AND " + "NOT EXISTS (SELECT 1 FROM Production WHERE " + "Production.tipOborud = '" + FromDataBaseWideForSvera.TipOborud + "'" + ")" ; command = new SQLiteCommand(sqlCom, SQLconnect); command.ExecuteNonQuery(); sqlCom = "DELETE FROM EdIzm " + "WHERE EdIzm.edIzm = '" + FromDataBaseWideForSvera.EdIzm + "' AND " + "NOT EXISTS (SELECT 1 FROM Production WHERE " + "Production.edIzm = '" + FromDataBaseWideForSvera.EdIzm + "'" + ")" ; command = new SQLiteCommand(sqlCom, SQLconnect); command.ExecuteNonQuery(); sqlCom = "DELETE FROM EdIzm " + "WHERE EdIzm.edIzm = '" + FromDataBaseWideForSvera.EdIzmW + "' AND " + "NOT EXISTS (SELECT 1 FROM Production WHERE " + "Production.edIzm = '" + FromDataBaseWideForSvera.EdIzmW + "'" + ")" ; command = new SQLiteCommand(sqlCom, SQLconnect); command.ExecuteNonQuery(); } transaction.Commit(); if (motion == DataBaseMotion.Insert) { MessageBox.Show("Запись добавлена в базу", "Svera DataBase", MessageBoxButtons.OK, MessageBoxIcon.Information); } else if (motion == DataBaseMotion.Update) { MessageBox.Show("Запись обновлена в базе", "Svera DataBase", MessageBoxButtons.OK, MessageBoxIcon.Information); } else if (motion == DataBaseMotion.Delete) { MessageBox.Show("Запись удалена из базы", "Svera DataBase", MessageBoxButtons.OK, MessageBoxIcon.Information); } } if (command != null) { command.Dispose(); } if (SQLconnect != null) { SQLconnect.Close(); } } } catch (Exception ex) { if (SQLconnect != null) { SQLconnect.Close(); } MessageBox.Show(ex.Message); MessageBox.Show(ex.StackTrace); } }