一个数据接口层类

// ===============================================================================
// gaoxilin Data Access Application Block for .NET
//
// SqlDbHelper.cs
//
// This file contains the implementations of the SqlHelper and SqlHelperParameterCache
// classes.
//
// For more information see the Data Access Application Block Implementation Overview.
// ===============================================================================
// Release history
// VERSION DESCRIPTION
//   2.0 add UpdateDatatable.
//
// ===============================================================================
// Copyright (C)2007 Microsoft Corporation
// All rights reserved.
// THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY
// OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT
// LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR
// FITNESS FOR A PARTICULAR PURPOSE.
// ==============================================================================
using System;
using System.Data.SqlClient;
using System.Data;
namespace Gaoxilin.DbHelper
{
 /// <summary>
 /// The SqlHelper class is intended to encapsulate high performance, scalable best practices for
 /// common uses of SqlClient
 /// </summary>
 public class SqlDbHelper
 {
  public SqlDbHelper()
  {
   //
   // TODO: 在此处添加构造函数逻辑
   //
  }
  /// <summary>
  /// This method is used to attach array of SqlParameters to a SqlCommand.
  ///
  /// This method will assign a value of DbNull to any parameter with a direction of
  /// InputOutput and a value of null. 
  ///
  /// This behavior will prevent default values from being used, but
  /// this will be the less common case than an intended pure output parameter (derived as InputOutput)
  /// where the user provided no input value.
  /// </summary>
  /// <param >The command to which the parameters will be added</param>
  /// <param >An array of SqlParameters to be added to command</param>
  private static void AttachParameters(SqlCommand command, SqlParameter[] commandParameters)
  {
   if( command == null ) throw new ArgumentNullException( "command" );
   if( commandParameters != null )
   {
    foreach (SqlParameter p in commandParameters)
    {
     if( p != null )
     {
      // Check for derived output value with no value assigned
      if ( ( p.Direction == ParameterDirection.InputOutput ||
       p.Direction == ParameterDirection.Input ) &&
       (p.Value == null))
      {
       p.Value = DBNull.Value;
      }
      command.Parameters.Add(p);
     }
    }
   }
  }
  #region ExecuteNonQuery
  /// <summary>
  /// Execute a SqlCommand (that returns no resultset and takes no parameters) against the database specified in
  /// the connection string
  /// </summary>
  /// <remarks>
  /// e.g.: 
  ///  int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders");
  /// </remarks>
  /// <param >A valid connection string for a SqlConnection</param>
  /// <param >The CommandType (stored procedure, text, etc.)</param>
  /// <param >The stored procedure name or T-SQL command</param>
  /// <returns>An int representing the number of rows affected by the command</returns>
  public static  int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText)
  {
   return  SqlDbHelper.ExecuteNonQuery(connectionString,commandType,commandText,null);
  }
  /// <summary>
  /// Execute a SqlCommand (that returns no resultset and takes no parameters) against the provided SqlConnection.
  /// </summary>
  /// <remarks>
  /// e.g.: 
  ///  int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders");
  /// </remarks>
  /// <param >A valid SqlConnection</param>
  /// <param >The CommandType (stored procedure, text, etc.)</param>
  /// <param >The stored procedure name or T-SQL command</param>
  /// <returns>An int representing the number of rows affected by the command</returns>
  public static  int ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText)
  {
   return SqlDbHelper.ExecuteNonQuery(connection,commandType,commandText,null);
  }
  /// <summary>
  /// Execute a SqlCommand (that returns no resultset) against the database specified in the connection string
  /// using the provided parameters
  /// </summary>
  /// <remarks>
  /// e.g.: 
  ///  int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
  /// </remarks>
  /// <param >A valid connection string for a SqlConnection</param>
  /// <param >The CommandType (stored procedure, text, etc.)</param>
  /// <param >The stored procedure name or T-SQL command</param>
  /// <param >An array of SqlParamters used to execute the command</param>
  /// <returns>An int representing the number of rows affected by the command</returns>
  public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
  {
   using(SqlConnection connection=new SqlConnection(connectionString))
   {
    return  SqlDbHelper.ExecuteNonQuery(connection,commandType,commandText,commandParameters);
   }
  }
  /// <summary>
  /// Execute a SqlCommand (that returns no resultset) against the specified SqlConnection
  /// using the provided parameters.
  /// </summary>
  /// <remarks>
  /// e.g.: 
  ///  int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
  /// </remarks>
  /// <param >A valid SqlConnection</param>
  /// <param >The CommandType (stored procedure, text, etc.)</param>
  /// <param >The stored procedure name or T-SQL command</param>
  /// <param >An array of SqlParamters used to execute the command</param>
  /// <returns>An int representing the number of rows affected by the command</returns>
  public static int ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
  {
   using(SqlCommand cmd=new SqlCommand(commandText,connection))
   {
    cmd.CommandType=commandType;
    if(commandParameters!=null)
    {
     SqlDbHelper.AttachParameters(cmd,commandParameters);
    }
                connection.Open();
    return cmd.ExecuteNonQuery();
   }
  }
  #endregion
  #region ExecuteDataset
  /// <summary>
  /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the database specified in
  /// the connection string.
  /// </summary>
  /// <remarks>
  /// e.g.: 
  ///  DataSet ds = ExecuteDataset(connString, CommandType.StoredProcedure, "GetOrders");
  /// </remarks>
  /// <param >A valid connection string for a SqlConnection</param>
  /// <param >The CommandType (stored procedure, text, etc.)</param>
  /// <param >The stored procedure name or T-SQL command</param>
  /// <returns>A dataset containing the resultset generated by the command</returns>
  public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText)
  {
   return  SqlDbHelper.ExecuteDataset(connectionString,commandType,commandText,null);
  }
  /// <summary>
  /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlConnection.
  /// </summary>
  /// <remarks>
  /// e.g.: 
  ///  DataSet ds = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders");
  /// </remarks>
  /// <param >A valid SqlConnection</param>
  /// <param >The CommandType (stored procedure, text, etc.)</param>
  /// <param >The stored procedure name or T-SQL command</param>
  /// <returns>A dataset containing the resultset generated by the command</returns>
  public static DataSet ExecuteDataset(SqlConnection connection, CommandType commandType, string commandText)
  {
   return SqlDbHelper.ExecuteDataset(connection,commandType,commandText,null);
  }
  /// <summary>
  /// Execute a SqlCommand (that returns a resultset) against the database specified in the connection string
  /// using the provided parameters.
  /// </summary>
  /// <remarks>
  /// e.g.: 
  ///  DataSet ds = ExecuteDataset(connString, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
  /// </remarks>
  /// <param >A valid connection string for a SqlConnection</param>
  /// <param >The CommandType (stored procedure, text, etc.)</param>
  /// <param >The stored procedure name or T-SQL command</param>
  /// <param >An array of SqlParamters used to execute the command</param>
  /// <returns>A dataset containing the resultset generated by the command</returns>
  public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
  {
   using(SqlConnection connection=new SqlConnection(connectionString))
   {
    return  SqlDbHelper.ExecuteDataset(connection,commandType,commandText,commandParameters);
   }
  }
  /// <summary>
  /// Execute a SqlCommand (that returns a resultset) against the specified SqlConnection
  /// using the provided parameters.
  /// </summary>
  /// <remarks>
  /// e.g.: 
  ///  DataSet ds = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
  /// </remarks>
  /// <param >A valid SqlConnection</param>
  /// <param >The CommandType (stored procedure, text, etc.)</param>
  /// <param >The stored procedure name or T-SQL command</param>
  /// <param >An array of SqlParamters used to execute the command</param>
  /// <returns>A dataset containing the resultset generated by the command</returns>
  public static DataSet ExecuteDataset(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
  {
   using(SqlCommand cmd=new SqlCommand(commandText,connection))
   {
    cmd.CommandType=commandType;
    using(SqlDataAdapter dataAdapter=new SqlDataAdapter(cmd))
    {
     DataSet ds=new DataSet();
     if(commandParameters!=null)
     {
      SqlDbHelper.AttachParameters(cmd,commandParameters); 
     }
     dataAdapter.Fill(ds);
     return ds;
    }
   }
  }
  #endregion
  #region ExecuteReader
  /// <summary>
  /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the database specified in
  /// the connection string.
  /// </summary>
  /// <remarks>
  /// e.g.: 
  ///  SqlDataReader dr = ExecuteReader(connString, CommandType.StoredProcedure, "GetOrders");
  /// </remarks>
  /// <param >A valid connection string for a SqlConnection</param>
  /// <param >The CommandType (stored procedure, text, etc.)</param>
  /// <param >The stored procedure name or T-SQL command</param>
  /// <returns>A SqlDataReader containing the resultset generated by the command</returns>
  public static SqlDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText)
  {
   return  SqlDbHelper.ExecuteReader(connectionString,commandType,commandText,null);
  } 
  /// <summary>
  /// Execute a SqlCommand (that returns a resultset) against the database specified in the connection string
  /// using the provided parameters.
  /// </summary>
  /// <remarks>
  /// e.g.: 
  ///  SqlDataReader dr = ExecuteReader(connString, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
  /// </remarks>
  /// <param >A valid connection string for a SqlConnection</param>
  /// <param >The CommandType (stored procedure, text, etc.)</param>
  /// <param >The stored procedure name or T-SQL command</param>
  /// <param >An array of SqlParamters used to execute the command</param>
  /// <returns>A SqlDataReader containing the resultset generated by the command</returns>
  public static SqlDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
  {
   using(SqlConnection connection=new SqlConnection(connectionString))
   {
    return  SqlDbHelper.ExecuteReader(connection,commandType,commandText,commandParameters);
   }
  }
  /// <summary>
  /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlConnection.
  /// </summary>
  /// <remarks>
  /// e.g.: 
  ///  SqlDataReader dr = ExecuteReader(conn, CommandType.StoredProcedure, "GetOrders");
  /// </remarks>
  /// <param >A valid SqlConnection</param>
  /// <param >The CommandType (stored procedure, text, etc.)</param>
  /// <param >The stored procedure name or T-SQL command</param>
  /// <returns>A SqlDataReader containing the resultset generated by the command</returns>
  public static SqlDataReader ExecuteReader(SqlConnection connection, CommandType commandType, string commandText)
  {
   return SqlDbHelper.ExecuteReader(connection,commandType,commandText,null);   
  }
  /// <summary>
  /// Execute a SqlCommand (that returns a resultset) against the specified SqlConnection
  /// using the provided parameters.
  /// </summary>
  /// <remarks>
  /// e.g.: 
  ///  SqlDataReader dr = ExecuteReader(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
  /// </remarks>
  /// <param >A valid SqlConnection</param>
  /// <param >The CommandType (stored procedure, text, etc.)</param>
  /// <param >The stored procedure name or T-SQL command</param>
  /// <param >An array of SqlParamters used to execute the command</param>
  /// <returns>A SqlDataReader containing the resultset generated by the command</returns>
  public static SqlDataReader ExecuteReader(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
  {
   using(SqlCommand cmd=new SqlCommand(commandText,connection))
   {
    cmd.CommandType=commandType;
    if(commandParameters!=null)
    {
     SqlDbHelper.AttachParameters(cmd,commandParameters); 
    }
                connection.Open();
    return cmd.ExecuteReader();
   }
  }
  #endregion
  #region ExecuteScalar
  /// <summary>
  /// Execute a SqlCommand (that returns a 1x1 resultset and takes no parameters) against the database specified in
  /// the connection string.
  /// </summary>
  /// <remarks>
  /// e.g.: 
  ///  int orderCount = (int)ExecuteScalar(connString, CommandType.StoredProcedure, "GetOrderCount");
  /// </remarks>
  /// <param >A valid connection string for a SqlConnection</param>
  /// <param >The CommandType (stored procedure, text, etc.)</param>
  /// <param >The stored procedure name or T-SQL command</param>
  /// <returns>An object containing the value in the 1x1 resultset generated by the command</returns>
  public static object ExecuteScalar(string connectionString, CommandType commandType, string commandText)
  {
   return  SqlDbHelper.ExecuteScalar(connectionString,commandType,commandText,null);
  }
  /// <summary>
  /// Execute a SqlCommand (that returns a 1x1 resultset) against the database specified in the connection string
  /// using the provided parameters.
  /// </summary>
  /// <remarks>
  /// e.g.: 
  ///  int orderCount = (int)ExecuteScalar(connString, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24));
  /// </remarks>
  /// <param >A valid connection string for a SqlConnection</param>
  /// <param >The CommandType (stored procedure, text, etc.)</param>
  /// <param >The stored procedure name or T-SQL command</param>
  /// <param >An array of SqlParamters used to execute the command</param>
  /// <returns>An object containing the value in the 1x1 resultset generated by the command</returns>
  public static object ExecuteScalar(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
  {
   using(SqlConnection connection=new SqlConnection(connectionString))
   {
    return  SqlDbHelper.ExecuteScalar(connection,commandType,commandText,commandParameters);
   }
  }
  /// <summary>
  /// Execute a SqlCommand (that returns a 1x1 resultset and takes no parameters) against the provided SqlConnection.
  /// </summary>
  /// <remarks>
  /// e.g.: 
  ///  int orderCount = (int)ExecuteScalar(conn, CommandType.StoredProcedure, "GetOrderCount");
  /// </remarks>
  /// <param >A valid SqlConnection</param>
  /// <param >The CommandType (stored procedure, text, etc.)</param>
  /// <param >The stored procedure name or T-SQL command</param>
  /// <returns>An object containing the value in the 1x1 resultset generated by the command</returns>
  public static object ExecuteScalar(SqlConnection connection, CommandType commandType, string commandText)
  {
   return SqlDbHelper.ExecuteScalar(connection,commandType,commandText,null);
   
  }
  /// <summary>
  /// Execute a SqlCommand (that returns a 1x1 resultset) against the specified SqlConnection
  /// using the provided parameters.
  /// </summary>
  /// <remarks>
  /// e.g.: 
  ///  int orderCount = (int)ExecuteScalar(conn, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24));
  /// </remarks>
  /// <param >A valid SqlConnection</param>
  /// <param >The CommandType (stored procedure, text, etc.)</param>
  /// <param >The stored procedure name or T-SQL command</param>
  /// <param >An array of SqlParamters used to execute the command</param>
  /// <returns>An object containing the value in the 1x1 resultset generated by the command</returns>
  public static object ExecuteScalar(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
  {
   using(SqlCommand cmd=new SqlCommand(commandText,connection))
   {
    cmd.CommandType=commandType;
    if(commandParameters!=null)
    {
     SqlDbHelper.AttachParameters(cmd,commandParameters); 
    }
                connection.Open();
    return cmd.ExecuteScalar();
   }
  }
  #endregion
  #region UpdateDataset
  /// <summary>
  /// Executes the respective command for each inserted, updated, or deleted row in the DataSet.
  /// </summary>
  /// <remarks>
  /// e.g.: 
  ///  UpdateDataset(conn, insertCommand, deleteCommand, updateCommand, dataSet, "Order");
  /// </remarks>
  /// <param >A valid transact-SQL statement or stored procedure to insert new records into the data source</param>
  /// <param >A valid transact-SQL statement or stored procedure to delete records from the data source</param>
  /// <param >A valid transact-SQL statement or stored procedure used to update records in the data source</param>
  /// <param >The DataSet used to update the data source</param>
  /// <param >The DataTable used to update the data source.</param>
  public static void UpdateDataset(SqlCommand insertCommand, SqlCommand deleteCommand, SqlCommand updateCommand, DataSet dataSet, string tableName)
  {
   if( insertCommand == null ) throw new ArgumentNullException( "insertCommand" );
   if( deleteCommand == null ) throw new ArgumentNullException( "deleteCommand" );
   if( updateCommand == null ) throw new ArgumentNullException( "updateCommand" );
   if( tableName == null || tableName.Length == 0 ) throw new ArgumentNullException( "tableName" );

   // Create a SqlDataAdapter, and dispose of it after we are done
   using (SqlDataAdapter dataAdapter = new SqlDataAdapter())
   {
    // Set the data adapter commands
    dataAdapter.UpdateCommand = updateCommand;
    dataAdapter.InsertCommand = insertCommand;
    dataAdapter.DeleteCommand = deleteCommand;

    // Update the dataset changes in the data source
    dataAdapter.Update (dataSet, tableName);

    // Commit all the changes made to the DataSet
    dataSet.AcceptChanges();
   }
  }
  #endregion
        #region UpdateDataTable
        public static void UpdateDatatable(string ConnectionString, string SelectCommandText, DataTable dataTable)
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                using (SqlDataAdapter sda = new SqlDataAdapter(SelectCommandText, connection))
                {
                    using (SqlCommandBuilder commandBuilder = new SqlCommandBuilder())
                    {
                        commandBuilder.DataAdapter = sda;
                        sda.Update(dataTable);
                        dataTable.AcceptChanges();
                    }
                }
            }
        }
        #endregion
    }
}