C#连接MySql的小封装

突然想起之前有封装过C#连接mySql的一些类，发出来和大家交流一下~

小小借鉴了SSH的设计模式，强大的SSH太值得学习了。

封装了四个类：

1.ValueObejct（和我另一篇文章中的值对象是一个意思，但这个里的做的好一些，文章地址）

2.DBHelper（数据库连接辅助类，这块写的不是太好，如果想换成ORACLE或者其他数据库要重写这个类）

3.CommDao（数据库访问对象，这个类将与数据库做交互）

4.EntityHelper（实体类辅助类）

还用到了一个Log4cs类，这个里面我什么都没写，就用console输出了一下，也可以将其他日志实现类接进去。

项目中需要引用mysql在.net下的connector。下载地址http://www.mysql.com/downloads/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=2234yflyfl;";            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不能重复。

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