Example #1
0
        /// <summary>
        /// /// 获取DataTable带参数
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="procName"></param>
        /// <param name="objParm"></param>
        /// <returns></returns>
        public DataTable GetDataTableFromProc(string conn, string procName, params IDataParameter[] objParm)
        {
            DataTable  dtRecord = null;
            SqlCommand cmd      = GetSqlCommand(conn, EnumExecType.ExecProc);

            try
            {
                cmd.CommandText = GetParm(procName);
                if (objParm != null && objParm.Length > 0)
                {
                    for (int i = 0; i < objParm.Length; i++)
                    {
                        if (objParm[i].Value == null)
                        {
                            objParm[i].Value = System.DBNull.Value;
                        }
                        else if (objParm[i].Direction == ParameterDirection.Output && objParm[i].DbType == DbType.String)
                        {
                            ((SqlParameter)objParm[i]).Size = 1000;
                        }
                        cmd.Parameters.Add((SqlParameter)objParm[i]);
                    }
                    SqlLog.OutPutParmLog(objParm);
                }
                SqlDataAdapter Adapter = new SqlDataAdapter();
                Adapter.SelectCommand = cmd;
                dtRecord = new DataTable();
                Adapter.Fill(dtRecord);
            }
            catch (System.Exception objEx)
            {
                dtRecord = null;
                throw objEx;
            }
            finally
            {
                if (cmd != null)
                {
                    cmd.Connection.Close();
                    cmd.Dispose();
                }
            }
            return(dtRecord);
        }
Example #2
0
        /// <summary>
        /// 执行SQL带参数
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="sql"></param>
        /// <param name="objParams"></param>
        /// <returns></returns>
        public int ExecSql(string conn, string sql, int step, params IDataParameter[] objParm)
        {
            SqlCommand cmd          = GetSqlCommand(conn, EnumExecType.ExecSql);
            int        affectedRows = 0;

            try
            {
                cmd.CommandText = GetParm(sql, step);
                if (objParm != null && objParm.Length > 0)
                {
                    for (int i = 0; i < objParm.Length; i++)
                    {
                        if (objParm[i].Value == null)
                        {
                            objParm[i].Value = System.DBNull.Value;
                        }
                        ((SqlParameter)objParm[i]).ParameterName = (i + 1 + step * 10).ToString();
                        cmd.Parameters.Add((SqlParameter)objParm[i]);
                    }
                    SqlLog.OutPutParmLog(objParm);
                }
                if (cmd.Connection.State == ConnectionState.Closed)
                {
                    cmd.Connection.Open();
                }
                affectedRows = cmd.ExecuteNonQuery();
            }
            catch (System.Exception objEx)
            {
                affectedRows = -1;
                throw objEx;
            }
            finally
            {
                if (cmd != null)
                {
                    cmd.Connection.Close();
                    cmd.Dispose();
                }
            }
            return(affectedRows);
        }
Example #3
0
        /// <summary>
        /// 获取绑定参数
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="loop"></param>
        /// <param name="isLog"></param>
        /// <returns></returns>
        private string GetParm(string sql, int loop, bool isLog)
        {
            int intPara = 1;
            int i       = -1;

            for (int idx = sql.IndexOf("?"); idx > 0; idx = sql.IndexOf("?"))
            {
                if (loop == 0)
                {
                    sql = sql.Substring(0, idx) + "@" + (intPara++) + sql.Substring(idx + 1, sql.Length - idx - 1);
                }
                else
                {
                    sql = sql.Substring(0, idx) + "@" + ((++i) + 1 + loop * 10).ToString() + sql.Substring(idx + 1, sql.Length - idx - 1);
                }
            }
            if (isLog)
            {
                SqlLog.OutPutSql(sql);
            }
            return(sql);
        }
Example #4
0
        public int ExecProc(OracleCommand cmd, string procName, params IDataParameter[] objParams)
        {
            int intAffectedRows = 0;

            try
            {
                cmd.CommandText = GetParam(procName);
                if (objParams != null)
                {
                    for (int i = 0; i < objParams.Length; i++)
                    {
                        if (objParams[i].Value == null)
                        {
                            objParams[i].Value = System.DBNull.Value;
                        }
                        else if (objParams[i].Direction == ParameterDirection.Output && objParams[i].DbType == DbType.String)
                        {
                            ((OracleParameter)objParams[i]).Size = 1000;
                        }
                        cmd.Parameters.Add((OracleParameter)objParams[i]);
                    }
                }
                SqlLog.OutPutParmLog(objParams);
                if (cmd.Connection.State == ConnectionState.Closed)
                {
                    cmd.Connection.Open();
                }
                intAffectedRows = cmd.ExecuteNonQuery();
            }
            catch (System.Exception objEx)
            {
                intAffectedRows = -1;
                throw objEx;
            }

            return(intAffectedRows);
        }
