C#數據庫幫助類SqlHelper

    using System;

    using System.Collections.Generic;

    using System.Linq;

    using System.Web;

    using System.Data.OleDb;

    using System.Data;

    using System.Configuration;

    using System.Data.Sql;

    using System.Data.SqlClient;

    namespace DAL

    {

        public class SqlHelper

        {

            public static string connstring = ConfigurationManager.ConnectionStrings["zzconstr"].ConnectionString;

            //public static string connstring = "Data Source=ZZ-PC;Initial Catalog=IPTVDB;User ID=sa;Password=sa";

            /// <summary>

            /// 執行非查詢，返回受影響行數,異常返回-1；

            /// </summary>

            /// <param name="sql"></param>

            /// <param name="type"></param>

            /// <param name="pars"></param>

            /// <returns></returns>

            public static bool ExceNonQuery(string sql, CommandType type, IDataParameter[] pars)

            {


            SqlConnection con = new SqlConnection(connstring);  
            SqlCommand com = new SqlCommand(sql, con);  

            if (pars != null && pars.Length > 0)  
            {  
                foreach (SqlParameter pp in pars)//把參數集全部加進去  
                    com.Parameters.Add(pp);  
            }  
            try  
            {  
                con.Open();  
                int t = com.ExecuteNonQuery();  
                if (t > 0)  
                {  
                    return true;  
                }  
                else  
                    return false;  
            }  
            catch (Exception e) { return false; }  
            finally  
            {  
                com.Parameters.Clear();  
                com.Dispose();  
                con.Close();  
            }  
        }  
        /// <summary>  
        /// 執行sql語句的查詢，返回查詢的數量。異常返回-1.  
        /// </summary>  
        /// <param name="sql"></param>  
        /// <param name="type"></param>  
        /// <param name="pars"></param>  
        /// <returns></returns>  
        public static int ExceQuery(string sql, CommandType type, IDataParameter[] pars)  
        {  
            SqlConnection con = new SqlConnection(connstring);  
            SqlCommand com = new SqlCommand(sql, con);  
            com.CommandType = type;  
            if (pars != null && pars.Length > 0)  
            {  
                foreach (SqlParameter pp in pars)//把參數集全部加進去  
                    com.Parameters.Add(pp);  
            }  
            try  
            {  
                con.Open();  
                if (com.ExecuteScalar() != null)//查詢結果為空時返回0  
                {  
                    int t = (int)com.ExecuteScalar();  

                    return t;  
                }  
                else  
                    return -1;  
            }  
            catch (Exception e) { return -1; }  
            finally  
            {  
                com.Parameters.Clear();  
                com.Dispose();  
                con.Close();  
            }  
        }  
        /// <summary>  
        /// 執行查詢，返回一個數據集  
        /// </summary>  
        /// <param name="sql"></param>  
        /// <param name="pars"></param>  
        /// <returns></returns>  
        public static DataSet ExcueReturnDataset(string sql, IDataParameter[] pars)  
        {  
            SqlConnection con = new SqlConnection(connstring);  
            DataSet set = new DataSet();  
            SqlCommand com = new SqlCommand(sql, con);  
            if (pars != null && pars.Length > 0)  
            {  
                foreach (SqlParameter pp in pars)//把參數集全部加進去  
                    com.Parameters.Add(pp);  
            }  

            SqlDataAdapter adpter = new SqlDataAdapter(com);  

            try  
            {  
                set.Clear();  
                adpter.Fill(set);  
                return set;  
            }  
            catch (Exception ex) { return null; }  
            finally {  
                com.Parameters.Clear();  
                com.Dispose();  
                con.Close();   
            }  

        }  
        public static DataSet ExcueReturnDataset(string sql,CommandType type,IDataParameter[] pars)  
        {  
            SqlConnection con = new SqlConnection(connstring);  
            DataSet set = new DataSet();  
            SqlCommand com = new SqlCommand(sql, con);  
            com.CommandType = type;  
            if (pars != null && pars.Length > 0)  
            {  
                foreach (SqlParameter pp in pars)//把參數集全部加進去  
                    com.Parameters.Add(pp);  
            }  

            SqlDataAdapter adpter = new SqlDataAdapter(com);  

            try  
            {  
                set.Clear();  
                adpter.Fill(set);  
                return set;  
            }  
            catch (Exception ex) { return null; }  
            finally  
            {  
                com.Parameters.Clear();  
                com.Dispose();  
                con.Close();  
            }  

        }  
        public static IDataReader ExcueReturnDataReader(string sql, IDataParameter[] pars)  
        {  
            SqlConnection con = new SqlConnection(connstring);  
            SqlCommand com = new SqlCommand(sql, con);  

            SqlDataReader reader;  
            if (pars != null && pars.Length > 0)  
            {  
                foreach (SqlParameter pp in pars)//把參數集全部加進去  
                    com.Parameters.Add(pp);  
            }  
            try  
            {  
                con.Open();  
                reader = com.ExecuteReader(CommandBehavior.CloseConnection);  
                return reader;  
            }  
            catch (Exception ex)  
            {   

                return null;   
            }  
            finally {  
                com.Parameters.Clear();  
                com.Dispose();  
                //con.Close();  
            }  

        }  
        /// <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 connection = new SqlConnection(connstring))  
            {  
                int result;  
                connection.Open();  
                SqlCommand command = new SqlCommand(storedProcName, connection);  
                command.CommandType = CommandType.StoredProcedure;  
                if (parameters!= null && parameters.Length > 0)  
                {  
                    foreach (SqlParameter pp in parameters)//把參數集全部加進去  
                        command.Parameters.Add(pp);  
                }  
                command.Parameters.Add("@return","").Direction = ParameterDirection.ReturnValue;  
                rowsAffected = command.ExecuteNonQuery();  
                result = (int)command.Parameters["@return"].Value;  
                connection.Close();  
                return result;  
            }  
        }  
        /// <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 connection = new SqlConnection(connstring))  
            {  
                DataSet dataSet = new DataSet();  
                SqlCommand com = new SqlCommand(storedProcName, connection);  
                com.CommandType =CommandType.StoredProcedure;  
                if (parameters != null && parameters.Length > 0)  
                {  
                    foreach (SqlParameter pp in parameters)//把參數集全部加進去  
                        com.Parameters.Add(pp);  
                }  

                SqlDataAdapter adpter = new SqlDataAdapter(com);  
                adpter.Fill(dataSet, tableName);  
                return dataSet;  
            }  
        }  
        /// <summary>  
        /// 執行查詢語句，返回DataSet  
        /// </summary>  
        /// <param name="SQLString">查詢語句</param>  
        /// <returns>DataSet</returns>  
        public DataSet Query(string SQLString)  
        {  
            using (SqlConnection connection = new SqlConnection(connstring))  
            {  
                DataSet ds = new DataSet();  
                try  
                {  
                    connection.Open();  
                    SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);  
                    command.Fill(ds, "ds");  
                }  
                catch (System.Data.SqlClient.SqlException ex)  
                {  
                    throw new Exception(ex.Message);  
                }  
                return ds;  
            }  
        }  
    }  
}  </pre>