C#连接MySql的小封装

突然想起之前有封装过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不能重复。

好了，到此为止，这个小框架已经搭起来了，剩下就是使用框架去做一些事情了。