HahaMil数据库是一个基于·NET Framework的数据库操作组件，类似于微软的SqlHelper类，但更适合实际应用。
HahaMil数据库中包含数据库操作方法，HahaMil数据库用于简化程序编码中需要重复的写SqlConnection、SqlCommand、SqlDataReader等等，封装过后通常是只需要传入一些参数如数据库信息，SQL语句、绑定变量、参数等，就可以执行数据库操作。
HahaMil数据库目前支持SQL Server和Oracle，未来将支持更多数据库。
DLL下载地址：http://www.hi1988.com/hahamil/HahaMil.Data.rar
HahaMil数据库代码附在后面，希望大家一起指出其中的错误和需要优化改进的地方，不胜感激。
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data; namespace HahaMil.Data { /// <summary> /// HahaMil 数据库接口 /// </summary> public interface IHahaDatabase { string getDBType(); void BeginTransaction(); void Commit(); DataTable ExecSelect(string sql); int ExecSQL(string sql); int ExecSQL(string sql, params object[] values); } }
namespace HahaMil.Data { /// <summary> /// HahaMil SQLServer数据库 /// </summary> public class HahaSQLServerDatabase : IHahaDatabase { private string strSQLServerConnection = "server={0};database={1};uid={2};pwd={3}";//连接字符串 weixj 2014-09-18 private SqlConnection conn; private SqlCommand comm; private SqlTransaction tran; /// <summary> /// 构造函数（设置数据库连接信息） /// </summary> /// <param name="server">服务器</param> /// <param name="database">数据库</param> /// <param name="uid">用户</param> /// <param name="pwd">密码</param> public HahaSQLServerDatabase(string server, string database, string uid, string pwd) { strSQLServerConnection = string.Format(strSQLServerConnection, server, database, uid, pwd);//根据用户设置生成连接字符串 weixj 2014-09-18 conn = new SqlConnection(strSQLServerConnection); comm = new SqlCommand(); comm.Connection = conn; } /// <summary> /// 获取数据库类型 /// </summary> /// <returns>数据库类型</returns> public string getDBType() { return "SQLServer"; } /// <summary> /// 开始事务 /// </summary> public void BeginTransaction() { if (conn.State != ConnectionState.Open) { conn.Open(); } tran = conn.BeginTransaction(); } /// <summary> /// 提交事务 /// </summary> public void Commit() { try { tran.Commit(); } catch (Exception ex) { tran.Rollback(); throw new Exception("SQL执行失败，原因：" + ex.Message); } finally { if (conn.State != ConnectionState.Closed) { conn.Close(); } } } /// <summary> /// 查询 /// </summary> /// <param name="sql">查询SQL</param> /// <returns>查询结果DataTable</returns> public DataTable ExecSelect(string sql) { DataTable dt = new DataTable(); if (conn.State != ConnectionState.Open) { conn.Open(); } comm.CommandText = sql; comm.Transaction = tran; SqlDataAdapter adapter = new SqlDataAdapter(comm); adapter.Fill(dt); return dt; } /// <summary> /// 执行SQL /// </summary> /// <param name="sql">sql语句</param> /// <returns>执行后影响的行数</returns> public int ExecSQL(string sql) { if (conn.State != ConnectionState.Open)//因BeginTransction时打开连接，所以在此判断 weixj 2014-09-23 { conn.Open(); } comm.CommandText = sql; comm.Transaction = tran; int rows; rows = comm.ExecuteNonQuery(); return rows; } /// <summary> /// 执行SQL /// </summary> /// <param name="sql">sql语句（绑定变量在sql语句中使用{0}、{1}、{2}、……、{n}表示）</param> /// <param name="values">绑定变量</param> /// <returns>执行后影响的行数</returns> public int ExecSQL(string sql, params object[] values) { for (int i = 0; i < values.Length; i++) { sql = sql.Replace("{" + i.ToString() + "}", "@para" + i.ToString());//+拼接代价较高，应考虑使用效率更高的方法 weixj 2014-09-23 } if (conn.State != ConnectionState.Open) { conn.Open(); } comm.CommandText = sql; comm.Parameters.Clear(); comm.Transaction = tran; for (int i = 0; i < values.Length; i++) { comm.Parameters.Add("@para" + i.ToString(), values[i]); } int rows; rows = comm.ExecuteNonQuery(); return rows; } } }
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data; using System.Data.OracleClient; using System.Data.OleDb; namespace HahaMil.Data { /// <summary> /// HahaMil Oracle数据库 /// </summary> public class HahaOracleDatabase : IHahaDatabase { private string strOracleServerConnection = "Provider=OraOLEDB.Oracle.1;User ID={2};Password={3};Data Source=(DESCRIPTION = (ADDRESS_LIST= (ADDRESS = (PROTOCOL = TCP)(HOST = {0})(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = {1})))"; OleDbConnection conn; OleDbCommand comm; OleDbTransaction tran; /// <summary> /// 构造函数（设置数据库连接信息） /// </summary> /// <param name="server">服务器</param> /// <param name="database">数据库</param> /// <param name="uid">用户</param> /// <param name="pwd">密码</param> public HahaOracleDatabase(string server, string database, string uid, string pwd) { strOracleServerConnection = string.Format(strOracleServerConnection, server, database, uid, pwd); conn = new OleDbConnection(strOracleServerConnection); comm = new OleDbCommand(); comm.Connection = conn; } /// <summary> /// 获取数据库类型 /// </summary> /// <returns>数据库类型</returns> public string getDBType() { return "Oracle"; } /// <summary> /// 开始事务 /// </summary> public void BeginTransaction() { if (conn.State != ConnectionState.Open) { conn.Open(); } tran = conn.BeginTransaction(); } /// <summary> /// 提交事务 /// </summary> public void Commit() { try { tran.Commit(); } catch (Exception ex) { tran.Rollback();//在提交时设置发生错误回滚数据，程序中不必再单独写回滚语句。 weixj 2014-09-23 throw new Exception("SQL执行失败，原因：" + ex.Message); } finally { if (conn.State != ConnectionState.Closed) { conn.Close(); } } } /// <summary> /// 查询 /// </summary> /// <param name="sql">查询SQL</param> /// <returns>查询结果DataTable</returns> public DataTable ExecSelect(string sql) { if (conn.State != ConnectionState.Open) { conn.Open(); } DataTable dt = new DataTable(); comm.Transaction = tran; comm.CommandText = sql; OleDbDataAdapter adapter = new OleDbDataAdapter(comm); adapter.Fill(dt); return dt; } /// <summary> /// 执行SQL /// </summary> /// <param name="sql">sql语句</param> /// <returns>执行后影响的行数</returns> public int ExecSQL(string sql) { if (conn.State != ConnectionState.Open) { conn.Open(); } comm.Transaction = tran; comm.CommandText = sql; int rows; rows = comm.ExecuteNonQuery(); return rows; } /// <summary> /// 执行SQL /// </summary> /// <param name="sql">sql语句（绑定变量在sql语句中使用{0}、{1}、{2}、……、{n}表示）</param> /// <param name="values">绑定变量</param> /// <returns>执行后影响的行数</returns> public int ExecSQL(string sql, params object[] values) { for (int i = 0; i < values.Length; i++) { sql = sql.Replace("{" + i.ToString() + "}", ":para" + i.ToString()); } if (conn.State != ConnectionState.Open) { conn.Open(); } comm.Transaction = tran; comm.CommandText = sql; comm.Parameters.Clear(); for (int i = 0; i < values.Length; i++) { comm.Parameters.Add("para" + i.ToString(), values[i]); } int rows; string s = comm.CommandText; rows = comm.ExecuteNonQuery(); return rows; } } }
以下是测试类：
using System; using System.Collections.Generic; using System.Data; using System.Linq; using System.Text; using HahaMil.Data;//使用HahaMil数据库时引入 namespace HahaTest { class HahaDBTest { public static void Main(String[] args) { string server = "(local)"; string database = "MyTest"; string uid = "hahamil"; string pwd = "ha_public"; //创建数据库 IHahaDatabase db = new HahaSQLServerDatabase(server, database, uid, pwd); IHahaDatabase db_oracle = new HahaOracleDatabase(server, database, uid, pwd); //以下以SQLServer数据库为例测试，Oracle数据库使用方法一样。 //测试执行SQL db.ExecSQL("delete from Mytest1 where name like ‘test%‘"); for (int i = 0; i < 100; i++) { //测试执行带有绑定变量的SQL db.ExecSQL("insert into Mytest1 (ID,Name,CreatedTime) values({0},{1},{2})", System.Guid.NewGuid(), "test" + i.ToString(), DateTime.Now); } //测试执行查询 DataTable dt = db.ExecSelect("select * from Mytest1"); if (dt.Rows.Count > 0) { foreach (DataRow row in dt.Rows) { Console.Write(row["ID"].ToString()); Console.Write("\t"); Console.Write(row["Name"].ToString()); Console.Write("\t"); Console.Write(row["CreatedTime"].ToString()); Console.Write("\n"); } } //测试DB事务 db.BeginTransaction(); db.ExecSQL("delete from Mytest1 where name like ‘test%‘"); db.ExecSQL("insert into Mytest1 (ID,Name,CreatedTime) values({0},{1},{2})", System.Guid.NewGuid(), "testTransction", DateTime.Now); db.Commit(); //end Console.ReadLine(); } } }