ADO.NET對數據庫操作經典類 [轉]－冠霖的部落格

using System;
using System.Collections;
using System.Collections.Specialized;
using System.Runtime.Remoting.Messaging;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace LTP.SQLServerDAL
{
/// <summary>
/// ADO.NET數據庫操作基礎類。
/// </summary>
public abstract class DbManagerSQL
{
  //數據庫連接字符串
  protected static string connectionString = ConfigurationSettings.AppSettings["ConnectionString"];
  public DbManagerSQL()
  {
   //
   // TODO: 在此處添加構造函數邏輯
   //
  }
  /// <summary>
  /// 執行SQL語句，返回影響的記錄數
  /// </summary>
  /// <param name="SQLString"></param>
  /// <returns></returns>
  public static int ExecuteSql(string SQLString)
  {
   using (SqlConnection connection = new SqlConnection(connectionString))
   {
    using (SqlCommand cmd = new SqlCommand(SQLString,connection))
    {
     try
     {
      connection.Open();
      int rows=cmd.ExecuteNonQuery();
      return rows;
     }
     catch(System.Data.SqlClient.SqlException E)
     {
      throw new Exception(E.Message);
     }
    }
   }
  }
  /// <summary>
  /// 執行兩條SQL語句，實現數據庫事務。
  /// </summary>
  /// <param name="SQLString1"></param>
  /// <param name="SQLString2"></param>
  public static void ExecuteSqlTran(string SQLString1,string SQLString2)
  {
   using (SqlConnection connection = new SqlConnection(connectionString))
   {
    connection.Open();
    SqlCommand cmd = new SqlCommand();
    cmd.Connection=connection;
    SqlTransaction tx=connection.BeginTransaction();
    cmd.Transaction=tx;
    try
    {
     cmd.CommandText=SQLString1;
     cmd.ExecuteNonQuery();
     cmd.CommandText=SQLString2;
     cmd.ExecuteNonQuery();
     tx.Commit();
    }
    catch(System.Data.SqlClient.SqlException E)
    {
     tx.Rollback();
     throw new Exception(E.Message);
    }
    finally
    {
     cmd.Dispose();
     connection.Close();
    }
   }
  }
  /// <summary>
  /// 執行多條SQL語句，實現數據庫事務，每條語句以「;」分割。
  /// </summary>
  /// <param name="SQLStringList"></param>
  public static void ExecuteSqlTran(string SQLStringList)
  {
   using (OdbcConnection conn = new OdbcConnection(connectionString))
   {
    conn.Open();
    OdbcCommand cmd = new OdbcCommand();
    cmd.Connection=conn;
    OdbcTransaction tx=conn.BeginTransaction();
    cmd.Transaction=tx;
    try
    {
     string [] split= SQLStringList.Split(new Char [] { ';'});
     foreach (string strsql in split)
     {
      if (strsql.Trim()!="")
      {
       cmd.CommandText=strsql;
       cmd.ExecuteNonQuery();
      }
     }
     tx.Commit();
    }
    catch(System.Data.Odbc.OdbcException E)
    {
     tx.Rollback();
     throw new Exception(E.Message);
    }
   }
  }
  /// <summary>
  /// 執行帶一個存儲過程參數的的SQL語句。
  /// </summary>
  /// <param name="SQLString"></param>
  /// <param name="content"></param>
  /// <returns></returns>
  public static int ExecuteSql(string SQLString,string content)
  {
   using (SqlConnection connection = new SqlConnection(connectionString))
   {
    SqlCommand cmd = new SqlCommand(SQLString,connection);
    System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter ( "@content", SqlDbType.NText);
    myParameter.Value = content ;
    cmd.Parameters.Add(myParameter);
    try
    {
     connection.Open();
     int rows=cmd.ExecuteNonQuery();
     return rows;
    }
    catch(System.Data.SqlClient.SqlException E)
    {
     throw new Exception(E.Message);
    }
    finally
    {
     cmd.Dispose();
     connection.Close();
    }
   }
  }
  /// <summary>
  /// 向數據庫裡插入圖像格式的字段
  /// </summary>
  /// <param name="strSQL"></param>
  /// <param name="fs"></param>
  /// <returns></returns>
  public static int ExecuteSqlInsertImg(string strSQL,byte[] fs)
  {
   using (SqlConnection connection = new SqlConnection(connectionString))
   {
    SqlCommand cmd = new SqlCommand(strSQL,connection);
    System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter ( "@fs", SqlDbType.Image);
    myParameter.Value = fs ;
    cmd.Parameters.Add(myParameter);
    try
    {
     connection.Open();
     int rows=cmd.ExecuteNonQuery();
     return rows;
    }
    catch(System.Data.SqlClient.SqlException E)
    {
     throw new Exception(E.Message);
    }
    finally
    {
     cmd.Dispose();
     connection.Close();
    }

   }
  }
  /// <summary>
  /// 執行一條計算查詢結果語句，返回查詢結果（整數）。
  /// </summary>
  /// <param name="strSQL"></param>
  /// <returns></returns>
  public static int GetCount(string strSQL)
  {
   using (SqlConnection connection = new SqlConnection(connectionString))
   {
    SqlCommand cmd = new SqlCommand(strSQL,connection);
    try
    {
     connection.Open();
     SqlDataReader result = cmd.ExecuteReader();
     int i=0;
     while(result.Read())
     {
      i=result.GetInt32(0);
     }
     result.Close();
     return i;
    }
    catch(System.Data.SqlClient.SqlException e)
    {
     throw new Exception(e.Message);
    }
    finally
    {
     cmd.Dispose();
     connection.Close();
    }
   }
  }
  /// <summary>
  /// 執行一條計算查詢結果語句，返回查詢結果（object）。
  /// </summary>
  /// <param name="SQLString"></param>
  /// <returns></returns>
  public static object GetSingle(string SQLString)
  {
   using (SqlConnection connection = new SqlConnection(connectionString))
   {
    SqlCommand cmd = new SqlCommand(SQLString,connection);
    try
    {
     connection.Open();
     object obj = cmd.ExecuteScalar();
     if((Object.Equals(obj,null))||(Object.Equals(obj,System.DBNull.Value)))
     {
      return null;
     }
     else
     {
      return obj;
     }
    }
    catch(System.Data.SqlClient.SqlException e)
    {
     throw new Exception(e.Message);
    }
    finally
    {
     cmd.Dispose();
     connection.Close();
    }
   }
  }
  /// <summary>
  /// 執行查詢語句，返回SqlDataReader
  /// </summary>
  /// <param name="strSQL"></param>
  /// <returns></returns>
  public static SqlDataReader ExecuteReader(string strSQL)
  {
   using (SqlConnection connection = new SqlConnection(connectionString))
   {
    SqlCommand cmd = new SqlCommand(strSQL,connection);
    SqlDataReader myReader;
    try
    {
     connection.Open();
     myReader = cmd.ExecuteReader();
     return myReader;
    }
    catch(System.Data.SqlClient.SqlException e)
    {
     throw new Exception(e.Message);
    }
    finally
    {
     cmd.Dispose();
     connection.Close();
    }
   }
  }
  /// <summary>
  /// 執行查詢語句，返回DataSet
  /// </summary>
  /// <param name="SQLString"></param>
  /// <returns></returns>
  public static DataSet Query(string SQLString)
  {
   using (SqlConnection connection = new SqlConnection(connectionString))
   {
    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;
   }

  }

#region 存儲過程操作

  /// <summary>
  /// 運行存儲過程
  /// </summary>
  /// <param name="storedProcName"></param>
  /// <param name="parameters"></param>
  /// <returns></returns>
  public static SqlDataReader RunProcedure(string storedProcName, IDataParameter[] parameters )
  {
   using (SqlConnection connection = new SqlConnection(connectionString))
   {
    SqlDataReader returnReader;
    connection.Open();
    SqlCommand command = BuildQueryCommand( connection,storedProcName, parameters );
    command.CommandType = CommandType.StoredProcedure;

    returnReader = command.ExecuteReader();
    //Connection.Close();
    return returnReader;
   }
  }
  private static SqlCommand BuildQueryCommand(SqlConnection connection,string storedProcName, IDataParameter[] parameters)
  {

    SqlCommand command = new SqlCommand( storedProcName, connection );
    command.CommandType = CommandType.StoredProcedure;
    foreach (SqlParameter parameter in parameters)
    {
     command.Parameters.Add( parameter );
    }
    return command;

  }
  public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName )
  {
   using (SqlConnection connection = new SqlConnection(connectionString))
   {
    DataSet dataSet = new DataSet();
    connection.Open();
    SqlDataAdapter sqlDA = new SqlDataAdapter();
    sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters );
    sqlDA.Fill( dataSet, tableName );
    connection.Close();