.net中的常用操作類

OperateDatabase.cs檔案代碼：

using System;

using System.Data;

using System.Collections;

using System.Data.SqlClient;

using System.Configuration;

namespace Gogofly.OperateSqlServer

{

 public class OperateDatabase

 {

  #region  定義屬性

  /// <summary>

  /// 定義在web.config中辨別連接配接字元串的Name屬性的值

  /// </summary>

  private static string connectionStringNameInWebConfig = "SQLSERVERCONNECTIONSTRING";

  public static string ConnectionStringNameInWebConfig

  {

   get

   {

    return connectionStringNameInWebConfig;

   }

   set

   {   ///字元串不能為空或空引用

    if(!string.IsNullOrEmpty(value))

    {

     connectionStringNameInWebConfig = value;

    }

  }

  /// 儲存執行資料庫操作傳回值的參數辨別

  private static string returnValueString = "RETURNVALUESTRING";

  public static string ReturnValueString

    return returnValueString;

   }   

  #endregion

  #region 建立參數

  /// 建立參數

  /// <param name="ParamName">存儲過程名稱</param>

  /// <param name="DbType">參數類型</param>

  /// <param name="Size">參數大小</param>

  /// <param name="Direction">參數方向</param>

  /// <param name="Value">參數值</param>

  /// <returns>新的 parameter 對象</returns>

  private static SqlParameter CreateParam(string ParamName,SqlDbType DbType,

   Int32 Size,ParameterDirection Direction,object Value)

   SqlParameter param;   

   if(Size > 0)

