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);
}
}
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);
}
}
public static List <FromDataBase> LoadFromDataBase()
{
SQLiteDataAdapter da = null;
DataSet ds = null;
SQLiteConnection SQLconnect = null;
SQLiteDataReader reader = null;
SQLiteCommand command = null;
try
{
List <FromDataBase> dataBaseList = new List <FromDataBase>();
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 = 'Production';";
reader = command.ExecuteReader();
if (reader.HasRows == true)
{
FunctionUse.TestTableAndColumns(SQLconnect);
strSelect = "SELECT id, naim, naimKrat, elGroup, tipPostav, tipOborud, markaOborud, docObozn, kod, postav, edIzm, edIzmW, koefToWord, massa, vedRabot, vzriv, klimat, otdel, ip, vedTable FROM Production";
da = new SQLiteDataAdapter(strSelect, SQLconnect);
ds = new DataSet();
da.Fill(ds, "Production");
if (ds.Tables["Production"].Rows.Count > 0)
{
FromDataBase fromDataBaseSample;
for (int i = 0; i <= ds.Tables["Production"].Rows.Count - 1; i++)
{
fromDataBaseSample = new FromDataBase(
long.Parse(ds.Tables["Production"].Rows[i].ItemArray[0].ToString()),
ds.Tables["Production"].Rows[i].ItemArray[1].ToString(),
ds.Tables["Production"].Rows[i].ItemArray[2].ToString(),
(YesNoDif)int.Parse(ds.Tables["Production"].Rows[i].ItemArray[3].ToString()),
ds.Tables["Production"].Rows[i].ItemArray[4].ToString(),
ds.Tables["Production"].Rows[i].ItemArray[5].ToString(),
ds.Tables["Production"].Rows[i].ItemArray[6].ToString(),
ds.Tables["Production"].Rows[i].ItemArray[7].ToString(),
ds.Tables["Production"].Rows[i].ItemArray[8].ToString(),
ds.Tables["Production"].Rows[i].ItemArray[9].ToString(),
ds.Tables["Production"].Rows[i].ItemArray[10].ToString(),
ds.Tables["Production"].Rows[i].ItemArray[11].ToString(),
double.Parse(ds.Tables["Production"].Rows[i].ItemArray[12].ToString()),
double.Parse(ds.Tables["Production"].Rows[i].ItemArray[13].ToString()),
ds.Tables["Production"].Rows[i].ItemArray[14].ToString(),
ds.Tables["Production"].Rows[i].ItemArray[15].ToString(),
ds.Tables["Production"].Rows[i].ItemArray[16].ToString(),
ds.Tables["Production"].Rows[i].ItemArray[17].ToString(),
ds.Tables["Production"].Rows[i].ItemArray[18].ToString(),
BytesToList(ds.Tables["Production"].Rows[i].ItemArray[19] as byte[]));
dataBaseList.Add(fromDataBaseSample);
}
dataBaseList.Sort();
}
}
if (da != null)
{
da.Dispose();
}
if (SQLconnect != null)
{
SQLconnect.Close();
}
if (reader != null)
{
reader.Dispose();
}
if (command != null)
{
command.Dispose();
}
}
return(dataBaseList);
}
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("LoadFromDataBase", ex);
}
}
