dbhelpersql.cs

【原创】很多情况下

        /// <returns>查询结果（object）</returns>
        public static object GetSingle(string strSQL, string SQLConString)
        {
            object obj = null;
            using (SqlConnection Conn = new SqlConnection(SQLConString))
            {
                using (SqlCommand Cmd = new SqlCommand(strSQL, Conn))
                {
                    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>
        /// <param name="SQLConString">链接字符串</param>
        /// <returns>SqlDataReader</returns>
        public static SqlDataReader ExecuteReader(string strSQL, string SQLConString)
        {
            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>
        /// <param name="SQLConString">链接字符串</param>
        /// <returns>DataSet</returns>
        public static DataSet Query(string strSQL, string SQLConString)
        {
            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>
        /// <param name="SQLConString">链接字符串</param>
        /// <returns>DataSet</returns>
        public DataSet Query(string strSQL, int Times, string SQLConString)
        {
            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 执行存储过程操作
        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>SqlDataReader</returns>
        public static SqlDataReader RunProcedure(string storedProcName, IDataParameter[] parameters)
        {
            SqlConnection Conn = new SqlConnection(SQLConString);
            SqlDataReader returnReader;
            Conn.Open();
            SqlCommand Cmd = BuildQueryCommand(Conn, storedProcName, parameters);
            Cmd.CommandType = CommandType.StoredProcedure;
            returnReader = Cmd.ExecuteReader();
            return returnReader;
        }


        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <param name="tableName">DataSet结果中的表名</param>
        /// <returns>DataSet</returns>
        public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName)
        {
            using (SqlConnection Conn = new SqlConnection(SQLConString))
            {
                DataSet dataSet = new DataSet();
                Conn.Open();
                SqlDataAdapter adapter = new SqlDataAdapter();
                adapter.SelectCommand = BuildQueryCommand(Conn, storedProcName, parameters);
                adapter.Fill(dataSet, tableName);
                Conn.Close();
                return dataSet;
            }
        }

        /// <summary>
        /// 构建 SqlCommand 对象(用来返回一个结果集，而不是一个整数值)
        /// </summary>
        /// <param name="connection">数据库连接</param>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>SqlCommand</returns>
        private static SqlCommand BuildQueryCommand(SqlConnection Conn, string storedProcName, IDataParameter[] parameters)
        {
            SqlCommand Cmd = new SqlCommand(storedProcName, Conn);
            Cmd.CommandType = CommandType.StoredProcedure;
            foreach (SqlParameter parameter in parameters)
            {
                Cmd.Parameters.Add(parameter);
            }
            return Cmd;
        }

        /// <summary>
        /// 执行存储过程，返回影响的行数		
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <param name="rowsAffected">影响的行数</param>
        /// <returns></returns>
        public static int RunProcedure(string storedProcName, IDataParameter[] parameters, out int RowsAffected)
        {
            using (SqlConnection Conn = new SqlConnection(SQLConString))
            {
                int intResult;
                Conn.Open();
                SqlCommand Cmd = BuildIntCommand(Conn, storedProcName, parameters);
                RowsAffected = Cmd.ExecuteNonQuery();
                intResult = (int)Cmd.Parameters["ReturnValue"].Value;
                return intResult;
            }
        }

        /// <summary>
        /// 创建 SqlCommand 对象实例(用来返回一个整数值)	
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>SqlCommand 对象实例</returns>
        private static SqlCommand BuildIntCommand(SqlConnection Conn, string StoredProcName, IDataParameter[] Parameters)
        {
            SqlCommand Cmd = BuildQueryCommand(Conn, StoredProcName, Parameters);
            Cmd.Parameters.Add(new SqlParameter("ReturnValue",
                SqlDbType.Int, 4, ParameterDirection.ReturnValue,
                false, 0, 0, string.Empty, DataRowVersion.Default, null));
            return Cmd;
        }
        #endregion	        

        #region 执行存储过程操作 （重写） （需传递链接字符串）
        /// <summary>
        /// 执行存储过程（需传递链接字符串）
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <param name="SQLConString">链接字符串</param>
        /// <returns>SqlDataReader</returns>
        public static SqlDataReader RunProcedure(string storedProcName, IDataParameter[] parameters, string SQLConString,bool MustHaveCon)
        {
            SqlConnection Conn = new SqlConnection(SQLConString);
            SqlDataReader returnReader;
            Conn.Open();
            SqlCommand Cmd = BuildQueryCommand(Conn, storedProcName, parameters);
            Cmd.CommandType = CommandType.StoredProcedure;
            returnReader = Cmd.ExecuteReader();
            return returnReader;
        }


        /// <summary>
        /// 执行存储过程（需传递链接字符串）
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <param name="tableName">DataSet结果中的表名</param>
        /// <param name="SQLConString">链接字符串</param>
        /// <returns>DataSet</returns>
        public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName, string SQLConString)
        {
            using (SqlConnection Conn = new SqlConnection(SQLConString))
            {
                DataSet dataSet = new DataSet();
                Conn.Open();
                SqlDataAdapter adapter = new SqlDataAdapter();
                adapter.SelectCommand = BuildQueryCommand(Conn, storedProcName, parameters);
                adapter.Fill(dataSet, tableName);
                Conn.Close();
                return dataSet;
            }
        }

        /// <summary>
        /// 构建 SqlCommand 对象(用来返回一个结果集，而不是一个整数值)（需传递链接字符串）
        /// </summary>
        /// <param name="connection">数据库连接</param>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <param name="SQLConString">链接字符串</param>
        /// <returns>SqlCommand</returns>
        private static SqlCommand BuildQueryCommand(SqlConnection Conn, string storedProcName, IDataParameter[] parameters, string SQLConString)
        {
            SqlCommand Cmd = new SqlCommand(storedProcName, Conn);
            Cmd.CommandType = CommandType.StoredProcedure;
            foreach (SqlParameter parameter in parameters)
            {
                Cmd.Parameters.Add(parameter);
            }
            return Cmd;
        }

        /// <summary>
        /// 执行存储过程，返回影响的行数 （需传递链接字符串）
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <param name="rowsAffected">影响的行数</param>
        /// <param name="SQLConString">链接字符串</param>
        /// <returns></returns>
        public static int RunProcedure(string storedProcName, IDataParameter[] parameters, out int RowsAffected, string SQLConString)
        {
            using (SqlConnection Conn = new SqlConnection(SQLConString))
            {
                int intResult;
                Conn.Open();
                SqlCommand Cmd = BuildIntCommand(Conn, storedProcName, parameters);
                RowsAffected = Cmd.ExecuteNonQuery();
                intResult = (int)Cmd.Parameters["ReturnValue"].Value;
                return intResult;
            }
        }

        /// <summary>
        /// 创建 SqlCommand 对象实例(用来返回一个整数值)	（需传递链接字符串）
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <param name="SQLConString">链接字符串</param>
        /// <returns>SqlCommand 对象实例</returns>
        private static SqlCommand BuildIntCommand(SqlConnection Conn, string StoredProcName, IDataParameter[] Parameters, string SQLConString)
        {
            SqlCommand Cmd = BuildQueryCommand(Conn, StoredProcName, Parameters);
            Cmd.Parameters.Add(new SqlParameter("ReturnValue",
                SqlDbType.Int, 4, ParameterDirection.ReturnValue,
                false, 0, 0, string.Empty, DataRowVersion.Default, null));
            return Cmd;
        }
        #endregion

        #region 公用方法

        #region 获取某表，某字段的最大值（该字段需为数字型）
        /// <summary>
        /// 获取某表，某字段的最大值（该字段需为数字型）
        /// </summary>
        /// <param name="FileName">列名</param>
        /// <param name="TableName">表名</param>
        /// <returns>该字段目前最大值+1</returns>
        public static int GetMaxID(string FileName, string TableName)
        {
            string strSQL = "SELECT max(" + FileName + ") + 1 FROM " + TableName;
            object obj = DbHelperSQL.GetSingle(strSQL);
            if (obj == null)
            {
                return 1;
            }
            else
            {
                return int.Parse(obj.ToString());
            }
        }
        #endregion

        #region 判断是否存在--表
        /// <summary>
        /// 判断是否存在表
        /// </summary>
        /// <param name="TableName">表名</param>
        /// <returns></returns>
        public static bool ExistsTable(string TableName)
        {
            string strCon = SQLConString_master;
            string strSQL = "SELECT Count(*) FROM sysobjects WHERE id = object_id(N'[" + TableName + "]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1";
            //string strSQL = "SELECT count(*) FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + TableName + "]') AND type in (N'U')";
            object obj = GetSingle(strSQL, strCon);
            int intResult;
            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
            {
                intResult = 0;
            }
            else
            {
                intResult = int.Parse(obj.ToString());
            }