Example #5
0
        /// <summary>
        /// ExecSQLForBatch
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="sql"></param>
        /// <param name="objValuesArr"></param>
        /// <param name="objDbTypes"></param>
        /// <returns></returns>
        public int ExecSqlForBatch(string conn, EnumExecType execType, string sql, ref int step, object[][] objValuesArr, params DbType[] objDbTypes)
        {
            //EnumExecType execType = EnumExecType.ExecSql;
            //if (typeStr == "1")
            //    execType = EnumExecType.ExecSql;
            //else if (typeStr == "2")
            //    execType = EnumExecType.ExecSqlForBatch;
            //else if (typeStr == "3")
            //    execType = EnumExecType.ExecSqlForBatchSimpleInsert;
            //else if (typeStr == "4")
            //    execType = EnumExecType.ExecProc;

            // 2100参数限制
            int divNum = 2100;

            SqlCommand[] sqlCommandArr = null;
            if (objValuesArr[0].Length * objDbTypes.Length > 2100)
            {
                sqlCommandArr = new SqlCommand[((objValuesArr[0].Length * objDbTypes.Length) / 2100) + 1];
                for (int m = 0; m < sqlCommandArr.Length; m++)
                {
                    sqlCommandArr[m] = GetSqlCommand(conn, execType);
                }
            }
            else
            {
                sqlCommandArr    = new SqlCommand[1];
                sqlCommandArr[0] = GetSqlCommand(conn, execType);
            }

            int intAffectedRows = 0;
            int num             = 0;

            try
            {
                SqlDbType[]   enmSqlDbType = ConvertDbTypeToSqlDbType(objDbTypes);
                SqlParameter  param        = null;
                StringBuilder sb           = new StringBuilder();
                for (int i = 0; i < objValuesArr[0].Length; i++)
                {
                    sb.Append("---> " + Convert.ToString(i + 1) + Environment.NewLine);
                    for (int j = 0; j < objValuesArr.Length; j++)
                    {
                        if (objValuesArr[j][i] == null)
                        {
                            objValuesArr[j][i] = System.DBNull.Value;
                        }
                        sb.Append(Convert.ToString(j + 1) + ":= " + objValuesArr[j][i] + "; ");
                    }
                    sb.AppendLine();
                }

                int div = 0;
                for (int i = 0; i < objValuesArr[0].Length; i++)
                {
                    for (int j = 0; j < enmSqlDbType.Length; j++)
                    {
                        div = ((i + 1) * enmSqlDbType.Length) / divNum;
                        if (j + 1 < 10)
                        {
                            param = new SqlParameter(Convert.ToString((i + step) * 10) + Convert.ToString(j + 1), enmSqlDbType[j]);
                        }
                        else
                        {
                            param = new SqlParameter(Convert.ToString(i + step) + Convert.ToString(j + 1), enmSqlDbType[j]);
                        }
                        //param = new SqlParameter(Convert.ToString((i + step) * 10) + Convert.ToString(j + 1), enmSqlDbType[j]);
                        param.Direction = ParameterDirection.Input;
                        param.Value     = objValuesArr[j][i];
                        sqlCommandArr[div].Parameters.Add(param);
                    }
                    sqlCommandArr[div].CommandText = GetParm(sql, (i + step) * 10, (i == 0));

                    if (i == 0)
                    {
                        SqlLog.OutPutSql("values: " + sb.ToString());
                    }
                    if (sqlCommandArr[div].Connection.State == ConnectionState.Closed)
                    {
                        sqlCommandArr[div].Connection.Open();
                    }
                    intAffectedRows += sqlCommandArr[div].ExecuteNonQuery();
                    num              = i + step;
                }
            }
            catch (System.Exception objEx)
            {
                intAffectedRows = -1;
                throw objEx;
            }
            finally
            {
                step = num + 1;
            }
            return(intAffectedRows);
        }
