public List <Teilstrecke> getAllTeilstrecken() { string comm = "select * from teilstrecke"; OleDbCommand command = new OleDbCommand(comm, this.connection); OleDbDataReader reader = command.ExecuteReader(); List <Teilstrecke> result = new List <Teilstrecke>(); if (reader.HasRows) { while (reader.Read()) { result.Add(new Teilstrecke(reader.GetString(0), new Point(decimal.ToInt32(reader.GetDecimal(1)), decimal.ToInt32(reader.GetDecimal(2))), new Point(decimal.ToInt32(reader.GetDecimal(3)), decimal.ToInt32(reader.GetDecimal(4))))); } } return(result); }
// Get Form config data from DB // This reads all the Form config data from DB public void GetFormConfigs() { // Number of Buttons on current Form int numButtons; // DB objects OleDbConnection conn = null; OleDbCommand comm = null; string sqlStr; // Read all Forms from DB try { conn = GetConnection(); conn.Open(); sqlStr = "SELECT fldID, fldFrmTitle, fldFrmImage, fldFrmType " + "FROM tblForms"; comm = new OleDbCommand(sqlStr, conn); OleDbDataReader reader = comm.ExecuteReader( System.Data.CommandBehavior.SingleResult); while (reader.Read()) { // Use FormConfig constructor to instantiate Form FormConfig cf = new FormConfig(reader.GetInt32(0), reader.GetString(1), reader.GetString(2), reader.GetString(3), imgDir); // Add form to this List this.Add(cf); } } catch (Exception ex) { //MessageBox.Show("Exception: " + ex.ToString()); } finally { if (conn != null) { conn.Close(); } } // Add Bottons to Forms for (int i = 0; i < this.Count; i++) { // Init num Buttons to zero numButtons = 0; // The first type of Form has Buttons that cause some action when clicked if (this[i].frmType == "typFrmButton") { // Instantiate a ButtonConfig array with max rows x max cols elements this[i].btnConfig = new ButtonConfig[this[i].maxBtnRows * this[i].maxBtnCols]; // Instantiate a LabelConfig array with max rows x max cols elements this[i].lblConfig = new LabelConfig[this[i].maxBtnRows * this[i].maxBtnCols]; // Read Buttons for this Form from DB try { conn = GetConnection(); conn.Open(); sqlStr = "SELECT fldBtnCfgImage, fldBtnCfgOper, fldBtnCfgTarget, fldFrmOpenIdx, fldID, fldPrice, fldName " + "FROM tblButtons " + "WHERE fldFormID = " + this[i].frmIdx; //MessageBox.Show(sqlStr); comm = new OleDbCommand(sqlStr, conn); OleDbDataReader reader = comm.ExecuteReader( System.Data.CommandBehavior.SingleResult); while (reader.Read()) { // Instantiate a Button with constructor this[i].btnConfig[numButtons] = new ButtonConfig(imgDir + reader.GetString(0), reader.GetString(1), reader.GetString(2), reader.GetInt32(3), reader.GetInt32(4), reader.GetDecimal(5)); // Instantiate a Label with constructor this[i].lblConfig[numButtons] = new LabelConfig(reader.GetString(6), reader.GetString(1), reader.GetString(2), reader.GetInt32(3), reader.GetDecimal(5)); numButtons++; } // Record current Forms number of Buttons this[i].numButtons = numButtons; } catch (Exception ex) { //MessageBox.Show("Exception: " + ex.ToString()); } finally { if (conn != null) { conn.Close(); } } } else if (this[i].frmType == "typFrmTopping") { // Instantiate a ButtonConfig array with max rows x max cols elements this[i].btnConfig = new ButtonConfig[this[i].maxBtnRows * this[i].maxBtnCols]; // Instantiate a LabelConfig array with max rows x max cols elements this[i].lblConfig = new LabelConfig[this[i].maxBtnRows * this[i].maxBtnCols]; // Read Buttons for this Form from DB try { conn = GetConnection(); conn.Open(); sqlStr = "SELECT fldBtnCfgImage, fldBtnCfgOper, fldBtnCfgTarget, fldFrmOpenIdx, fldID, fldPrice, fldName " + "FROM tblToppings " + "WHERE fldFormID = " + this[i].frmIdx; //MessageBox.Show(sqlStr); comm = new OleDbCommand(sqlStr, conn); OleDbDataReader reader = comm.ExecuteReader( System.Data.CommandBehavior.SingleResult); while (reader.Read()) { // Instantiate a Button with constructor this[i].btnConfig[numButtons] = new ButtonConfig(imgDir + reader.GetString(0), reader.GetString(1), reader.GetString(2), reader.GetInt32(3), reader.GetInt32(4), reader.GetDecimal(5), reader.GetString(6)); // Instantiate a Label with constructor this[i].lblConfig[numButtons] = new LabelConfig(reader.GetString(6), reader.GetString(1), reader.GetString(2), reader.GetInt32(3), reader.GetDecimal(5)); numButtons++; } // Record current Forms number of Buttons this[i].numButtons = numButtons; } catch (Exception ex) { //MessageBox.Show("Exception: " + ex.ToString()); } finally { if (conn != null) { conn.Close(); } } } } }
//this is called to find a person in the database //if found, it fills in the textboxes on the form with the appropriate info //returns true if a match was found public bool SelectOwlMember(int OwlMemberID, frmMainForm f) { int found = 0; using (OleDbConnection connection = new OleDbConnection(strConnection)) { try { connection.Open(); using (OleDbCommand command1 = new OleDbCommand("SELECT * FROM OWLMEMBER WHERE fldID = " + OwlMemberID, connection)) { OleDbDataReader reader = command1.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) { f.gbOwlMember.BackColor = Color.LimeGreen; f.txtID.Enabled = false; f.txtID.Text = Convert.ToString(reader.GetInt32(0)); f.txtName.Text = reader.GetString(1); f.dtBday.Value = reader.GetDateTime(2); } found++; } } using (OleDbCommand command2 = new OleDbCommand("SELECT * FROM STUDENT WHERE fldID = " + OwlMemberID, connection)) { OleDbDataReader reader = command2.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) { f.gbStudent.BackColor = Color.LimeGreen; f.txtMajor.Text = reader.GetString(1); f.txtGPA.Text = reader.GetDecimal(2).ToString(); } found++; } } using (OleDbCommand command3 = new OleDbCommand("SELECT * FROM UNDERGRADUATESTUDENT WHERE fldID = " + OwlMemberID, connection)) { OleDbDataReader reader = command3.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) { f.gbUndergrad.BackColor = Color.LimeGreen; f.txtID.Text = Convert.ToString(reader.GetInt32(0)); f.txtTuition.Text = Convert.ToString(reader.GetDecimal(1)); f.txtCredits.Text = Convert.ToString(reader.GetValue(3)); f.menuYear.Text = reader.GetValue(2).ToString(); } found++; } } using (OleDbCommand command4 = new OleDbCommand("SELECT * FROM GRADUATESTUDENT WHERE fldID = " + OwlMemberID, connection)) { OleDbDataReader reader = command4.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) { f.gbGrad.BackColor = Color.LimeGreen; f.menuDegree.Text = reader.GetString(1); f.txtGradStipend.Text = reader.GetDecimal(2).ToString(); } found++; } } using (OleDbCommand command5 = new OleDbCommand("SELECT * FROM FACULTY WHERE fldID = " + OwlMemberID, connection)) { OleDbDataReader reader = command5.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) { f.gbFaculty.BackColor = Color.LimeGreen; f.txtDepartment.Text = reader.GetString(1); f.menuRank.Text = reader.GetString(2); } found++; } } using (OleDbCommand command6 = new OleDbCommand("SELECT * FROM CHAIRPERSON WHERE fldID = " + OwlMemberID, connection)) { OleDbDataReader reader = command6.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) { f.gbChairperson.BackColor = Color.LimeGreen; f.txtChairStipend.Text = reader.GetDecimal(1).ToString(); } found++; } } connection.Close(); } catch (OleDbException ex) { Console.Write("Error: " + ex.Message); connection.Close(); } } // end using block if (found == 0) { return(false); } else { return(true); } // FormController.clear(this); }
public List <pelangganModels> GetListHutangByCompany(string kdCompany) { List <pelangganModels> dList = new List <pelangganModels>(); string strSQL = @"Select b.noang,b.nama,b.alamat,b.simwajib,b.simpanan as simpanan_tmk, a.tgl_registrasi, IIF(ISNULL(a.batas_kredit),0,a.batas_kredit) as batas_kredit, IIF(ISNULL(a.bunga),0,a.bunga) as persen_bunga, IIF(ISNULL(a.waktu),0,a.waktu) as jangka_waktu , IIF(ISNULL(a.tbunga),0,a.tbunga) as bunga_per_bulan, IIF(ISNULL(a.bunga2),0,a.bunga2) as pokok_per_bulan, IIF(ISNULL(a.pinjaman),0,a.pinjaman) as sisa_pinjaman, IIF(ISNULL(a.angsuran),0,a.angsuran) as angsuran_per_bulan FROM anggota as b LEFT JOIN (SELECT id,kota,kode,tgl_registrasi,nama,alamat,kode,batas_kredit,bunga,waktu,tbunga,bunga2,pinjaman,angsuran FROM pelanggan WHERE pinjaman > 10) as a on a.kota = b.noang WHERE b.sektor = ? ORDER BY b.nama"; string ConnStr = ManageString.GetConnStr(); using (OleDbConnection conn = new OleDbConnection(ConnStr)) { conn.Open(); using (OleDbCommand cmd = new OleDbCommand(strSQL, conn)) { cmd.CommandType = CommandType.Text; cmd.Parameters.AddWithValue("sektor", kdCompany); using (OleDbDataReader aa = cmd.ExecuteReader()) { if (aa.HasRows) { while (aa.Read()) { //string f_0 = aa.GetFieldType(0).ToString(); //string f_1 = aa.GetFieldType(1).ToString(); //string f_2 = aa.GetFieldType(2).ToString(); //string f_3 = aa.GetFieldType(3).ToString(); //string f_4 = aa.GetFieldType(4).ToString(); //string f_5 = aa.GetFieldType(5).ToString(); //string f_6 = aa.GetFieldType(6).ToString(); //string f_7 = aa.GetFieldType(7).ToString(); //string f_8 = aa.GetFieldType(8).ToString(); //string f_9 = aa.GetFieldType(9).ToString(); //string f_10 = aa.GetFieldType(10).ToString(); //string f_11 = aa.GetFieldType(11).ToString(); //string f_12 = aa.GetFieldType(12).ToString(); //Log.Debug(DateTime.Now + " F_0 : " + f_0 + "\n" + " F_1 : " + f_1 + "\n" + " F_2 : " + f_2 + "\n" + // " F_3 : " + f_3 + "\n" + " F_4 : " + f_4 + "\n" + " F_5 : " + f_5 + "\n" + // " F_6 : " + f_6 + "\n" + " F_7 : " + f_7 + "\n" + " F_8 : " + f_8 + "\n" + // " F_9 : " + f_9 + "\n" + " F_10 : " + f_10 + "\n" + " F_11 : " + f_11 + "\n" + // " F_12 : " + f_12); pelangganModels item = new pelangganModels(); item.kd_pelanggan = aa.GetString(0); item.nama = aa.GetString(1); item.alamat = aa.GetString(2); item.simpanan_wajib = Math.Round(Convert.ToDecimal(aa.GetDouble(3)), 2); item.simpanan_tmk = Math.Round(Convert.ToDecimal(aa.GetDouble(4)), 2); //item.tgl_registrasi = aa.GetDateTime(5); DateTime?dt = aa[5] as DateTime?; item.tgl_registrasi = dt; item.batas_kredit = aa.GetDecimal(6); item.persen_bunga = Math.Round(Convert.ToDecimal(aa.GetDouble(7)), 2); item.jangka_waktu = aa.GetInt32(8); item.bunga_per_bulan = Math.Round(Convert.ToDecimal(aa.GetDouble(9)), 2); item.pokok_per_bulan = Math.Round(Convert.ToDecimal(aa.GetDouble(10)), 2); item.sisa_pinjaman = Math.Round(Convert.ToDecimal(aa.GetDouble(11)), 2); item.angsuran_per_bulan = Math.Round(Convert.ToDecimal(aa.GetDouble(12)), 2); dList.Add(item); } Log.Debug(DateTime.Now + " GetPelangganREPO ====>>>>>> Jumlah LIST : " + dList.Count()); } } } } return(dList); }
public static int SendBulkSmsToClients() { int outs = 0; OleDbConnection ole_db_conn = null; OleDbCommand ole_db_comm = null; OleDbDataReader ole_db_reader = null; try { ole_db_conn = new OleDbConnection(String.Format(@"Provider=VFPOLEDB.1; Data Source={0};", Constants.DATA_PATH)); ole_db_conn.Open(); ole_db_comm = ole_db_conn.CreateCommand(); ole_db_comm.CommandText = String.Format("select * from {0} where STATUS='' or STATUS='0' or STATUS=' ' ", Constants.DBF_FILE_NAME); ole_db_reader = ole_db_comm.ExecuteReader(); if (!ole_db_reader.HasRows) { try { ole_db_reader.Close(); ole_db_conn.Close(); } catch (Exception) { } return(1); } List <ulong> rowIds = new List <ulong>(); List <String> intlNumbersWithCode = new List <string>(); List <string> messages = new List <string>(); while (ole_db_reader.Read()) { String strNumber = Convert.ToUInt64(ole_db_reader.GetDecimal(ole_db_reader.GetOrdinal("MOBILE"))).ToString(); if (strNumber.Length <= 10) { strNumber = "91" + strNumber; } intlNumbersWithCode.Add(strNumber); messages.Add(ole_db_reader.GetString(ole_db_reader.GetOrdinal("SMS"))); rowIds.Add(Convert.ToUInt64(ole_db_reader.GetDecimal(ole_db_reader.GetOrdinal("ID")))); } ole_db_reader.Close(); String requestID = Utils.SendBulkSms(intlNumbersWithCode, messages); //String requestID = ""; if (requestID == null) { throw new Exception("Could not send due to network error"); } foreach (ulong id in rowIds) { try { ole_db_comm = ole_db_conn.CreateCommand(); ole_db_comm.CommandText = String.Format("update {0} set STATUS='1' where ID={1}", Constants.DBF_FILE_NAME, id); ole_db_comm.ExecuteNonQuery(); } catch (Exception exp) { } } try { ole_db_conn.Close(); } catch (Exception exp) { } outs = 1; } catch (Exception exp) { try { if (ole_db_reader != null) { ole_db_reader.Close(); } if (ole_db_conn != null) { ole_db_conn.Close(); } } catch (Exception) { } outs = 0; Utils.Log(exp); } return(outs); }
private void button3_Click(object sender, EventArgs e) { try { REPORT_RESENDING_MODE = 1; label3.Text = "Local Report"; textBox1.Text = "0"; textBox2.Text = "0"; InitializeDataGridviewLocalReport(); button5.Enabled = false; button3.Enabled = false; button2.Enabled = false; button3.Text = "Loading...."; Thread th = new Thread(new ThreadStart(() => { try { OleDbConnection ole_db_conn = null; OleDbCommand ole_db_comm = null; OleDbDataReader ole_db_reader = null; try { ole_db_conn = new OleDbConnection(String.Format(@"Provider=VFPOLEDB.1; Data Source={0};", Constants.DATA_PATH)); ole_db_conn.Open(); ole_db_comm = ole_db_conn.CreateCommand(); ole_db_comm.CommandText = String.Format("select * from {0}", Constants.DBF_FILE_NAME); ole_db_reader = ole_db_comm.ExecuteReader(); if (!ole_db_reader.HasRows) { try { if (ole_db_reader != null) { ole_db_reader.Close(); } if (ole_db_conn != null) { ole_db_conn.Close(); } } catch (Exception) { } BeginInvoke(new MethodInvoker(() => { MessageBox.Show("No local report found", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning); button5.Enabled = false; button3.Enabled = true; button2.Enabled = true; button3.Text = "Local Report"; })); return; } int rIndex = 0; while (ole_db_reader.Read()) { try { List <String> row_data = new List <string>(); row_data.Add(Convert.ToUInt64(ole_db_reader.GetDecimal(ole_db_reader.GetOrdinal("ID"))).ToString()); row_data.Add(Convert.ToUInt64(ole_db_reader.GetDecimal(ole_db_reader.GetOrdinal("MOBILE"))).ToString()); row_data.Add(ole_db_reader.GetString(ole_db_reader.GetOrdinal("SMS"))); String status = ole_db_reader.GetString(ole_db_reader.GetOrdinal("STATUS")).Trim(); if (status.Equals("1")) { row_data.Add("Sent"); } else { row_data.Add("Not sent"); } BeginInvoke(new MethodInvoker(() => { dataGridView1.Rows.Insert(rIndex, row_data.ToArray()); setupGridView(); rIndex++; if (status.Equals("1")) { try { textBox1.Text = (Convert.ToUInt64(textBox1.Text.Trim()) + 1).ToString(); } catch (Exception) { } } else { try { textBox2.Text = (Convert.ToUInt64(textBox2.Text.Trim()) + 1).ToString(); } catch (Exception) { } } })); } catch (Exception) { } } try { if (ole_db_reader != null) { ole_db_reader.Close(); } if (ole_db_conn != null) { ole_db_conn.Close(); } } catch (Exception) { } BeginInvoke(new MethodInvoker(() => { button5.Enabled = true; button3.Enabled = true; button2.Enabled = true; button3.Text = "Local Report"; })); } catch (ThreadAbortException exp) { try { if (ole_db_reader != null) { ole_db_reader.Close(); } if (ole_db_conn != null) { ole_db_conn.Close(); } } catch (Exception) { } throw new Exception(); } catch (ThreadInterruptedException exp) { try { if (ole_db_reader != null) { ole_db_reader.Close(); } if (ole_db_conn != null) { ole_db_conn.Close(); } } catch (Exception) { } throw new Exception(); } catch (Exception exp) { try { if (ole_db_reader != null) { ole_db_reader.Close(); } if (ole_db_conn != null) { ole_db_conn.Close(); } } catch (Exception) { } BeginInvoke(new MethodInvoker(() => { MessageBox.Show("Local Report could not be loaded", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning); button5.Enabled = false; button3.Enabled = true; button2.Enabled = true; button3.Text = "Local Report"; })); Utils.Log(exp); } } catch (Exception exp) { //Nothing } })); workerThreads.Add(th); th.Start(); } catch (Exception exp) { Utils.Log(exp); } }
public DataTable retrieveInformation() { Utils utils = new Utils(); todayDate = getCurrentDate(); table = new DataTable(); table.Columns.Add("Branch", typeof(string)); table.Columns.Add("Product", typeof(string)); table.Columns.Add("Contract No", typeof(string)); table.Columns.Add("Rental Amount", typeof(string)); table.Columns.Add("Rental No", typeof(string)); table.Columns.Add("Contract peroid", typeof(string)); table.Columns.Add("Debtor balance", typeof(string)); table.Columns.Add("Default Interest", typeof(string)); table.Columns.Add("No of days Over in arrears", typeof(string)); table.Columns.Add("Customer name", typeof(string)); table.Columns.Add("Address", typeof(string)); table.Columns.Add("Marketing officer", typeof(string)); table.Columns.Add("Collector name", typeof(string)); Connection conn = new Connection(); try { Console.WriteLine("Fetching data from the DB, please wait......."); OleDbConnection oledbConnection = new OleDbConnection(); string SELECT = "SELECT " + "LEPRT.CTCODE, " + "LEPRT.PRODCT, " + "LEPRT.CNTRNO, " + "LEPRT.RENTAL, " + "LEPRT.RENSNO, " + "XEPCS.PERIOD, " + "XEPCS.CLOBAL, " + "XEPCS.NORNAR, " + "XEPCS.NAMECH, " + "XEPCS.ADDRES, " + "XEPCS.MONAME, " + "XEPCS.DCNAME " + "FROM LEPRT LEFT JOIN XEPCS ON LEPRT.CTCODE = XEPCS.CTCODE AND LEPRT.PRODCT = XEPCS.PRODCT AND LEPRT.CNTRNO = XEPCS.CNTRNO " + "WHERE LEPRT.RENDDT = " + todayDate + " AND (LEPRT.STATUS = 'A' OR LEPRT.STATUS = 'E')"; oledbConnection.ConnectionString = conn.IMAS("DC@LENLIB"); OleDbCommand myOledbCommand = new OleDbCommand(SELECT, oledbConnection); myOledbCommand.Connection.Open(); OleDbDataReader myOledbDataReader = myOledbCommand.ExecuteReader(); if (myOledbDataReader.HasRows && myOledbDataReader.FieldCount > 0) { while (myOledbDataReader.Read()) { //LEPRT table LEPRT_CTCODE_branch = myOledbDataReader.GetString(0); LEPRT_PRODCT_product = myOledbDataReader.GetString(1); LEPRT_CNTRNO_contractno = myOledbDataReader.GetString(2); LEPRT_RENTAL_rentalamount = myOledbDataReader.GetDecimal(3); LEPRT_RENSNO_rentalno = myOledbDataReader.GetDecimal(4); //XEPCS table XEPCS_PERIOD_period = myOledbDataReader.GetValue(5).ToString(); XEPCS_OPNBAL_debtorbalance = myOledbDataReader.GetDecimal(6); //get the default interest //LEPDI_default_interest = getDefaultInterest(LEPRT_CTCODE_branch, LEPRT_PRODCT_product, LEPRT_CNTRNO_contractno); //XEPCS table contunues...... XEPCS_NORNAR_arrears = myOledbDataReader.GetDecimal(7); XEPCS_NAMECH_customername = myOledbDataReader.GetString(10); XEPCS_ADDRES_address = myOledbDataReader.GetString(8); XEPCS_MONAME_officername = myOledbDataReader.GetString(9); XEPCS_DCNAME_collector_name = myOledbDataReader.GetString(11); //calculate debtor balance and days of arrears newDebtorBalance = XEPCS_OPNBAL_debtorbalance + LEPRT_RENTAL_rentalamount; daysArrears = newDebtorBalance / LEPRT_RENTAL_rentalamount; //formatted date var formattedDaysArrears = utils.doFormat(daysArrears); var formattedDebtorBalance = utils.doFormat(newDebtorBalance); //add data to the table collection table.Rows.Add( LEPRT_CTCODE_branch.ToString(), LEPRT_PRODCT_product.ToString(), LEPRT_CNTRNO_contractno.ToString(), LEPRT_RENTAL_rentalamount.ToString(), LEPRT_RENSNO_rentalno.ToString(), XEPCS_PERIOD_period.ToString(), formattedDebtorBalance.ToString(), LEPDI_default_interest.ToString(), formattedDaysArrears.ToString(), XEPCS_ADDRES_address.ToString(), XEPCS_MONAME_officername.ToString(), XEPCS_NAMECH_customername.ToString(), XEPCS_DCNAME_collector_name.ToString() ); LEPRT_CTCODE_branch = null; LEPRT_PRODCT_product = null; LEPRT_CNTRNO_contractno = null; LEPRT_RENTAL_rentalamount = 0.0m; LEPRT_RENSNO_rentalno = 0.0m; XEPCS_PERIOD_period = null; XEPCS_OPNBAL_debtorbalance = 0.0m; XEPCS_NORNAR_arrears = 0.0m; XEPCS_ADDRES_address = null; XEPCS_MONAME_officername = null; XEPCS_NAMECH_customername = null; XEPCS_DCNAME_collector_name = null; LEPDI_default_interest = 0.0m; newDebtorBalance = 0.0m; daysArrears = 0.0m; defaultInterest = 0.0m; } myOledbCommand.Connection.Close(); myOledbCommand.Dispose(); Console.WriteLine("Data fetch completed with " + table.Rows.Count + " row(s)"); } } catch (Exception e) { Console.WriteLine(e.ToString()); } return(table); }
protected void LoadProducts() { DataTable dt = new DataTable(); dt.Columns.Add("ProductID"); dt.Columns.Add("RowPosition", typeof(int)); dt.Columns.Add("ProductName"); dt.Columns.Add("SKU"); dt.Columns.Add("Price"); dt.DefaultView.Sort = "RowPosition ASC"; DataTable dtAll = new DataTable(); dtAll.Columns.Add("ProductID"); dtAll.Columns.Add("RowPosition", typeof(int)); dtAll.Columns.Add("ProductName"); dtAll.Columns.Add("SKU"); dtAll.Columns.Add("Price"); dtAll.DefaultView.Sort = "RowPosition ASC"; OleDbConnection myConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|Northwind.mdb"); myConn.Open(); OleDbCommand myComm = new OleDbCommand("SELECT TOP 15 ProductID, ProductName, UnitPrice FROM Products", myConn); OleDbDataReader reader = myComm.ExecuteReader(); int i = 0; while (reader.Read()) { DataRow row = dt.NewRow(); row["ProductID"] = reader.GetInt32(0); row["RowPosition"] = i; row["ProductName"] = reader.GetString(1); row["SKU"] = "SKU" + i.ToString(); row["Price"] = reader.GetDecimal(2); dt.Rows.Add(row); DataRow row2 = dtAll.NewRow(); row2["ProductID"] = reader.GetInt32(0); row2["RowPosition"] = i; row2["ProductName"] = reader.GetString(1); row2["SKU"] = "SKU" + i.ToString(); row2["Price"] = reader.GetDecimal(2); dtAll.Rows.Add(row2); i++; } reader.Close(); myConn.Close(); Session["Products"] = dt; Session["AllProducts"] = dtAll; DataTable dt2 = new DataTable(); dt2.Columns.Add("ProductID"); dt2.Columns.Add("RowPosition", typeof(int)); dt2.Columns.Add("ProductName"); dt2.Columns.Add("SKU"); dt2.Columns.Add("Price"); dt2.DefaultView.Sort = "RowPosition ASC"; Session["PurchasedProducts"] = dt2; }
public bool Find(string attrFilter, string contextFilter, string attrOrderBy, int startRec, int numberOfRecs) { m_HasResults = false; m_HasMoreResults = false; m_VirtualCount = 0; if (numberOfRecs == 0) { numberOfRecs = 50; } string docQuery = @"select Id from Documents as D "; if (contextFilter != null && contextFilter.Length != 0) { docQuery += @" inner join OPENQUERY(ACCUFLOW_INDEXINGSRV,'select FileName from scope() where " + contextFilter + " ') as I " + @" on D.StorageFileName = I.FileName "; } docQuery += @" where 1=1 "; if (attrFilter != null && attrFilter.Length != 0) { docQuery += @" and " + attrFilter; // SECURITY FILTER WILL BE POSTED HERE } string securityFilter = GetSecurityFilter(); if (securityFilter.Length > 0) { docQuery += " and " + securityFilter; } if (attrOrderBy != null && attrOrderBy.Length != 0) { docQuery += @" order by D." + attrOrderBy; } OleDbCommand cmd = Db.Connection.CreateCommand(); cmd.CommandText = docQuery; OleDbDataReader reader = cmd.ExecuteReader(); int rec = 0; while (rec <= startRec) { if (!reader.Read()) { m_VirtualCount = rec; m_HasMoreResults = false; reader.Close(); cmd.Dispose(); return(false); } rec++; } System.Text.StringBuilder resIdSet = new System.Text.StringBuilder(1000); m_HasMoreResults = true; do { if (resIdSet.Length != 0) { resIdSet.Append(" , "); } resIdSet.Append(Convert.ToInt32(reader.GetDecimal(0)).ToString()); if (!reader.Read()) { m_HasMoreResults = false; m_VirtualCount = rec; break; } rec++; }while(rec <= startRec + numberOfRecs); if (m_HasMoreResults) { int readAhead = startRec + numberOfRecs * 6; for (; rec < readAhead && reader.Read(); rec++) { ; } m_VirtualCount = rec; m_HasMoreResults = (rec == readAhead); } reader.Close(); cmd.Dispose(); string docSelect = @"select *, IsRead= case when exists (select top 1 1 from userReadDocs where UserId=" + UserIdentity.Current.UserId.ToString() + @" and DocId=D.Id) then 1 else 0 end from documents as D where id in (" + resIdSet + ")"; if (attrOrderBy != null && attrOrderBy.Length != 0) { docSelect += " order by " + attrOrderBy + " , IsRead "; } else { docSelect += " order by IsRead "; } OleDbDataAdapter resFiller = new OleDbDataAdapter(docSelect, Db.Connection); if (m_ResultSet == null) { m_ResultSet = new DataTable(); } resFiller.Fill(m_ResultSet); m_HasResults = true; return(true); }
public void DoTestTypes(DbTypeParametersCollection row) { testTypesInvocations++; exp = null; string rowId = "43968_" + this.testTypesInvocations.ToString(); OleDbDataReader rdr = null; OleDbConnection con = null; try { row.ExecuteInsert(rowId); row.ExecuteSelectReader(rowId, out rdr, out con); while (rdr.Read()) { //Run over all the columns in the result set row. //For each column, try to read it as a Decimal. for (int i = 0; i < row.Count; i++) { if (row[i].Value.GetType() == typeof(decimal)) //The value in the result set should be a Decimal. { try { BeginCase(string.Format("Calling GetDecimal() on a field of dbtype {0}", row[i].DbTypeName)); decimal retDecimal = rdr.GetDecimal(i); Compare(row[i].Value, retDecimal); } catch (Exception ex) { exp = ex; } finally { EndCase(exp); exp = null; } } else //The value in the result set should NOT be Decimal. In this case an Invalid case exception should be thrown. { try { BeginCase(string.Format("Calling GetDecimal() on a field of dbtype {0}", row[i].DbTypeName)); decimal retDecimal = rdr.GetDecimal(i); ExpectedExceptionNotCaught("InvalidCastException"); } catch (InvalidCastException ex) { ExpectedExceptionCaught(ex); } catch (Exception ex) { exp = ex; } finally { EndCase(exp); exp = null; } } } } } finally { row.ExecuteDelete(rowId); if ((rdr != null) && (!rdr.IsClosed)) { rdr.Close(); } if ((con != null) && (con.State != ConnectionState.Closed)) { con.Close(); } } }
public List <Estadisticas> getStadistic(int id, string parameter) { conn = new Connection(); OleDbConnection objConn = conn.Conn(); string commText = ""; switch (parameter) { case "BANCO": commText = "select count(*), banco from (select distinct(CPARTYACCOUNT_ID), banco from YTBL_DETALLEPROGCORTE where ID_PROGCORTE = " + id + ") group by banco"; break; case "CIUDAD": commText = "select count(*), CIUDAD from (select distinct(CPARTYACCOUNT_ID), CIUDAD from YTBL_DETALLEPROGCORTE where ID_PROGCORTE = " + id + ") GROUP BY CIUDAD"; break; case "BUSINESS": commText = "select count(*), BUSINESS from (select distinct(CPARTYACCOUNT_ID), BUSINESS from YTBL_DETALLEPROGCORTE where ID_PROGCORTE = " + id + ") GROUP BY BUSINESS"; break; case "COMPANY": commText = "select count(*), COMPANY from (select distinct(CPARTYACCOUNT_ID), COMPANY from YTBL_DETALLEPROGCORTE where ID_PROGCORTE = " + id + " ) GROUP BY COMPANY"; break; case "CUENTAS": commText = "select COUNT(distinct CPARTYACCOUNT_ID), 'Cuentas' from YTBL_DETALLEPROGCORTE where ID_PROGCORTE = " + id; break; } objConn.Open(); OleDbCommand cmd = new OleDbCommand(); cmd.Connection = objConn; cmd.CommandText = commText; cmd.CommandType = CommandType.Text; OleDbDataReader myReader = cmd.ExecuteReader(); List <Estadisticas> ListEstadictica = new List <Estadisticas>(); Ytbl_CondicionesCorte condicion = new Ytbl_CondicionesCorte(); try { if (myReader.HasRows) { while (myReader.Read()) { Estadisticas estadistica = new Estadisticas(); try { if (parameter == "CIUDAD") { estadistica.cantidad = int.Parse(myReader.GetDecimal(0).ToString()); if (myReader.GetValue(1).ToString() != "") { estadistica.nombre = condicion.getNameProperty(myReader.GetValue(1).ToString(), "CIUDAD"); } else { estadistica.nombre = "Ninguno"; } } else { estadistica.cantidad = int.Parse(myReader.GetDecimal(0).ToString()); if (myReader.GetValue(1).ToString() != "") { estadistica.nombre = myReader.GetValue(1).ToString(); } else { estadistica.nombre = "Ninguno"; } } } catch (Exception ex) { estadistica.nombre = "Ninguno"; } ListEstadictica.Add(estadistica); } } return(ListEstadictica); } catch (Exception ex) { myReader.Close(); objConn.Close(); Logs.WriteErrorLog("Error en la consulta de datos por ID||" + ex.ToString()); return(ListEstadictica); } finally { myReader.Close(); objConn.Close(); } }
public void DoTestTextualFieldsThatContainNumbers(DbTypeParametersCollection row) { //Leave only textual fields in the collection, and set their value to the string "10" SetTextualFieldsWithNumericValues(ref row); if (row.Count < 1) { return; } testTypesInvocations++; exp = null; string rowId = "43968_" + this.testTypesInvocations.ToString(); OleDbDataReader rdr = null; OleDbConnection con = null; try { row.ExecuteInsert(rowId); row.ExecuteSelectReader(rowId, out rdr, out con); while (rdr.Read()) { //Run over all the columns in the result set row. //For each column, try to read it as a Decimal. //Because all the fields are textual, this should throw an InvalidCastException. for (int i = 0; i < row.Count; i++) { try { BeginCase(string.Format("Calling GetDecimal() on a textual field of dbtype {0} with value '{1}'", row[i].DbTypeName, row[i].Value)); decimal retDecimal = rdr.GetDecimal(i); ExpectedExceptionNotCaught(typeof(InvalidCastException).FullName); } catch (InvalidCastException ex) { ExpectedExceptionCaught(ex); } catch (Exception ex) { exp = ex; } finally { EndCase(exp); exp = null; } } } } finally { row.ExecuteDelete(rowId); if ((rdr != null) && (!rdr.IsClosed)) { rdr.Close(); } if ((con != null) && (con.State != ConnectionState.Closed)) { con.Close(); } } }
void copyOrderTable() { string sql = "SELECT NППЗаказа, NЗаказаЗавода, NЗаказа, ГрафикN, ВидГрафика, ОснованиеЗаказа, ДатаПолученияЗаказа, ОбозначениеТО, КодТО, NИзделия, NДетали, Кол_во, ФКол_во, Заказчик, ПланДатаИзготовления, КодПриоритета, ПланТП, ДатаТП, ДатаПоступленияЦех, ФактДатаИзготовления, ПланТрудоемкость, ЦехИсполнитель, Отчет, ДатаОтчета, Технолог, Слесарь, СодержаниеЗаявки, ФактТрудоемкость, NНакладной, NАкта, СкладПолуч, НеодноврВыпЗак, Конструктор, p_unit, pause, OrderCards, Life, Lifeday, ObjectType, Quckly, MaterialDate, Запчасть, Стойкость, Доп_заказ, TNVED, PriceDate, PeoDate, OtizDate FROM Заказы";// where NППЗаказа=1"; OleDbConnection conn = new OleDbConnection(Properties.Settings.Default.connStr); conn.Open(); OleDbCommand cmd = new OleDbCommand(sql, conn); OleDbDataReader r = cmd.ExecuteReader(); while (r.Read()) { Order o = new Order(); o.orderId = r.GetInt32(0); o.orderNumber = r.GetString(1); o.orderFullNumber = r.GetString(2); if (r[3] != DBNull.Value) { o.graphId = r.GetInt32(3); } if (r[4] != DBNull.Value) { o.graphTypeId = r.GetByte(4); } if (r[5] != DBNull.Value) { o.reasonOrder = r.GetByte(5); } if (r[6] != DBNull.Value) { o.orderDate = r.GetDateTime(6); } if (r[7] != DBNull.Value) { o.obozTo = r.GetString(7); } if (r[8] != DBNull.Value) { o.kodTO = r.GetInt16(8); } o.productNumber = r.GetString(9); o.partNumber = r.GetString(10); if (r[11] != DBNull.Value) { o.qty = r.GetInt32(11); } if (r[12] != DBNull.Value) { o.realQty = r.GetInt32(12); } if (r[13] != DBNull.Value) { o.customerId = r.GetInt32(13); } if (r[14] != DBNull.Value) { o.planDate = r.GetDateTime(14); } if (r[15] != DBNull.Value) { o.priorityId = r.GetByte(15); } if (r[16] != DBNull.Value) { o.planTechDate = r.GetDateTime(16); } if (r[17] != DBNull.Value) { o.techDate = r.GetDateTime(17); } if (r[18] != DBNull.Value) { o.shopDate = r.GetDateTime(18); } if (r[19] != DBNull.Value) { o.factReadyDate = r.GetDateTime(19); } if (r[20] != DBNull.Value) { o.planLabourIntensity = r.GetDouble(20); } if (r[21] != DBNull.Value) { o.performerShop = r.GetInt32(21); } if (r[22] != DBNull.Value) { o.includeInReport = r.GetBoolean(22); } if (r[23] != DBNull.Value) { o.reportDate = r.GetDateTime(23); } if (r[24] != DBNull.Value) { o.technologistId = r.GetInt32(24); } if (r[25] != DBNull.Value) { o.locksmithId = r.GetInt32(25); } if (r[26] != DBNull.Value) { o.orderNote = r.GetString(26); } if (r[27] != DBNull.Value) { o.factLabourIntensity = r.GetDouble(27); } if (r[28] != DBNull.Value) { o.consignmentNote = r.GetString(28); } if (r[29] != DBNull.Value) { o.actNumber = r.GetString(29); } if (r[30] != DBNull.Value) { o.destinationStore = r.GetString(30); } if (r[31] != DBNull.Value) { o.anotherTimeReady = r.GetBoolean(31); } if (r[32] != DBNull.Value) { o.designerId = -1; //// !!!!!!!!!!!! r.GetInt32(32); } if (r[33] != DBNull.Value) { o.unitOfMeasureId = -1;/// r.GetInt32(33); } if (r[34] != DBNull.Value) { o.stopped = r.GetBoolean(34); } if (r[35] != DBNull.Value) { o.orderCard = r.GetInt32(35); } if (r[36] != DBNull.Value) { o.serviceLifeYear = r.GetInt16(36); } if (r[37] != DBNull.Value) { o.serviceLifeDay = r.GetInt32(37); } if (r[38] != DBNull.Value) { o.objectType = r.GetByte(38); } if (r[39] != DBNull.Value) { o.urgent = r.GetBoolean(39); } if (r[40] != DBNull.Value) { o.materialDate = r.GetDateTime(40); } if (r[41] != DBNull.Value) { o.spare = r.GetBoolean(41); } if (r[42] != DBNull.Value) { o.resistance = r.GetInt32(42); } if (r[43] != DBNull.Value) { o.additionalOrder = r.GetBoolean(43); } if (r[44] != DBNull.Value) { o.classifier = r.GetDecimal(44).ToString(); } if (r[45] != DBNull.Value) { o.priceDate = r.GetDateTime(45); } if (r[46] != DBNull.Value) { o.peoDate = r.GetDateTime(46); } if (r[47] != DBNull.Value) { o.otizDate = r.GetDateTime(47); } /* * * string json = JsonConvert.SerializeObject(o, Formatting.None, new JsonSerializerSettings * { * NullValueHandling = NullValueHandling.Ignore * }); * */ Order o2 = rest.saveEntity <Order>(o); if (o2.orderId < 1) { break; } // MessageBox.Show(json); } r.Close(); cmd.Dispose(); conn.Close(); MessageBox.Show("Done!"); }
private void user_MouseEnter(object sender, EventArgs e) { if (flag == 0) { string ss = textBox1.Text; string conStr = "provider=Microsoft.ACE.OLEDB.12.0;Persist Security Info=False;Data Source=" + "myNewIC.mdb"; //建立数据库引擎连接 OleDbConnection conn = new OleDbConnection(conStr); //建立适配器,通过SQL语句去搜索数据库 OleDbDataAdapter mydata = new OleDbDataAdapter("select 卡号,户主姓名,挂失位,余额 from myTable where cstr(卡号)='" + ss + "'", conn); OleDbDataAdapter mydata2 = new OleDbDataAdapter("select 一季度,二季度,三季度,四季度 from myTable where cstr(卡号)='" + ss + "'", conn); //建立数据集 DataSet datas = new DataSet(); DataSet datas2 = new DataSet(); //用FILL的方式将适配器已经连接好的数据表填充到数据集MYDS这张表 mydata.Fill(datas, 0, 1, "myTable"); //填充表格0开始填1行 mydata2.Fill(datas2, 0, 1, "myTable"); //用显示控件来显示表 tab.DataSource = datas.Tables[0]; //DataGridView显示 tab2.DataSource = datas2.Tables[0]; //提取户主姓名 conn.Open(); OleDbDataReader myReader = null; //sql语句 string sql = "select * from myTable where cstr(卡号)='" + ss + "'"; //命令 OleDbCommand cmd = new OleDbCommand(sql, conn); myReader = cmd.ExecuteReader(); if (myReader.Read()) { string name = myReader.GetString(2); label1.Text = "尊敬的" + name + ",\n欢迎您来到用户用电信息查询系统!"; } else { MessageBox.Show("未查询到该用户", "Warning"); } //提取季度用电总和 sql = "select sum(一季度+二季度+三季度+四季度) from myTable where cstr(卡号)='" + ss + "'"; cmd = new OleDbCommand(sql, conn); string sum = Convert.ToString(cmd.ExecuteScalar()); string avg_sea = Convert.ToString(Convert.ToDecimal(Convert.ToDecimal(Convert.ToString(cmd.ExecuteScalar())) / 4)); sql = "select avg(一季度) from myTable where cstr(权限位)='0'"; cmd = new OleDbCommand(sql, conn); decimal sea1 = Convert.ToDecimal(Convert.ToString(cmd.ExecuteScalar())); sql = "select avg(二季度) from myTable where cstr(权限位)='0'"; cmd = new OleDbCommand(sql, conn); decimal sea2 = Convert.ToDecimal(Convert.ToString(cmd.ExecuteScalar())); sql = "select avg(三季度) from myTable where cstr(权限位)='0'"; cmd = new OleDbCommand(sql, conn); decimal sea3 = Convert.ToDecimal(Convert.ToString(cmd.ExecuteScalar())); sql = "select avg(四季度) from myTable where cstr(权限位)='0'"; cmd = new OleDbCommand(sql, conn); decimal sea4 = Convert.ToDecimal(Convert.ToString(cmd.ExecuteScalar())); string avg_all = Convert.ToString(sea1 + sea2 + sea3 + sea4); //所有用户平均值 string compare = ""; if (Convert.ToDecimal(sum) <= Convert.ToDecimal(avg_all)) { compare = "低"; } else { compare = "高"; } label4.Text = "四个季度电费总和: " + sum + "\t元" + "(" + compare + "于全体用户平均水平" + avg_all + "元)"; //label显示 label5.Text = "四个季度电费平均值:" + avg_sea + "\t元"; //提取作图数据 sql = "select * from myTable where cstr(卡号)='" + ss + "'"; cmd = new OleDbCommand(sql, conn); myReader = cmd.ExecuteReader(); decimal[] data = new decimal[4]; if (myReader.Read()) { data[0] = myReader.GetDecimal(6); data[1] = myReader.GetDecimal(7); data[2] = myReader.GetDecimal(8); data[3] = myReader.GetDecimal(9); money = myReader.GetDecimal(4); } else { MessageBox.Show("未查询到该用户", "Warning"); } conn.Close(); List <string> xData = new List <string>() { "一季度", "二季度", "三季度", "四季度" }; List <decimal> yData = new List <decimal>() { data[0], data[1], data[2], data[3] }; List <decimal> zData = new List <decimal>() { sea1, sea2, sea3, sea4 }; chart1.Series[0].Points.DataBindXY(xData, yData); chart1.Series[1].Points.DataBindXY(xData, zData); } }
/* Calculate average of price for the particular stocks*/ public void MarqueeUpdate() { //2,5 OleDbCommand cmdStockName = new OleDbCommand(); OleDbDataReader drStock = null; cmdStockName.Connection = _oConnDM; cmdStockName.CommandType = CommandType.StoredProcedure; cmdStockName.CommandText = "QryStockLatestValue"; _pBarDoubleM.Visible = true; try { if (_oConnDM.State != ConnectionState.Open) { _oConnDM.Open(); } drStock = cmdStockName.ExecuteReader(); if (drStock.HasRows) { //_pBarDoubleM.Value = 0; while (drStock.Read()) //Reading Stock Names { CommonDoubleM.MarqueeString = CommonDoubleM.MarqueeString + " [" + drStock.GetString(2) + ": " + drStock.GetDecimal(5).ToString() + "] "; } } else { CommonDoubleM.MarqueeString = "Welcome to Market Manager - Double'M'"; } } catch (Exception ex) { _lblMsgDoubleM.Text = ex.Message; CommonDoubleM.LogDM(ex.Message); } finally { cmdStockName.Dispose(); _pBarDoubleM.Visible = false; if (_oConnDM.State != ConnectionState.Closed) { _oConnDM.Close(); } } }
public Ytbl_CondicionesCorte SelectCondicionesCorteById(int id) { Connection conn = new Connection(); OleDbConnection objConn = conn.Conn(); string commText = "select * from YTBL_CONDICIONESNOCORTE where id =" + id; objConn.Open(); OleDbCommand cmd = new OleDbCommand(); cmd.Connection = objConn; cmd.CommandText = commText; cmd.CommandType = CommandType.Text; OleDbDataReader myReader = cmd.ExecuteReader(); int RecordCount = 0; Ytbl_CondicionesCorte CondicionCorte = new Ytbl_CondicionesCorte(); try { if (myReader.HasRows) { while (myReader.Read()) { RecordCount++; CondicionCorte.Id = int.Parse(myReader.GetDecimal(0).ToString()); try { CondicionCorte.Provider = myReader.GetString(1).ToString(); } catch (Exception ex) { CondicionCorte.Provider = null; } try { CondicionCorte.Ciudad = myReader.GetString(2).ToString(); } catch (Exception ex) { CondicionCorte.Ciudad = null; } try { CondicionCorte.PaymentMode = myReader.GetString(3).ToString(); } catch (Exception ex) { CondicionCorte.PaymentMode = null; } try { CondicionCorte.Business = myReader.GetString(4).ToString(); } catch (Exception ex) { CondicionCorte.Business = null; } try { CondicionCorte.Company = myReader.GetString(5).ToString(); } catch (Exception ex) { CondicionCorte.Company = null; } CondicionCorte.Id_Corte = Int32.Parse(myReader.GetDecimal(6).ToString()); try { CondicionCorte.Fecha = DateTime.Parse(myReader.GetDateTime(7).ToString()); } catch (Exception ex) { CondicionCorte.Fecha = null; } CondicionCorte.IsValid = myReader.GetString(8).ToString(); CondicionCorte.Usuario = myReader.GetString(9).ToString(); } } return(CondicionCorte); } catch (Exception ex) { myReader.Close(); objConn.Close(); Logs.WriteErrorLog("Error en la consulta de datos por ID||" + ex.ToString()); return(CondicionCorte); } finally { myReader.Close(); objConn.Close(); } }
/// <summary> /// Test CUBRID data types Get...() /// </summary> private static void Test_Various_DataTypes() { using (OleDbConnection conn = new OleDbConnection()) { conn.ConnectionString = TestCasesOld.connString; conn.Open(); TestCasesOld.ExecuteSQL("drop table if exists t", conn); string sql = "create table t("; sql += "c_integer_ai integer AUTO_INCREMENT, "; sql += "c_smallint smallint, "; sql += "c_integer integer, "; sql += "c_bigint bigint, "; sql += "c_numeric numeric(15,1), "; sql += "c_float float, "; sql += "c_decimal decimal(15,3), "; sql += "c_double double, "; sql += "c_char char, "; sql += "c_varchar varchar(4096), "; sql += "c_time time, "; sql += "c_date date, "; sql += "c_timestamp timestamp, "; sql += "c_datetime datetime, "; sql += "c_bit bit(1), "; sql += "c_varbit bit varying(4096), "; sql += "c_monetary monetary, "; sql += "c_string string"; sql += ")"; TestCasesOld.ExecuteSQL(sql, conn); sql = "insert into t values("; sql += "1, "; sql += "11, "; sql += "111, "; sql += "1111, "; sql += "1.1, "; sql += "1.11, "; sql += "1.111, "; sql += "1.1111, "; sql += "'a', "; sql += "'abcdfghijk', "; sql += "TIME '13:15:45 pm', "; sql += "DATE '00-10-31', "; sql += "TIMESTAMP '13:15:45 10/31/2008', "; sql += "DATETIME '13:15:45 10/31/2008', "; sql += "B'0', "; sql += "B'0', "; sql += "123456789, "; sql += "'qwerty'"; sql += ")"; TestCasesOld.ExecuteSQL(sql, conn); sql = "select * from t"; using (OleDbCommand cmd = new OleDbCommand(sql, conn)) { try { OleDbDataReader reader = cmd.ExecuteReader(); while (reader.Read()) //only one row will be available { Debug.Assert(reader.GetInt32(0) == 1); Debug.Assert(reader.GetInt16(1) == 11); Debug.Assert(reader.GetInt32(2) == 111); Debug.Assert(reader.GetInt64(3) == 1111); Debug.Assert(reader.GetDecimal(4) == (decimal)1.1); Debug.Assert(reader.GetFloat(5) == (float)1.11); //"Single" Debug.Assert(reader.GetDecimal(6) == (decimal)1.111); Debug.Assert(reader.GetDouble(7) == (double)1.1111); //We use GetString() because GetChar() is not supported or System.Data.OleDb. //http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbdatareader.getchar Debug.Assert(reader.GetString(8) == "a"); //"String" ("Char" in CUBRID) Debug.Assert(reader.GetString(9) == "abcdfghijk"); //"String" ("String in CUBRID) //GetGateTime cannot cast just the time value in a DateTime object, so we use TimeSpan Debug.Assert(reader.GetTimeSpan(10) == new TimeSpan(13, 15, 45)); //"TimeSpan" Debug.Assert(reader.GetDateTime(11) == new DateTime(2000, 10, 31)); //"DateTime" Debug.Assert(reader.GetDateTime(12) == new DateTime(2008, 10, 31, 13, 15, 45)); //"DateTime" Console.WriteLine(reader.GetValue(13)); Debug.Assert(reader.GetDateTime(13) == new DateTime(2008, 10, 31, 13, 15, 45)); //"DateTime" //The GetByte() method does not perform any conversions and the driver does not give tha data as Byte //http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbdatareader.getbyte //Use GetValue() or GetBytes() methods to retrieve BIT coulumn value // Debug.Assert((reader.GetValue(14) as byte[])[0] == (byte)0); //"Byte[]" ("bit(1)" in CUBRID) //Or Byte[] value = new Byte[1]; reader.GetBytes(14, 0, value, 0, 1); // Debug.Assert(value[0] == (byte)0);//"Byte[]" ("bit(1)" in CUBRID) //Debug.Assert((reader.GetValue(14) as byte[])[0] == (byte)0); //"Byte[]" ("bit varying(4096)" in CUBRID) //Or // reader.GetBytes(15, 0, value, 0, 1); // Debug.Assert(value[0] == (byte)0);//"Byte[]" ("bit varying(4096)" in CUBRID) Debug.Assert(reader.GetDouble(16) == 123456789.0); //"Double" ("Monetary" in CUBRID) Debug.Assert(reader.GetString(17) == "qwerty"); //"String" } } catch (Exception ex) { Console.WriteLine(ex.Message); } } TestCasesOld.ExecuteSQL("drop table if exists t", conn); } }
public UserGestCor GetUsersById(int id) { conn = new Connection(); OleDbConnection objConn = conn.Conn(); string commText = "select * from YTBL_USERSGESTCOR where id =" + id; objConn.Open(); OleDbCommand cmd = new OleDbCommand(); cmd.Connection = objConn; cmd.CommandText = commText; cmd.CommandType = CommandType.Text; OleDbDataReader myReader = cmd.ExecuteReader(); UserGestCor User = new UserGestCor(); GestCorProfile RolName = new GestCorProfile(); try { if (myReader.HasRows) { while (myReader.Read()) { User.Id = int.Parse(myReader.GetDecimal(2).ToString()); User.NickUser = myReader.GetString(1); User.IdRol = int.Parse(myReader.GetDecimal(2).ToString()); User.NameRol = RolName.getNameRol(int.Parse(myReader.GetDecimal(2).ToString())); User.IsValid = myReader.GetString(3); try { User.DateFrom = myReader.GetDateTime(4); } catch (Exception ex) { User.DateFrom = null; } try { User.DateTo = myReader.GetDateTime(5); } catch (Exception ex) { User.DateTo = null; } } } return(User); } catch (Exception ex) { myReader.Close(); objConn.Close(); Logs.WriteErrorLog("Error en la consulta de datos||" + ex.ToString()); return(User); } finally { myReader.Close(); objConn.Close(); } }
public List <Station> getAll() { List <Station> stations = new List <Station>(); string connectionString = cs; using (OleDbConnection oleDbConnection = new OleDbConnection(connectionString)) { OleDbCommand oleDbCommand = new OleDbCommand("select * from station"); oleDbConnection.Open(); oleDbCommand.Connection = oleDbConnection; OleDbDataReader oleDbDataReader = oleDbCommand.ExecuteReader(); while (oleDbDataReader.Read()) { int id = (int)oleDbDataReader.GetDecimal(0); string sname = (string)oleDbDataReader.GetString(1); Station s = new Station(sname, id); stations.Add(s); } } foreach (Station s in stations) { using (OleDbConnection oleDbConnection = new OleDbConnection(connectionString)) { OleDbCommand oleDbCommand = new OleDbCommand("select * from question where question.station_id = ?"); oleDbCommand.Parameters.Add("?", s.StationID); oleDbConnection.Open(); oleDbCommand.Connection = oleDbConnection; OleDbDataReader oleDbDataReader = oleDbCommand.ExecuteReader(); while (oleDbDataReader.Read()) { Question q = null; int qid = (int)oleDbDataReader.GetDecimal(0); string desc = (string)oleDbDataReader.GetString(1); int quote = (int)oleDbDataReader.GetDecimal(3); q = new Question(qid, desc, quote, -1); s.Questions.Add(q); } } foreach (Question q in s.Questions) { using (OleDbConnection oleDbConnection = new OleDbConnection(connectionString)) { OleDbCommand oleDbCommand = new OleDbCommand("select * from answer where quest_id = ?"); oleDbCommand.Parameters.Add("?", q.QId); oleDbConnection.Open(); oleDbCommand.Connection = oleDbConnection; OleDbDataReader oleDbDataReader = oleDbCommand.ExecuteReader(); while (oleDbDataReader.Read()) { Answer a = null; int aid = (int)oleDbDataReader.GetDecimal(0); string desc = (string)oleDbDataReader.GetString(1); int corr = (int)oleDbDataReader.GetDecimal(3); if (corr == 1) { q.CorrectAnswer = aid; } a = new Answer(aid, desc); q.Answers.Add(a); } } } } Allstations = stations; return(stations); }
private void button8_Click(object sender, EventArgs e) { string conStr = "provider=Microsoft.ACE.OLEDB.12.0;Persist Security Info=False;Data Source=" + "myNewIC.mdb"; //建立数据库引擎连接 OleDbConnection conn = new OleDbConnection(conStr); OleDbDataReader myReader = null; //sql语句 string sql = "select * from myTable where cstr(卡号)='" + textBox1.Text + "'"; //命令 conn.Open(); OleDbCommand cmd = new OleDbCommand(sql, conn); myReader = cmd.ExecuteReader(); decimal mm = 0; if (myReader.Read()) { mm = myReader.GetDecimal(4); } kflag = 0; button8.Visible = false; button9.Visible = true; while (kflag == 0) { mm = mm - Convert.ToDecimal(0.6); if (mm <= Convert.ToDecimal(0)) { MessageBox.Show("余额不足", "Warning"); kflag = 1; conStr = "provider=Microsoft.ACE.OLEDB.12.0;Persist Security Info=False;Data Source=" + "myNewIC.mdb"; //创建数据库连接 conn = new OleDbConnection(conStr); //打开数据库连接 conn.Open(); sql = "update myTable set 余额='" + 0 + "' where 卡号='" + textBox1.Text + "'"; cmd = new OleDbCommand(sql, conn); cmd.ExecuteNonQuery(); OleDbDataAdapter mydata = new OleDbDataAdapter("select 卡号,户主姓名,挂失位,余额 from myTable where cstr(卡号)='" + id + "'", conn); //建立数据集 DataSet datas = new DataSet(); //用FILL的方式将适配器已经连接好的数据表填充到数据集MYDS这张表 mydata.Fill(datas, 0, 1, "myTable"); //用显示控件来显示表 tab.DataSource = datas.Tables[0]; conn.Close(); button8.Visible = true; button9.Visible = false; } else { textBox4.Text = Convert.ToString(mm); Application.DoEvents(); System.Threading.Thread.Sleep(100); } } }
public List <pelangganModels> GetListHutang(string kdPelanggan) { List <pelangganModels> dList = new List <pelangganModels>(); string SqlString = @"Select a.id,a.kode as no_trans,a.tgl_registrasi,a.nama,a.alamat,a.kode as kd_pelanggan, a.batas_kredit,a.bunga as persen_bunga, a.waktu as jangka_waktu , a.tbunga as bunga_per_bulan, a.bunga2 as pokok_per_bulan, a.pinjaman as sisa_pinjaman, a.angsuran as angsuran_per_bulan , b.simwajib,b.simpanan as simpanan_tmk from anggota as b LEFT join pelanggan as a on a.kota = b.noang where a.pinjaman > 10 and b.noang = ? "; string ConnStr = ManageString.GetConnStr(); using (OleDbConnection conn = new OleDbConnection(ConnStr)) { conn.Open(); using (OleDbCommand cmd = new OleDbCommand(SqlString, conn)) { cmd.CommandType = CommandType.Text; cmd.Parameters.AddWithValue("noang", kdPelanggan); using (OleDbDataReader aa = cmd.ExecuteReader()) { if (aa.HasRows) { //Log.Debug(DateTime.Now + " GetPelangganREPO ====>>>>>> Jumlah Data : " + aa.Cast<object>().Count()); //Log.Debug(DateTime.Now + " aa READ >>>>>> " + aa.Read().ToString()); while (aa.Read()) { //Log.Debug(DateTime.Now + " NOTRANS ====>>>>>> " + aa["no_trans"].ToString()); //string f_0 = aa.GetFieldType(0).ToString(); //string f_1 = aa.GetFieldType(1).ToString(); //string f_2 = aa.GetFieldType(2).ToString(); //string f_3 = aa.GetFieldType(3).ToString(); //string f_4 = aa.GetFieldType(4).ToString(); //string f_5 = aa.GetFieldType(5).ToString(); //string f_6 = aa.GetFieldType(6).ToString(); //string f_7 = aa.GetFieldType(7).ToString(); //string f_8 = aa.GetFieldType(8).ToString(); //string f_9 = aa.GetFieldType(9).ToString(); //string f_10 = aa.GetFieldType(10).ToString(); //string f_11 = aa.GetFieldType(11).ToString(); //string f_12 = aa.GetFieldType(12).ToString(); //Log.Debug(DateTime.Now + " F_0 : " + f_0 + "\n" + " F_1 : " + f_1 + "\n" + " F_2 : " + f_2 + "\n" + // " F_3 : " + f_3 + "\n" + " F_4 : " + f_4 + "\n" + " F_5 : " + f_5 + "\n" + // " F_6 : " + f_6 + "\n" + " F_7 : " + f_7 + "\n" + " F_8 : " + f_8 + "\n" + // " F_9 : " + f_9 + "\n" + " F_10 : " + f_10 + "\n" + " F_11 : " + f_11 + "\n" + // " F_12 : " + f_12); pelangganModels item = new pelangganModels(); item.id = aa.GetInt32(0); item.no_trans = aa.GetString(1); item.tgl_registrasi = aa.GetDateTime(2); item.nama = aa.GetString(3); item.alamat = aa.GetString(4); item.kd_pelanggan = aa.GetString(5); item.batas_kredit = aa.GetDecimal(6); item.persen_bunga = Math.Round(Convert.ToDecimal(aa.GetFloat(7)), 2); item.jangka_waktu = aa.GetInt16(8); item.bunga_per_bulan = Math.Round(Convert.ToDecimal(aa.GetDouble(9)), 2); item.pokok_per_bulan = Math.Round(Convert.ToDecimal(aa.GetDouble(10)), 2); item.sisa_pinjaman = Math.Round(Convert.ToDecimal(aa.GetDouble(11)), 2); item.angsuran_per_bulan = Math.Round(Convert.ToDecimal(aa.GetDouble(12)), 2); item.simpanan_wajib = Math.Round(Convert.ToDecimal(aa.GetDouble(13)), 2); item.simpanan_tmk = Math.Round(Convert.ToDecimal(aa.GetDouble(14)), 2); dList.Add(item); } //Log.Debug(DateTime.Now + " GetPelangganREPO ====>>>>>> Jumlah LIST : " + dList.Count()); } } } } return(dList); }
private void FillDGV() { string connectionString = ConfigurationManager.ConnectionStrings["DairyDepartureConnectionString"].ConnectionString; using (OleDbConnection conn = new OleDbConnection(connectionString)) { conn.Open(); string sql = @"SELECT m.Name_manufacturer, p.Name_product, p.[%-fat], p.[Mass/volume], s.[Price], sum(s.[Count])-IIF( ISNULL((select sum(sl.[Count]) from Sells as sl where sl.ID_supply = s.ID_supply)), 0, (select sum(sl.[Count]) from Sells as sl where sl.ID_supply = s.ID_supply)) AS Total, p.ID_product FROM Manufacturer AS m, Product AS p, Supply AS s WHERE m.ID_manufacturer = p.ID_manufacturer and s.ID_product = p.ID_product and s.ID_supply not in ( SELECT s1.ID_supply FROM Sells AS sl, Supply AS s1 WHERE (((sl.ID_supply)=[s1].[ID_supply])) GROUP BY s1.ID_supply HAVING min(s1.Count)<Sum([sl].[Count]) ) group by m.Name_manufacturer, p.Name_product, p.[%-fat], p.[Mass/volume], s.[Price], p.ID_product, s.ID_supply; "; using (OleDbCommand comm = new OleDbCommand(sql, conn)) { using (OleDbDataReader reader = comm.ExecuteReader()) { int i = 0; while (reader.Read()) { dataGridView1.Rows.Add(reader.GetString(0), reader.GetString(1), reader.GetInt32(2), reader.GetString(3), reader.GetDecimal(4), reader.GetDouble(5), -1, reader.GetInt32(6)); dataGridView1.Rows[i].ReadOnly = true; i++; } } } } }
private void BuildLPInputData(int Min_Vol) { using (OleDbConnection conn = GetConnection.BIPSConn) { using (OleDbCommand cmd = new OleDbCommand(SQLCommands.GetNewLPInput(inputTable, scenario, tableSelection.ScenarioNonLinearSolutionTable), conn)) { using (OleDbDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { OutputData outputData = new OutputData(); outputData.OHUB = reader.IsDBNull((int)InputColumns.OHUB) ? "" : reader.GetString((int)InputColumns.OHUB); outputData.HubMiles = reader.IsDBNull((int)InputColumns.HUB_MILES) ? 0 : reader.GetInt32((int)InputColumns.HUB_MILES); outputData.LaneEmptyVol = reader.IsDBNull((int)InputColumns.MT_VOLUME) ? 0 : reader.GetInt32((int)InputColumns.MT_VOLUME); outputData.AvailableBidVol = reader.IsDBNull((int)InputColumns.AVL_BID_VOL) ? 0 : reader.GetInt32((int)InputColumns.AVL_BID_VOL); outputData.IMLaneRank = reader.IsDBNull((int)InputColumns.IMD_RANK) ? 0 : reader.GetInt32((int)InputColumns.IMD_RANK); outputData.LaneRevenue = reader.IsDBNull((int)InputColumns.LANE_REVENUE) ? 0 : (double)reader.GetDecimal((int)InputColumns.LANE_REVENUE); outputData.LaneCost = reader.IsDBNull((int)InputColumns.LANE_COST) ? 0 : (double)reader.GetDecimal((int)InputColumns.LANE_COST); outputData.LaneProfit = outputData.LaneRevenue - outputData.LaneCost; outputData.LaneWeeklyTrailerTurns = reader.IsDBNull((int)InputColumns.WEEKLY_TURNS) ? 0 : (double)reader.GetDecimal((int)InputColumns.WEEKLY_TURNS); outputData.LaneMinVol = reader.IsDBNull((int)InputColumns.MODEL_MIN) ? 0 : reader.GetInt32((int)InputColumns.MODEL_MIN); outputData.LaneMaxVol = reader.IsDBNull((int)InputColumns.MODEL_MIN) ? 0 : reader.GetInt32((int)InputColumns.MODEL_MIN); outputData.TotalBidVol = reader.IsDBNull((int)InputColumns.TTL_BID_VOL) ? 0 : reader.GetInt32((int)InputColumns.TTL_BID_VOL); outputData.LaneID = i; outputData.ScenarioID = scenario; outputData.DHUB = reader.IsDBNull((int)InputColumns.DHUB) ? "" : reader.GetString((int)InputColumns.DHUB); LoadedMoves[i] = theModel.NumVar(Min_Vol, outputData.AvailableBidVol, NumVarType.Float, "LM_" + outputData.Lane); outputData.LoadedMoves = LoadedMoves[i]; EmptyMoves[i] = theModel.NumVar(0, outputData.LaneEmptyVol, NumVarType.Float, "EM_" + outputData.Lane); outputData.EmptyMoves = EmptyMoves[i]; theData.AddModelData(i, outputData); LaneRevenue[i] = outputData.LaneRevenue; LaneCost[i] = outputData.LaneCost; TrailerTurns[i] = outputData.LaneWeeklyTrailerTurns; LaneMargin[i] = LaneRevenue[i] - LaneCost[i]; i++; } } } } }
public List <SinoSZToolFlowDesign.DOL.Flow_BaseDefine> GetFlows() { List <Flow_BaseDefine> _ret = new List <Flow_BaseDefine>(); string _sql = "select [ID],[FLOWNAME],[DESCRIPTION],[ROOTDWID] FROM [FLOW_ENTITYTYPE]"; using (OleDbConnection cn = OpenConnection(connectString)) { try { OleDbCommand _cmd = new OleDbCommand(_sql, cn); OleDbDataReader dr = _cmd.ExecuteReader(); while (dr.Read()) { Flow_BaseDefine _fd = new Flow_BaseDefine(dr.IsDBNull(0) ? "" : dr.GetString(0), dr.IsDBNull(1) ? "" : dr.GetString(1), dr.IsDBNull(2) ? "" : dr.GetString(2), dr.IsDBNull(3) ? "0" : dr.GetDecimal(3).ToString() ); _ret.Add(_fd); } dr.Close(); cn.Close(); return(_ret); } catch (Exception e) { //写系统错误日志 throw e; return(null); } } }
private void textBox1_MouseDoubleClick(object sender, MouseEventArgs e) { if (myport.IsOpen) { while (cflag == 0) //接受下位机卡号 { byte[] data = new byte[8]; byte[] rdata = new byte[6]; myport.Read(data, 0, 8); if ((data[6] == 0x33) && (data[7] == 0x44)) { for (int i = 0; i <= 5; i++) { rdata[i] = data[i]; } cflag = 1; ss = byteToHexStr(rdata); } } textBox1.Text = ss; //生成链接数据库字符串 string conStr = "provider=Microsoft.ACE.OLEDB.12.0;Persist Security Info=False;Data Source=" + "myNewIC.mdb"; //创建数据库连接 OleDbConnection conn = new OleDbConnection(conStr); //打开数据库连接 conn.Open(); OleDbDataReader myReader = null; //sql语句 string sql = "select * from myTable where cstr(卡号)='" + ss + "'"; //命令 OleDbCommand cmd = new OleDbCommand(sql, conn); myReader = cmd.ExecuteReader(); if (myReader.Read()) //是否查到卡号 { if (myReader.GetBoolean(3)) //是否挂失 { www(); MessageBox.Show("此卡已挂失", "Warning"); cflag = 0; ID.Visible = false; textBox1.Visible = false; button1.Visible = true; textBox1.Text = ""; } else { if (myReader.GetBoolean(1)) //是否管理员 { MessageBox.Show("请点击管理员登录", "Warning"); cflag = 0; myport.Close(); ID.Visible = false; textBox1.Visible = false; button1.Visible = true; textBox1.Text = ""; } else { //把余额转后后发送给下位机 decimal money = myReader.GetDecimal(4); if (money >= 100) { string mm = Convert.ToString(money * 10); byte[] myMoney = new byte[8]; myMoney[0] = 0xaa; myMoney[5] = 0x00; myMoney[6] = 0x33; myMoney[7] = 0x44; for (int x = 0; x <= 3; x++) { if (mm[x] == '0') { myMoney[4 - x] = 0x30; } if (mm[x] == '1') { myMoney[4 - x] = 0x31; } if (mm[x] == '2') { myMoney[4 - x] = 0x32; } if (mm[x] == '3') { myMoney[4 - x] = 0x33; } if (mm[x] == '4') { myMoney[4 - x] = 0x34; } if (mm[x] == '5') { myMoney[4 - x] = 0x35; } if (mm[x] == '6') { myMoney[4 - x] = 0x36; } if (mm[x] == '7') { myMoney[4 - x] = 0x37; } if (mm[x] == '8') { myMoney[4 - x] = 0x38; } if (mm[x] == '9') { myMoney[4 - x] = 0x39; } } myport.Write(myMoney, 0, 8); } tString = textBox1.Text; //传递给user窗口 user form = new user(); form.ShowDialog(); this.Hide(); myport.Close(); cflag = 0; } } } else { www(); MessageBox.Show("此卡无效", "Warning"); cflag = 0; ID.Visible = false; textBox1.Visible = false; button1.Visible = true; textBox1.Text = ""; } myReader.Close(); conn.Close(); myport.Close(); } else { MessageBox.Show("请打开串口", "Warning"); ID.Visible = false; textBox1.Visible = false; button1.Visible = true; } }
public List <SinoSZToolFlowDesign.DOL.Flow_StateDefine> GetFlowStatusByFlow(SinoSZToolFlowDesign.DOL.Flow_BaseDefine flow_BaseDefine) { List <Flow_StateDefine> _ret = new List <Flow_StateDefine>(); string _sql = "select [ID],[STATENAME],[STATEDISPLAYNAME],[STATEDESCRIPT],[STATETYPE],[DISPLAYORDER] "; _sql += " FROM [FLOW_ENTITYSTATUS] where [FLOWID] = @FLOWID"; using (OleDbConnection cn = OpenConnection(connectString)) { try { OleDbCommand _cmd = new OleDbCommand(_sql, cn); _cmd.Parameters.Add(new OleDbParameter("@FLOWID", flow_BaseDefine.ID)); OleDbDataReader dr = _cmd.ExecuteReader(); while (dr.Read()) { Flow_StateDefine _fd = new Flow_StateDefine(dr.IsDBNull(0) ? "" : dr.GetString(0), dr.IsDBNull(1) ? "" : dr.GetString(1), dr.IsDBNull(2) ? "" : dr.GetString(2), dr.IsDBNull(3) ? "" : dr.GetString(3), dr.IsDBNull(4) ? "" : dr.GetString(4), dr.IsDBNull(5) ? 0 : Convert.ToInt32(dr.GetDecimal(5)) ); _ret.Add(_fd); } dr.Close(); cn.Close(); return(_ret); } catch (Exception e) { throw e; //写系统错误日志 return(null); } } }
/// <summary> /// method to pull data from database /// </summary> /// <returns></returns> public static List <clsItemsLogic> SelectItem() { List <clsItemsLogic> items = new List <clsItemsLogic>(); using (OleDbConnection db = clsItemsSQL.GetConnection()) { db.Open(); clsItemsSQL sql = new clsItemsSQL(); OleDbCommand command = new OleDbCommand(sql.select(), db); OleDbDataReader read = command.ExecuteReader(); while (read.Read()) { items.Add(new clsItemsLogic() { ItemCode = read.GetString(0) ?? "", ItemDesc = read.GetString(1) ?? "", ItemPrice = read.GetDecimal(2) }); } db.Close(); } return(items); }
public List <SinoSZToolFlowDesign.DOL.Flow_StateActionDefine> GetFlowStatusAction(SinoSZToolFlowDesign.DOL.Flow_StateDefine flow_StateDefine) { List <Flow_StateActionDefine> _ret = new List <Flow_StateActionDefine>(); string _sql = "select A.[ID],A.[ACTIONNAME],A.[ACTIONTITLE], "; _sql += "B.[ID],B.[STATENAME],B.[STATEDISPLAYNAME],B.[STATEDESCRIPT],B.[STATETYPE],B.[DISPLAYORDER], "; _sql += "A.[ACTIONTYPE],A.[USERTYPE],A.[DISPLAYORDER],A.[ACTIONPARAM] "; _sql += " FROM [FLOW_STATETRANSITION] A,[FLOW_ENTITYSTATUS] B where A.[STATEID] = @STATEID "; _sql += " and B.[ID] = A.[TARGETSTATEID] "; using (OleDbConnection cn = OpenConnection(connectString)) { try { OleDbCommand _cmd = new OleDbCommand(_sql, cn); _cmd.Parameters.Add(new OleDbParameter("@STATEID", flow_StateDefine.ID)); OleDbDataReader dr = _cmd.ExecuteReader(); while (dr.Read()) { Flow_StateDefine _endStateDefine = new Flow_StateDefine(dr.IsDBNull(3) ? "" : dr.GetString(3), dr.IsDBNull(4) ? "" : dr.GetString(4), dr.IsDBNull(5) ? "" : dr.GetString(5), dr.IsDBNull(6) ? "" : dr.GetString(6), dr.IsDBNull(7) ? "" : dr.GetString(7), dr.IsDBNull(8) ? 0 : Convert.ToInt32(dr.GetDecimal(8)) ); Flow_StateActionDefine _sa = new Flow_StateActionDefine(dr.IsDBNull(0) ? "" : dr.GetString(0), dr.IsDBNull(1) ? "" : dr.GetString(1), dr.IsDBNull(2) ? "" : dr.GetString(2), flow_StateDefine, _endStateDefine, dr.IsDBNull(9) ? "" : dr.GetString(9), dr.IsDBNull(10) ? 0 : Convert.ToInt32(dr.GetDecimal(10)), dr.IsDBNull(11) ? 0 : Convert.ToInt32(dr.GetDecimal(11)), dr.IsDBNull(12) ? "" : dr.GetString(12) ); _ret.Add(_sa); } dr.Close(); cn.Close(); return(_ret); } catch (Exception e) { throw e; //写系统错误日志 return(null); } } }
//find all undergraduate students, graduate students, faculty, and chairpersons //creates an instance of each class public void createOwlMemberList() { using (OleDbConnection connection = new OleDbConnection(strConnection)) { try { connection.Open(); using (OleDbCommand command1 = new OleDbCommand("SELECT OWLMEMBER.fldID, OWLMEMBER.fldName, OWLMEMBER.fldBirthDate, " + "STUDENT.fldMajor, STUDENT.fldGPA " + "FROM (OWLMEMBER INNER JOIN STUDENT ON OWLMEMBER.fldID = STUDENT.fldID);", connection)) { OleDbDataReader reader = command1.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) { int id = reader.GetInt32(0); string name = reader.GetString(1); DateTime bday = reader.GetDateTime(2); string major = reader.GetString(3); decimal gpa = reader.GetDecimal(4); Student student = new Student(id, name, bday, major, gpa); //GlobalData.owlList.addToList(student); } } } using (OleDbCommand command2 = new OleDbCommand("SELECT OWLMEMBER.fldID, OWLMEMBER.fldName, OWLMEMBER.fldBirthDate, " + "STUDENT.fldMajor, STUDENT.fldGPA, " + "UNDERGRADUATESTUDENT.fldTuition, UNDERGRADUATESTUDENT.fldYear, UNDERGRADUATESTUDENT.fldCredits " + "FROM (OWLMEMBER INNER JOIN STUDENT ON OWLMEMBER.fldID = STUDENT.fldID) INNER JOIN UNDERGRADUATESTUDENT ON STUDENT.fldID = UNDERGRADUATESTUDENT.fldID;", connection)) { OleDbDataReader reader = command2.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) { int id = reader.GetInt32(0); string name = reader.GetString(1); DateTime bday = reader.GetDateTime(2); string major = reader.GetString(3); decimal gpa = reader.GetDecimal(4); decimal tuition = reader.GetDecimal(5); string year = reader.GetString(6); int credits = reader.GetInt32(7); UndergradStudent UG = new UndergradStudent(id, name, bday, major, gpa, tuition, year, credits); //GlobalData.owlList.addToList(UG); } } } using (OleDbCommand command3 = new OleDbCommand("SELECT OWLMEMBER.fldID, OWLMEMBER.fldName,OWLMEMBER.fldBirthdate, " + "STUDENT.fldMajor, STUDENT.fldGPA, " + "GRADUATESTUDENT.fldDegreeProgram, GRADUATESTUDENT.fldStipend " + "FROM (OWLMEMBER INNER JOIN STUDENT ON OWLMEMBER.fldID = STUDENT.fldID) INNER JOIN GRADUATESTUDENT ON STUDENT.fldID = GRADUATESTUDENT.fldID;", connection)) { OleDbDataReader reader = command3.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) { int id = reader.GetInt32(0); string name = reader.GetString(1); DateTime bday = reader.GetDateTime(2); string major = reader.GetString(3); decimal gpa = reader.GetDecimal(4); string program = reader.GetString(5); decimal stipend = reader.GetDecimal(6); GradStudent GS = new GradStudent(id, name, bday, major, gpa, program, stipend); // GlobalData.owlList.addToList(GS); } } } using (OleDbCommand command4 = new OleDbCommand("SELECT OWLMEMBER.fldID, OWLMEMBER.fldName, OWLMEMBER.fldBirthDate, " + "FACULTY.fldDepartment, FACULTY.fldRank " + "FROM OWLMEMBER INNER JOIN FACULTY ON OWLMEMBER.fldID = FACULTY.fldId;", connection)) { OleDbDataReader reader = command4.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) { int id = reader.GetInt32(0); string name = reader.GetString(1); DateTime bday = reader.GetDateTime(2); string dept = Convert.ToString(reader.GetTextReader(3)); string rank = Convert.ToString(reader.GetTextReader(4)); Faculty member = new Faculty(id, name, bday, dept, rank); //GlobalData.owlList.addToList(member); } } } using (OleDbCommand command5 = new OleDbCommand("SELECT OWLMEMBER.fldID, OWLMEMBER.fldName,OWLMEMBER.fldBirthdate, " + "FACULTY.fldDepartment, FACULTY.fldRank, CHAIRPERSON.fldStipend " + "FROM (OWLMEMBER INNER JOIN FACULTY ON OWLMEMBER.fldID = FACULTY.fldId) INNER JOIN CHAIRPERSON ON FACULTY.fldID = CHAIRPERSON.fldID;", connection)) { OleDbDataReader reader = command5.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) { int id = reader.GetInt32(0); string name = reader.GetString(1); DateTime bday = reader.GetDateTime(2); string dept = reader.GetString(3); string rank = reader.GetString(4); decimal stipend = reader.GetDecimal(5); Chairperson chair = new Chairperson(id, name, bday, dept, rank, stipend); //GlobalData.owlList.addToList(chair); } } } connection.Close(); } catch (OleDbException ex) { Console.Write("Error: " + ex.Message); connection.Close(); } } }
public List <LoanPaymentVO> GetLoanPayments(int loanId) { List <LoanPaymentVO> results = new List <LoanPaymentVO>(); try { command = new OleDbCommand("SELECT loan_id, payment_amount FROM Loan_Payment WHERE loan_id = '" + loanId + "'", connection); dataReader = command.ExecuteReader(); while (dataReader.Read()) { results.Add(new LoanPaymentVO(-1, dataReader.GetInt32(0), (double)dataReader.GetDecimal(1))); } } catch (Exception e) { if (OnFormError != null) { OnFormError(this, new FormErrorArg("Error getting information from database: " + e.Message, e)); } } return(results); }