7.2数据库操作通用类DBHelper

7.2　数据库操作通用类DBHelper

学习目标

掌握数据库通用类DBHelper

这里就以ASP.NET操作SQL Server数据库为例来分析通用类DBHelper实现方法。其实程序语言操作数据库通常要实现的操作无非就是增、删、改、查这些操作。

7.2.1　建立数据库连接GetConn

程序语言要操作数据库中的数据信息，首先要建立一个程序语言与数据库之间的连接通道，在ADO.NET操作SQL Server中用的就是SqlConnection这个类来完成数据库连接通道的创建，核心用的就是SqlConnection的ConnectionString属性建立的连接字符串。由于每次数据库的操作都和ConnectionString关联，所以可以把这个连接字符串封装到一个方法中，不妨命名为GetConn，该方法源代码如下。

public SqlConnection GetConn()
{
  SqlConnection conn = new SqlConnection(@"server=hst;uid=sa;
  pwd=sa;database=bjtvcom_db");
  return conn;
}

7.2.2　标准SQL语句操作的方法

1．读操作（SELECT）

读取数据表操作，也就是SELECT语句，可能是普通SQL SELECT语句，例如：

Select id,name,sex,city from student order by id asc;
Select id,name,sex,city from student where sex='男' and city=
'北京' order by id asc;

也可能是带有参数的SQL SELECT语句，例如：

Select id,name,sex,city from student where sex=@sex and city=@city
order by id asc;

考虑到SQL注入的问题，最好能用参数化SQL语句来完成上述操作。这样一来，读操作（SELECT）就分两种情形，一种是标准SQL语句，一种是参数化SQL语句，当然就得写两个方法了，读取的结果刚好符合.NET中System.Data.DataTable的结构，故两个方法返回值类型都是System.Data.DataTable类型，又考虑到#语言OOP支持方法重载，故这里的两个方法可以使用相同的名字，而源代码可以写出两种形式即可。

接下来给出这两个方法的源代码。

//不带参数的SQL语句执行读取操作方法
public DataTable GetTable(string sql)
{
    SqlConnection conn = this.GetConn();
    SqlDataAdapter sda = new SqlDataAdapter(sql, conn);
    DataTable table = new DataTable();
    sda.Fill(table);
    return table;
}
//带有参数的SQL语句执行读取操作方法，这里使用Hashtable来传递参数key/
value对
public DataTable GetTable(string sql, Hashtable ht)
{
    SqlConnection conn = this.GetConn();
    SqlDataAdapter sda = new SqlDataAdapter(sql, conn); foreach (DictionaryEntry de in ht)
    {
       sda.SelectCommand.Parameters.AddWithValue(de.Key.ToString(), de.Value.ToString());
    }
    DataTable table = new DataTable();
    sda.Fill(table);
    return table;
}

2．维护操作（INSERT、DELETE、UPDATE）

维护操作当然也是分两种情形，一种是标准SQL语句完成维护操作，一种是参数化的SQL语句完成维护操作。

接下来给出这两个方法的源代码。

//标准SQL语句执行维护操作的方法
public int Execute(string sql)
{
    SqlConnection conn = this.GetConn();
    conn.Open();
    SqlCommand comm = new SqlCommand(sql, conn);
    int c = comm.ExecuteNonQuery();
    conn.Close();
    return c;
}
//参数化SQL语句执行维护操作的方法，这里使用Hashtable来传递参数key/value对
public int Execute(string sql, Hashtable ht)
{
    SqlConnection conn = this.GetConn();
    conn.Open();
    SqlCommand comm = new SqlCommand(sql, conn);
    foreach (DictionaryEntry de in ht)
    {
       comm.Parameters.AddWithValue(de.Key.ToString(), de.Value.ToString());
    }
    int c = comm.ExecuteNonQuery();
    conn.Close();
    return c;
  }

7.2.3　使用存储过程操作的方法

1．读操作（SELECT）

存储过程实现读取操作，最终返回的依然是一个table，只是程序发给db的指令类型发生了变化，故代码格式和上述SQL语句操作类似，当然同样也存在有参数的情形、没有参数的情形，也就是说有两个方法。

接下来给出这两个方法的源代码。

  //不带参数的存储过程调用的方法
  public DataTable GetTableByProc(string procName)
  {
    SqlConnection conn = this.GetConn();
    SqlDataAdapter sda = new SqlDataAdapter(procName, conn);
    sda.SelectCommand.CommandType = CommandType.StoredProcedure;
    DataTable table = new DataTable();
    sda.Fill(table);
    return table;
  }
  //带参数的存储过程调用的方法
  public DataTable GetTableByProc(string procName, Hashtable ht)
  {
    SqlConnection conn = this.GetConn();
    SqlDataAdapter sda = new SqlDataAdapter(procName, conn);
    sda.SelectCommand.CommandType = CommandType.StoredProcedure;
    foreach (DictionaryEntry de in ht)
    {
       sda.SelectCommand.Parameters.AddWithValue(de.Key.
       ToString(), de.Value.ToString());
    }
    DataTable table = new DataTable();
    sda.Fill(table);
    return table;
}

2．维护操作（INSERT、DELETE、UPDATE）

维护操作当然也分两种情形，一种是没有参数的存储过程完成的维护操作，一种是带有参数的存储过程完成的维护操作。

接下来给出这两个方法的源代码。

  //没有参数的存储过程完成的维护操作
  public int ExecuteProc(string procName)
  {
    SqlConnection conn = this.GetConn();
    conn.Open();
    SqlCommand comm = new SqlCommand(procName, conn);
    comm.CommandType = CommandType.StoredProcedure;
    int c = comm.ExecuteNonQuery();
    conn.Close();
    return c;
  }
  //带有参数的存储过程完成的维护操作
  public int ExecuteProc(string procName, Hashtable ht)
  {
    SqlConnection conn = this.GetConn();
    conn.Open();
    SqlCommand comm = new SqlCommand(procName, conn);
    comm.CommandType = CommandType.StoredProcedure;
    foreach (DictionaryEntry de in ht)
    {
       comm.Parameters.AddWithValue(de.Key.ToString(), de.Value.
       ToString());
    }
    int c = comm.ExecuteNonQuery();
    conn.Close();
    return c;
  }

7.2.4　完整DBHelper源代码

通过以上的操作功能分析，接下来给出完整DBHelper源代码，代码中本身已经编写了部分注释，考虑到篇幅问题，删除了部分注释。

using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Collections;

//操作SQL Server数据库的通用类
public class DBHelper
{
  //生成连接字符串
  public SqlConnection GetConn()
  {
    SqlConnection conn = new SqlConnection(@"server=ALISION;
    uid=sa;pwd=sa;database=db");
    return conn;
  }
  //根据SELECT SQL语句，没有参数，返回一个DataTable
  public DataTable GetTable(string sql)
  {
    SqlConnection conn = this.GetConn();
    SqlDataAdapter sda = new SqlDataAdapter(sql, conn);
    DataTable table = new DataTable();
    sda.Fill(table);
    return table;
  }
  //根据SELECT SQL语句，有参数，返回一个DataTable
  public DataTable GetTable(string sql, Hashtable ht)
  {
    SqlConnection conn = this.GetConn();
    SqlDataAdapter sda = new SqlDataAdapter(sql, conn);
     foreach (DictionaryEntry de in ht)
    {
       sda.SelectCommand.Parameters.AddWithValue(de.Key.
       ToString(), de.Value.ToString());
    }
    DataTable table = new DataTable();
    sda.Fill(table);
    return table;
  }
  //根据SELECT SQL语句，没有参数，返回一个DataRow
  public DataRow GetRow(string sql)
  {
    DataRow row;
    if (this.GetTable(sql).Rows.Count >= 1)
    {
       row = this.GetTable(sql).Rows[0];
    }
    else
    {
       row = null;
    }
    return row;
  }
  //根据SELECT SQL语句，有参数，返回一个DataRow
  public DataRow GetRow(string sql, Hashtable ht)
  {
    DataRow row;
    if (this.GetTable(sql, ht).Rows.Count >= 1)
    {
       row = this.GetTable(sql, ht).Rows[0];
    }
    else
    {
       row = null;
    }
    return row;
  }
  //根据SELECT SQL语句，没有参数，返回首行首列的值
  public string GetValue(string sql)
  {
    string str = "";
    if (this.GetRow(sql) != null)
    {
       str = this.GetRow(sql)[0].ToString();
    }
    return str;
  }
  //根据SELECT SQL语句，有参数，返回首行首列的值
  public string GetValue(string sql, Hashtable ht)
  {
    string str = "";
    if (this.GetRow(sql, ht) != null)
    {
       str = this.GetRow(sql, ht)[0].ToString();
    }
    return str;
  }
  //根据存储过程，没有参数，返回一个DataTable
  public DataTable GetTableByProc(string procName)
  {
    SqlConnection conn = this.GetConn();
    SqlDataAdapter sda = new SqlDataAdapter(procName, conn);
    sda.SelectCommand.CommandType = CommandType.StoredProcedure;
    DataTable table = new DataTable();
    sda.Fill(table);
    return table;
  }
  //根据存储过程，有参数，返回一个DataTable
  public DataTable GetTableByProc(string procName, Hashtable ht)
  {
    SqlConnection conn = this.GetConn();
    SqlDataAdapter sda = new SqlDataAdapter(procName, conn);
    sda.SelectCommand.CommandType = CommandType.StoredProcedure;
    foreach (DictionaryEntry de in ht)
    {
       sda.SelectCommand.Parameters.AddWithValue(de.Key.
       ToString(), de.Value.ToString());
    }
    DataTable table = new DataTable();
    sda.Fill(table);
    return table;
  }
  //根据存储过程，没有参数，返回一个DataRow
  public DataRow GetRowByProc(string procName)
  {
    DataRow row;
    if (this.GetTableByProc(procName).Rows.Count >= 1)
    {
       row = this.GetTableByProc(procName).Rows[0];
    }
    else
    {
       row = null;
    }
    return row;
  }
  //根据存储过程，有参数，返回一个DaTarow
  public DataRow GetRowByProc(string procName, Hashtable ht)
  {
    DataRow row;
    if (this.GetTableByProc(procName, ht).Rows.Count >= 1)
    {
       row = this.GetTableByProc(procName, ht).Rows[0];
    }
    else
    {
       row = null;
    }
    return row;
  }
  //根据存储过程，没有参数，返回首行首列的值
  public string GetValueByProc(string procName)
  {
    string str = "";
    if (this.GetRowByProc(procName) != null)
    {
       str = this.GetRowByProc(procName)[0].ToString();
    }
    return str;
  }
  //根据存储过程，有参数，返回首行首列的值
  public string GetValueByProc(string procName, Hashtable ht)
  {
    string str = "";
    if (this.GetRowByProc(procName, ht) != null)
    {
          str = this.GetRowByProc(procName, ht)[0].
ToString();
    }
    return str;
  }
  //普通SQL语句，执行维护操作，没有参数
  public int Execute(string sql)
  {
    SqlConnection conn = this.GetConn();
    conn.Open();
    SqlCommand comm = new SqlCommand(sql, conn);
    int c = comm.ExecuteNonQuery();
    conn.Close();
    return c;
  }
  //普通SQL语句，执行维护操作，有参数
  public int Execute(string sql, Hashtable ht)
  {
    SqlConnection conn = this.GetConn();
    conn.Open();
    SqlCommand comm = new SqlCommand(sql, conn);
    foreach (DictionaryEntry de in ht)
    {
       comm.Parameters.AddWithValue(de.Key.ToString(),
       de.Value.ToString());
    }
    int c = comm.ExecuteNonQuery();
    conn.Close();
    return c;
  }
  //存储过程，执行维护操作，没有参数
  public int ExecuteProc(string procName)
  {
    SqlConnection conn = this.GetConn();
    conn.Open();
    SqlCommand comm = new SqlCommand(procName, conn);
    comm.CommandType = CommandType.StoredProcedure;
    int c = comm.ExecuteNonQuery();
    conn.Close();
    return c;
  }
  //存储过程，执行维护操作，有参数
  public int ExecuteProc(string procName, Hashtable ht)
  {
    SqlConnection conn = this.GetConn();
    conn.Open();
    SqlCommand comm = new SqlCommand(procName, conn);
    comm.CommandType = CommandType.StoredProcedure;
    foreach (DictionaryEntry de in ht)
    {
       comm.Parameters.AddWithValue(de.Key.ToString(), de.Value.ToString());
    }
    int c = comm.ExecuteNonQuery();
    conn.Close();
    return c;
  }
}

完整代码可以参照/Code/ch07/DBHelper.cs。