/// <summary>
/// 执行多条SQL语句，实现数据库事务。
/// </summary>
/// <param name="SQLStringList">ArrayList</param>
public static void ExecuteSqlTran(ArrayList sqlList, string ConString)
{
bool mustCloseConnection = false;
using (DB2Connection conn = new DB2Connection(ConString))
{
conn.Open();
using (DB2Transaction trans = conn.BeginTransaction())
{
DB2Command cmd = new DB2Command();
try
{
for (int i = 0; i < sqlList.Count; i++)
{
string cmdText = sqlList[i].ToString();
PrepareCommand(cmd, conn, trans, CommandType.Text, cmdText, null, out mustCloseConnection);
int val = cmd.ExecuteNonQuery();
}
trans.Commit();
}
catch
{
trans.Rollback();
throw;
}
finally
{
conn.Close();
cmd.Dispose();
}
}
}
}
/// <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();
}
}
}
/// <summary>
/// 执行查询,返回查询结果的第一行第一列
/// </summary>
/// <typeparam name="T">返回值类型</typeparam>
/// <param name="sql">SQL查询语句</param>
/// <param name="parameters">参数集合</param>
/// <returns>查询结果的第一行第一列</returns>
public override T ExecuteScalar <T>(string sql, KdtParameterCollection parameters)
{
try
{
T value = default(T);
// 执行SQL命令
using (DB2Command cmd = new DB2Command(ReplaceSqlText(ReplaceSqlText(sql, parameters), parameters), _db2Cn))
{
InitCommand(cmd); // 初始化
// 赋值参数
var hasConvertParams = ConvertToSqlParameter(parameters);
foreach (var item in hasConvertParams)
{
cmd.Parameters.Add(item.Value);
}
value = cmd.ExecuteScalar().Convert <T>();
cmd.Cancel();
cmd.Dispose();
}
return(value);
}
catch (Exception ex)
{
KdtLoger.Instance.Error(ex);
throw new DataException(string.Format("执行查询,返回查询结果的第一行第一列错误,原因为:{0}", ex.Message));
}
}
/// <summary>
/// 执行非查询SQL语句
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="parameters">参数集合</param>
/// <returns>受影响的记录数</returns>
public override int ExecuteNoQuery(string sql, KdtParameterCollection parameters)
{
try
{
int effected = 0;
// 执行SQL命令
using (DB2Command cmd = new DB2Command(ReplaceSqlText(sql, parameters), _db2Cn))
{
InitCommand(cmd); // 初始化
// 赋值参数
var hasConvertParams = ConvertToSqlParameter(parameters);
foreach (var item in hasConvertParams)
{
cmd.Parameters.Add(item.Value);
}
effected = cmd.ExecuteNonQuery();
cmd.Cancel();
cmd.Dispose();
}
return(effected);
}
catch (Exception ex)
{
KdtLoger.Instance.Error(ex);
throw new DataException(string.Format("执行非查询SQL语句错误,原因为:{0}", ex.Message));
}
}
/// <summary>
/// 获取数据记录集列表
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="ctype">类型</param>
/// <param name="param">参数</param>
/// <returns>返回记录集列表</returns>
public IDataReader GetDataReader(string sql, CommandType ctype, params IDataParameter[] param)
{
Open();
var cmd = new DB2Command();
PrepareCommand(cmd, _connSql, null, ctype, sql, param);
var dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
cmd.Dispose();
return(dr);
}
/// <summary>
/// 读取KEY VALUE值
/// </summary>
/// <param name="sql"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public override KeyValueCollection ExecuteKVCollection(string sql, KdtParameterCollection parameters)
{
try
{
KeyValueCollection entity = new KeyValueCollection();
// 执行SQL命令
using (DB2Command cmd = new DB2Command(ReplaceSqlText(sql, parameters), _db2Cn))
{
InitCommand(cmd); // 初始化
// 赋值参数
var hasConvertParams = ConvertToSqlParameter(parameters);
foreach (var item in hasConvertParams)
{
cmd.Parameters.Add(item.Value);
}
// 执行填充数据
using (DB2DataReader reader = cmd.ExecuteReader())
{
entity = GetEntity(reader);
// 反射参数值
ReflectParamValue(parameters, hasConvertParams);
reader.Close();
reader.Dispose();
}
cmd.Cancel();
cmd.Dispose();
}
return(entity);
}
catch (DB2Exception me)
{
KdtLoger.Instance.Error(me);
throw new DataException(me.Message);
}
catch (Exception ex)
{
KdtLoger.Instance.Error(ex);
throw new DataException(string.Format("执行SQL查询,返回数据集合错误,原因为:{0}", ex.Message));
}
}
/// <summary>
/// 执行一条计算查询结果语句，返回查询结果（object）。
/// </summary>
/// <param name="sql">计算查询结果语句</param>
/// <param name="ctype">类型</param>
/// <param name="param">参数</param>
/// <returns>查询结果（object）</returns>
public object GetExecuteScalar(string sql, CommandType ctype, params IDataParameter[] param)
{
Open();
var cmd = new DB2Command();
try
{
PrepareCommand(cmd, _connSql, null, ctype, sql, param);
var obj = cmd.ExecuteScalar();
cmd.Parameters.Clear();
cmd.Dispose();
return(Equals(obj, null) || Equals(obj, DBNull.Value) ? null : obj);
}
finally
{
Close();
}
}
/// <summary>
/// 执行sql语句
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="ctype">类型</param>
/// <param name="param">参数</param>
public int GetExecuteNonQuery(string sql, CommandType ctype, params IDataParameter[] param)
{
Open();
int i;
var cmd = new DB2Command();
try
{
PrepareCommand(cmd, _connSql, null, ctype, sql, param);
i = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
cmd.Dispose();
}
finally
{
Close();
}
return(i);
}
public static List <DatosDeConsultaAltaTemprana> ConsultaAltaTemprana(decimal cuil)
{
log.InfoFormat("Se ejecuta el método YH_ConsultaAltaTemprana con el parámetro {0}", cuil);
List <DatosDeConsultaAltaTemprana> response = new List <DatosDeConsultaAltaTemprana>();
DB2Command cmd = AdministradorDeConexion.ObtenerComando();
using (DB2Connection conexion = AdministradorDeConexion.ObtenerConnexion())
{
try
{
cmd.CommandText = string.Format("{0}.YH_ConsultaAltaTemprana", AdministradorDeConexion.GetSchema());
cmd.Parameters.Add(new DB2Parameter("@cuil", cuil));
cmd.Connection = conexion;
conexion.Open();
var reader = cmd.ExecuteReader();
while (reader.Read())
{
response.Add(Adapter.toConsultaAltaTempranaResponse(reader));
}
}
catch (Exception ex)
{
log.ErrorFormat("Se produjo un error en el método YH_ConsultaAltaTemprana {0} - {1} - {2}", ex.Message, ex.Source, ex.StackTrace);
throw ex;
}
finally
{
if (conexion.IsOpen)
{
conexion.Close();
}
conexion.Dispose();
cmd.Dispose();
}
}
return(response);
}
//若传入的是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();
}
}
