public void SeparateTable(TableIniT aTable, string aPrimaryKey, string aWhereSelect, ToolStripProgressBar aPB1, MySqlConnection aConnHost, MySqlConnection aConnDesc) { string lsColumnName = "", lsData = "", lsDataType = "", lsInsertInto = "", lsDataInto = "", lsSQL = "", lsPrimaryKeyData = "", lsExeCData = "", lsPrimaryKeyData2 = ""; Int32 liCount = 0; if (aWhereSelect == "") { lsSQL = "Select count(*) as cnt From " + aTable.ToString().ToLower(); } else { lsSQL = "Select count(*) as cnt From " + aTable.ToString().ToLower() + " Where " + aWhereSelect; } MySqlCommand lsCommData = new MySqlCommand(lsSQL, aConnHost); MySqlDataReader lsReadData; lsReadData = lsCommData.ExecuteReader(); if (lsReadData.HasRows) { while (lsReadData.Read()) { liCount = Convert.ToInt32(lsReadData["cnt"]); } } lsReadData.Close(); aPB1.Minimum = 0; aPB1.Maximum = liCount; aPB1.Visible = true; if (aWhereSelect == "") { lsSQL = "Select * From " + aTable.ToString().ToLower(); } else { lsSQL = "Select * From " + aTable.ToString().ToLower() + " Where " + aWhereSelect; } MySqlCommand lsCommExeC = new MySqlCommand(); lsCommExeC.Connection = aConnDesc; lsCommData.CommandText = lsSQL; lsReadData = lsCommData.ExecuteReader(); if (lsReadData.HasRows) { Int32 i = 0, j = 0; while (lsReadData.Read()) { try { lsInsertInto = "Insert Into " + aTable.ToString().ToLower() + "("; lsDataInto = " Values ("; for (Int32 k = 0; k <= lsReadData.FieldCount - 1; k++) { lsColumnName = lsReadData.GetName(k).ToString().ToLower(); lsInsertInto = lsInsertInto + " " + lsColumnName + ","; lsDataType = lsReadData.GetFieldType(k).ToString().ToLower(); lsData = lsReadData[lsColumnName].ToString(); if (lsColumnName == aPrimaryKey) { lsPrimaryKeyData = lsData; } switch (lsDataType) { case "system.string": { lsData = lsData.Replace("'", "''"); lsDataInto = lsDataInto + " '" + lsData + "',"; break; } case "system.datetime": { if (lsData == "") { lsData = "null"; lsDataInto = lsDataInto + " " + lsData + ","; } else { lsDataInto = lsDataInto + " '" + lsData + "',"; } break; } case "system.double": { if (lsData == "") { lsData = "0"; lsDataInto = lsDataInto + " " + lsData + ","; } else { lsDataInto = lsDataInto + " " + lsData + ","; } break; } case "system.decimal": { if (lsData == "") { lsData = "0"; lsDataInto = lsDataInto + " " + lsData + ","; } else { lsDataInto = lsDataInto + " " + lsData + ","; } break; } case "system.int16": { if (lsData == "") { lsData = "0"; lsDataInto = lsDataInto + " " + lsData + ","; } else { lsDataInto = lsDataInto + " " + lsData + ","; } break; } case "system.int32": { if (lsData == "") { lsData = "0"; lsDataInto = lsDataInto + " " + lsData + ","; } else { lsDataInto = lsDataInto + " " + lsData + ","; } break; } case "system.uint32": { if (lsData == "") { lsData = "0"; lsDataInto = lsDataInto + " " + lsData + ","; } else { lsDataInto = lsDataInto + " " + lsData + ","; } break; } default: { lsDataInto = lsDataInto + " " + lsData + ","; break; } } } lsInsertInto = lsInsertInto.Substring(0, lsInsertInto.Length - 1) + ") "; lsDataInto = lsDataInto.Substring(0, lsDataInto.Length - 1) + ") "; lsExeCData = lsInsertInto + lsDataInto; lsSQL = "Delete From " + aTable.ToString().ToLower() + " Where " + aPrimaryKey + "='" + lsPrimaryKeyData + "'"; lsCommExeC.CommandText = lsSQL; lsCommExeC.ExecuteNonQuery(); lsCommExeC.CommandText = lsExeCData; lsCommExeC.ExecuteNonQuery(); i++; aPB1.Value = i; } catch (Exception e) { string ls = "ไม่สามารถ SeparateTable ได้ "; WriteLogError(ls, e, "", "SeparateTable"); MessageBox.Show(ls + " " + e.Message.ToString(), e.Source.ToString()); } } } aPB1.Visible = false; lsReadData.Close(); }
public ComboBox SelectCbo(ComboBox Cbo, string lsWhereCode, TableIniT aFlag) { string lsSQL = "", ls = ""; if (aFlag == TableIniT.YearName) { Int32 i = 2546; ArrayList lsList = new ArrayList(); for (i = 2546; i <= System.DateTime.Now.Year + 543; i++) { lsList.Add(new CboState(i.ToString(), i.ToString())); } Cbo.DataSource = lsList; Cbo.DisplayMember = "LongName"; Cbo.ValueMember = "ShortName"; Cbo.Text = ""; } else if (Cbo.Name.ToString() == TableIniT.CboLinkPicture.ToString()) { ArrayList lsList1 = new ArrayList(); lsSQL = "Select id, name From cboselect Where cboname = '" + Cbo.Name + "' Order by name"; MySqlCommand lsComm1 = new MySqlCommand(lsSQL, Gdb); MySqlDataReader lsReader1; lsReader1 = lsComm1.ExecuteReader(); if (lsReader1.HasRows) { while (lsReader1.Read()) { //Cbo.Items.Add(lsReader.GetValue(1).ToString()); lsList1.Add(new CboState(lsReader1.GetValue(0).ToString().Trim(), lsReader1.GetValue(1).ToString().Trim())); //lsSQL = lsReader.GetValue(1).ToString(); } } lsReader1.Close(); lsSQL = "Select m.plcode, t.plnamee From typeroom t, memberpricelist m " + "Where m.memid = '" + lsWhereCode + "' and m.plcode = t.plcode Order by plnamee"; MySqlCommand lsComm2 = new MySqlCommand(lsSQL, Gdb); MySqlDataReader lsReader2; lsReader2 = lsComm2.ExecuteReader(); if (lsReader2.HasRows) { while (lsReader2.Read()) { //Cbo.Items.Add(lsReader.GetValue(1).ToString()); lsList1.Add(new CboState(lsReader2.GetValue(0).ToString().Trim(), lsReader2.GetValue(1).ToString().Trim())); //lsSQL = lsReader.GetValue(1).ToString(); } } if (aFlag == TableIniT.TypeRoom) { lsList1.Add(new CboState("-", "-")); } Cbo.DataSource = lsList1; Cbo.DisplayMember = "LongName"; Cbo.ValueMember = "ShortName"; Cbo.Text = ""; lsReader2.Close(); } else { string lsWhere = ""; switch (aFlag) { case TableIniT.Nationality: lsSQL = "Select nationcode, nationname From nationality Order By nationname"; break; case TableIniT.SubDistrict: lsSQL = "Select s.subdistrictcode, concat(s.subdistrictnamet, ' / ', d.districtnamet, ' / ' , p.provnamet) From subdistrict s, district d, province1 p Where s.subdistrictnamet like '" + lsWhereCode + "%' and s.districtcode = d.districtcode and s.provcode = p.provcode order by p.provnamet, d.districtnamet"; break; case TableIniT.CboDistrictFromProvCode: lsSQL = "Select districtcode, districtnamet From district Where provcode = '" + lsWhereCode + "'"; break; case TableIniT.CboDistrictFromSubDistrict: lsSQL = "Select d.districtcode, d.districtnamet From district d Where districtcode = (Select districtcode From subdistrict Where subdistrictcode = '" + lsWhereCode + "') "; break; case TableIniT.District: { lsSQL = "Select districtcode, districtnamet From district Where provcode = '" + lsWhereCode + "' Order by districtnamet"; break; } case TableIniT.CboSubDistrictFromDistrict: { lsSQL = "Select subdistrictcode, subdistrictnamet From subdistrict Where districtcode = '" + lsWhereCode + "' Order by subdistrictnamet"; break; } case TableIniT.Province: { lsSQL = "Select provcode, provname From province Order by provname"; break; } case TableIniT.Member: if (lsWhereCode == "") { lsWhere = "1 = 1"; } else { lsWhere = " provcode = '" + lsWhereCode + "'"; } lsSQL = "Select memid, memnamee1 From member Where " + lsWhere + " and flag = '1' and flagsale = '1' Order by memnamee1"; break; case TableIniT.MemberFromRegCode: lsSQL = "Select regisid, nameeng From regis Where regcode = '" + lsWhereCode + "' Order by nameeng"; break; case TableIniT.Shift: lsSQL = "Select shiftcode, shiftname From shift Order by shiftcode"; break; case TableIniT.TypeRoom: if (lsWhereCode == "") { //lsSQL = "Select plcode, plnamee From typeroom Order by plnamee"; lsSQL = "Select m.plcode, t.plnamee From typeroom t, memberpricelist m " + "Where m.memid = '" + lsWhereCode + "' and m.plcode = t.plcode Order by plnamee"; } else { lsSQL = "Select m.plcode, t.plnamee From typeroom t, memberpricelist m " + "Where m.memid = '" + lsWhereCode + "' and m.plcode = t.plcode Order by plnamee"; } break; case TableIniT.Region: lsSQL = "Select regioncode, regionname From region Order by sort1"; break; case TableIniT.Staff: lsSQL = "Select staffcode, staffname From staff Order by staffname"; break; case TableIniT.Status: lsSQL = "Select statuscode, statusname From status Where flag = '1' Order by statusname"; break; case TableIniT.HotelChain: lsSQL = "Select hotelchaincode, hotelchainname From hotelchain Order by hotelchainname"; break; case TableIniT.TypeMem: lsSQL = "Select tmemcode, tmemname From typemem Order by tmemname"; break; case TableIniT.TypeBis: lsSQL = "Select tbiscode, tbisname From typebis Order by tbisname"; break; case TableIniT.GreenLeft: lsSQL = "Select greencode, greenname From greenleft Order by greenname"; break; case TableIniT.Location: lsSQL = "Select locationcode, locationnamet From location Order by locationnamet"; break; case TableIniT.MemberOwner: lsSQL = "Select distinct ownerid, ownernamet From memberowner Order by ownernamet"; break; case TableIniT.Star: lsSQL = "Select starcode, starname From star Order by starname"; break; case TableIniT.CboVoucherView: lsSQL = "Select id, name From cboselect Where cboname = '" + lsWhereCode + "' Order by name"; break; case TableIniT.CboPrefix: lsSQL = "Select id, name From cboselect Where cboname = '" + lsWhereCode + "' Order by name"; break; case TableIniT.CboMemViewFilter: lsSQL = "Select id, name From cboselect Where cboname = '" + lsWhereCode + "' Order by name"; break; case TableIniT.CboShopView: lsSQL = "Select id, name From cboselect Where cboname = '" + lsWhereCode + "' Order by name"; break; case TableIniT.CboTSend: lsSQL = "Select id, name From cboselect Where cboname = '" + lsWhereCode + "' Order by name"; break; case TableIniT.CboLinkPicture: lsSQL = "Select id, name From cboselect Where cboname = '" + lsWhereCode + "' Order by name"; break; case TableIniT.CboTypePayment: lsSQL = "Select tpaycode, tpaynamee From typepayment Order by tpaynamee"; break; case TableIniT.CboAddress: lsSQL = "Select id, name From cboselect Where cboname = 'cboaddress' Order by name"; break; case TableIniT.MonthName: lsSQL = "Select monthid, monthnamee From monthname "; break; case TableIniT.MemberStatus: lsSQL = "Select id, name From cboselect Where cboname = 'cbomemberstatus' Order by id"; break; case TableIniT.Counter: if (lsWhereCode == "ip") { lsSQL = "Select ip, countername From counter Where flag = '1' Order By counterid"; } else if (lsWhereCode == "report") { lsSQL = "Select ip, countername From counter Order By counterid"; } else { lsSQL = "Select counterid, countername From counter Where flag = '1' Order By counterid"; } break; } //lsSQL = "Select nationcode, nationname From nationality "; MySqlCommand lsComm = new MySqlCommand(lsSQL, Gdb); MySqlDataReader lsReader; lsReader = lsComm.ExecuteReader(); if (lsReader.HasRows) { ArrayList lsList = new ArrayList(); if (aFlag == TableIniT.TypeRoom) { lsList.Add(new CboState("-", "-")); } else if (aFlag == TableIniT.CboDistrictFromSubDistrict) { lsList.Add(new CboState("-", "-")); } while (lsReader.Read()) { //Cbo.Items.Add(lsReader.GetValue(1).ToString()); ls = lsReader.GetValue(1).ToString().Trim(); lsList.Add(new CboState(lsReader.GetValue(0).ToString().Trim(), lsReader.GetValue(1).ToString().Trim())); //lsSQL = lsReader.GetValue(1).ToString(); } Cbo.DataSource = lsList; Cbo.DisplayMember = "LongName"; Cbo.ValueMember = "ShortName"; Cbo.Text = ""; } lsReader.Close(); switch (aFlag) { case TableIniT.CboDistrictFromSubDistrict: { //lsSQL = Cbo.SelectedValue.ToString(); //ls = Cbo.SelectedItem.ToString(); Cbo.SelectedValue = ls; //Cbo.SelectedItem = ls; //lsSQL = Cbo.SelectedText; break; } } } return(Cbo); }
public string SelectCboProvDistrSubDistr(ComboBox CboProv, ComboBox CboDistr, ComboBox CboSubDistr, TextBox TxtPostCode, string lsWhereCode, TableIniT aFlag) { Boolean lbFlagSubDistr = false; string lsSQL = "", ls = "", lsReturn = "", lsProv = "", lsSubDistr = ""; switch (aFlag) { case TableIniT.SubDistrict: lsSQL = "Select s.subdistrictcode, concat(s.subdistrictnamet, ' / ', d.districtnamet, ' / ' , p.provnamet) From subdistrict s, district d, province p Where s.subdistrictnamet like '" + lsWhereCode + "%' and s.districtcode = d.districtcode and s.provcode = p.provcode order by p.provnamet, d.districtnamet"; break; case TableIniT.CboDistrictFromSubDistrict: lsSQL = "Select d.districtcode, d.districtnamet, p.provcode, p.provnamet, pc.postcode, s.subdistrictcode, s.subdistrictnamet, p.provnamee, d.districtnamee, s.subdistrictnamee From district d, province p, subdistrict s , postcode pc Where d.districtcode = (Select districtcode From subdistrict Where subdistrictcode = '" + lsWhereCode + "') and d.provcode = p.provcode and pc.subdistrictcode = s.subdistrictcode and s.districtcode = d.districtcode and s.subdistrictcode = '" + lsWhereCode + "'"; break; } MySqlCommand lsComm = new MySqlCommand(lsSQL, Gdb); MySqlDataReader lsReader; lsReader = lsComm.ExecuteReader(); if (lsReader.HasRows) { ArrayList lsListSubDistr = new ArrayList(); ArrayList lsListDistr = new ArrayList(); ArrayList lsListProv = new ArrayList(); if (aFlag == TableIniT.TypeRoom) { lsListDistr.Add(new CboState("-", "-")); } else if (aFlag == TableIniT.CboDistrictFromSubDistrict) { lsListSubDistr.Add(new CboState("-", "-")); lsListDistr.Add(new CboState("-", "-")); lsListProv.Add(new CboState("-", "-")); } while (lsReader.Read()) { ls = lsReader.GetValue(0).ToString().Trim(); lsProv = lsReader.GetValue(2).ToString().Trim(); lsSubDistr = lsReader.GetValue(4).ToString().Trim(); lsListSubDistr.Add(new CboState(lsReader.GetValue(5).ToString().Trim(), lsReader.GetValue(6).ToString().Trim())); lsListDistr.Add(new CboState(lsReader.GetValue(0).ToString().Trim(), lsReader.GetValue(1).ToString().Trim())); lsListProv.Add(new CboState(lsReader.GetValue(2).ToString().Trim(), lsReader.GetValue(3).ToString().Trim())); TxtPostCode.Text = lsReader.GetValue(4).ToString().Trim(); lsSubDistrictNameE = lsReader["subdistrictnamee"].ToString(); lsDistrictNameE = lsReader["districtnamee"].ToString(); lsProvNameE = lsReader["provnamee"].ToString(); } if (CboSubDistr.DataSource == null) { CboSubDistr.DataSource = lsListSubDistr; CboSubDistr.DisplayMember = "LongName"; CboSubDistr.ValueMember = "ShortName"; CboSubDistr.Text = ""; lbFlagSubDistr = true; } CboDistr.DataSource = lsListDistr; CboDistr.DisplayMember = "LongName"; CboDistr.ValueMember = "ShortName"; CboDistr.Text = ""; CboProv.DataSource = lsListProv; CboProv.DisplayMember = "LongName"; CboProv.ValueMember = "ShortName"; CboProv.Text = ""; } lsReader.Close(); switch (aFlag) { case TableIniT.CboDistrictFromSubDistrict: { CboDistr.SelectedValue = ls; CboProv.SelectedValue = lsProv; if (lbFlagSubDistr == true) { CboSubDistr.SelectedValue = lsSubDistr; } break; } } return(lsReturn); }