SqlHelper

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Collections;

/// <summary>
    /// SQLHelper 的摘要描述
    /// </summary>
public class SQLHelper
{
    private static readonly string ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;

    /// <summary>
    /// Prepare a command for execution
    /// </summary>
    /// <param name="cmd">SqlCommand object</param>
    /// <param name="conn">SqlConnection object</param>
    /// <param name="trans">SqlTransaction object</param>
    /// <param name="cmdType">Cmd type e.g. stored procedure or text</param>
    /// <param name="cmdText">Command text, e.g. Select * from Products</param>
    /// <param name="cmdParms">SqlParameters to use in the command</param>
    private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, SqlParameter[] Parms)
    {

        if (conn.State != ConnectionState.Open)
            conn.Open();

        cmd.Connection = conn;
        cmd.CommandText = cmdText;

        if (trans != null)
            cmd.Transaction = trans;

        cmd.CommandType = CommandType.Text;

        if (Parms != null)
        {
            foreach (SqlParameter parm in Parms)
                cmd.Parameters.Add(parm);
        }
    }

    #region ExecuteReader():執行并返回一個數據集
    /// <summary>
    /// 執行一段SqlCommand并返回一個結果集
    /// </summary>
    /// <param name="strSql">sql語句</param>
    /// <returns></returns>
    public static SqlDataReader ExecuteReader(string strSql)
    {
        SqlCommand cmd = new SqlCommand();
        SqlConnection conn = new SqlConnection(ConnectionString);

        // we use a try/catch here because if the method throws an exception we want to
        // close the connection throw code, because no datareader will exist, hence the
        // commandBehaviour.CloseConnection will not work
        try
        {
            PrepareCommand(cmd, conn, null, strSql, null);
            SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            cmd.Parameters.Clear();
            return rdr;
        }
        catch
        {
            conn.Close();
            throw;
        }
    }

    /// <summary>
    /// 執行一段SqlCommand并返回一個結果集
    /// </summary>
    /// <param name="strSql">sql語句</param>
    /// <param name="Parms">參數</param>
    /// <returns></returns>
    public static SqlDataReader ExecuteReader(string strSql, SqlParameter[] Parms)
    {
        SqlCommand cmd = new SqlCommand();
        SqlConnection conn = new SqlConnection(ConnectionString);

        // we use a try/catch here because if the method throws an exception we want to
        // close the connection throw code, because no datareader will exist, hence the
        // commandBehaviour.CloseConnection will not work
        try
        {
            PrepareCommand(cmd, conn, null, strSql, Parms);
            SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            cmd.Parameters.Clear();
            return rdr;
        }
        catch
        {
            conn.Close();
            throw;
        }
    }
    #endregion

    #region ExecuteNonQueryWithTransaction():以事務的方式執行并返回一個結果，1代表成功，-1代表失敗
    /// <summary>
    /// 以事務的方式執行并返回一個結果，1代表成功，-1代表失敗
    /// </summary>
    /// <param name="strSql"></param>
    /// <returns></returns>
    public static int ExecuteNonQueryWithTransaction(string strSql)
    {
        int retcount = -1;
        SqlCommand cmd = new SqlCommand();
        SqlConnection conn = new SqlConnection(ConnectionString);

        conn.Open();
        SqlTransaction trans = conn.BeginTransaction(IsolationLevel.ReadCommitted);

        try
        {
            PrepareCommand(cmd, conn, trans, strSql, null);
            int val = cmd.ExecuteNonQuery();
            trans.Commit();
            retcount = 1;
        }
        catch
        {
            trans.Rollback();
        }
        finally
        {
            conn.Close();
        }
        return retcount;
    }
    /// <summary>
    /// 以事務的方式執行并返回一個結果，1代表成功，-1代表失敗
    /// </summary>
    /// <param name="strSql"></param>
    /// <returns></returns>
    public static int ExecuteNonQueryWithTransaction(string strSql, SqlParameter[] Parms)
    {
        int retcount = -1;
        SqlCommand cmd = new SqlCommand();
        SqlConnection conn = new SqlConnection(ConnectionString);

        conn.Open();
        SqlTransaction trans = conn.BeginTransaction(IsolationLevel.ReadCommitted);
        try
        {
            PrepareCommand(cmd, conn, trans, strSql, Parms);
            int val = cmd.ExecuteNonQuery();
            trans.Commit();
            retcount = 1;
        }
        catch
        {
            trans.Rollback();
        }
        finally
        {
            conn.Close();
        }
        return retcount;
    }

    public static int ExecuteNonQueryWithTransaction(Hashtable SQLStringList)
    {
        int retcount = -1;
        using (SqlConnection conn = new SqlConnection(ConnectionString))
        {
            conn.Open();
            using (SqlTransaction trans = conn.BeginTransaction())
            {
                SqlCommand cmd = new SqlCommand();
                try
                {
                    foreach (DictionaryEntry myDE in SQLStringList)
                    {
                        string cmdText = myDE.Key.ToString().Split('|')[0];
                        SqlParameter[] cmdParms = (SqlParameter[])myDE.Value;
                        PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
                        int var = cmd.ExecuteNonQuery();
                        cmd.Parameters.Clear();
                    }
                    trans.Commit();
                    retcount = 1;
                }
                catch
                {
                    trans.Rollback();
                    throw;
                }
            }
        }
        return retcount;
    }
    #endregion

    #region ExecuteNonQuery():執行并返回一個結果，1代表成功，-1代表失敗
    /// <summary>
    /// 執行并返回一個結果，1代表成功，-1代表失敗
    /// </summary>
    /// <param name="strSql"></param>
    /// <returns></returns>
    public static int ExecuteNonQuery(string strSql)
    {
        int retcount = -1;
        using (SqlConnection conn = new SqlConnection(ConnectionString))
        {
            using (SqlCommand cmd = new SqlCommand(strSql, conn))
            {
                PrepareCommand(cmd, conn, null, strSql, null);
                retcount = cmd.ExecuteNonQuery();
                conn.Close();
            }
        }
        return retcount;
    }

    /// <summary>
    /// 執行并返回一個結果，1代表成功，-1代表失敗
    /// </summary>
    /// <param name="strSql"></param>
    /// <param name="Parms"></param>
    /// <returns></returns>
    public static int ExecuteNonQuery(string strSql, SqlParameter[] Parms)
    {
        int retcount = -1;
        using (SqlConnection conn = new SqlConnection(ConnectionString))
        {
            using (SqlCommand cmd = new SqlCommand(strSql, conn))
            {
                PrepareCommand(cmd, conn, null, strSql, Parms);
                retcount = cmd.ExecuteNonQuery();
                conn.Close();
            }
        }
        return retcount;
    }
    #endregion

    #region ExecuteScalar():返回數據集第一行第一列的數據
    /// <summary>
    /// 返回數據集第一行第一列的數據
    /// </summary>
    /// <param name="strSql"></param>
    /// <returns></returns>
    public static object ExecuteScalar(string strSql)
    {
        object retobject = null;
        using (SqlConnection conn = new SqlConnection(ConnectionString))
        {
            using (SqlCommand cmd = new SqlCommand(strSql, conn))
            {
                conn.Open();
                retobject = cmd.ExecuteScalar();
                conn.Close();
            }
        }
        return retobject;
    }

    /// <summary>
    /// 返回數據集第一行第一列的數據
    /// </summary>
    /// <param name="strSql"></param>
    /// <param name="Parms">參數</param>
    /// <returns></returns>
    public static object ExecuteScalar(string strSql, SqlParameter[] Parms)
    {
        object retobject = null;
        using (SqlConnection conn = new SqlConnection(ConnectionString))
        {
            using (SqlCommand cmd = new SqlCommand(strSql, conn))
            {
                cmd.Parameters.AddRange(Parms);
                conn.Open();
                retobject = cmd.ExecuteScalar();
                conn.Close();
            }
        }
        return retobject;
    }
    #endregion

    #region GetDataTable():根據sql返回一個數據表
    /// <summary>
    /// 根據sql返回一個數據表
    /// </summary>
    /// <param name="strSql"></param>
    /// <returns></returns>
    public static DataTable GetDataTable(string strSql)
    {
        DataTable dt = new DataTable();
        using (SqlConnection conn = new SqlConnection(ConnectionString))
        {
            using (SqlCommand cmd = new SqlCommand(strSql, conn))
            {
                conn.Open();
                SqlDataReader dr = cmd.ExecuteReader();
                dt.Load(dr);
                dr.Close();
                conn.Close();
            }
        }
        return dt;
    }
    /// <summary>
    /// 根據sql返回一個數據表
    /// </summary>
    /// <param name="strSql"></param>
    /// <param name="Parms"></param>
    /// <returns></returns>
    public static DataTable GetDataTable(string strSql, SqlParameter[] Parms)
    {
        DataTable dt = new DataTable();
        using (SqlConnection conn = new SqlConnection(ConnectionString))
        {
            using (SqlCommand cmd = new SqlCommand(strSql, conn))
            {
                cmd.Parameters.AddRange(Parms);
                conn.Open();
                SqlDataReader dr = cmd.ExecuteReader();
                dt.Load(dr);
                conn.Close();
            }
        }
        return dt;
    }
    #endregion
}