Example #6
0
        /// <summary>
        /// 批量操作
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="sql"></param>
        /// <param name="objValuesArr"></param>
        /// <param name="objDbTypes"></param>
        /// <returns></returns>
        public int ExecSqlForBatch(string conn, string sql, ref int step, object[][] objValuesArr, params DbType[] objDbTypes)
        {
            SqlCommand cmd             = GetSqlCommand(conn, EnumExecType.ExecSql);
            int        intAffectedRows = 0;
            int        num             = 0;

            try
            {
                SqlDbType[]   enmSqlDbType = ConvertDbTypeToSqlDbType(objDbTypes);
                SqlParameter  param        = null;
                StringBuilder sb           = new StringBuilder();
                for (int i = 0; i < objValuesArr[0].Length; i++)
                {
                    sb.Append("---> " + Convert.ToString(i + 1) + Environment.NewLine);
                    for (int j = 0; j < objValuesArr.Length; j++)
                    {
                        if (objValuesArr[j][i] == null)
                        {
                            objValuesArr[j][i] = System.DBNull.Value;
                        }
                        sb.Append(Convert.ToString(j + 1) + ":= " + objValuesArr[j][i] + "; ");
                    }
                    sb.AppendLine();
                }

                for (int i = 0; i < objValuesArr[0].Length; i++)
                {
                    for (int j = 0; j < enmSqlDbType.Length; j++)
                    {
                        param           = new SqlParameter(Convert.ToString((i + step) * 10) + Convert.ToString(j + 1), enmSqlDbType[j]);
                        param.Direction = ParameterDirection.Input;
                        param.Value     = objValuesArr[j][i];
                        cmd.Parameters.Add(param);
                    }
                    cmd.CommandText = GetParm(sql, (i + step) * 10, (i == 0));
                    if (i == 0)
                    {
                        SqlLog.OutPutSql("values: " + sb.ToString());
                    }
                    if (cmd.Connection.State == ConnectionState.Closed)
                    {
                        cmd.Connection.Open();
                    }
                    intAffectedRows += cmd.ExecuteNonQuery();
                    num              = i + step;
                }
            }
            catch (System.Exception objEx)
            {
                intAffectedRows = -1;
                throw objEx;
            }
            finally
            {
                step = num + 1;
                if (cmd != null)
                {
                    cmd.Connection.Close();
                    cmd.Dispose();
                }
            }
            return(intAffectedRows);
        }
Example #7
0
        /// <summary>
        /// 获取DataTable带参数
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="sql"></param>
        /// <param name="dtRecord"></param>
        /// <param name="objParams"></param>
        /// <returns></returns>
        public DataTable GetDataTable(string conn, string sql, params IDataParameter[] objParm)
        {
            DataTable  dtRecord = null;
            SqlCommand cmd      = GetSqlCommand(conn, EnumExecType.ExecSql);

            try
            {
                //cmd.CommandText = "set transaction isolation level read uncommitted";
                //cmd.ExecuteNonQuery();
                cmd.CommandText = GetParm(sql);
                if (objParm != null && objParm.Length > 0)
                {
                    for (int i = 0; i < objParm.Length; i++)
                    {
                        if (objParm[i].Value == null)
                        {
                            objParm[i].Value = System.DBNull.Value;
                        }
                        ((SqlParameter)objParm[i]).ParameterName = (i + 1).ToString();
                        cmd.Parameters.Add((SqlParameter)objParm[i]);
                    }
                    SqlLog.OutPutParmLog(objParm);
                }

                SqlDataReader sqlReader = cmd.ExecuteReader();
                dtRecord = new DataTable();
                dtRecord.Load(sqlReader);
                sqlReader.Close();

                //int fieldCount = sqlReader.FieldCount;
                //for (int i = 0; i < fieldCount; i++)
                //{
                //    if (dtRecord.Columns.IndexOf(sqlReader.GetName(i)) > 0)
                //        dtRecord.Columns.Add(sqlReader.GetName(i) + i.ToString(), sqlReader.GetFieldType(i));
                //    else
                //        dtRecord.Columns.Add(sqlReader.GetName(i), sqlReader.GetFieldType(i));
                //}
                //dtRecord.BeginLoadData();
                //object[] objValues = new object[fieldCount];
                //while (sqlReader.Read())
                //{
                //    sqlReader.GetValues(objValues);
                //    dtRecord.LoadDataRow(objValues, true);
                //}
                //sqlReader.Close();
                //dtRecord.EndLoadData();

                //SqlDataAdapter Adapter = new SqlDataAdapter();
                //Adapter.SelectCommand = cmd;
                //dtRecord = new DataTable();
                //Adapter.Fill(dtRecord);
            }
            catch (System.Exception objEx)
            {
                dtRecord = null;
                throw objEx;
            }
            finally
            {
                if (cmd != null)
                {
                    cmd.Connection.Close();
                    cmd.Dispose();
                }
            }
            return(dtRecord);
        }
