/// <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();
}
}