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