void MusteriyiGoster() { try { DatabaseAccess db = new DatabaseAccess(); System.Data.SqlServerCe.SqlCeDataReader data = db.DataOku("Select * from musteri where [no]=@0", musteriNo); if (data.Read()) { txtNo.Text = data["no"].ToString(); txtAdSoyad.Text = data["adsoyad"].ToString(); this.Text = "Müşteri - " + txtAdSoyad.Text; txtEposta.Text = data["eposta"].ToString(); txtTel.Text = data["telefon"].ToString(); txtAnaPara.Text = data["anapara"].ToString(); txtKar.Text = data["kar"].ToString(); //txtKar2.Text = txtAKar.Text = data["akar"].ToString(); txtHesapBilgileri.Text = data["hesap"].ToString(); txtMusteriNotu.Text = data["not"].ToString(); //txtAKar2.Text = datePickKayit.Value = Convert.ToDateTime(data["ktarih"].ToString()); datePickOdeme.Value = Convert.ToDateTime(data["otarih"].ToString()); } } catch { MessageBox.Show("Müşteri bulunurken kritik hata oluştu!", "HATA", MessageBoxButtons.OK, MessageBoxIcon.Warning); this.Close(); } }
private void btnAra_Click(object sender, EventArgs e) { int musterino; if (int.TryParse(textBox1.Text, out musterino)) { try { DatabaseAccess db = new DatabaseAccess(); data = db.DataOku("select isim from musteri where [no]=@0", listMusteri.Items[musterino].Tag.ToString()); if (data.Read()) { MusteriDuzenle(listMusteri.Items[musterino].Tag.ToString(), listMusteri.Items[musterino].Text); } else { MusterileriListele(textBox1.Text); } //DatabaseAccess db = new DatabaseAccess(); //data = db.DataOku("select * from musteri where [no]=@0", musterino.ToString()); //if (data.Read()) // MusteriDuzenle(musterino.ToString()); //else // MusterileriListele(textBox1.Text); } catch { MusterileriListele(textBox1.Text); } } else { MusterileriListele(textBox1.Text); } }
void OdemeBilgileriGoster() { try { DatabaseAccess db = new DatabaseAccess(); System.Data.SqlServerCe.SqlCeDataReader data = db.DataOku("Select * from log where [no]=@0", odemeNo); if (data.Read()) { txtNo.Text = data["no"].ToString(); txtTutar.Text = data["tutar"].ToString(); txtOdemeTarihi.Text = Convert.ToDateTime(data["otarih"].ToString()).ToShortDateString(); this.Text = "Ödeme Bilgileri - Tarih:" + txtOdemeTarihi.Text; string durum = data["durumu"].ToString(); if (durum == "0") { radioOdenmedi.Checked = true; } else { radioOdendi.Checked = true; } txtAciklama.Text = data["aciklama"].ToString(); } } catch { MessageBox.Show("Ödeme bilgileri bulunurken kritik hata oluştu!", "HATA", MessageBoxButtons.OK, MessageBoxIcon.Warning); this.Close(); } }
// ----------- Функции ------------- public static int DB_GetMaxLength(string table, string column) { System.Data.SqlServerCe.SqlCeCommand sqlQuery = DB.CreateQuery(); sqlQuery.CommandText = "select DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='" + table + "' and COLUMN_NAME='" + column + "'"; System.Data.SqlServerCe.SqlCeDataReader sqlReader = ExecuteReader(sqlQuery); int len = 0; if (sqlReader.Read()) { len = (int)sqlReader.GetSqlInt32(sqlReader.GetOrdinal("CHARACTER_MAXIMUM_LENGTH")); } sqlReader.Close(); return(len); }
void MusterileriListele() { OdemeZamaniKontrol(); try { double anapara1 = 0, kar1 = 0, akar1 = 0; int demo = 0; listMusteri.Items.Clear(); DatabaseAccess db = new DatabaseAccess(); data = db.DataOku("select * from musteri order by no"); while (data.Read()) { string anapara = data["anapara"].ToString(); anapara1 += Convert.ToDouble(anapara); string kar = PRG.YuzdeToPara(anapara, data["kar"].ToString()); kar1 += Convert.ToDouble(kar); string akar = PRG.YuzdeToPara(anapara, data["akar"].ToString()); akar1 += Convert.ToDouble(akar); ListViewItem item = new ListViewItem(); item.Tag = item.Text = data["no"].ToString(); item.SubItems.Add(data["adsoyad"].ToString()); item.SubItems.Add(anapara); item.SubItems.Add("%" + data["kar"].ToString() + "(" + kar + ")"); item.SubItems.Add("%" + data["akar"].ToString() + "(" + akar + ")"); item.SubItems.Add(PRG.OdenecekTutar(item.Text, "1")); string bekleyen = PRG.OdenecekTutar(item.Text, "0"); if (bekleyen == "0") { item.ForeColor = Color.Green; } else { item.ForeColor = Color.Red; } item.SubItems.Add(bekleyen); item.SubItems.Add(Convert.ToDateTime(data["otarih"].ToString()).ToShortDateString()); if (demo != 5) { demo++; listMusteri.Items.Add(item); } } lblAnaPara.Text = anapara1.ToString(); lblKar.Text = kar1.ToString(); lblAKar.Text = akar1.ToString(); lblOdenmis.Text = PRG.OdenecekTutar("1"); lblBekleyen.Text = PRG.OdenecekTutar("0"); } catch { MessageBox.Show("Müşteriler okunurken kritik hata oluştu!", "HATA", MessageBoxButtons.OK, MessageBoxIcon.Warning); } }
void MusterileriListele() { listMusteri.Items.Clear(); DatabaseAccess db = new DatabaseAccess(); data = db.DataOku("select * from musteri order by no"); while (data.Read()) { ListViewItem item = new ListViewItem(); item.Tag = data["no"].ToString(); item.Text = (listMusteri.Items.Count + 1).ToString(); item.SubItems.Add(data["isim"].ToString()); item.SubItems.Add(data["site"].ToString()); item.SubItems.Add(Convert.ToDateTime(data["baslangic"].ToString()).ToShortDateString()); item.SubItems.Add(Convert.ToDateTime(data["bitis"].ToString()).ToShortDateString()); listMusteri.Items.Add(item); } }
//Загрузка списка игр void LoadGamesToList() { // Сначала очистить: m_listview_Games.Items.Clear(); // Если папка не выбрана, не показывать ничего! if (!SELECTOR_FOLDER__is_enabled) { return; } // Теперь загрузить: System.Data.SqlServerCe.SqlCeCommand sqlQuery = DB.CreateQuery(); sqlQuery.CommandText = "SELECT g.id, g.StartDate, g.Place, g.Comment, g.Type, g.DealsInMatch, g.GameOptions, n.Name as N, s.Name as S, e.Name as E, w.Name as W FROM Games g LEFT JOIN Players n ON g.fk_N = n.id LEFT JOIN Players s ON g.fk_S = s.id LEFT JOIN Players e ON g.fk_E = e.id LEFT JOIN Players w ON g.fk_W = w.id"; if (SELECTOR_FOLDER__is_enabled) { if (SELECTOR_FOLDER == -1) { sqlQuery.CommandText += " WHERE g.fk_Folder_id IS NULL"; } else { sqlQuery.CommandText += (" WHERE g.fk_Folder_id = " + SELECTOR_FOLDER); } } sqlQuery.CommandText += " ORDER BY g.StartDate DESC, g.id ASC"; System.Data.SqlServerCe.SqlCeDataReader sqlReader = DB.ExecuteReader(sqlQuery); while (sqlReader.Read()) { Load1Game(null, sqlReader); } sqlReader.Close(); // Подогнать ширину столбцов this.m_listview_Games.Columns[0].Width = -1; //Комментарий this.m_listview_Games.Columns[1].Width = -1; //Дата this.m_listview_Games.Columns[2].Width = -1; //Тип this.m_listview_Games.Columns[3].Width = -1; //Опции this.m_listview_Games.Columns[4].Width = -1; //Место this.m_listview_Games.Columns[5].Width = -1; //Игроки }
void MusterileriListele(string ara) { OdemeZamaniKontrol(); try { ara = ClearSqlInjection(ara); int demo = 0; listMusteri.Items.Clear(); DatabaseAccess db = new DatabaseAccess(); data = db.DataOku("select * from musteri where adsoyad like '%" + ara + "%' order by no"); while (data.Read()) { string anapara = data["anapara"].ToString(); string kar = PRG.YuzdeToPara(anapara, data["kar"].ToString()); string akar = PRG.YuzdeToPara(anapara, data["akar"].ToString()); ListViewItem item = new ListViewItem(); item.Tag = item.Text = data["no"].ToString(); item.SubItems.Add(data["adsoyad"].ToString()); item.SubItems.Add(anapara); item.SubItems.Add("%" + data["kar"].ToString() + "(" + kar + ")"); item.SubItems.Add("%" + data["akar"].ToString() + "(" + akar + ")"); item.SubItems.Add(PRG.OdenecekTutar(item.Text, "1")); string bekleyen = PRG.OdenecekTutar(item.Text, "0"); if (bekleyen == "0") { item.ForeColor = Color.Green; } else { item.ForeColor = Color.Red; } item.SubItems.Add(bekleyen); item.SubItems.Add(Convert.ToDateTime(data["otarih"].ToString()).ToShortDateString()); if (demo != 5) { demo++; listMusteri.Items.Add(item); } } } catch { MessageBox.Show("Müşteriler okunurken kritik hata oluştu!", "HATA", MessageBoxButtons.OK, MessageBoxIcon.Warning); } }
void MusterileriListele(string ara) { ara = ClearSqlInjection(ara); string command = "select * from musteri where site like '%" + ara + "%' or isim like '%" + ara + "%' or eposta like '%" + ara + "%' order by no"; listMusteri.Items.Clear(); DatabaseAccess db = new DatabaseAccess(); data = db.DataOku(command); while (data.Read()) { ListViewItem item = new ListViewItem(); item.Tag = data["no"].ToString(); item.Text = (listMusteri.Items.Count + 1).ToString(); item.SubItems.Add(data["isim"].ToString()); item.SubItems.Add(data["site"].ToString()); item.SubItems.Add(Convert.ToDateTime(data["baslangic"].ToString()).ToShortDateString()); item.SubItems.Add(Convert.ToDateTime(data["bitis"].ToString()).ToShortDateString()); listMusteri.Items.Add(item); } }
void OdemeZamaniKontrol() { try { DatabaseAccess db = new DatabaseAccess(); //System.Data.SqlServerCe.SqlCeParameter tarih1 = new System.Data.SqlServerCe.SqlCeParameter("@0", SqlDbType.DateTime); data = db.DataOku("select no,otarih,kar,anapara from musteri where otarih<@0", DateTime.Now.Date.AddDays(1).ToString()); while (data.Read()) { string tarih = data["otarih"].ToString(); DateTime otarih = Convert.ToDateTime(tarih); string no = data["no"].ToString(); string tutar = PRG.YuzdeToPara(data["anapara"].ToString(), data["kar"].ToString()); DatabaseAccess db2 = new DatabaseAccess(); db2.Sorgu("insert into log (musterino,tutar,otarih,durumu,aciklama) values (@0,@1,@2,@3,@4)", no, tutar, otarih.ToString(), "0", ""); DatabaseAccess db3 = new DatabaseAccess(); db3.Sorgu("update musteri set otarih=@0 where no=@1", otarih.AddMonths(1).ToString(), no); } } catch { MessageBox.Show("Ödeme zamanı kontrol edilirken kritik hata oluştu!", "HATA", MessageBoxButtons.OK, MessageBoxIcon.Warning); } }
void OdemeleriGoster() { try { lblOdenmis.Text = PRG.OdenecekTutar(musteriNo, "1"); lblBekleyen.Text = PRG.OdenecekTutar(musteriNo, "0"); lblAKari.Text = txtAKar2.Text; lblKari.Text = txtKar2.Text; listOdemeler.Items.Clear(); DatabaseAccess db = new DatabaseAccess(); data = db.DataOku("select * from log where musterino=@0", musteriNo); while (data.Read()) { ListViewItem item = new ListViewItem(); item.Tag = item.Text = data["no"].ToString(); item.SubItems.Add(data["tutar"].ToString()); item.SubItems.Add(Convert.ToDateTime(data["otarih"].ToString()).ToShortDateString()); switch (data["durumu"].ToString()) { case "1": item.SubItems.Add("Ödendi"); item.ForeColor = Color.Green; break; case "0": item.SubItems.Add("Ödenmedi"); item.ForeColor = Color.Red; break; default: break; } //1 - tamamlandı //0 - ödenmedi item.SubItems.Add(data["aciklama"].ToString()); listOdemeler.Items.Add(item); } } catch { MessageBox.Show("Ödeme bilgileri bulunurken kritik hata oluştu!", "HATA", MessageBoxButtons.OK, MessageBoxIcon.Warning); this.Close(); } }
void MusteriyiListele() { try { DatabaseAccess db = new DatabaseAccess(); System.Data.SqlServerCe.SqlCeDataReader data = db.DataOku("Select * from musteri where [no]=@0", musterino); if (data.Read()) { txtVeritabaniNo.Text = data["no"].ToString(); txtAdSoyad.Text = data["isim"].ToString(); txtEposta.Text = data["eposta"].ToString(); txtKAdi.Text = data["kadi"].ToString(); txtKSifre.Text = data["ksifre"].ToString(); txtSite.Text = data["site"].ToString(); txtUcret.Text = data["ucret"].ToString(); txtTel.Text = data["tel"].ToString(); datePickBaslangic.Value = Convert.ToDateTime(data["baslangic"].ToString()); datePickBitis.Value = Convert.ToDateTime(data["bitis"].ToString()); } } catch { MessageBox.Show("Müşteri bulunurken kritik hata oluştu!", "HATA", MessageBoxButtons.OK, MessageBoxIcon.Warning); this.Close(); } }
// **** контролы **** //m_textbox1 добавить //m_textbox2 переименовать //m_combo1 source //m_combo2 destination //---------------------------------------------------------------------------------------------------- //Загрузка комбы папок void LoadFoldersToCombo(ComboBox combo) { bool is_from = (combo == m_combo1); bool is_to = (combo == m_combo2); // Сохранить SELECTOR_FOLDER... int saved__SELECTOR_FOLDER = SELECTOR_FOLDER; int saved__SELECTOR_FOLDER2 = SELECTOR_FOLDER2; bool saved__SELECTOR_FOLDER__is_enabled = SELECTOR_FOLDER__is_enabled; bool saved__SELECTOR_FOLDER2__is_enabled = SELECTOR_FOLDER2__is_enabled; // Сначала очистить: combo.Items.Clear(); combo.SelectedIndex = -1; // Теперь загрузить: //1. NULL System.Data.SqlServerCe.SqlCeCommand sqlQuery = DB.CreateQuery(); sqlQuery.CommandText = "SELECT COUNT(id) FROM Games WHERE fk_Folder_id is NULL"; object o = DB.ExecuteScalar(sqlQuery); int fid = -1; string fname = "- нет папки -"; if (o != null && o != DBNull.Value) { fname += " (" + (int)o + ")"; } int index = combo.Items.Add(new comboitem_id_name(fid, fname)); // Выбрать: if (is_from && saved__SELECTOR_FOLDER__is_enabled && saved__SELECTOR_FOLDER == fid || is_to && saved__SELECTOR_FOLDER2__is_enabled && saved__SELECTOR_FOLDER2 == fid) { combo.SelectedIndex = index; } // 2. остальные sqlQuery = DB.CreateQuery(); sqlQuery.CommandText = "SELECT f.id as [fid], f.Name as [fname], COUNT(g.id) as [fcount] FROM Folders f LEFT JOIN Games g ON f.id=g.fk_Folder_id GROUP BY f.id, f.Name ORDER BY [fname]"; System.Data.SqlServerCe.SqlCeDataReader sqlReader = DB.ExecuteReader(sqlQuery); while (sqlReader.Read()) { fid = sqlReader.IsDBNull(sqlReader.GetOrdinal("fid")) ? -1 : sqlReader.GetInt32(sqlReader.GetOrdinal("fid")); fname = ""; if (fid == -1) { fname = "- нет папки -"; } if (!sqlReader.IsDBNull(sqlReader.GetOrdinal("fname"))) { fname = sqlReader.GetString(sqlReader.GetOrdinal("fname")); } fname += " (" + (sqlReader.IsDBNull(sqlReader.GetOrdinal("fcount")) ? 0 : sqlReader.GetInt32(sqlReader.GetOrdinal("fcount"))) + ")"; index = combo.Items.Add(new comboitem_id_name(fid, fname)); // Выбрать: if (is_from && saved__SELECTOR_FOLDER__is_enabled && saved__SELECTOR_FOLDER == fid || is_to && saved__SELECTOR_FOLDER2__is_enabled && saved__SELECTOR_FOLDER2 == fid) { combo.SelectedIndex = index; } } sqlReader.Close(); }
bool CloneGame(int gameId) { System.Data.SqlServerCe.SqlCeTransaction trans = null; System.Data.SqlServerCe.SqlCeCommand sqlQuery = null; System.Data.SqlServerCe.SqlCeDataReader sqlReader = null; System.Data.SqlServerCe.SqlCeDataReader sqlReader2 = null; try { int comm_maxlen = DB.DB_GetMaxLength("Games", "Comment"); // Склонировать (используя транзакцию) sqlQuery = DB.CreateQuery(); trans = DB.sqlConnection.BeginTransaction(); sqlQuery.Transaction = trans; sqlQuery.CommandText = "INSERT INTO Games(Type, GameOptions, DealsInMatch, FirstDealer, ZoneSwims, fk_Folder_id, fk_N, fk_S, fk_E, fk_W, Place, Comment, StartDate) (SELECT Type, GameOptions, DealsInMatch, FirstDealer, ZoneSwims, fk_Folder_id, fk_N, fk_S, fk_E, fk_W, Place, (CASE WHEN Comment is NULL THEN '{клон}' ELSE (SUBSTRING(Comment, 1, (" + comm_maxlen + " - LEN(' {клон}'))) + ' {клон}') END) as Comment_clon, StartDate FROM Games WHERE id=" + gameId + ")"; if (sqlQuery.ExecuteNonQuery() == 0) { trans.Rollback(); MessageBox.Show("Игра #" + gameId + " не была склонирована!", "db error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1); return(false); } int new_gameId = DB.GetLastInsertId(trans); sqlQuery.CommandText = "SELECT id FROM Matches WHERE fk_Game_id=" + gameId + " ORDER BY id"; sqlReader = sqlQuery.ExecuteReader(); while (sqlReader.Read()) { int cur_matchId = sqlReader.GetInt32(0); sqlQuery.CommandText = "INSERT INTO Matches(fk_Game_id, SCORE_NS, SCORE_EW) (SELECT " + new_gameId + " as fk_Game_id__new, SCORE_NS, SCORE_EW FROM Matches WHERE id=" + cur_matchId + ")"; if (sqlQuery.ExecuteNonQuery() == 0) { sqlReader.Close(); trans.Rollback(); MessageBox.Show("Игра #" + gameId + " не была склонирована!\nОшибка клонирования матча #" + cur_matchId + "!", "db error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1); return(false); } int new_cur_matchId = DB.GetLastInsertId(trans); /****int added = 0; * sqlQuery.CommandText = "INSERT INTO Deals_Rob(fk_Match_id, Pair, Contract, Oners, Result, CardsDistribution) (SELECT " + new_cur_matchId + " as fk_Match_id__new, Pair, Contract, Oners, Result, CardsDistribution FROM Deals_Rob WHERE fk_Match_id=" + cur_matchId + ")"; * added = sqlQuery.ExecuteNonQuery(); * * sqlQuery.CommandText = "INSERT INTO Deals_Sport(fk_Match_id, Pair, Contract, Result, CardsDistribution, Figures, Fits, StrongestPair) (SELECT " + new_cur_matchId + " as fk_Match_id__new, Pair, Contract, Result, CardsDistribution, Figures, Fits, StrongestPair FROM Deals_Sport WHERE fk_Match_id=" + cur_matchId + ")"; * added = sqlQuery.ExecuteNonQuery(); * * sqlQuery.CommandText = "INSERT INTO Deals_Double(fk_Match_id, CardsDistribution, Pair1, Contract1, Result1, Pair2, Contract2, Result2, IsSecondStarted) (SELECT " + new_cur_matchId + " as fk_Match_id__new, CardsDistribution, Pair1, Contract1, Result1, Pair2, Contract2, Result2, IsSecondStarted FROM Deals_Double WHERE fk_Match_id=" + cur_matchId + ")"; * added = sqlQuery.ExecuteNonQuery();*****/ int cur_dealId = -1; sqlQuery.CommandText = "SELECT id FROM Deals_Rob WHERE fk_Match_id=" + cur_matchId + " ORDER BY id"; sqlReader2 = sqlQuery.ExecuteReader(); while (sqlReader2.Read()) { cur_dealId = (int)sqlReader2.GetSqlInt32(0); sqlQuery.CommandText = "INSERT INTO Deals_Rob(fk_Match_id, Pair, Contract, Oners, Result, CardsDistribution) (SELECT " + new_cur_matchId + " as fk_Match_id__new, Pair, Contract, Oners, Result, CardsDistribution FROM Deals_Rob WHERE id=" + cur_dealId + ")"; if (sqlQuery.ExecuteNonQuery() == 0) { sqlReader2.Close(); sqlReader.Close(); trans.Rollback(); MessageBox.Show("Игра #" + gameId + " не была склонирована!\nОшибка клонирования сдачи #" + cur_dealId + " матча #" + cur_matchId + "!", "db error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1); return(false); } } sqlReader2.Close(); sqlQuery.CommandText = "SELECT id FROM Deals_Sport WHERE fk_Match_id=" + cur_matchId + " ORDER BY id"; sqlReader2 = sqlQuery.ExecuteReader(); while (sqlReader2.Read()) { cur_dealId = (int)sqlReader2.GetSqlInt32(0); sqlQuery.CommandText = "INSERT INTO Deals_Sport(fk_Match_id, Pair, Contract, Result, CardsDistribution, Figures, Fits, StrongestPair) (SELECT " + new_cur_matchId + " as fk_Match_id__new, Pair, Contract, Result, CardsDistribution, Figures, Fits, StrongestPair FROM Deals_Sport WHERE id=" + cur_dealId + ")"; if (sqlQuery.ExecuteNonQuery() == 0) { sqlReader2.Close(); sqlReader.Close(); trans.Rollback(); MessageBox.Show("Игра #" + gameId + " не была склонирована!\nОшибка клонирования сдачи #" + cur_dealId + " матча #" + cur_matchId + "!", "db error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1); return(false); } } sqlReader2.Close(); sqlQuery.CommandText = "SELECT id FROM Deals_Double WHERE fk_Match_id=" + cur_matchId + " ORDER BY id"; sqlReader2 = sqlQuery.ExecuteReader(); while (sqlReader2.Read()) { cur_dealId = (int)sqlReader2.GetSqlInt32(0); sqlQuery.CommandText = "INSERT INTO Deals_Double(fk_Match_id, CardsDistribution, Pair1, Contract1, Result1, Pair2, Contract2, Result2, IsSecondStarted) (SELECT " + new_cur_matchId + " as fk_Match_id__new, CardsDistribution, Pair1, Contract1, Result1, Pair2, Contract2, Result2, IsSecondStarted FROM Deals_Double WHERE id=" + cur_dealId + ")"; if (sqlQuery.ExecuteNonQuery() == 0) { sqlReader2.Close(); sqlReader.Close(); trans.Rollback(); MessageBox.Show("Игра #" + gameId + " не была склонирована!\nОшибка клонирования сдачи #" + cur_dealId + " матча #" + cur_matchId + "!", "db error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1); return(false); } } sqlReader2.Close(); } sqlReader.Close(); // Добавить в список sqlQuery.CommandText = "SELECT g.id, g.StartDate, g.Place, g.Comment, g.Type, g.DealsInMatch, g.GameOptions, n.Name as N, s.Name as S, e.Name as E, w.Name as W FROM Games g LEFT JOIN Players n ON g.fk_N = n.id LEFT JOIN Players s ON g.fk_S = s.id LEFT JOIN Players e ON g.fk_E = e.id LEFT JOIN Players w ON g.fk_W = w.id WHERE g.id=" + new_gameId; sqlReader = sqlQuery.ExecuteReader(); if (sqlReader.Read()) { Load1Game(null, sqlReader); sqlReader.Close(); trans.Commit(); LoadFoldersToCombo(true); // перезагрузить список папок MessageBox.Show("Игра #" + gameId + " успешно склонирована в #" + new_gameId, "Клонирование успешно", MessageBoxButtons.OK, MessageBoxIcon.Asterisk, MessageBoxDefaultButton.Button1); return(true); } else { sqlReader.Close(); trans.Rollback(); MessageBox.Show("Игра #" + gameId + " не была склонирована в #" + new_gameId, "Клонирование неудачно", MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1); return(false); } } catch (System.Data.SqlServerCe.SqlCeException ex) { if (trans != null) { DB.SafeTransRollback(trans); } if (sqlReader != null && sqlReader.IsClosed == false) { sqlReader.Close(); } if (sqlReader2 != null && sqlReader2.IsClosed == false) { sqlReader2.Close(); } if (ex.NativeError == 0) { return(false); } else { throw; } } }
// Редактирование игры void ContextMenu_OnClickEdit(object sender, EventArgs e) { ListViewItem lvi = m_listview_Games.Items[m_listview_Games.SelectedIndices[0]]; int gameId = (int)lvi.Tag; // Открыть окно редактирования GameOptionsForm_Common form = new GameOptionsForm_Common(true, gameId); if (form.ShowDialog() == DialogResult.OK && form.done2steps == true) { // Конечные id папки и игроков (-1 для NULL, если нет то создать) int folder_id = DB.DB_GetAttributeId(form.Folder_Id, form.Folder_Name, "Folders", "id", "Name"); int n_id = DB.DB_GetAttributeId(form.N_Id, form.N_Name, "Players", "id", "Name"); int s_id = DB.DB_GetAttributeId(form.S_Id, form.S_Name, "Players", "id", "Name"); int e_id = DB.DB_GetAttributeId(form.E_Id, form.E_Name, "Players", "id", "Name"); int w_id = DB.DB_GetAttributeId(form.W_Id, form.W_Name, "Players", "id", "Name"); // Обрезанные строки места и комментария string place__cut = DB.DB_GetCuttedString(form.Place, "Games", "Place"); string comment__cut = DB.DB_GetCuttedString(form.Comment, "Games", "Comment"); // Редактировать в БД System.Data.SqlServerCe.SqlCeCommand sqlQuery = DB.CreateQuery(); sqlQuery.CommandText = "UPDATE Games SET fk_Folder_id = @folder, fk_N = @n, fk_S = @s, fk_E = @e, fk_W = @w, Place = @place, Comment = @comment WHERE id=" + gameId; sqlQuery.Parameters.Add("folder", (folder_id != -1 ? (object)folder_id : (object)DBNull.Value)); sqlQuery.Parameters.Add("n", (n_id != -1 ? (object)n_id : (object)DBNull.Value)); sqlQuery.Parameters.Add("s", (s_id != -1 ? (object)s_id : (object)DBNull.Value)); sqlQuery.Parameters.Add("e", (e_id != -1 ? (object)e_id : (object)DBNull.Value)); sqlQuery.Parameters.Add("w", (w_id != -1 ? (object)w_id : (object)DBNull.Value)); sqlQuery.Parameters.Add("place", (place__cut.Length > 0 ? (object)place__cut : (object)DBNull.Value)); sqlQuery.Parameters.Add("comment", (comment__cut.Length > 0 ? (object)comment__cut : (object)DBNull.Value)); sqlQuery.Prepare(); while (DB.ExecuteNonQuery(sqlQuery, true) == 0) { if (MessageBox.Show("Игра " + gameId + " не была изменена!", "db error", MessageBoxButtons.RetryCancel, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button2) == DialogResult.Retry) { DB.sqlConnection.Close(); DB.sqlConnection.Open(); } else { return; } } // Если изменилась папка, убрать из списка; иначе обновить запись об игре в списке if (SELECTOR_FOLDER__is_enabled && folder_id != SELECTOR_FOLDER) { // Убрать из списка m_listview_Games.Items.RemoveAt(m_listview_Games.SelectedIndices[0]); // Перезагрузить список папок LoadFoldersToCombo(true); } else { // Обновить запись об игре в списке sqlQuery = DB.CreateQuery(); sqlQuery.CommandText = "SELECT g.id, g.StartDate, g.Place, g.Comment, g.Type, g.DealsInMatch, g.GameOptions, n.Name as N, s.Name as S, e.Name as E, w.Name as W FROM Games g LEFT JOIN Players n ON g.fk_N = n.id LEFT JOIN Players s ON g.fk_S = s.id LEFT JOIN Players e ON g.fk_E = e.id LEFT JOIN Players w ON g.fk_W = w.id WHERE g.id=" + gameId; System.Data.SqlServerCe.SqlCeDataReader sqlReader = DB.ExecuteReader(sqlQuery); if (sqlReader.Read()) { Load1Game(lvi, sqlReader); } sqlReader.Close(); } } }
//Загрузка комбы папок void LoadFoldersToCombo(bool reload) { // Сначала очистить: m_combo_Folders.Items.Clear(); // Теперь загрузить: //1. NULL System.Data.SqlServerCe.SqlCeCommand sqlQuery = DB.CreateQuery(); sqlQuery.CommandText = "SELECT COUNT(id) FROM Games WHERE fk_Folder_id is NULL"; object o = DB.ExecuteScalar(sqlQuery); int fid = -1; string fname = "- нет папки -"; if (o != null && o != DBNull.Value) { fname += " (" + (int)o + ")"; } int index = m_combo_Folders.Items.Add(new comboitem_id_name(fid, fname)); // Выбрать: if (SELECTOR_FOLDER__is_enabled && SELECTOR_FOLDER == fid) { if (reload) { dont_react_on_folder_selection = true; } m_combo_Folders.SelectedIndex = index; if (reload) { dont_react_on_folder_selection = false; } } // 2. остальные sqlQuery = DB.CreateQuery(); sqlQuery.CommandText = "SELECT f.id as [fid], f.Name as [fname], COUNT(g.id) as [fcount] FROM Folders f LEFT JOIN Games g ON f.id=g.fk_Folder_id GROUP BY f.id, f.Name ORDER BY [fname]"; System.Data.SqlServerCe.SqlCeDataReader sqlReader = DB.ExecuteReader(sqlQuery); while (sqlReader.Read()) { fid = sqlReader.IsDBNull(sqlReader.GetOrdinal("fid")) ? -1 : sqlReader.GetInt32(sqlReader.GetOrdinal("fid")); fname = ""; if (fid == -1) { fname = "- нет папки -"; } if (!sqlReader.IsDBNull(sqlReader.GetOrdinal("fname"))) { fname = sqlReader.GetString(sqlReader.GetOrdinal("fname")); } fname += " (" + (sqlReader.IsDBNull(sqlReader.GetOrdinal("fcount")) ? 0 : sqlReader.GetInt32(sqlReader.GetOrdinal("fcount"))) + ")"; index = m_combo_Folders.Items.Add(new comboitem_id_name(fid, fname)); // Выбрать: if (SELECTOR_FOLDER__is_enabled && SELECTOR_FOLDER == fid) { if (reload) { dont_react_on_folder_selection = true; } m_combo_Folders.SelectedIndex = index; if (reload) { dont_react_on_folder_selection = false; } } } sqlReader.Close(); }