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