仿Petshop写的数据库操作类

using System;
using System.ComponentModel;
using System.Collections;
using System.Diagnostics;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace MY.Components
{
 /// <summary>
 /// ADO.NET data access using the SQL Server Managed Provider.
 /// </summary>
 public class Database : IDisposable
 {
  // connection to data source
  private SqlConnection con;
  protected SqlConnection objconn;

  public Database()
  {
   objconn=new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
  }
  #region 数据库各种操作方法
  public SqlConnection objconnopen
  {
   get
   {
    objconn.Open();
    return objconn;
   }
  }
  public void objconnclose()
  {
   objconn.Close();
   objconn.Dispose();
  }
  /// <summary>
  /// Run stored procedure.
  /// </summary>
  /// <param name="procName">Name of stored procedure.</param>
  /// <returns>Stored procedure return value.</returns>
  public int RunProc(string procName)
  {
   SqlCommand cmd = CreateCommand(procName, null);
   cmd.ExecuteNonQuery();
   this.Close();
   return (int)cmd.Parameters["ReturnValue"].Value;
  }

  /// <summary>
  /// Run stored procedure.
  /// </summary>
  /// <param name="procName">Name of stored procedure.</param>
  /// <param name="prams">Stored procedure params.</param>
  /// <returns>Stored procedure return value.</returns>
  public int RunProc(string procName, SqlParameter[] prams)
  {
   SqlCommand cmd = CreateCommand(procName, prams);
   cmd.ExecuteNonQuery();
   this.Close();
   return (int)cmd.Parameters["ReturnValue"].Value;
  }

  /// <summary>
  /// Run stored procedure.
  /// </summary>
  /// <param name="procName">Name of stored procedure.</param>
  /// <param name="dataReader">Return result of procedure.</param>
  public void RunProc(string procName, out SqlDataReader dataReader)
  {
   SqlCommand cmd = CreateCommand(procName, null);
   dataReader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
  }

  /// <summary>
  /// Run stored procedure.
  /// </summary>
  /// <param name="procName">Name of stored procedure.</param>
  /// <param name="prams">Stored procedure params.</param>
  /// <param name="dataReader">Return result of procedure.</param>
  public void RunProc(string procName, SqlParameter[] prams, out SqlDataReader dataReader)
  {
   SqlCommand cmd = CreateCommand(procName, prams);
   dataReader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
  }

  /// <summary>
  /// Create command object used to call stored procedure.
  /// </summary>
  /// <param name="procName">Name of stored procedure.</param>
  /// <param name="prams">Params to stored procedure.</param>
  /// <returns>Command object.</returns>
  private SqlCommand CreateCommand(string procName, SqlParameter[] prams)
  {
   // make sure connection is open
   Open();

   //command = new SqlCommand( sprocName, new SqlConnection( ConfigManager.DALConnectionString ) );
   SqlCommand cmd = new SqlCommand(procName, con);
   cmd.CommandType = CommandType.StoredProcedure;

   // add proc parameters
   if (prams != null)
   {
    foreach (SqlParameter parameter in prams)
     cmd.Parameters.Add(parameter);
   }
   
   // return param
   cmd.Parameters.Add(
    new SqlParameter("ReturnValue", SqlDbType.Int, 4,
    ParameterDirection.ReturnValue, false, 0, 0,
    string.Empty, DataRowVersion.Default, null));

   return cmd;
  }

  /// <summary>
  /// Open the connection.
  /// </summary>
  private void Open()
  {
   // open connection
   if (con == null)
   {
    con = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
    con.Open();
   }    
  }

  /// <summary>
  /// Close the connection.
  /// </summary>
  public void Close()
  {
   if (con != null)
    con.Close();
  }

  /// <summary>
  /// Release resources.
  /// </summary>
  public void Dispose()
  {
   // make sure connection is closed
   if (con != null)
   {
    con.Dispose();
    con = null;
   }    
  }

  /// <summary>
  /// Make input param.
  /// </summary>
  /// <param name="ParamName">Name of param.</param>
  /// <param name="DbType">Param type.</param>
  /// <param name="Size">Param size.</param>
  /// <param name="Value">Param value.</param>
  /// <returns>New parameter.</returns>
  public SqlParameter MakeInParam(string ParamName, SqlDbType DbType, int Size, object Value)
  {
   return MakeParam(ParamName, DbType, Size, ParameterDirection.Input, Value);
  }  

  /// <summary>
  /// Make input param.
  /// </summary>
  /// <param name="ParamName">Name of param.</param>
  /// <param name="DbType">Param type.</param>
  /// <param name="Size">Param size.</param>
  /// <returns>New parameter.</returns>
  public SqlParameter MakeOutParam(string ParamName, SqlDbType DbType, int Size)
  {
   return MakeParam(ParamName, DbType, Size, ParameterDirection.Output, null);
  }  

  /// <summary>
  /// Make stored procedure param.
  /// </summary>
  /// <param name="ParamName">Name of param.</param>
  /// <param name="DbType">Param type.</param>
  /// <param name="Size">Param size.</param>
  /// <param name="Direction">Parm direction.</param>
  /// <param name="Value">Param value.</param>
  /// <returns>New parameter.</returns>
  public SqlParameter MakeParam(string ParamName, SqlDbType DbType, Int32 Size, ParameterDirection Direction, object Value)
  {
   SqlParameter param;

   if(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;
  }

  /// <summary>
  /// 执行SQL
  /// </summary>
  /// <param name="sql"></param>
  public void ExeCommand(string sql)
  {
   SqlConnection  con=new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
   SqlCommand cmd=new SqlCommand(sql,con);
   cmd.CommandType=CommandType.Text;
   cmd.Connection.Open();
   cmd.ExecuteScalar();
   cmd.Connection.Close();
  
  }
  /// <summary>
  /// 读取数据，注意要显式关闭
  /// </summary>
  /// <param name="sql"></param>
  /// <returns></returns>
  public SqlDataReader Reader(string sql)
  {  
            SqlConnection  con=new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
   SqlCommand cmd=new SqlCommand(sql,con);
   cmd.Connection.Open();
   SqlDataReader dr=cmd.ExecuteReader();
   return dr;
  }

  /// <summary>
  /// 创建数据集
  /// </summary>
  /// <param name="tableName"></param>
  /// <param name="ss"></param>
  /// <returns></returns>
  public DataSet CreateDataSet(string tableName,string ss)
  {
  
   SqlConnection  con=new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
   SqlDataAdapter adpt=new SqlDataAdapter(ss,con);
   DataSet ds=new DataSet();
   adpt.Fill(ds,tableName);
   Close();
   return ds;
  }
  
  /// <summary>
  /// 得到DataTable
  /// </summary>
  /// <param name="strQuery"></param>
  /// <param name="strTable"></param>
  /// <returns></returns>
  public DataTable SqlGetDataset(string strQuery,string strTable)
  {
   string SQL_SELECT=strQuery;
   SqlConnection  con=new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);

   SqlCommand sqlCmd=new SqlCommand(strQuery,con);   
   try
   {
    sqlCmd.Connection.Close();
    sqlCmd.Connection.Open();
   }
   catch(Exception e)
   {
    throw e;
   }
   SqlDataAdapter da=new SqlDataAdapter();
   da.SelectCommand=sqlCmd;
   DataSet ds=new DataSet();
   da.Fill(ds,strTable);
   da.Dispose();
   return ds.Tables[strTable];
  }
  
  /// <summary>
  /// 得到结果集数
  /// </summary>
  /// <param name="strQuery"></param>
  /// <returns></returns>
  public int SqlRunCommandScalar(string strQuery)
  {
   string SQL_SELECT=strQuery;
   SqlConnection  con=new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
   SqlCommand sqlCmd=new SqlCommand(strQuery,con);
   //sqlCmd.Cancel();
   try
   {
    sqlCmd.Connection.Close();
    sqlCmd.Connection.Open();
   }
   catch(Exception e)
   {
    throw e;
   }
   return Convert.ToInt32(sqlCmd.ExecuteScalar());
  }
  #endregion

    }
}