dbhelpersql.cs

【原创】很多情况下

                {
                    try
                    {
                        Conn.Open();
                        obj = Cmd.ExecuteScalar();
                        if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                        {
                            obj = null;
                        }
                    }
                    catch (System.Data.SqlClient.SqlException e)
                    {
                        throw new Exception(e.Message);
                    }
                    finally
                    {
                        Conn.Close();
                    }
                }
            }
            return obj;
        }        
        #endregion

        #region 执行查询语句，返回SqlDataReader
        /// <summary>
        /// 执行查询语句，返回SqlDataReader
        /// </summary>
        /// <param name="strSQL">查询语句</param>
        /// <returns>SqlDataReader</returns>
        public static SqlDataReader ExecuteReader(string strSQL)
        {
            SqlConnection Conn = new SqlConnection(SQLConString);
            SqlCommand Cmd = new SqlCommand(strSQL, Conn);
            SqlDataReader Reader = null;
            try
            {
                Conn.Open();
                Reader = Cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (System.Data.SqlClient.SqlException e)
            {
                Conn.Close ();
                throw new Exception(e.Message);
            }
            return Reader;
        }
        #endregion

        #region 执行查询语句，返回DataSet
        /// <summary>
        /// 执行查询语句，返回DataSet
        /// </summary>
        /// <param name="strSQL">SQL语句</param>
        /// <returns>DataSet</returns>
        public static DataSet Query(string strSQL)
        {
            DataSet ds = new DataSet();
            using (SqlConnection Conn = new SqlConnection(SQLConString))
            {                
                try
                {
                    Conn.Open();
                    SqlDataAdapter adapter = new SqlDataAdapter(strSQL, Conn);
                    adapter.Fill(ds, "ds");
                }
                catch (System.Data.SqlClient.SqlException ex)
                {
                    throw new Exception(ex.Message);
                }
                finally
                {
                    Conn.Close();
                }               
            }
            return ds;
        }

        /// <summary>
        /// 执行查询语句，设置超时时间，返回DataSet
        /// </summary>
        /// <param name="strSQL">SQL语句</param>
        /// <param name="Times">超时时间</param>
        /// <returns>DataSet</returns>
        public DataSet Query(string strSQL, int Times)
        {
            using (SqlConnection Conn = new SqlConnection(SQLConString))
            {
                DataSet ds = new DataSet();
                try
                {
                    Conn.Open();
                    SqlDataAdapter command = new SqlDataAdapter(strSQL, Conn);
                    command.SelectCommand.CommandTimeout = Times;
                    command.Fill(ds, "ds");
                }
                catch (System.Data.SqlClient.SqlException ex)
                {
                    throw new Exception(ex.Message);
                }
                finally
                {
                    Conn.Close();
                }
                return ds;
            }
        }

       
        #endregion

        #endregion                

        #region 执行简单SQL语句（重写）（需传递链接字符串的）

        #region 执行SQL语句，返回影响的记录数（需传递数据库链接字符串）
        /// <summary>
        /// 执行SQL语句，返回影响的记录数（需传递数据库链接字符串）
        /// </summary>
        /// <param name="strSQL">SQL语句</param>
        /// <param name="SQLConString">数据库链接字符串</param>
        /// <param name="HaveConString">true/false均可，只做为重写的选择</param>
        /// <returns>影响的记录数</returns>
        public static int ExecuteSql(string strSQL, string SQLConString, bool HaveConString)
        {
            using (SqlConnection Conn = new SqlConnection(SQLConString))
            {
                using (SqlCommand Cmd = new SqlCommand(strSQL, Conn))
                {
                    try
                    {
                        Conn.Open();
                        int intRows = Cmd.ExecuteNonQuery();
                        return intRows;
                    }
                    catch (System.Data.SqlClient.SqlException E)
                    {
                        Conn.Close();
                        throw new Exception(E.Message);
                    }
                }
            }
        }
        #endregion 

        #region 执行多条SQL语句，实现数据库事务，返回事务中全部语句共影响的行数（需传递链接字符串）
        /// <summary>
        /// 执行多条SQL语句，实现数据库事务，返回事务中全部语句共影响的行数（需传递链接字符串）等于0成功但无影响记录，大于0成功，小于0失败
        /// </summary>
        /// <param name="SQLStringList">多条SQL语句</param>	
        /// <param name="SQLConString">数据库链接字符串</param>
        /// <returns>返回事务中全部语句共影响的行数</returns>
        public static int ExecuteSqlTran(ArrayList SQLStringList, string SQLConString)
        {
            int intResult = 0;
            using (SqlConnection Conn = new SqlConnection(SQLConString))
            {
                using (SqlCommand Cmd = new SqlCommand())
                {
                    Conn.Open();
                    Cmd.Connection = Conn;
                    SqlTransaction DbTrans = Conn.BeginTransaction();
                    Cmd.Transaction = DbTrans;
                    try
                    {
                        for (int i = 0; i < SQLStringList.Count; i++)
                        {
                            string strSQL = SQLStringList[i].ToString();
                            if (strSQL.Trim().Length > 1)
                            {
                                Cmd.CommandText = strSQL;
                                intResult += Cmd.ExecuteNonQuery();
                            }
                        }
                        //事务提交
                        DbTrans.Commit();
                    }
                    catch (System.Data.SqlClient.SqlException E)
                    {
                        intResult = -1;
                        DbTrans.Rollback();
                        throw new Exception(E.Message);
                    }
                    finally
                    {
                        Conn.Close();
                    }
                }
            }
            return intResult;
        }
        #endregion

        #region 执行多条SQL语句,没有事务处理，多条语句串行执行，失败返回-1 ExecSampleSQL（需传递链接字符串）
        /// <summary>
        /// 执行单条SQL语句，失败返回-1 (多条也按单条执行，没有事务处理) （需传递链接字符串）
        /// </summary>
        /// <param name="SQLStringList">SQL语句集</param>
        /// <param name="SQLConString">链接字符串</param>
        /// <returns>失败返回-1,成功返回已成功执行的SQL条数</returns>
        public static int ExecSampleSQL(ArrayList SQLStringList, string SQLConString)
        {
            int Val = 0;
            using (System.Data.SqlClient.SqlConnection Conn = new SqlConnection(SQLConString))
            {
                using (System.Data.SqlClient.SqlCommand Cmd = new SqlCommand())
                {
                    Cmd.Connection = Conn;
                    try
                    {
                        Conn.Open();
                        for (int iCnt = 0; iCnt < SQLStringList.Count; iCnt++)
                        {
                            if (SQLStringList[iCnt].ToString() == "")
                            {
                                continue;
                            }
                            Cmd.CommandText = SQLStringList[iCnt].ToString().Trim();
                            Cmd.ExecuteNonQuery();
                            Val++;
                        }
                    }
                    catch (Exception e)
                    {
                        Val = -1;
                        throw new Exception(e.Message);
                    }
                    finally
                    {
                        Conn.Close();
                    }
                }
            }
            return Val;
        }

        /// <summary>
        /// 执行单条SQL语句，失败返回-1 (多条也按单条执行，没有事务处理)（需传递链接字符串）
        /// </summary>
        /// <returns>失败返回-1,成功返回已成功执行的SQL条数</returns>
        public static int ExecSampleSQL(string[] SQLStringList, string SQLConString)
        {
            int Val = 0;
            using (System.Data.SqlClient.SqlConnection Conn = new SqlConnection(SQLConString))
            {
                using (System.Data.SqlClient.SqlCommand Cmd = new SqlCommand())
                {
                    Cmd.Connection = Conn;
                    try
                    {
                        Conn.Open();
                        for (int iCnt = 0; iCnt < SQLStringList.Length; iCnt++)
                        {
                            if (SQLStringList[iCnt].ToString() == "")
                            {
                                continue;
                            }
                            Cmd.CommandText = SQLStringList[iCnt].ToString().Trim();
                            Cmd.ExecuteNonQuery();
                            Val++;
                        }
                    }
                    catch (Exception e)
                    {
                        Val = -1;
                        throw new Exception(e.Message);
                    }
                    finally
                    {
                        Conn.Close();
                    }
                }
            }
            return Val;
        }
        #endregion

        #region 执行带一个存储过程参数的的SQL语句（需传递连接件字符串）
        /// <summary>
        /// 执行带一个存储过程参数的的SQL语句（需传递连接件字符串）
        /// </summary>
        /// <param name="strSQL">SQL语句</param>
        /// <param name="ParContent">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>
        /// <param name="SQLConString">链接字符串</param>
        /// <returns>影响的记录数</returns>
        public static int ExecuteSql(string strSQL, string ParContent, string SQLConString)
        {
            int intRows = -1;
            using (SqlConnection Conn = new SqlConnection(SQLConString))
            {
                using (SqlCommand Cmd = new SqlCommand(strSQL, Conn))
                {
                    System.Data.SqlClient.SqlParameter Par = new System.Data.SqlClient.SqlParameter("@ParContent", SqlDbType.NText);

                    Par.Value = ParContent;
                    Cmd.Parameters.Add(Par);
                    try
                    {
                        Conn.Open();
                        intRows = Cmd.ExecuteNonQuery();
                    }
                    catch (System.Data.SqlClient.SqlException E)
                    {
                        throw new Exception(E.Message);
                    }
                    finally
                    {
                        Conn.Close();
                    }

                }
            }
            return intRows;
        }
        #endregion

        #region 向数据库里插入图像格式的字段 （需传递链接字符串）
        /// <summary>
        /// 向数据库里插入图像格式的字段 （需传递链接字符串）
        /// </summary>
        /// <param name="strSQL">SQL语句</param>
        /// <param name="fs">图像字节,数据库的字段类型为Image的情况</param>
        /// <param name="SQLConString">链接字符串</param>
        /// <returns>影响的记录数</returns>
        public static int ExecuteSqlInsertImg(string strSQL, byte[] fs, string SQLConString)
        {
            int intRows = -1;
            using (SqlConnection Conn = new SqlConnection(SQLConString))
            {
                using (SqlCommand Cmd = new SqlCommand(strSQL, Conn))
                {
                    System.Data.SqlClient.SqlParameter Par = new System.Data.SqlClient.SqlParameter("@fs", SqlDbType.Image);
                    Par.Value = fs;
                    Cmd.Parameters.Add(Par);
                    try
                    {
                        Conn.Open();
                        intRows = Cmd.ExecuteNonQuery();
                    }
                    catch (System.Data.SqlClient.SqlException E)
                    {
                        throw new Exception(E.Message);
                    }
                    finally
                    {
                        Conn.Close();
                    }
                }
            }
            return intRows;
        }
        #endregion

        #region 执行一条计算查询结果语句，返回查询结果（object）（需传递链接字符串）
        /// <summary>
        /// 执行一条计算查询结果语句，返回查询结果（object）（需传递链接字符串）
        /// </summary>
        /// <param name="strSQL">计算查询结果语句</param>
        /// <param name="strCon">连接字符串</param>