Example #8
0
        /// <summary>
        /// 批量操作
        /// </summary>
        /// <param name="p_strConnstr"></param>
        /// <param name="p_strSQL"></param>
        /// <param name="p_objValuesArr"></param>
        /// <param name="p_lngAffectedRows"></param>
        /// <param name="p_objDbTypes"></param>
        /// <returns></returns>
        public int ExecSqlForBatch(string conn, EnumExecType execType, string sql, ref int step, object[][] objValuesArr, params DbType[] objDbTypes)
        {
            OracleCommand cmd = null;

            if (execType == EnumExecType.ExecSql || execType == EnumExecType.ExecSqlForBatch || execType == EnumExecType.ExecSqlForBatchSimpleInsert)
            {
                cmd = GetSqlCommand(conn, CommandType.Text);
            }
            else if (execType == EnumExecType.ExecProc)
            {
                cmd = GetSqlCommand(conn, CommandType.StoredProcedure);
            }
            else
            {
                cmd = GetSqlCommand(conn, CommandType.Text);
            }

            int intAffectedRows = 0;

            try
            {
                cmd.ArrayBindCount = objValuesArr[0].Length;
                cmd.CommandText    = GetParam(sql);
                OracleDbType[]  enmOracleDbType = ConvertDbTypeToOracleDbType(objDbTypes);
                OracleParameter param           = null;
                for (int i = 0; i < enmOracleDbType.Length; i++)
                {
                    param           = new OracleParameter(Convert.ToString(i + 1 + step * 10), enmOracleDbType[i]);
                    param.Direction = ParameterDirection.Input;
                    param.Value     = objValuesArr[i];
                    cmd.Parameters.Add(param);
                }
                StringBuilder sb = new StringBuilder();
                for (int i = 0; i < objValuesArr[0].Length; i++)
                {
                    sb.Append("---> " + Convert.ToString(i + 1) + Environment.NewLine);
                    for (int j = 0; j < objValuesArr.Length; j++)
                    {
                        if (objValuesArr[j][i] == null)
                        {
                            objValuesArr[j][i] = System.DBNull.Value;
                        }
                        sb.Append(Convert.ToString(j + 1) + ":= " + objValuesArr[j][i] + "; ");
                    }
                    sb.AppendLine();
                }
                SqlLog.OutPutSql("Values: " + sb.ToString());
                if (cmd.Connection.State == ConnectionState.Closed)
                {
                    cmd.Connection.Open();
                }
                intAffectedRows = cmd.ExecuteNonQuery();
            }
            catch (System.Exception objEx)
            {
                intAffectedRows = -1;
                throw objEx;
            }
            finally
            {
                if (cmd != null)
                {
                    cmd.Connection.Close();
                    cmd.Dispose();
                }
            }
            return(intAffectedRows);
        }
Example #9
0
        /// <summary>
        /// 获取DataTable带参数
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="sql"></param>
        /// <param name="dtRecord"></param>
        /// <param name="objParams"></param>
        /// <returns></returns>
        public int GetDataTable(string conn, string sql, ref DataTable dtRecord, params IDataParameter[] objParams)
        {
            OracleCommand cmd             = GetSqlCommand(conn, CommandType.Text);
            int           intAffectedRows = 0;

            try
            {
                cmd.CommandText = GetParam(sql);
                if (objParams != null)
                {
                    for (int i = 0; i < objParams.Length; i++)
                    {
                        if (objParams[i].Value == null)
                        {
                            objParams[i].Value = System.DBNull.Value;
                        }
                        ((OracleParameter)objParams[i]).ParameterName = (i + 1).ToString();
                        cmd.Parameters.Add((OracleParameter)objParams[i]);
                    }
                }
                SqlLog.OutPutParmLog(objParams);

                OracleDataReader oraReader = cmd.ExecuteReader();
                dtRecord = new DataTable();
                dtRecord.Load(oraReader);
                oraReader.Close();

                //int fieldCount = oraReader.FieldCount;
                //for (int i = 0; i < fieldCount; i++)
                //{
                //    if (dtRecord.Columns.IndexOf(oraReader.GetName(i)) > 0)
                //        dtRecord.Columns.Add(oraReader.GetName(i) + i.ToString(), oraReader.GetFieldType(i));
                //    else
                //        dtRecord.Columns.Add(oraReader.GetName(i), oraReader.GetFieldType(i));
                //}
                //dtRecord.BeginLoadData();
                //object[] objValues = new object[fieldCount];
                //while (oraReader.Read())
                //{
                //    oraReader.GetValues(objValues);
                //    dtRecord.LoadDataRow(objValues, true);
                //}
                //oraReader.Close();
                //dtRecord.EndLoadData();

                //OracleDataAdapter Adapter = new OracleDataAdapter();
                //Adapter.SelectCommand = cmd;
                //dtRecord = new DataTable();
                //Adapter.Fill(dtRecord);
                for (int i = 0; i < dtRecord.Columns.Count; i++)
                {
                    dtRecord.Columns[i].ColumnName = dtRecord.Columns[i].ColumnName.ToLower();
                }
            }
            catch (System.Exception objEx)
            {
                intAffectedRows = -1;
                weCare.Core.Utils.ExceptionLog.OutPutException(objEx);
                throw objEx;
            }
            finally
            {
                if (cmd != null)
                {
                    cmd.Connection.Close();
                    cmd.Dispose();
                }
            }
            return(intAffectedRows);
        }