.NET C# 访问数据库的工具类
为了操作数据库方便，写了这样的两个工具类。 这样只要简单的继承一个类就可以实现基本的数据库操作，而不用每次都写完整的SQL语句。也许里面会存在很多问题，但是学习之用还是有所帮助的。
数据库连接类：
/** * 作者：张书振 * 日期：2012-07-02 * 描述：数据库通用连接辅助类 */using System;using System.Data.Common;using System.Data;namespace Org.JianQiao.Db{ /// <summary> /// 数据库通用连接辅助类 /// </summary> public class DbUtil { public const string PROVIDER_NAME = "System.Data.SqlClient"; public const string CONNECTION_STRING = "Data Source=(local)\\JIANQIAO; Initial Catalog=School;Integrated Security=True"; /// <summary> /// 获得特定数据库的连接对象 /// </summary> /// <returns>成功返回连接对象，否则返回null</returns> public static DbConnection getDbConnection() { DbConnection connection = null; if (CONNECTION_STRING != null) { try { DbProviderFactory factory = DbProviderFactories.GetFactory(PROVIDER_NAME); connection = factory.CreateConnection(); connection.ConnectionString = CONNECTION_STRING; } catch (Exception ex) { // 如果已经创建连接，将连接引用设为null if (connection != null) { connection = null; } Console.WriteLine(ex.Message); } } return connection; } /// <summary> /// 获得特定数据库的特定工厂 /// </summary> /// <returns></returns> public static DbProviderFactory getDbProviderFactory() { DbProviderFactory factory = DbProviderFactories.GetFactory(PROVIDER_NAME); return factory; } /// <summary> /// 通过执行SQL语句返回DataTable对象 /// </summary> /// <param name="querySQL"></param> /// <returns></returns> public static DataTable executeQuery(String querySQL) { DataTable resultSet = new DataTable(); DbProviderFactory factory = DbUtil.getDbProviderFactory(); using (DbConnection conn = factory.CreateConnection()) { conn.ConnectionString = CONNECTION_STRING; conn.Open(); DbCommand command = factory.CreateCommand(); command.CommandText = querySQL; command.Connection = conn; using (DbDataReader reader = command.ExecuteReader()) { resultSet.Load(reader); } } return resultSet; } /// <summary> /// 执行统计等聚合函数的查询，只返回一个值 /// </summary> /// <param name="querySQL"></param> /// <returns></returns> public static int executeScalar(String querySQL) { DbProviderFactory factory = DbUtil.getDbProviderFactory(); using (DbConnection conn = factory.CreateConnection()) { conn.ConnectionString = CONNECTION_STRING; conn.Open(); DbCommand command = factory.CreateCommand(); command.Connection = conn; command.CommandText = querySQL; return Convert.ToInt32(command.ExecuteScalar()); } } /// <summary> /// 执行更新、删除或插入操作，返回影响的行数 /// </summary> /// <param name="updateSQL"></param> /// <returns></returns> public static int executeNonQuery(String updateSQL) { DbProviderFactory factory = DbUtil.getDbProviderFactory(); using (DbConnection conn = factory.CreateConnection()) { conn.ConnectionString = CONNECTION_STRING; conn.Open(); DbCommand command = factory.CreateCommand(); command.Connection = conn; command.CommandText = updateSQL; return Convert.ToInt32(command.ExecuteNonQuery()); } } }}通用模板类，执行通用的增删改查方法：
using System;using System.Data;using System.Collections;namespace Org.JianQiao.Db{ public class DaoTemplate { protected string tableName; public DaoTemplate() { } /// <summary> /// 通过指定的ID删除一条记录 /// </summary> /// <param name="id">记录ID</param> /// <returns>删除成功返回true</returns> public bool delete(string id) { String deleteSQL = "delete from " + this.tableName + " where id='" + id + "'"; int affectedRows = DbUtil.executeNonQuery(deleteSQL); return affectedRows == 1 ? true : false; } public bool update(Hashtable row) { String updateSQL = "update " + tableName + " set "; bool isFirst = true; foreach (String key in row.Keys) { if (key == "id") { continue; } if (isFirst) { updateSQL += key + "='" + row[key] + "'"; isFirst = false; } else { updateSQL += "," + key + "='" + row[key] + "'"; } } updateSQL += " where id=" + row["id"]; int affectedRows = DbUtil.executeNonQuery(updateSQL); return affectedRows == 1 ? true : false; } public int insert(Hashtable row) { String insertSQL = "insert into " + tableName; insertSQL += "("; bool isFirst = true; foreach (String key in row.Keys) { if (key == "id") { continue; } if (isFirst) { insertSQL += key; isFirst = false; } else { insertSQL += "," + key; } } insertSQL += ") values("; isFirst = true; foreach (String key in row.Keys) { if (key == "id") { continue; } if (isFirst) { insertSQL += "'" + row[key]; isFirst = false; } else { insertSQL += "','" + row[key]; } } insertSQL += "')"; int affectedRows = DbUtil.executeNonQuery(insertSQL); String idSQL = "select max(id) from " + this.tableName; int insertedId = DbUtil.executeScalar(idSQL); return insertedId; } /// <summary> /// 通过指定的条件获得数据 /// </summary> /// <param name="whereSQL"></param> /// <returns></returns> public DataTable fetchAllWithWhere(String whereSQL) { String querySQL = "select * from " + this.tableName + " where " + whereSQL; return DbUtil.executeQuery(querySQL); } /// <summary> /// 返回表中所有的记录 /// </summary> /// <returns>DataTable类型的记录</returns> public DataTable fetchAll() { String querySQL = "select * from " + this.tableName; return DbUtil.executeQuery(querySQL); } public Hashtable fetchOne(String id) { DataTable table = fetchAllWithWhere("id='" + id + "'"); Hashtable result = null; if (table.Rows.Count > 0) { result = new Hashtable(); DataRow row = table.Rows[0]; foreach (DataColumn column in table.Columns) { result.Add(column.ToString(), row[column]); } } return result; } }}下面是一个具体的DAO类，只要继承自DaoTemplate类，并指定表名即可使用：
using System;using System.Collections.Generic;using System.Linq;using System.Text;using Org.JianQiao.Db;namespace DatabaseLib.Dao{ class StudentDAO : DaoTemplate { public StudentDAO() { base.tableName = "Student"; } }}下面是测试方法：
using System;using System.Linq;using System.Text;using Org.JianQiao.Db;using DatabaseLib.Dao;using System.Data;using System.Collections;namespace DatabaseLib{ public class Program { public static void Main(string[] args) { StudentDAO s = new StudentDAO(); Hashtable h = new Hashtable(); //h.Add("id", "2"); h.Add("sno", "S002"); h.Add("sname", "好吧"); Console.WriteLine(s.insert(h));//插入一条数据并返回插入ID Console.ReadKey(); } }}
TAG:
