SqlHelper.cs

//===================================================================
//This file is tony's data access helper for .NET
//For more information please go to http://dotnet.tonygan.com
//===================================================================

using System;
using System.Data;
using System.Xml;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;

namespace Tony.Common
{
 /// <summary>
 /// The SqlHelper class is intended to encapsulate high performance,
 /// scalable best practices for common uses of SqlClient.
 /// </summary>
 public abstract class SqlHelper
 {
  #region private utility methods & constructors

  //Database connection strings
  public static readonly string ConnectionString = ConfigurationSettings.AppSettings["SQLConnectionString"];

  //Since this class provides only static methods, make the default constructor private to prevent
  //instances from being created with "new SqlHelper()".
  private SqlHelper() { }

  #endregion

  # region ExecuteNonQuery

  public int ExecuteNonQuery(string cmdText)
  {
   // Create & open a SqlConnection, and dispose of it after we are done
   using (SqlConnection connection = new SqlConnection(ConnectionString))
   {
    using (SqlCommand command = new SqlCommand(cmdText, connection))
    {
     try
     {
      connection.Open();
      return command.ExecuteNonQuery();
     }
     catch(SqlException ex)
     {
      throw new Exception(ex.Message);
     }
    }
   }
  }

  public static void ExecuteSqlTransaction(ArrayList cmdTextList)
  {
   using (SqlConnection connection = new SqlConnection(ConnectionString))
   {
    connection.Open();
    SqlCommand command = new SqlCommand();
    command.Connection = connection;
    SqlTransaction trans = connection.BeginTransaction();
    command.Transaction = trans;
    try
    {
     for (int n = 0; n < cmdTextList.Count; n++)
     {
      string cmdText = cmdTextList[n].ToString();
      if (cmdText.Trim().Length > 1)
      {
       command.CommandText = cmdText;
       command.ExecuteNonQuery();
      }
     }
     trans.Commit();
    }
    catch (SqlException ex)
    {
     trans.Rollback();
     throw new Exception(ex.Message);
    }
   }
  }

  #endregion

  #region ExecuteScalar

  public static object ExecuteScalar(string cmdText)
  {
   using (SqlConnection connection = new SqlConnection(ConnectionString))
   {
    using (SqlCommand command = new SqlCommand(cmdText, connection))
    {
     try
     {
      connection.Open();
      object obj = command.ExecuteScalar();
      if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
      {
       return null;
      }
      else
      {
       return obj;
      }
     }
     catch (SqlException ex)
     {
      connection.Close();
      throw new Exception(ex.Message);
     }
    }
   }
  }

  #endregion

        #region ExecuteReader

  public static SqlDataReader ExecuteReader(string cmdText)
  {
   SqlConnection connection = new SqlConnection(ConnectionString);
   SqlCommand command = new SqlCommand(cmdText, connection);
   try
   {
    connection.Open();
    SqlDataReader dataReader = command.ExecuteReader(CommandBehavior.CloseConnection);
    return dataReader;
   }
   catch (SqlException ex)
   {
    throw new Exception(ex.Message);
   }
   /*finally //不能在此关闭，否则，返回的对象将无法使用
   {
    command.Dispose();
    connection.Close();
   }*/
  }

  #endregion

        #region ExecuteDataSet

  public static DataSet ExecuteDataSet(string cmdText)
  {
   using (SqlConnection connection = new SqlConnection(ConnectionString))
   {
    DataSet dataSet = new DataSet();
    try
    {
     connection.Open();
     SqlDataAdapter dataAdapter = new SqlDataAdapter(cmdText, connection);
     dataAdapter.Fill(dataSet);
    }
    catch (SqlException ex)
    {
     throw new Exception(ex.Message);
    }
    return dataSet;
   }
  }

    #endregion
 }
}