SqlHelp類的重寫

程式設計的一個重要思想是代碼和組建的重用性，這樣不僅可以提高程式的開發效率，減少程式員的代碼工作量，不要讓程式員變成敲代碼的工具

重要的是體現物件導向的特徵．為了這個目的我們往往建立個公用類抽出公用的方法為程式員留出變成介面，這樣可以提高我們的開發效率．

呵呵，下面進入正題－－－－剛接觸asp.net時寫的一個SqlHelper類，程式寫的不是太好，適合初學者.....

#region Copyright (c) 2006, By 王加鋒
/*================================================================
 * 
 * Copyright (c) 2006, 王加鋒, All rights reserved.
 * 
 * FileName   : Class1.cs
 * Author     : 王加鋒
 * functions  :將資料庫操作的資訊都放到裡面，實現代碼重用和組件化編程
 * CreateDate : 2006/10/15
 * ChangeDate : 2006/10/23
 * 
=================================================================*/
#endregion

#region--引入命名空間
using System;
using System.Data;
using System.Data.SqlClient;
using System.Web.UI.WebControls;
#endregion

namespace DataBaseClassLibrary
{
 /// <summary>
 /// 將資料庫操作的資訊都放到裡面，實現代碼重用和組件化編程。
 /// </summary>
 public class Class1
 {
  #region---成員變數
  private SqlConnection myconnection= null;
  private SqlCommand Mycommand = null;
  private SqlDataAdapter da = null;
  private DataSet ds = null; 
  private SqlDataReader DR = null; 
  #endregion

  #region--設定屬性，返回類內建成員
  //設定屬性返回SqlConnection對象
  public SqlConnection MySqlconnection
  {
   get
   {
    return myconnection;
   }
   set
   {
    myconnection = value;
   }

  }
  #endregion

  #region----建構函式重載
  //建構函式部分
  public Class1(string database)
  {
   string Str = "server = .; database ="+ database+"; uid = sa; pwd = 123123";
   MySqlconnection = new SqlConnection(Str);
  }
  public Class1(string server, string database, string uid, string pwd)
  {
   string Str = "server = "+server+"; database ="+database+"; uid ="+uid+"; pwd = "+pwd;
   MySqlconnection = new SqlConnection(Str);
  }
  #endregion

  #region--返回一個SqlCommand對象
  /// <summary>
  /// 返回一個SqlCommand對象,Sql語句執行形式
  /// </summary>
  /// <param name="command">輸入要執行的SqlCommand語句</param>
  /// <returns>SqlCommand對象</returns>
  public SqlCommand ReturnCommand(string command)
  {
   try
   {
    return (new SqlCommand(command,MySqlconnection));
   }
   catch
   {
    return null;
   }
  }
  #endregion

  #region--執行預存程序或SQL語句
  public int ExcuteCommands(string Strs)
  {
   int j=0;
   try
   {
    if(this.MySqlconnection.State == ConnectionState.Closed)
     this.MySqlconnection.Open();
    Mycommand = new SqlCommand(Strs,MySqlconnection);
    j=Convert.ToInt32(Mycommand.ExecuteNonQuery());
   }
   catch
   {
    return 0;
   }
   return j;
  }
  #endregion
  
  #region--建立帶參數的SqlCommand的執行過程，並調用預存程序
  /// <summary>
  /// 建立帶參數的SqlCommand的執行過程，並調用預存程序
  /// </summary>
  /// <param name="ExcuteprocName">要執行的預存程序的名字</param>
  /// <param name="pa">SqlParameter型的參數數組</param>
  /// <returns>返回受影響的行數</returns>
  public int ReturnCommand(string ExcuteprocName,params SqlParameter[] pa)
  {
   int j = 0;
   try
   {
    if(this.MySqlconnection.State == ConnectionState.Closed)
     this.MySqlconnection.Open();
    Mycommand = new SqlCommand(ExcuteprocName,MySqlconnection);
    Mycommand.CommandType = CommandType.StoredProcedure;
    foreach(SqlParameter sp in pa)
    {
     Mycommand.Parameters .Add (sp);
    }
    j = (int)Mycommand.ExecuteNonQuery();
   }
   catch 
   {
    return 0;
   }
   finally
   {
    MySqlconnection.Close();
   }
   return j;
  }
  #endregion

  #region--返回SqlDataReader對象
  /// <summary>
  /// 返回SqlDataReader對象
  /// </summary>
  /// <param name="string">要執行的SqlCommand語句</param>
  /// <returns>SqlDataReader對象</returns>
  public SqlDataReader ReturnSqlDataReader(string command)
  {
   try
   {
    if(this.MySqlconnection.State == ConnectionState.Closed)
     this.MySqlconnection.Open();
    Mycommand = new SqlCommand(command,MySqlconnection);
    DR = Mycommand.ExecuteReader();
   }
   catch
   {
    //System.Web.HttpContext.Current.Response.Write(ex.Message);
    return null;
   }
   finally
   {
    MySqlconnection.Close();
   }
   return DR;
  }
  #endregion

