private void InsertRows(DataBaseMotion motion)
{
if (ProverkGrid(motion) == true || motion == DataBaseMotion.Delete)
{
int prevind = -1;
long newDataBaseId = -1;
if (ComboDate.SelectedItem != null)
{
prevind = ComboDate.SelectedIndex;
newDataBaseId = (ComboDate.SelectedItem as FromDataBase).IdBase;
}
SveraB.CreateOrUpdate(motion);
SveraB.UpdateDatabase();
if (SveraB.DataBaseList.Count > 0)
{
updateCom();
ChangeFilter();
if (ComboDate.DataSource != null)
{
if (motion == DataBaseMotion.Update)
{
ComboDate.SelectedIndex = (ComboDate.DataSource as List <FromDataBase>).FindIndex(db => db.IdBase == newDataBaseId);
}
else if (motion == DataBaseMotion.Insert)
{
ComboDate.SelectedIndex = (ComboDate.DataSource as List <FromDataBase>).IndexOf((ComboDate.DataSource as List <FromDataBase>).OrderBy(db => db.IdBase).Last());
}
else if (motion == DataBaseMotion.Delete)
{
if (prevind > 0)
{
ComboDate.SelectedIndex = prevind - 1;
}
else
{
ComboDate.SelectedIndex = 0;
}
}
}
if (ComboDate.Items.Count > 0 && ComboDate.SelectedIndex == -1)
{
ComboDate.SelectedIndex = 0;
}
}
}
}
private void InsertRows(DataBaseMotion motion)
{
int prevind = -1;
long newDataBaseId = -1;
if (ComboDate.SelectedItem != null)
{
prevind = ComboDate.SelectedIndex;
newDataBaseId = (ComboDate.SelectedItem as SveraGrFormDataBase).IdBase;
}
sveraGr.CreateOrUpdateGr(motion, checkGroup.SelectedIndex, ComboDate.SelectedItem as SveraGrFormDataBase);
sveraGr.UpdateDatabase();
ComboDate.DataSource = sveraGr.DataBaseListGr;
if (sveraGr.DataBaseListGr.Count > 0)
{
if (ComboDate.DataSource != null)
{
if (motion == DataBaseMotion.Update)
{
ComboDate.SelectedIndex = (ComboDate.DataSource as List <SveraGrFormDataBase>).FindIndex(db => db.IdBase == newDataBaseId);
}
else if (motion == DataBaseMotion.Insert)
{
ComboDate.SelectedIndex = (ComboDate.DataSource as List <SveraGrFormDataBase>).IndexOf((ComboDate.DataSource as List <SveraGrFormDataBase>).OrderBy(db => db.IdBase).Last());
}
else if (motion == DataBaseMotion.Delete)
{
if (prevind > 0)
{
ComboDate.SelectedIndex = prevind - 1;
}
else
{
ComboDate.SelectedIndex = 0;
}
}
}
if (ComboDate.Items.Count > 0 && ComboDate.SelectedIndex == -1)
{
ComboDate.SelectedIndex = 0;
}
}
}
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);
}
}
private bool ProverkGrid(DataBaseMotion motion)
{
bool proverkGrid = true;
try
{
if (motion != DataBaseMotion.Delete)
{
foreach (DataGridViewRow r in sverPDataGrid.Rows)
{
r.DefaultCellStyle.BackColor = Color.Empty;
if (r.Cells[SveraParam.ColDatagridNames.Value].Value != null)
{
r.Cells[SveraParam.ColDatagridNames.Value].Value = r.Cells[SveraParam.ColDatagridNames.Value].Value.ToString().Trim();
}
if (r.Cells[SveraParam.ColDatagridNames.Value].Value == null || r.Cells[SveraParam.ColDatagridNames.Value].Value.ToString() == "")
{
if (r.Tag.ToString() == SveraParam.RowsDatagridNames.naim)
{
r.Cells[SveraParam.ColDatagridNames.Value].Style.BackColor = Color.Red;
proverkGrid = false;
}
else if (r.Tag.ToString() == SveraParam.RowsDatagridNames.naimKrat)
{
r.Cells[SveraParam.ColDatagridNames.Value].Style.BackColor = Color.Red;
proverkGrid = false;
}
else if (r.Tag.ToString() == SveraParam.RowsDatagridNames.tipPostav)
{
r.Cells[SveraParam.ColDatagridNames.Value].Style.BackColor = Color.Red;
proverkGrid = false;
}
else if (r.Tag.ToString() == SveraParam.RowsDatagridNames.tipOborud)
{
r.Cells[SveraParam.ColDatagridNames.Value].Style.BackColor = Color.Red;
proverkGrid = false;
}
//else if (r.Tag.ToString() == SveraParam.RowsDatagridNames.markaOborud)
//{
// r.Cells[SveraParam.ColDatagridNames.Value].Style.BackColor = Color.Red;
// proverkGrid = false;
//}
else if (r.Tag.ToString() == SveraParam.RowsDatagridNames.edIzm)
{
r.Cells[SveraParam.ColDatagridNames.Value].Style.BackColor = Color.Red;
proverkGrid = false;
}
else if (r.Tag.ToString() == SveraParam.RowsDatagridNames.edIzmW)
{
r.Cells[SveraParam.ColDatagridNames.Value].Style.BackColor = Color.Red;
proverkGrid = false;
}
else if (r.Tag.ToString() == SveraParam.RowsDatagridNames.elGroup)
{
r.Cells[SveraParam.ColDatagridNames.Value].Style.BackColor = Color.Red;
proverkGrid = false;
}
}
}
if (proverkGrid == false)
{
MessageBox.Show("Заполните необходимые поля", "Svera DataBase", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
if (SveraB.DataBaseList.Count > 0 && motion == DataBaseMotion.Insert)
{
if (SveraB.DataBaseList.FindIndex(dt => dt.EqualsForDataBase(SveraB.FromDataBaseWideForSvera) == true) > -1)
{
MessageBox.Show("Оборудование с данными характеристиками уже присутствует в базе", "Svera DataBase", MessageBoxButtons.OK, MessageBoxIcon.Information);
proverkGrid = false;
}
}
if (SveraB.DataBaseList.Count > 0 && motion == DataBaseMotion.Update)
{
if (SveraB.DataBaseList.FindIndex(dt => dt.Equals(SveraB.FromDataBaseWideForSvera) == true) > -1)
{
MessageBox.Show("Оборудование с данными характеристиками уже присутствует в базе", "Svera DataBase", MessageBoxButtons.OK, MessageBoxIcon.Information);
proverkGrid = false;
}
}
}
}
else
{
if (SveraB.FromDataBaseWideForSvera.IdBase <= 0)
{
MessageBox.Show("Текущая запись не существует", "Svera DataBase", MessageBoxButtons.OK, MessageBoxIcon.Information);
proverkGrid = false;
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
MessageBox.Show(ex.StackTrace);
}
return(proverkGrid);
}