   {   ///使用size建立大小不為0的參數

    param = new SqlParameter(ParamName,DbType,Size);

   else

    param = new SqlParameter(ParamName,DbType);

   ///建立輸出類型參數

   param.Direction = Direction;

   if(!(Direction == ParameterDirection.Output && Value == null))

    param.Value = Value;

   ///傳回參數

   return param;

  /// 建立輸入類型參數

  /// <param name="DbType">參數類型</param></param>

  /// <returns>新的parameter 對象</returns>

  public static SqlParameter CreateInParam(string ParamName,

   SqlDbType DbType,int Size,object Value)

   return CreateParam(ParamName,DbType,Size,ParameterDirection.Input,Value);

  /// 建立輸出類型參數

  public static SqlParameter CreateOutParam(string ParamName,

   SqlDbType DbType,int Size)

   return CreateParam(ParamName,DbType,Size,ParameterDirection.Output,null);

  /// 建立傳回類型參數

  public static SqlParameter CreateReturnParam(string ParamName,

   return CreateParam(ParamName,DbType,Size,ParameterDirection.ReturnValue,null);

  #region 建立SqlCommand和SqlDataAdapter

  /// 建立SqlCommand對象

  /// <param name="procName">存儲過程的名稱</param>

  /// <param name="prams">存儲過程所需參數</param>

  /// <returns>傳回SqlCommand對象</returns>

  private static SqlCommand CreateSqlCommand(string procName,

   params SqlParameter[] prams)

  {   ///建立資料庫連接配接

   SqlConnection sqlCon = CreateSqlConnection();

   ///打開資料庫連接配接

   if(sqlCon == null) return null;

   if(sqlCon.State == ConnectionState.Closed)

    sqlCon.Open();

   ///設定SqlCommand的屬性

   SqlCommand cmd = new SqlCommand(procName,sqlCon);

   cmd.CommandType = CommandType.StoredProcedure;

   ///添加存儲過程參數

   if(prams != null)

    foreach(SqlParameter parameter in prams)

     cmd.Parameters.Add(parameter);

   ///添加傳回參數

   cmd.Parameters.Add(new SqlParameter(returnValueString,

    SqlDbType.Int,4,ParameterDirection.ReturnValue,

    false,0,0,string.Empty,DataRowVersion.Default,null));

   ///傳回SqlCommand對象

   return cmd;

  /// 建立SqlDataAdapter對象

  /// <returns>傳回SqlDataAdapter對象</returns>

  private static SqlDataAdapter CreateSqlDataAdapter(string procName,

   ///設定SqlDataAdapter的屬性

   SqlDataAdapter da = new SqlDataAdapter(procName,sqlCon);

   da.SelectCommand.CommandType = CommandType.StoredProcedure;

     da.SelectCommand.Parameters.Add(parameter);

   da.SelectCommand.Parameters.Add(new SqlParameter(returnValueString,

   ///傳回SqlDataAdapter對象

   return da;

        /// <summary>

        /// 防止SQL注入式攻擊

        /// </summary>

        /// <returns>傳回整型資料</returns>

        public static int checkLogin(string loginName, string loginPwd)

        {

            int result;

            ///建立資料庫連接配接

            SqlConnection con = CreateSqlConnection();

            myCommand.Parameters.Add(new SqlParameter("@loginName", SqlDbType.VarChar, 50));

            myCommand.Parameters["@loginName"].Value = loginName;

            myCommand.Parameters.Add(new SqlParameter("@loginPwd", SqlDbType.VarChar, 225));

            myCommand.Parameters["@loginPwd"].Value = loginPwd;

            myCommand.Parameters.Add(new SqlParameter("@RoleID", SqlDbType.Int));

            myCommand.Parameters["@RoleID"].Value = 1;

            myCommand.Connection.Open();

            result = (int)myCommand.ExecuteScalar();

            myCommand.Connection.Close();

            return result;

        }

  #region 管理連接配接

  /// 建立連接配接.

  private static SqlConnection CreateSqlConnection()

  {   ///擷取連接配接字元串

   string conStr = (string)Cache.GetData(connectionStringNameInWebConfig);

   if(string.IsNullOrEmpty(conStr))

   {  

    try

    {   ///如果連接配接字元串為空，則從配置檔案中擷取連接配接字元串

     conStr = ConfigurationManager.ConnectionStrings[connectionStringNameInWebConfig].ConnectionString;

    catch(Exception ex)

     throw new Exception(ex.Message,ex);

    ///把字元串添加到緩存中

    Cache.CachingData(connectionStringNameInWebConfig,conStr);    

   if(!string.IsNullOrEmpty(conStr))

   { /// 建立資料庫連接配接

    return(new SqlConnection(conStr));

   return null;

        /// 說明：GetDataSet資料集，傳回資料源的資料集

        /// 傳回值：資料集DataSet

        /// 參數：sQueryString SQL字元串，TableName 資料表名稱

        public static DataSet GetDataSet(string sQueryString, string TableName)

            con.Open();

            SqlDataAdapter dbAdapter = new SqlDataAdapter(sQueryString, con);

            DataSet dataset = new DataSet();

            dbAdapter.Fill(dataset, TableName);

            con.Close();

            return dataset;

  #region 執行存儲過程

  /// 執行存儲過程

  /// <returns></returns>

  public static int RunProc(string procName)

  {   ///建立SqlCommand對象

   SqlCommand cmd = CreateSqlCommand(procName,null);

   if(cmd == null) return -1;

   try

   {   ///執行存儲過程

    cmd.ExecuteNonQuery();

   catch(Exception ex)

    throw new Exception(ex.Message,ex);

   finally

   {   ///關閉連接配接

    if(cmd.Connection.State == ConnectionState.Open)

     cmd.Connection.Close();

   ///傳回執行結果

   return (int)cmd.Parameters[returnValueString].Value;

  /// <param name="procName">存儲過程名稱</param>

  /// <param name="prams">存儲過程參數</param>

  public static int RunProc(string procName,params SqlParameter[] prams)

   SqlCommand cmd = CreateSqlCommand(procName,prams);

   return (int)cmd.Parameters[returnValueString].Value;  

  /// <param name="dr">傳回SqlDataReader對象</param>

  public static void RunProc(string procName,out SqlDataReader dr)

   if(cmd == null)

    dr = null;

    return;

   {   ///讀取資料

    dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

   }  

  /// <param name="prams">存儲過程參數</param>  

  public static void RunProc(string procName,out SqlDataReader dr,params SqlParameter[] prams)

  } 

  /// <param name="ds">傳回DataSet對象</param>

  public static void RunProc(string procName,ref DataSet ds)

  {   ///初始化ds

   if(null == ds) ds = new DataSet();

   ///建立SqlDataAdapter

   SqlDataAdapter da = CreateSqlDataAdapter(procName,null);

   if(da == null) return;

   {   ///填充資料

    da.Fill(ds);

    if(da.SelectCommand.Connection.State == ConnectionState.Open)

     da.SelectCommand.Connection.Close();

  public static void RunProc(string procName,ref DataSet ds,params SqlParameter[] prams)

   SqlDataAdapter da = CreateSqlDataAdapter(procName,prams);

  /// <param name="start">開始的記錄</param>

  /// <param name="max">最大記錄數量</param>

  public static void RunProc(string procName,ref DataSet ds,

   int start,int max)

    da.Fill(ds,start,max,"TableName");

   int start,int max,params SqlParameter[] prams)

  /// <returns>傳回存儲過程傳回值</returns>

  public static int RunProcScalar(string procName)

   int result;

    ///執行存儲過程

    result = (int)cmd.ExecuteScalar();

   ///傳回查詢結果

   return result;

  public static int RunProcScalar(string procName,params SqlParameter[] prams)

  {    ///建立SqlCommand對象

  #endregion 

 }

 internal class Cache

  #region 緩存資料

  /// 定義緩存區

  private static Hashtable cache = Hashtable.Synchronized(new Hashtable());

  /// 緩存資料到Cache中，如果已經存在緩存項，則重寫緩存的資料。

  /// <param name="key"></param>

  /// <param name="value"></param>

  public static void CachingData(string key,object value)

   cache[key] = value;   

  /// 擷取緩存的資料

  public static object GetData(string key)

   return (object)cache[key];

}