public DataTable SelectFs(DateTime time1, DateTime time2, string mchtid, string termid, string actnbr) { string[] date = new string[2]; date[0] = dealdate(time1); date[1] = dealdate(time2); string sql = "SELECT rownum 序号,mchtname 商户名称,mchtid 商户号,termid 终端号,settledate 交易日期,transtime 交易时间,tracenbr 流水号,refnbr 参考号,t1.transname 交易类型,actnbr 转出卡号,cardtype 卡性质,amount/100 金额,-mchtfee/100 手续费,amount/100+mchtfee/100 净额 from qsjg_fs_his t left join cs_transcode t1 on t1.transid = t.transtype where transtype!='S00' and respcode='00' and "; if (date[0].CompareTo(date[1]) > 0) { MessageBox.Show("起始时间不能大于结束时间", "提示"); return(tmptable); } DbHelper = new MyDbHelper(MyDbHelper.connectstring_cardqs); int i = 1; int allNum = 0; while (date[0].CompareTo(date[1]) <= 0) //循环每天查询 { //查询语句设置 if (date[0].CompareTo("") != 0) { sql = sql + "settledate ='" + date[0] + "' and "; } if (termid.CompareTo("") != 0) { sql = sql + "termid = '" + termid + "' and "; } if (mchtid.CompareTo("") != 0) { sql = sql + "mchtid ='" + mchtid + "'and "; } if (actnbr.CompareTo("") != 0) { sql = sql + "actnbr ='" + actnbr + "'and "; } sql = sql + "1=1 order by rownum asc"; time1 = time1.AddDays(1); date[0] = dealdate(time1); if (i == 1 && tmptable == null) { tmptable = DbHelper.ExecuteDataTable("result", sql); } else { DataTable tmp = DbHelper.ExecuteDataTable("result", sql); tmptable.Merge(tmp); } i++; allNum = tmptable.Rows.Count; sql = "SELECT rownum+" + allNum + " 序号, mchtname 商户名称,mchtid 商户号,termid 终端号,settledate 交易日期,transtime 交易时间,tracenbr 流水号,refnbr 参考号,t1.transname 交易类型,actnbr 转出卡号,cardtype 卡性质,amount/100 金额,-mchtfee/100 手续费,amount/100+mchtfee/100 净额 from qsjg_fs_his t left join cs_transcode t1 on t1.transid = t.transtype where transtype!='S00' and respcode='00' and "; } return(tmptable); }
public IDbClass GetDbClass() { IDbClass dbClass = null; string connectString = GetConnectString(); dbClass = MyDbHelper.GetDbClass(connectString, dbType); if (dbClass != null) { dbClass.Open(); } return(dbClass); }
public DataTable SelectXft(DateTime time1, DateTime time2, string psam, string areatelno) { string[] date = new string[2]; date[0] = dealdate(time1); date[1] = dealdate(time2); string sql = "select rownum 序号,trim(area)||trim(telno) 绑定电话,psam,settledate 交易日期,transtime 交易时间,t1.appname 交易类型,outpan 转出卡号,inpan 转入卡号,amount/100 金额,-mchtfee/100 手续费,amount/100+mchtfee/100 净额 from qsjg_xft_his t,apptype t1 where t1.apptype=t.apptype and payretcode='00' and "; if (date[0].CompareTo(date[1]) > 0) { MessageBox.Show("起始时间不能大于结束时间", "提示"); return(tmptable); } DbHelper = new MyDbHelper(MyDbHelper.connectstring_cardqs); int i = 1; int allNum = 0; while (date[0].CompareTo(date[1]) <= 0) //循环每天查询 { //查询语句设置 if (date[0].CompareTo("") != 0) { sql = sql + "settledate ='" + date[0] + "' and "; } if (psam.CompareTo("") != 0) { sql = sql + "psam = '" + psam + "' and "; } if (areatelno.Trim().CompareTo("-") != 0) { sql = sql + "trim(area)||trim(telno) ='" + areatelno.Trim().Remove(4, 1) + "'and "; } sql = sql + "1=1 order by rownum asc"; time1 = time1.AddDays(1); date[0] = dealdate(time1); if (i == 1 && tmptable == null) { tmptable = DbHelper.ExecuteDataTable("result", sql); } else { DataTable tmp = DbHelper.ExecuteDataTable("result", sql); tmptable.Merge(tmp); } i++; allNum = tmptable.Rows.Count; sql = "select rownum+" + allNum + " 序号,trim(area)||trim(telno) 绑定电话,psam,settledate 交易日期,transtime 交易时间,t1.appname 交易类型,outpan 转出卡号,inpan 转入卡号,amount/100 金额,-mchtfee/100 手续费,amount/100+mchtfee/100 净额 from qsjg_xft_his t,apptype t1 where t1.apptype=t.apptype and payretcode='00' and "; } return(tmptable); }
//载入窗体 private void datereport_Load(object sender, EventArgs e) { string sComboData; try { if (loading.ibb_flag == "1") { DbHelper = new MyDbHelper(MyDbHelper.connectstring_cardqs); OleDbDataReader dbReader = DbHelper.ExecuteDataReader("select * from report where report_type!='0' order by proc_name "); while (dbReader.Read()) { sComboData = dbReader.GetString(0).ToString().Trim() + " - " + dbReader.GetString(1).ToString().Trim(); combo.Items.Add((object)sComboData); } } else if (loading.ibb_flag == "2") { DbHelper = new MyDbHelper(MyDbHelper.connectstring_cardqs); OleDbDataReader dbReader = DbHelper.ExecuteDataReader("select * from report_xft where report_type!='0' order by report_num "); while (dbReader.Read()) { sComboData = dbReader.GetString(0).ToString().Trim() + " - " + dbReader.GetString(1).ToString().Trim(); combo.Items.Add((object)sComboData); } } else if (loading.ibb_flag == "3") { DbHelper = new MyDbHelper(MyDbHelper.connectstring_prec); OleDbDataReader dbReader = DbHelper.ExecuteDataReader("select * from report_bf where report_type!='0' order by report_num "); while (dbReader.Read()) { sComboData = dbReader.GetString(0).ToString().Trim() + " - " + dbReader.GetString(1).ToString().Trim(); combo.Items.Add((object)sComboData); } } } catch (Exception ex) { MyLog.Log("3", "向combox读取数据失败:" + ex.Message); } finally { if (DbHelper != null) { DbHelper.Dispose();//新加的 } } }
private void FrmNormalView_Load(object sender, EventArgs e) { IGetAttribute attri = _object as IGetAttribute; if (attri != null) { this.dgvNormal.DataSource = NameAliasValue.ToDataTable(attri.GetAttributes()); } ICreateSql creatsql = _object as ICreateSql; if (creatsql != null) { CreateSqlDelegate action = MyDbHelper.GetCreateSqlFunction(creatsql, _dbClass.GetClassDbType()); if (action != null) { List <CreateSqlObject> csos = action(_dbClass.GetCurrentTableSpaceName()); tbSql.Text = CreateSqlObject.ToCollectionSqls(csos); } } }
public void RunProcedure_one(string sProcName, string ddate) { try { MyDbHelper DbHelper = new MyDbHelper(MyDbHelper.connectstring_cardqs); OleDbParameter[] sqlparam = new OleDbParameter[2]; sqlparam[0] = MyDbHelper.MakeInParam("ddate", OleDbType.Char, 8, ddate); sqlparam[1] = MyDbHelper.MakeOutParam("ret", OleDbType.Char, 200, null); DbHelper.ExecuteNonQuery(sProcName.Trim(), sqlparam, true); } catch (Exception ex) { MyLog.Log("3", "执行单日期" + sProcName + "存储过程流程时发生异常:" + ex.Message); } finally { if (DbHelper != null) { DbHelper.Dispose();//新加的 } } }
public void RunProcedure(string sProcName, string beginDate, string endDate, string constr) { try { MyDbHelper DbHelper = new MyDbHelper(constr); OleDbParameter[] sqlparam = new OleDbParameter[3]; sqlparam[0] = MyDbHelper.MakeInParam("date1", OleDbType.Char, 8, beginDate); sqlparam[1] = MyDbHelper.MakeInParam("date2", OleDbType.Char, 8, endDate); sqlparam[2] = MyDbHelper.MakeOutParam("ret", OleDbType.Char, 200, null); DbHelper.ExecuteNonQuery(sProcName.Trim(), sqlparam, true); } catch (Exception ex) { MyLog.Log("3", "执行双日期" + sProcName + "存储过程流程时发生异常:" + ex.Message); } finally { if (DbHelper != null) { DbHelper.Dispose();//新加的 } } }
private DataTable CreateDataSet(string sTable, string constr) { DataTable table = null; try { DbHelper = new MyDbHelper(constr); string strsql = "select * from " + sTable + ""; table = DbHelper.ExecuteDataTable(sTable, strsql); } catch (Exception ex) { MyLog.Log("3", "读取数据失败:" + ex.Message); } finally { if (DbHelper != null) { DbHelper.Dispose();//新加的 } } return(table); }
private void LoadSql() { StringBuilder sb = new StringBuilder(); sb.AppendLine(CreateSqlObject.ToCollectionSqls(MyDbHelper.GetCreateSqlFunction(_table, _dbClass.GetClassDbType())(null))); List <IConstraintClass> ccs = _dbClass.GetConstraints(_table.TableName); foreach (IConstraintClass item in ccs) { sb.AppendLine(CreateSqlObject.ToCollectionSqls( MyDbHelper.GetCreateSqlFunction(item, _dbClass.GetClassDbType())(null) )); } List <IIndexClass> ics = _dbClass.GetIndexs(_table.TableName); foreach (IIndexClass item in ics) { sb.AppendLine(CreateSqlObject.ToCollectionSqls( MyDbHelper.GetCreateSqlFunction(item, _dbClass.GetClassDbType())(null) )); } tbSql.Text = sb.ToString().TrimEnd('\r', '\n'); }
public Trans(string connectionString) { conn = MyDbHelper.CreateConnection(connectionString); conn.Open(); dbTrans = conn.BeginTransaction(); }
public Trans() { conn = MyDbHelper.CreateConnection(); conn.Open(); dbTrans = conn.BeginTransaction(); }
private void pictureBox1_Click(object sender, EventArgs e) { reflow_control r_con = new reflow_control(); string reportNum, beginDate, endDate; //根据列表中报表编号找到对应的数据库表中数据表名,模板名,SHEET名 string sTableName = ""; string sModelName = ""; string sSheetName = ""; string sReportName = ""; string sProcName = ""; int iRowNum = listView.Items.Count; //进度条代码 proBar.Value = 0; proBar.Maximum = iRowNum * 10; proBar.Step = 1; for (int i = 0; i < iRowNum; i++) { beginDate = listView.Items[i].SubItems[1].Text.Trim(); endDate = listView.Items[i].SubItems[2].Text.Trim(); reportNum = listView.Items[i].SubItems[0].Text.Substring(0, 2); string strCmd = ""; try { string constr = ""; if (loading.ibb_flag == "1") { strCmd = "select * from report where REPORT_NUM = " + reportNum; constr = MyDbHelper.connectstring_cardqs; } else if (loading.ibb_flag == "2") { strCmd = "select * from report_xft where REPORT_NUM = " + reportNum; constr = MyDbHelper.connectstring_cardqs; } else if (loading.ibb_flag == "3") { strCmd = "select * from report_bf where REPORT_NUM = " + reportNum; constr = MyDbHelper.connectstring_prec; } DbHelper = new MyDbHelper(constr); OleDbDataReader dbReader = DbHelper.ExecuteDataReader(strCmd); if (dbReader.Read()) { sTableName = dbReader.GetString(4).ToString(); sModelName = dbReader.GetString(2).ToString(); sSheetName = dbReader.GetString(3).ToString(); sReportName = dbReader.GetString(1).ToString(); sProcName = dbReader.GetString(5).ToString(); } try { r_con.RunProcedure(sProcName.Trim(), beginDate, endDate, constr); } catch (Exception errorRunPro) { MessageBox.Show(errorRunPro.ToString()); } //写EXCEL文件 r_con.WriteExcelFile(sModelName, sTableName, sSheetName, beginDate, endDate, constr);//将表中内容放入EXCEL listView.Items[i].SubItems[3].Text = "完成统计"; proBar.Value = (i + 1) * 10; } catch (Exception ex) { MyLog.Log("3", "读取数据失败:" + ex.Message); } finally { if (DbHelper != null) { DbHelper.Dispose();//新加的 } } } }