网络版的数据库操作类

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

namespace BrainSoft.WebQuote.DataManagement
{
 /// <summary>
 /// 与数据库进行交互，对数据进行提取与更新
 ///</summary>
 public class DataManager
 {
  #region 自定义变量
  private string connString =System.Configuration.ConfigurationSettings.AppSettings["connectionString"].ToString();
  protected SqlConnection conn =new SqlConnection();
  #endregion

  #region 构造函数
  public DataManager()
  {
   
  }
  #endregion

  #region 私有方法
  ///<summary>
  /// 打开连接
  ///</summary>
  private voidOpenConnection()
  {
   CheckConnection();
  }
  ///<summary>
  /// 关闭连接
  ///</summary>
  private voidCloseConnection()
  {
   if(conn.State == ConnectionState.Open)
   {
    conn.Close();
    conn.Dispose();
   }
  }  
  
  ///<summary>
  /// 检查连接，如果连接关闭，则打开
  ///</summary>
  ///<returns></returns>
  private boolCheckConnection()
  {
   if(conn.State == ConnectionState.Closed)
   {
    try
    {
     conn.ConnectionString= this.connString;
     conn.Open();
    }
    catch(Exception)
    {
     returnfalse;
    }
   }
   returntrue;
  }

  #endregion

  #region 公共方法
  ///<summary>
  /// 创建连接对象
  ///</summary>
  ///<returns></returns>
  public SqlConnectionCreateCon()
  {
   //连接数据库类
   SqlConnectionconn = new SqlConnection(this.connString);
   returnconn;
  }
  ///<summary>
  /// 执行SQL操作
  ///</summary>
  /// <paramname="sql"></param>
  public bool GetNonData(stringsql)
  {
   //根据传来的SQL语句执行插入/删除/更新等操作
   try
   {
    OpenConnection();
    SqlCommandcomm = new SqlCommand(sql, conn);
    comm.ExecuteNonQuery();
    conn.Close();
    conn.Dispose();
    comm.Dispose();
    returntrue;
   }
   catch(Exception)
   {
    returnfalse;
   }
   finally
   {
    CloseConnection();
   }

  }
  ///<summary>
  /// 查询数据表

  ///</summary>
  /// <paramname="sql">SQL语句</param>
  ///<returns></returns>
  public DataTableGetDataTable(string sql)
  {
   ////返回一个装载了SQL制定留言的数据表，

   try
   {
    OpenConnection();
    SqlDataAdapterda = new SqlDataAdapter(sql, conn);
    DataSetds = new DataSet();
    da.Fill(ds);
    returnds.Tables[0];
   }
   catch(Exception)
   {
    returnnull;
   }
   finally
   {
    CloseConnection();
   }
  }
  ///<summary>
  /// 返回一个装载了SQL制定留言的数据表，

  ///</summary>
  /// <paramname="sql"></param>
  ///<returns></returns>
  public DataSetGetDataSet(string sql )
  {
   ////返回一个装载了SQL制定留言的数据表，
   try
   {
    OpenConnection();
    SqlDataAdapterda = new SqlDataAdapter(sql, conn);
    DataSetds = new DataSet();
    da.Fill(ds);
    returnds;
   }
   finally
   {
    CloseConnection();
   }
  }

