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>