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 static List <SveraGrFormDataBase> LoadFromDataBaseGr(List <FromDataBase> fromDataBaseWide) { SQLiteDataAdapter da = null; DataSet ds = null; SQLiteConnection SQLconnect = null; SQLiteDataReader reader = null; SQLiteCommand command = null; try { List <SveraGrFormDataBase> fromDataBaseGr = new List <SveraGrFormDataBase>(); string strSelect; string databasePath = FunctionUse.GetDataBasePath(); if (System.IO.File.Exists(databasePath) == true) { SQLconnect = new SQLiteConnection(); SQLconnect.ConnectionString = "Data Source=" + databasePath + ";"; SQLconnect.ParseViaFramework = true; SQLconnect.Open(); command = new SQLiteCommand(SQLconnect); command.CommandText = "SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'GroupTable';"; reader = command.ExecuteReader(); if (reader.HasRows == true) { FunctionUse.TestTableAndColumns(SQLconnect); strSelect = "SELECT id, naimId, groupId FROM GroupTable"; da = new SQLiteDataAdapter(strSelect, SQLconnect); ds = new DataSet(); da.Fill(ds, "GroupTable"); if (ds.Tables["GroupTable"].Rows.Count > 0) { SveraGrFormDataBase fromDataBaseSample; for (int i = 0; i <= ds.Tables["GroupTable"].Rows.Count - 1; i++) { string naim = SveraGrFormDataBase.GetNaim(fromDataBaseWide, long.Parse(ds.Tables["GroupTable"].Rows[i].ItemArray[1].ToString())); if (naim != "") { fromDataBaseSample = new SveraGrFormDataBase( long.Parse(ds.Tables["GroupTable"].Rows[i].ItemArray[0].ToString()), long.Parse(ds.Tables["GroupTable"].Rows[i].ItemArray[1].ToString()), ds.Tables["GroupTable"].Rows[i].ItemArray[2].ToString(), naim ); fromDataBaseGr.Add(fromDataBaseSample); } fromDataBaseGr.Sort(); } } } if (da != null) { da.Dispose(); } if (SQLconnect != null) { SQLconnect.Close(); } if (reader != null) { reader.Dispose(); } if (command != null) { command.Dispose(); } } return(fromDataBaseGr); } catch (Exception ex) { if (da != null) { da.Dispose(); } if (SQLconnect != null) { SQLconnect.Close(); } if (reader != null) { reader.Dispose(); } if (command != null) { command.Dispose(); } throw new Exception("LoadFromDataBaseGr", ex); } }