HahaMil数据库（数据库操作组件）

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();
        }
    }
}