突然想起之前有封装过C#连接mySql的一些类,发出来和大家交流一下~
小小借鉴了SSH的设计模式,强大的SSH太值得学习了。
封装了四个类:
1.ValueObejct(和我另一篇文章中的值对象是一个意思,但这个里的做的好一些,文章地址)
2.DBHelper(数据库连接辅助类,这块写的不是太好,如果想换成ORACLE或者其他数据库要重写这个类)
3.CommDao(数据库访问对象,这个类将与数据库做交互)
4.EntityHelper(实体类辅助类)
还用到了一个Log4cs类,这个里面我什么都没写,就用console输出了一下,也可以将其他日志实现类接进去。
项目中需要引用mysql在.net下的connector。下载地址MySQL :: Download Connector/NET
好了,上代码。
ValueObject:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Reflection;
using Amer.ManageService.Util;
using Amer.ManageService.Entity;
namespace Amer.ManageService.DAO
{
/// <summary>
/// 实体类容器
/// </summary>
public class ValueObject
{
/// <summary>
/// 实体名称
/// </summary>
private string entityName;
private Dictionary<string, string> map = new Dictionary<string, string>();
/// <summary>
/// 根据实体构造
/// </summary>
/// <param name="entityName"></param>
public ValueObject(string entity)
{
entityName = entity;
this.map = EntityHelper.getEntityMap(entityName);
}
/// <summary>
/// 根据key得到string
/// </summary>
/// <param name="key"></param>
/// <returns></returns>
public string getString(string key)
{
string tem = null;
try
{
map.TryGetValue(key, out tem);
}
catch (Exception e)
{
Log4cs.OutputLog(this.ToString() + "->getstring:", e.Message);
}
return tem;
}
/// <summary>
/// 指定key设置value
/// </summary>
/// <param name="key"></param>
/// <param name="value"></param>
public void setString(string key, string value)
{
try
{
map[key] = value;
}
catch (Exception e)
{
Log4cs.OutputLog(this.ToString() + "->setstring:", e.Message);
}
}
/// <summary>
/// 返回实体名称
/// </summary>
/// <returns></returns>
public string getEntityName(){
return this.entityName;
}
/// <summary>
/// 返回vo的map
/// </summary>
/// <returns></returns>
public Dictionary<string, string> getMap()
{
return this.map;
}
}
}
DBHelper:
using System;
using MySql.Data.MySqlClient;
using Amer.ManageService.Util;
namespace Amer.ManageService.DAO
{
/// <summary>
/// DBHelper
/// 提供连接mysql,执行sql的方法
/// </summary>
public static class DBHelper
{
static MySqlConnection mySqlConnection = null;
static MySqlCommand mySqlCommand = null;
/// <summary>
/// 初始化
/// </summary>
/// <returns>初始化成功/失败</returns>
public static Boolean Initialization()
{
try
{
mySqlConnection = getMySqlCon(getConnectStr());
mySqlCommand = new MySqlCommand();
mySqlConnection.Open();
mySqlCommand.Connection = mySqlConnection;
}
catch(Exception e)
{
Log4cs.OutputLog("DBHelper", e.Message);
return false;
}
return true;
}
/// <summary>
/// 得到连接数据库字符串
/// </summary>
/// <returns>mysql连接字符串</returns>
private static string getConnectStr()
{
string connectStr = "Database=manage;";
connectStr += "Data Source=127.0.0.1;";
connectStr += "User Id=root;";
connectStr += "Password=xxxxxx;";
connectStr += "pooling=false;";
connectStr += "CharSet=utf8;";
connectStr += "port=3306";
return connectStr;
}
/// <summary>
/// 建立mysql数据库链接
/// </summary>
/// <param name="conStr">mysql连接字符串</param>
/// <returns>mysql连接</returns>
private static MySqlConnection getMySqlCon(string conStr)
{
return new MySqlConnection(conStr);
}
/// <summary>
/// 得到执行命令语句对象
/// </summary>
/// <returns>sql语句执行对象</returns>
public static MySqlCommand getSqlCommand()
{
return mySqlCommand;
}
}
}
CommDao:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Amer.ManageService.Util;
using Amer.ManageService.Entity;
using MySql.Data.MySqlClient;
namespace Amer.ManageService.DAO
{
/// <summary>
/// 增删查改
/// </summary>
public static class CommDao
{
/// <summary>
/// 增加数据
/// </summary>
/// <param name="vo"></param>
public static void insert(ValueObject vo)
{
try
{
var cmd = getInsertCommand(vo);
cmd.ExecuteNonQuery();
Log4cs.OutputLog("Excute Sql", cmd.CommandText);
}
catch (Exception e)
{
Log4cs.OutputLog("CommDao->insert", e.Message);
}
}
/// <summary>
/// 更新数据
/// </summary>
/// <param name="vo"></param>
public static void update(ValueObject vo)
{
try
{
if (vo.getString("id") == null)
throw new Exception("id不存在!");
var cmd = getUpdateCommand(vo);
cmd.ExecuteNonQuery();
Log4cs.OutputLog("Excute Sql", cmd.CommandText);
}
catch(Exception e)
{
Log4cs.OutputLog("CommDao->update", e.Message);
}
}
/// <summary>
/// 删除数据
/// </summary>
/// <param name="vo"></param>
public static void delete(ValueObject vo)
{
try
{
if (vo.getString("id") == null)
throw new Exception("id不存在!");
var cmd = getDeleteCommand(vo);
cmd.ExecuteNonQuery();
Log4cs.OutputLog("Excute Sql", cmd.CommandText);
}
catch (Exception e)
{
Log4cs.OutputLog("CommDao->delete", e.Message);
}
}
/// <summary>
/// 查询数据返回list
/// </summary>
/// <param name="vo">param的entity类型的vo的list</param>
/// <returns></returns>
public static List<ValueObject> find(ValueObject param, string entityExtName,string method)
{
List<ValueObject> list = new List<ValueObject>();
string sql = EntityHelper.getEntityExtSql(entityExtName, method, "query");
try
{
var cmd = getFindListCommand(sql, param);
Log4cs.OutputLog("Excute Sql", cmd.CommandText);
var reader = cmd.ExecuteReader();
while (reader.Read())
{
ValueObject temVo = new ValueObject(param.getEntityName());
for (int i = 0; i < reader.FieldCount; i++)
{
temVo.setString(reader.GetName(i), reader[reader.GetName(i)].ToString());
}
list.Add(temVo);
}
}
catch (Exception e)
{
Log4cs.OutputLog("CommDao->find", e.Message);
}
return list;
}
/// <summary>
/// 根据id查询数据返回vo
/// </summary>
/// <param name="vo"></param>
/// <returns></returns>
public static ValueObject findById(ValueObject vo)
{
try
{
if(vo.getString("id") == null)
throw new Exception("id不存在!");
var cmd = getFindByIdCommand(vo);
var reader = cmd.ExecuteReader();
Log4cs.OutputLog("Excute Sql", cmd.CommandText);
if (!reader.HasRows)
throw new Exception("没有这条记录!");
if (reader.Read())
{
var map = vo.getMap();
for (int i = 0; i < reader.FieldCount; i++)
{
vo.setString(reader.GetName(i), reader[reader.GetName(i)].ToString());
}
}
}
catch (Exception e)
{
Log4cs.OutputLog("CommDao->findById", e.Message);
}
return vo;
}
/// <summary>
/// 得到插入sql执行对象
/// </summary>
/// <param name="vo">需要插入vo对象</param>
/// <returns></returns>
private static MySqlCommand getInsertCommand(ValueObject vo)
{
MySqlCommand cmd = DBHelper.getSqlCommand();
StringBuilder insert = new StringBuilder("INSERT INTO ");
StringBuilder values = new StringBuilder("VALUES(");
insert.Append(vo.getEntityName()).Append("(");
var map = vo.getMap();
foreach (var item in map)
{
if (item.Key == "id" || item.Value == null) continue;
insert.Append(item.Key).Append(",");
values.Append("@").Append(item.Key).Append(",");
}
insert.Remove(insert.Length - 1, 1);
insert.Append(") ");
values.Remove(values.Length - 1, 1);
values.Append(") ");
insert.Append(values);
cmd.CommandText = insert.ToString();
cmd.Prepare();
foreach (var item in map)
{
if (item.Key == "id" || item.Value == null) continue;
cmd.Parameters.AddWithValue("@"+item.Key, item.Value);
}
return cmd;
}
/// <summary>
/// 得到更新sql执行对象
/// </summary>
/// <param name="vo">需要更新赋有id的vo对象</param>
/// <returns></returns>
private static MySqlCommand getUpdateCommand(ValueObject vo)
{
MySqlCommand cmd = DBHelper.getSqlCommand();
StringBuilder update = new StringBuilder("UPDATE ");
StringBuilder where = new StringBuilder(" WHERE ");
update.Append(vo.getEntityName()).Append(" SET ");
var map = vo.getMap();
foreach (var item in map)
{
if (item.Key == "id" || item.Value == null) continue;
update.Append(item.Key).Append("=").Append("@").Append(item.Key).Append(",");
}
update.Remove(update.Length - 1, 1);
where.Append("id=@id");
update.Append(where);
cmd.CommandText = update.ToString();
cmd.Prepare();
foreach (var item in map)
{
if (item.Value == null) continue;
cmd.Parameters.AddWithValue("@" + item.Key, item.Value);
}
return cmd;
}
/// <summary>
/// 得到删除sql执行对象
/// </summary>
/// <param name="vo">赋有id的vo对象</param>
/// <returns></returns>
private static MySqlCommand getDeleteCommand(ValueObject vo)
{
MySqlCommand cmd = DBHelper.getSqlCommand();
StringBuilder delete = new StringBuilder("DELETE FROM ");
StringBuilder where = new StringBuilder(" WHERE ");
delete.Append(vo.getEntityName());
var map = vo.getMap();
where.Append("id=@id");
delete.Append(where);
cmd.CommandText = delete.ToString();
cmd.Prepare();
cmd.Parameters.AddWithValue("@id", map["id"]);
return cmd;
}
/// <summary>
/// 得到查询sql执行对象
/// </summary>
/// <param name="vo">赋有id的vo对象</param>
/// <returns></returns>
private static MySqlCommand getFindByIdCommand(ValueObject vo)
{
MySqlCommand cmd = DBHelper.getSqlCommand();
StringBuilder find = new StringBuilder("SELECT * FROM ");
StringBuilder where = new StringBuilder(" WHERE ");
find.Append(vo.getEntityName());
var map = vo.getMap();
where.Append("id=@id");
find.Append(where);
cmd.CommandText = find.ToString();
cmd.Prepare();
cmd.Parameters.AddWithValue("@id", map["id"]);
return cmd;
}
/// <summary>
/// 得到查询列表sql执行对象
/// </summary>
/// <param name="param">查询条件</param>
/// <returns></returns>
private static MySqlCommand getFindListCommand(string sql,ValueObject param)
{
MySqlCommand cmd = DBHelper.getSqlCommand();
var map = param.getMap();
cmd.CommandText = sql;
cmd.Prepare();
foreach (var item in map)
{
if (item.Value != null)
cmd.Parameters.AddWithValue("@" + item.Key, item.Value);
else
{
cmd.CommandText = cmd.CommandText.Replace("@" + item.Key, "1");
cmd.CommandText = cmd.CommandText.Replace(item.Key, "1");
}
}
return cmd;
}
}
}
EntityHelper:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Amer.ManageService.Util;
namespace Amer.ManageService.Entity
{
public static class EntityHelper
{
private static string entityXmlFileName = @"Entity.xml";
private static string entityExtXmlFileName = @"Entity-ext.xml";
/// <summary>
/// 从xml文件读取数据
/// </summary>
/// <param name="xmlFileName"></param>
/// <returns></returns>
private static DataSet getXMLData(string xmlFileName)
{
DataSet ds = new DataSet();
ds.ReadXml(xmlFileName);
return ds;
}
/// <summary>
/// 从Entity.xml读取实体字段返回map
/// </summary>
/// <param name="entityName"></param>
/// <returns></returns>
public static Dictionary<string, string> getEntityMap(string entityName)
{
//判断是entity还是entity-ext
var tem=entityName.Split('_');
if(tem[tem.Length-1].Equals("ext"))
return getEntityExtMap(entityName);
var map = new Dictionary<string, string>();
DataSet ds = null;
try
{
ds = getXMLData(entityXmlFileName);
}
catch (Exception e)
{
Log4cs.OutputLog("EntityHelper->getEntityMap:", e.Message);
}
Int32 id = -1;
DataTable entity = ds.Tables["entity"];
DataTable field = ds.Tables["field"];
foreach(DataRow dr in entity.Rows)
{
if (entityName.Equals(dr["name"]))
{
id = Int32.Parse(dr["entity_Id"].ToString());
break;
}
}
if (id != -1)
{
foreach (DataRow dr in field.Rows)
{
if (dr["entity_Id"].Equals(id))
{
map.Add(dr["name"].ToString(), null);
}
}
return map;
}
else
{
return null;
}
}
/// <summary>
/// 从Entity-ext.xml读取实体字段与sql返回map
/// </summary>
/// <param name="entityExtName"></param>
/// <returns></returns>
private static Dictionary<string, string> getEntityExtMap(string entityExtName)
{
var map = new Dictionary<string, string>();
DataSet ds = null;
try
{
ds = getXMLData(entityExtXmlFileName);
}
catch (Exception e)
{
Log4cs.OutputLog("EntityHelper->getEntityExtMap:", e.Message);
}
Int32 id = -1;
DataTable entity_ext = ds.Tables["entity-ext"];
//向map中添加字段
foreach (DataRow extDr in entity_ext.Rows)
{
if (entityExtName.Equals(extDr["name"]))
{
id = Int32.Parse(extDr["entity-ext_Id"].ToString());
string entitys = extDr["entitys"].ToString();
foreach (string entity in entitys.Split(';'))
{
var tem = getEntityMap(entity);
foreach (var item in tem)
{
try
{
map.Add(item.Key, item.Value);
}
catch
{
}
}
}
break;
}
}
if (id == -1)
{
throw new Exception("没有在Entity-ext.xml中找到" + entityExtName);
}
return map;
}
/// <summary>
/// 返回ext中的sql
/// </summary>
/// <param name="entityExtName"></param>
/// <param name="method"></param>
/// <returns></returns>
public static string getEntityExtSql(string entityExtName, string method,string sqlType)
{
DataSet ds = null;
try
{
ds = getXMLData(entityExtXmlFileName);
}
catch (Exception e)
{
Log4cs.OutputLog("EntityHelper->getEntityExtMap:", e.Message);
}
string sql = null;
Int32 id = -1;
DataTable entity_ext = ds.Tables["entity-ext"];
DataTable sqlTable = ds.Tables[sqlType];
//找到entity0ext的id
foreach (DataRow extDr in entity_ext.Rows)
{
if (entityExtName.Equals(extDr["name"]))
{
id = Int32.Parse(extDr["entity-ext_Id"].ToString());
break;
}
}
if (id == -1)
{
throw new Exception("没有在Entity-ext.xml中找到" + entityExtName);
}
//寻找sql
foreach (DataRow queryDr in sqlTable.Rows)
{
string mtd = "";
try
{
mtd = queryDr["method"].ToString();
}
catch
{
Log4cs.OutputLog("EntityHelper->getEntityExtMap:", "没有找到method:" + method);
}
if (queryDr["entity-ext_Id"].Equals(id) && mtd.Equals(method))
{
sql = queryDr["sql"].ToString();
}
}
return sql;
//printDataSet(ds);
}
/// <summary>
/// 测试打印dataset
/// </summary>
/// <param name="ds"></param>
private static void printDataSet(DataSet ds)
{
Console.WriteLine(ds.DataSetName);
for (int i = 0; i < ds.Tables.Count; i++)
{
Console.WriteLine("DataTable[" + i + "] TableName=" + ds.Tables[i].TableName);
foreach (DataColumn dc in ds.Tables[i].Columns)
{
Console.Write(dc.ColumnName + "\t");
}
Console.WriteLine();
for (int j = 0; j< ds.Tables[i].Rows.Count; j++)
{
for (int k = 0; k < ds.Tables[i].Columns.Count; k++)
{
Console.Write(ds.Tables[i].Rows[j][k] + "\t");
}
Console.WriteLine();
}
Console.WriteLine();
Console.WriteLine("---------------------------------------");
}
}
}
}
在这里要多说两句,这个类将会从XML配置中生成ValueObject的map。Sql也是配置在XML中的。
两个XML,一个用于配置实体,一个用于配置sql。
1.Entity.xml
<?xml version="1.0" encoding="utf-8" ?>
<entitys>
<entity name="_area">
<field name="id" />
<field name="name" />
<field name="description" />
</entity>
<entity name="_test">
<field name="id" />
<field name="test1" />
<field name="test2" />
<field name="test3" />
</entity>
</entitys>
同一个entity中不能有重复字段。
2.Entity-ext.xml
<?xml version="1.0" encoding="utf-8" ?>
<entity-exts>
<entity-ext name="_area_ext">
<entitys>_area</entitys>
<query method="queryAreaList">
<sql>
<![CDATA[SELECT * FROM _area WHERE id=@id]]>
</sql>
</query>
<query method="queryAreaListTest">
<sql>
<![CDATA[SELECT * FROM _area]]>
</sql>
</query>
</entity-ext>
<entity-ext name="_area2_ext">
<entitys>_area;_test</entitys>
<query method="queryAreaList2">
<sql>
<![CDATA[SELECT * FROM _area]]>
</sql>
</query>
<delete method="deleteAreaList">
<sql>
<![CDATA[DELETE * FROM _area]]>
</sql>
</delete>
</entity-ext>
</entity-exts>
同一个entity-ext中method不能重复。
好了,到此为止,这个小框架已经搭起来了,剩下就是使用框架去做一些事情了。