  #region--返回DataSet對象
  /// <summary>
  /// 返回DataSet對象
  /// </summary>
  /// <param name="command">SqlCommand對象</param>
  /// <returns>DataSet對象</returns>
  public DataSet ReturnDataSet(string Strcommand)
  {
   try
   {
    Mycommand = new SqlCommand(Strcommand,MySqlconnection);
    da=new SqlDataAdapter(Mycommand);
    ds = new DataSet();
    da.Fill(ds,"table");
   }
   catch
   {
    //Response.Write(ex.Message);
    return null;
   }
   return ds;
  }
  #endregion

  #region---執行SQL語句或預存程序來填充DataGrid對象
  public void DataBings(string Str,DataGrid DataGrid1)
  {
   SqlCommand Mycommand = new SqlCommand(Str,MySqlconnection);
   SqlDataAdapter da = new SqlDataAdapter(Mycommand);
   DataSet ds = new DataSet();
   ds.Clear();
   da.Fill(ds);

   DataGrid1.DataSource = ds.Tables[0];
   DataGrid1.DataBind();
  }
  #endregion

  #region--返回帶參數的SqlCommand的執行過程的傳回值
  /// <summary>
  /// 建立帶參數的SqlCommand的執行過程，並調用預存程序
  /// </summary>
  /// <param name="ExcuteprocName">要執行的預存程序的名字</param>
  /// <param name="pa">SqlParameter型的參數數組</param>
  /// <returns>傳回值</returns>
  public int ReturnCommandvalue(string ExcuteprocName,params SqlParameter[] pa)
  {
   //int j=0;
   try
   {
    if(this.MySqlconnection.State == ConnectionState.Closed)
     this.MySqlconnection.Open();
    Mycommand = new SqlCommand(ExcuteprocName,MySqlconnection);
    Mycommand.CommandType = CommandType.StoredProcedure;
    foreach(SqlParameter sp in pa)
    {
     Mycommand.Parameters .Add (sp);
    }
    SqlParameter ps =  Mycommand.Parameters.Add("RETURN_VALUE",SqlDbType.Int);
    ps.Direction = ParameterDirection.ReturnValue;
    Mycommand.ExecuteNonQuery();
    //j = (int)Mycommand.Parameters["RETURN_VALUE"].Value;
   }
   catch 
   {
    return 0;
   }
   finally
   {
    MySqlconnection.Close();
   }
   return (int)Mycommand.Parameters["RETURN_VALUE"].Value;;
  }
  #endregion

  #region--返回帶參數的SqlCommand的執行過程的輸出參數
  /// <summary>
  /// 建立帶參數的SqlCommand的執行過程，並調用預存程序
  /// </summary>
  /// <param name="ExcuteprocName">要執行的預存程序的名字</param>
  /// <param name="ps">輸出參數</param>
  /// <param name="pa">SqlParameter型的參數數組</param>
  /// <returns>輸出參數</returns>
  public string OutputStoredProcedure(string ExcuteprocName,string ziduan ,string SqlDbTypes, params SqlParameter[] pa)
  {
   try
   {
    if(this.MySqlconnection.State == ConnectionState.Closed)
     this.MySqlconnection.Open();
    Mycommand = new SqlCommand(ExcuteprocName,MySqlconnection);
    Mycommand.CommandType = CommandType.StoredProcedure;
    foreach(SqlParameter sp in pa)
    {
     Mycommand.Parameters .Add (sp);
    }
    SqlParameter ps =  Mycommand.Parameters.Add("@"+ziduan,SqlDbTypes);
    ps.Size = 20;
    ps.Direction = ParameterDirection.Output;
    Mycommand.ExecuteNonQuery();
    MySqlconnection.Close();
    return Mycommand.Parameters["@"+ziduan].Value.ToString();
   }
   catch 
   {
    return null;
   }
  }
  #endregion

//  #region---返回視圖
//  public DataView ReturnDataView()
//  {
//  }
//  #endregion

  #region--檢查資料連線是否為關閉->關閉串連
  public void CloseConnection()
  {
   if(this.MySqlconnection.State == ConnectionState.Open)
   {
    this.MySqlconnection.Close();
   }
  }
  #endregion
 }
}
 呵呵，至於程式的調用很簡單的事，在這裡就不浪費時間了．．．．．．