HahaMil数据库(数据库操作组件)
来源:互联网 发布:阿里云的cdn怎么样 编辑:程序博客网 时间:2024/04/30 08:25
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(); } }}
0 0
- HahaMil数据库(数据库操作组件)
- .NET数据库操作简单化组件
- 数据库组件 Hxj.Data (三)(查询操作篇)
- 数据库组件 Hxj.Data (四)(添加操作篇)
- 数据库组件 Hxj.Data (六)(删除操作篇)
- 数据库组件 Hxj.Data (五)(更新操作篇)
- 使用组件封装数据库操作(一)
- 数据库操作组件的ATL实现
- DLL中添加ADO组件操作数据库
- SQLite数据库操作及ListView组件
- Android四大组件-SQLite数据库操作
- 数据库(二)操作数据库
- 数据库操作(不断更新)
- 数据库操作(代码)
- 数据库原理 数据库组件概况
- VB编写组件封装ASP的数据库操作
- 利用Jakarta Commons组件beanutils、dbutils简化JDBC数据库操作
- 利用Jakarta Commons组件beanutils、dbutils简化JDBC数据库操作
- Newtonsoft.Json.4.5.11使用方法总结---反序列化json字符串
- 从简单实例开始,学会写Makefile(二)
- java的selector用法
- 求二叉树的任意两个节点的最近公共祖先
- 【LeetCode】-Remove Duplicates from Sorted List
- HahaMil数据库(数据库操作组件)
- Longest Common Prefix
- 查找优化
- Android中文API(122) —— AudioRecord
- hdu 4569 TSP
- linux进程间通信-信号通信
- AudioRecord
- Oracel 常用函数
- 极客Web前端开发资源集锦