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
 }
}
 呵呵，至于程序的调用很简单的事，在这里就不浪费时间了．．．．．．