  ///<summary>
  /// 执行存储过程
  ///</summary>
  public objectExecuteProcedure(string procedureName,IDbDataParameter[]parameterList,out Hashtable outParameter,out DataTableoutDataTable)
  {
   try
   {
    objectresult = new object();
    DataSetdsList = new DataSet();
    outParameter= new Hashtable();
    outDataTable= new DataTable();

    OpenConnection();

    SqlCommand command =new SqlCommand();
    command.CommandType=CommandType.StoredProcedure;
    command.Connection=conn;
    command.CommandText=procedureName;
    command.CommandTimeout=600;

    if(parameterList!= null && parameterList.Length> 0)
    {
     for(inti = 0;i < parameterList.Length;i++)
     {
      command.Parameters.Add(parameterList[i]);
     }
    }

    SqlDataAdapterdataAdapter =new SqlDataAdapter();
    dataAdapter.SelectCommand=command;
    dataAdapter.Fill(dsList);
    if(dsList.Tables.Count> 0)
    {
     outDataTable= dsList.Tables[0];
    }
    else
    {
     outDataTable= new DataTable();
    }

    //将存储过程中OutPut、InputOutPut、ReturnValue类型的参数值取出
    if(parameterList!= null && parameterList.Length> 0)
    {
     for(inti = 0;i < parameterList.Length;i++)
     {
      if(parameterList[i].Direction== ParameterDirection.Output || parameterList[i].Direction ==ParameterDirection.InputOutput)
      {
       outParameter.Add(parameterList[i].ParameterName,parameterList[i].Value);
      }
      elseif(parameterList[i].Direction ==ParameterDirection.ReturnValue)
      {
       result= parameterList[i].Value;
      }
     }
    }
    returnresult;
   }
   catch(Exceptionex)
   {
    outParameter= new Hashtable();
    outDataTable= new DataTable();
    returnnull;
   }
   finally
   {
    CloseConnection();
   }
  }

  ///<summary>
  ///根据传来的条件查询该项是否有内容，有就返回true
  ///</summary>
  /// <paramname="sql"></param>
  ///<returns></returns>
  public bool IfEmpty(stringsql)
  {
   //根据传来的条件查询该项是否有内容，有就返回true
   try
   {
    boolifreader;
    SqlConnectionconstr = this.CreateCon();
    SqlCommandcommstr = new SqlCommand(sql, constr);
    commstr.Connection.Open();
    SqlDataReadermydr = commstr.ExecuteReader();
    if(mydr.Read())
    {
     ifreader= true;
    }
    else
    {
     ifreader= false;
    }
    commstr.Connection.Close();
    commstr.Dispose();
    returnifreader;
   }
   finally
   {
    CloseConnection();
   }
  }
  ///<summary>
  /// 同样是根据传来的ＳＱＬ语句返回一个字段的值

  ///</summary>
  /// <paramname="que"></param>
  ///<returns></returns>
  public stringGetScalarData(string que)
  {
   //同样是根据传来的ＳＱＬ语句返回一个字段的值

   try
   {
    OpenConnection();
    SqlCommandcmd = new SqlCommand(que, conn);
    returncmd.ExecuteScalar().ToString();
   }
   finally
   {
    CloseConnection();
   }
  }
  ///<summary>
  /// 返回查询语句中指定的查询项

  ///</summary>
  /// <paramname="sql">SQL语句</param>
  /// <paramname="num">查询项中的位置</param>
  ///<returns></returns>
  public string GetOneData(stringsql, int num)
  {
   stringreturnstr;
   try
   {
    OpenConnection();
    SqlCommandcomm = new SqlCommand(sql, conn);
    SqlDataReaderdr = comm.ExecuteReader();
    if(dr.Read())
    {
     returnstr= dr.GetValue(num).ToString();
    }
    else
    {
     returnstr= "没有此记录";
    }
    returnreturnstr;
   }
   finally
   {
    CloseConnection();
   }
  }
  
