/// <summary> /// 获取DataSet数据列表 /// </summary> /// <param name="sql">sql语句</param> /// <param name="ctype">类型</param> /// <param name="dataname">内存表</param> /// <param name="param">参数</param> /// <returns>返回自定义内存表</returns> public DataSet GetDataSet(string sql, CommandType ctype, string dataname, params IDataParameter[] param) { Open(); var cmd = new DB2Command(); PrepareCommand(cmd, _connSql, null, ctype, sql, param); using (var dap = new DB2DataAdapter(cmd)) { var ds = new DataSet(); try { dap.Fill(ds, dataname); dap.Dispose(); cmd.Parameters.Clear(); cmd.Dispose(); return(ds); } catch (DB2Exception ex) { throw new Exception(ex.Message); } finally { Close(); } } }
public ICustomActivityResult Execute() { DB2Connection con = null; DB2DataAdapter adapter = null; DataTable dt = new DataTable("resultSet"); try { DB2ConnectionStringBuilder cnb = new DB2ConnectionStringBuilder(ConnectionString); if (!string.IsNullOrEmpty(UserName)) { cnb.UserID = UserName; cnb.Password = Password; } con = new DB2Connection(cnb.ConnectionString); con.Open(); using (DB2Command command = new DB2Command(Query, con)) { command.CommandType = System.Data.CommandType.Text; command.CommandTimeout = Convert.ToInt32(TimeInSeconds); adapter = new DB2DataAdapter(command); adapter.Fill(dt); } return(this.GenerateActivityResult(dt)); } finally { if (adapter != null) { adapter.Dispose(); } adapter = null; if (con != null) { con.Close(); con.Dispose(); } con = null; dt.Dispose(); dt = null; } }
private DataTable GetDB2Table(CustomListData data) { DB2Connection con = GetConnection(data); data.Properties.TryGetValue("SQL Statement", StringComparison.OrdinalIgnoreCase, out var SQLStatement); DB2DataAdapter da = new DB2DataAdapter(new DB2Command(SQLStatement, con)); DataTable db2result = new DataTable(); da.Fill(db2result); con.Close(); da.Dispose(); return(db2result); }
private void treeRootView_AfterSelect(object sender, TreeViewEventArgs e) { try { if (_tableNameDic.ContainsKey(treeRootView.SelectedNode.Name)) { _dataTable = new DataTable(); string strSql = string.Format("SELECT * FROM {0}", _tableNameDic[treeRootView.SelectedNode.Name]); conn = new DB2Connection(GetConnString("localAPP")); _dataAdapter = new DB2DataAdapter(strSql, conn);; _dataAdapter.Fill(_dataTable); cmdBuilder = new DB2CommandBuilder(_dataAdapter); //_dataBindSource = new BindingSource(); _dataBindSource.DataSource = _dataTable; dgv.DataSource = _dataTable; _selectNodeName = treeRootView.SelectedNode.Name; //_dataBindSource.DataSource = _dataTable; } else { if (_dataAdapter != null) { _dataAdapter.Dispose(); } _dataTable = new DataTable(); dgv.DataSource = _dataTable; _dataBindSource.DataSource = _dataTable; } } catch (Exception) { throw; } finally { } }
//若传入的是excel文件目录则插入数据库,否则按照传入的语句返回查询结果 internal DataSet UpToDb2(string path) { DataSet dtst = new DataSet(); DB2Connection conn = new DB2Connection() { ConnectionString = "Database=cig;Password=Shaorong01;User ID=db2admin" }; DB2Command comm = new DB2Command { Connection = conn }; DB2DataAdapter codata = new DB2DataAdapter { SelectCommand = comm }; DB2CommandBuilder cb = new DB2CommandBuilder(codata);//构建数据库插入语句 try { if (path.IndexOf("xls") > 0) { //从excel读取策略日期参数; pam = Excel_cyc(path); //将投放策略日期存入数据库 if (pam.Length == 7) { string selectSql = string.Format("select * from DB2ADMIN.SALES_CYCLE where cyclcode='{0}'", pam[0]); DataTable temp1 = new DataTable(); comm.CommandText = selectSql; codata.Fill(temp1); if (temp1.Rows.Count < 1) { string insertSql = string.Format("INSERT INTO DB2ADMIN.SALES_CYCLE(YEAR, MONTH, WEEK, BEGINDATE, ENDDATE, " + "CYCLCODE, CYCLNAME) VALUES ('{0}','{1}','{2}','{3}','{4}','{5}','{6}')", pam[1], pam[2], pam[3], pam[4], pam[5], pam[0], pam[6]); comm.CommandText = insertSql; conn.Open(); comm.ExecuteNonQuery(); conn.Close(); } temp1.Dispose(); } string cycle = pam[0]; comm.CommandText = "SELECT CYCLE, PRODUCT_CODE, PRODUCT_NAME, SALE_DEPT, TYPE_NAME, CUST_TYPE, SGP_NUM ,QR_ID FROM DB2ADMIN.QUANTITIES_RECORD_NEW WHERE CYCLE='" + cycle + "'"; codata.Fill(dtst, "excel"); DataSet stt1 = new DataSet(); //从excel读取策略内容 stt1 = ReadExcel(path); if (stt1 != null) { //将excel内容转换为数据库结构 handleDataTable(stt1, cycle, ref dtst); } //丢弃读取的excel内容 stt1.Dispose(); //向数据库写入 codata.Update(dtst.Tables["excel"].Select(null, null, DataViewRowState.Added)); dtst.Clear(); } else { comm.CommandText = path; codata.Fill(dtst, "pam"); } return(dtst); // DataSetValue(dtst); } catch (Exception ex) { MessageBox.Show(ex.Message); return(null); } finally { codata.Dispose(); comm.Dispose(); conn.Dispose(); dtst.Dispose(); } }