Example #1
0
        /// <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();
                    }
                }
            }
        }
Example #2
0
        /// <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();
                }
            }
        }
Example #3
0
        /// <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));
            }
        }
Example #4
0
        /// <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));
            }
        }
Example #5
0
        /// <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);
        }
Example #6
0
        /// <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));
            }
        }
Example #7
0
        /// <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();
            }
        }
Example #8
0
        /// <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);
        }
Example #9
0
        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);
        }
Example #10
0
        //若传入的是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();
            }
        }