  ///<summary>
  /// 分页查询
  ///</summary>
  /// <paramname="tblName">表(视图)名</param>
  /// <paramname="fldName">查询字段名</param>
  /// <paramname="strWhere">查询条件</param>
  /// <paramname="pageSize">每页显示条数</param>
  /// <paramname="pageIndex">页码</param>
  /// <paramname="orderName1">排序字段名</param>
  /// <paramname="orderType1">排序类型0升1降</param>
  /// <paramname="orderName2">排序字段名</param>
  /// <paramname="orderType2">排序类型0升1降</param>
  /// <paramname="orderName3">排序字段名</param>
  /// <paramname="orderType3">排序类型0升1降</param>
  /// <paramname="orderName4">排序字段名</param>
  /// <paramname="orderType4">排序类型0升1降</param>
  /// <paramname="orderName5">排序字段名</param>
  /// <paramname="orderType5">排序类型0升1降</param>
  /// <paramname="outRowCount">返回影响行数</param>
  /// <paramname="outPageCount">返回可以分的页数</param>
  ///<returns>返回查询条件集合</returns>
  public DataTableGetDataTableByPage(string tblName,string fldName,stringstrWhere,int pageSize,int pageIndex,string orderName1,intorderType1,string orderName2,int orderType2,string orderName3,intorderType3,string orderName4,int orderType4,string orderName5,intorderType5,out int outRowCount,out int outPageCount)
  {
   try
   {
    outRowCount=0;
    outPageCount=0;

    DataTableOutDataTable ;
    HashtableoutParameter;
    SqlParameter[]parameterList=new SqlParameter[17];

    parameterList[0]=new SqlParameter("@TblName",System.Data.SqlDbType.NVarChar,128);
    parameterList[0].Value=tblName;

    parameterList[1]=new SqlParameter("@FldName",System.Data.SqlDbType.NVarChar,1000);
    parameterList[1].Value=fldName;

    parameterList[2]=new SqlParameter("@strWhere", System.Data.SqlDbType.NVarChar,3000);
    parameterList[2].Value=strWhere;

    parameterList[3]=new SqlParameter("@PageSize",System.Data.SqlDbType.Decimal);
    parameterList[3].Value=pageSize;

    parameterList[4]=new SqlParameter("@PageIndex",System.Data.SqlDbType.Decimal);
    parameterList[4].Value=pageIndex;

    parameterList[5]=new SqlParameter("@OrderName1",System.Data.SqlDbType.NVarChar,128);
    parameterList[5].Value=orderName1;

    parameterList[6]=new SqlParameter("@OrderType1", System.Data.SqlDbType.Bit);
    parameterList[6].Value=orderType1;

    parameterList[7]=new SqlParameter("@OrderName2",System.Data.SqlDbType.NVarChar,128);
    parameterList[7].Value=orderName2;

    parameterList[8]=new SqlParameter("@OrderType2", System.Data.SqlDbType.Bit);
    parameterList[8].Value=orderType2;

    parameterList[9]=new SqlParameter("@OrderName3",System.Data.SqlDbType.NVarChar,128);
    parameterList[9].Value=orderName3;

    parameterList[10]=new SqlParameter("@OrderType3", System.Data.SqlDbType.Bit);
    parameterList[10].Value=orderType3;

    parameterList[11]=new SqlParameter("@OrderName4",System.Data.SqlDbType.NVarChar,128);
    parameterList[11].Value=orderName4;

    parameterList[12]=new SqlParameter("@OrderType4", System.Data.SqlDbType.Bit);
    parameterList[12].Value=orderType4;

    parameterList[13]=new SqlParameter("@OrderName5",System.Data.SqlDbType.NVarChar,128);
    parameterList[13].Value=orderName5;

    parameterList[14]=new SqlParameter("@OrderType5", System.Data.SqlDbType.Bit);
    parameterList[14].Value=orderType5;

    parameterList[15]=new SqlParameter("@RowCount",System.Data.SqlDbType.Decimal);
    parameterList[15].Direction=ParameterDirection.Output;
    parameterList[15].Value="0";

    parameterList[16]=new SqlParameter("@PageCount",System.Data.SqlDbType.Decimal);
    parameterList[16].Direction=ParameterDirection.Output;
    parameterList[16].Value="0";

    //执行SQL             
    this.ExecuteProcedure("Pz_Core_PageSelect",parameterList,outoutParameter,out OutDataTable);
           
    //获取返回值
    outRowCount=Convert.ToInt32(parameterList[15].Value);//返回总行数
    outPageCount=Convert.ToInt32(parameterList[16].Value);//返回总页数

    returnOutDataTable;
   }
   catch(Exceptionex)
   {
    throw(ex);
   }
  }
  #endregion
 }
}