网络版的数据库操作类

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 void OpenConnection()
  {
   CheckConnection();
  }
  /// <summary>
  /// 关闭连接
  /// </summary>
  private void CloseConnection()
  {
   if (conn.State == ConnectionState.Open)
   {
    conn.Close();
    conn.Dispose();
   }
  }  
  
  /// <summary>
  /// 检查连接，如果连接关闭，则打开
  /// </summary>
  /// <returns></returns>
  private bool CheckConnection()
  {
   if (conn.State == ConnectionState.Closed)
   {
    try
    {
     conn.ConnectionString = this.connString;
     conn.Open();
    }
    catch (Exception)
    {
     return false;
    }
   }
   return true;
  }

  #endregion

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

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

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

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

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

  /// <summary>
  /// 执行存储过程
  /// </summary>
  public object ExecuteProcedure(string procedureName,IDbDataParameter[] parameterList,out Hashtable outParameter,out DataTable outDataTable)
  {
   try
   {
    object result = new object();
    DataSet dsList = 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(int i = 0;i < parameterList.Length;i++)
     {
      command.Parameters.Add(parameterList[i]);
     }
    }

    SqlDataAdapter dataAdapter =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(int i = 0;i < parameterList.Length;i++)
     {
      if(parameterList[i].Direction == ParameterDirection.Output || parameterList[i].Direction == ParameterDirection.InputOutput)
      {
       outParameter.Add(parameterList[i].ParameterName,parameterList[i].Value);
      }
      else if(parameterList[i].Direction == ParameterDirection.ReturnValue)
      {
       result = parameterList[i].Value;
      }
     }
    }
    return result;
   }
   catch(Exception ex)
   {
    outParameter = new Hashtable();
    outDataTable = new DataTable();
    return null;
   }
   finally
   {
    CloseConnection();
   }
  }

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

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

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

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

    DataTable OutDataTable ;
    Hashtable outParameter;
    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,out outParameter,out OutDataTable);
           
    //获取返回值
    outRowCount=Convert.ToInt32(parameterList[15].Value); //返回总行数
    outPageCount=Convert.ToInt32(parameterList[16].Value); //返回总页数

    return OutDataTable;
   }
   catch(Exception ex)
   {
    throw(ex);
   }
  }
  #endregion
 }
}