ADO.NET数据操作类

使用方法：先引用:using DB;

                 然后实例化类并传递数据库连接信息；

                 最后，调用数据操作方法即可。

 

using System;
using System.Data;
using System.Data.SqlClient;

namespace DB
{
 public class DbObject
 {
  protected SqlConnection Connection;
  private string connectionString;

  public DbObject()
  {
   connectionString=System.Configuration.ConfigurationSettings.AppSettings["DSN"];
   Connection=new SqlConnection(connectionString);
  }

  protected string ConnectionString
  {
   get
   {
    return connectionString;
   }
  }

  public SqlCommand BuildIntCommand(string storedProcName, IDataParameter[] parameters)
  {
   SqlCommand command = BuildQueryCommand(storedProcName, parameters);   

   command.Parameters.Add( new SqlParameter("ReturnValue",
    SqlDbType.Int,
    4, /* Size */
    ParameterDirection.ReturnValue,
    false, /* is nullable */
    0, /* byte precision */
    0, /* byte scale */
    string.Empty,
    DataRowVersion.Default,
    null ));

   return command;
  }

  public SqlCommand BuildQueryCommand(string storedProcName,IDataParameter[] parameters)
  {
   SqlCommand command = new SqlCommand(storedProcName,Connection);
   command.CommandType = CommandType.StoredProcedure;

   foreach (SqlParameter parameter in parameters)
   {
    command.Parameters.Add(parameter);
   }
   return command;
  }

  public int RunProcedure(string storedProcName,IDataParameter[] parameters, out int rowsAffected)
  {
   int result;

   Connection.Open();
   SqlCommand command = BuildIntCommand( storedProcName, parameters );
   rowsAffected = command.ExecuteNonQuery();
   result = (int)command.Parameters["ReturnValue"].Value;
   Connection.Close();
   return result;
  }

  public SqlDataReader RunProcedure(string storedProcName,IDataParameter[] parameters)
  {
   SqlDataReader returnReader;

   Connection.Open();
   SqlCommand command = BuildQueryCommand(storedProcName,parameters);
   command.CommandType = CommandType.StoredProcedure;

   returnReader = command.ExecuteReader(CommandBehavior.CloseConnection);
   return returnReader;
  }

  public DataSet RunProcedure(string storedProcName,IDataParameter[] parameters,string tableName)
  {
   DataSet dataSet = new DataSet();
   Connection.Open();
   SqlDataAdapter sqlDA = new SqlDataAdapter();
   sqlDA.SelectCommand = BuildQueryCommand(storedProcName,parameters);
   sqlDA.Fill(dataSet,tableName);
   Connection.Close();

   return dataSet;
  }

  public void RunProcedure(string storedProcName,IDataParameter[] parameters,DataSet dataSet,string tableName)
  {
   Connection.Open();
   SqlDataAdapter sqlDA = new SqlDataAdapter();
   sqlDA.SelectCommand = BuildIntCommand( storedProcName, parameters );
   sqlDA.Fill( dataSet, tableName );
   Connection.Close();   
  }
  public void RunProcedure(string storeProcName)
  {
                        Connection.Open();
   SqlCommand cmd=new SqlCommand(storeProcName,Connection);
   cmd.CommandType=CommandType.StoredProcedure;
   cmd.CommandTimeout=100;
   cmd.ExecuteNonQuery();
   Connection.Close();
  }
 }
}