ADO.Net

在接触dapper之前，我使用的是ADO.Net

简单介绍下常用对象：

Connection：数据库连接对象

Command：将sql语句发送给数据库，并返回结果

DataReader：用于读取sql查询返回的结果

DataAdapter：数据适配器，Fill(DataTable)方法将查询的数据返回成一个DataTable

为防止sql注入攻击，使用参数化查询，使用SqlParameter

简单封装了一个工具类：

using Microsoft.Extensions.Configuration;
using MySql.Data.MySqlClient;
using System.Collections.Generic;
using System.Data;
using Tools.dbHelper;

namespace Tools.sql
{
    public class MysqlHelper
    {
        private static MysqlHelper mysqlHelper;
        private static readonly object lockObj = new object();
        public static string connStr = string.Empty;
        private MysqlHelper() 
        {
            connStr = Config.AppConfig.CreateConfig().connStr;
        }

        public static MysqlHelper CreateMysqlHelper()
        {
            if (mysqlHelper == null)
            {
                lock (lockObj)
                {
                    if (mysqlHelper == null)
                    {
                        mysqlHelper = new MysqlHelper();
                    }
                }
            }
            return mysqlHelper;
        }

        
        /// <summary>
        /// 测试数据库是否连接
        /// </summary>
        /// <returns>0--未连接；1--已连接</returns>
        public int ChkConn()
        {
            using (MySqlConnection conn = new MySqlConnection(connStr))
            {
                try
                {
                    conn.Open();
                }
                catch
                {
                    return 0;
                }
                if (conn.State == ConnectionState.Open)
                {
                    return 1;
                }
                else
                {
                    return 0;
                }
            }
        }

        /// <summary>
        /// 查询多条记录
        /// </summary>
        /// <param name="sql">sql</param>
        /// <param name="paras">参数化查询参数</param>
        /// <returns>DataTable</returns>
        public DataTable GetDataTbl(string sql, params MySqlParameter[] paras)
        {
            DataTable table = new DataTable();
            MySqlDataAdapter adapter = new MySqlDataAdapter(sql, connStr);
            adapter.SelectCommand.Parameters.AddRange(paras);
            adapter.Fill(table);
            adapter.SelectCommand.Parameters.Clear();
            adapter.Dispose();
            return table;
        }

        /// <summary>
        /// 获取单行
        /// </summary>
        /// <param name="sqlstr"></param>
        /// <param name="sqlParas"></param>
        /// <returns></returns>
        public dynamic ExecuteReader(string sqlstr, MysqlReaderModel ReaderModelFunc, params MySqlParameter[] sqlParas)
        {
            dynamic model = null;
            using (MySqlConnection conn = new MySqlConnection(connStr))
            {
                conn.Open();
                MySqlCommand command = new MySqlCommand(sqlstr, conn);
                command.Parameters.AddRange(sqlParas);
                using (MySqlDataReader reader = command.ExecuteReader())
                {
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            model = ReaderModelFunc(reader);
                        }
                    }
                }
            }
            return model;
        }

        /// <summary>
        /// 使用事务获取单行
        /// </summary>
        /// <param name="sqlstr"></param>
        /// <param name="sqlParas"></param>
        /// <returns></returns>
        public dynamic ExecuteReader(MySqlConnection conn, MySqlTransaction tran, string sql, MysqlReaderModel ReaderModelFunc, params MySqlParameter[] paras)
        {
            dynamic model = null;
            using (MySqlCommand command = conn.CreateCommand())
            {
                command.Transaction = tran;
                command.Parameters.AddRange(paras);
                command.CommandText = sql;
                using (MySqlDataReader reader = command.ExecuteReader())
                {
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            model = ReaderModelFunc(reader);
                        }
                    }
                }
            }
            return model;
        }

        /// <summary>
        /// 返回首行首列
        /// </summary>
        /// <param name="sqlstr"></param>
        /// <param name="sqlParas"></param>
        /// <returns></returns>
        public object ExecuteScalar(string sqlstr, params MySqlParameter[] sqlParas)
        {
            object model = null;
            using (MySqlConnection conn = new MySqlConnection(connStr))
            {
                conn.Open();
                MySqlCommand command = new MySqlCommand(sqlstr, conn);
                command.Parameters.AddRange(sqlParas);
                model = command.ExecuteScalar();
            }
            return model;
        }

        /// <summary>
        /// 使用事务 返回首行首列
        /// </summary>
        /// <param name="sqlstr"></param>
        /// <param name="sqlParas"></param>
        /// <returns></returns>
        public object ExecuteScalar(MySqlConnection conn, MySqlTransaction tran, string sql, params MySqlParameter[] paras)
        {
            object model = null;
            using (MySqlCommand command = conn.CreateCommand())
            {
                command.Transaction = tran;
                command.Parameters.AddRange(paras);
                command.CommandText = sql;
                model = command.ExecuteScalar();
            }
            return model;
        }

        /// <summary>
        /// 增删改数据
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public int ExecuteNonQuery(string sql, params MySqlParameter[] paras)
        {
            using (MySqlConnection conn = new MySqlConnection(connStr))
            {
                conn.Open();
                MySqlCommand command = new MySqlCommand(sql, conn);
                command.Parameters.AddRange(paras);
                int flag = 0;
                try
                {
                    flag = command.ExecuteNonQuery();
                }
                catch
                {
                    flag = -1;
                }
                return flag;
            }
        }

        /// <summary>
        /// 增删改数据(事务)
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public int ExecuteNonQuery(MySqlConnection conn, MySqlTransaction tran, string sql, params MySqlParameter[] paras)
        {
            using (MySqlCommand command = conn.CreateCommand())
            {
                command.Transaction = tran;
                command.Parameters.AddRange(paras);
                command.CommandText = sql;
                int flag = 0;
                try
                {
                    flag = command.ExecuteNonQuery();
                }
                catch
                {
                    flag = -1;
                }
                return flag;
            }
        }

        /// <summary>
        /// 将表转换为数据集合
        /// </summary>
        /// <param name="table"></param>
        /// <param name="changeModelFunc">将表中的行转为对象的委托方法</param>
        /// <returns></returns>
        public List<T> DataTableToList<T>(DataTable table, ChangeToModel Func)
        {
            List<T> Models = null;
            if (table.Rows.Count > 0)
            {
                Models = new List<T>();
                foreach (DataRow dr in table.Rows)
                {
                    T model = Func(dr);
                    Models.Add(model);
                }
                return Models;
            }
            else
            {
                return null;
            }
        }

        public MySqlConnection GetConn()
        {
            MySqlConnection conn = new MySqlConnection(connStr);
            return conn;
        }

        /// <summary>
        /// 开始事务
        /// </summary>
        public MySqlTransaction StartTransaction(MySqlConnection conn)
        {
            MySqlTransaction transaction = conn.BeginTransaction();
            return transaction;
        }

        /// <summary>
        /// 提交事务
        /// </summary>
        public void CommitTransaction(MySqlTransaction transaction, MySqlConnection conn)
        {
            try
            {
                transaction.Commit();
                transaction.Dispose();
                conn.Dispose();
            }
            catch { }
        }

        public void RollbackTransaction(MySqlTransaction transaction, MySqlConnection conn)
        {
            try
            {
                transaction.Rollback();
                transaction.Dispose();
                conn.Dispose();
            }
            catch { }
        }
    }
}

上面使用了委托：

using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Text;

namespace Tools.dbHelper
{
    public delegate dynamic ChangeToModel(DataRow dr);
    public delegate dynamic MysqlReaderModel(MySqlDataReader reader);
}

我们可以把委托理解为存放方法的容器，将方法看成一个个对象，那么存放这些对象的容器就叫做委托。

为了处理数据库的null值，使用了扩展方法：

using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Text;

namespace Tools.dbHelper
{
    public static class SqlExt
    {
        public static string DrToString(this object obj)
        {
            if (obj == System.DBNull.Value)
            {
                return "";
            }
            else
            {
                return obj.ToString();
            }
        }

        public static int DrToInt(this object obj)
        {
            if (obj == System.DBNull.Value || obj==null)
            {
                return 0;
            }
            else
            {
                return int.Parse(obj.ToString());
            }
        }

        public static string DrToDateStr(this object obj)
        {
            if (obj == System.DBNull.Value)
            {
                return "0000-01-01 00:00:00";
            }
            else
            {
                string dateStr = "";
                try
                {
                    dateStr = Convert.ToDateTime(obj.DrToString()).ToString("yyyy-MM-dd");
                }
                catch
                {

                }
                return dateStr;
            }
        }

        public static DateTime DrToDate(this object obj)
        {
            if (obj == System.DBNull.Value || obj==null)
            {
                return new DateTime();
            }
            else
            {
                return Convert.ToDateTime(obj.DrToString());
            }
        }
    }
}

扯远了，回来看上面的类如何调用

查询：

public List<ActionModel> GetData(string controllerName)
        {
            sql = @"select * from action where controller_id=@controllerName";
            MySqlParameter conParam = new MySqlParameter("@controllerName", controllerName);
            DataTable tbl = mysql.GetDataTbl(sql, conParam);
            List<ActionModel> actlist = mysql.DataTableToList<ActionModel>(tbl, delegate (DataRow dr)
            {
                return new ActionModel()
                {
                    id = dr["id"].ToString(),
                    controller_id = dr["controller_id"].ToString(),
                    descr = dr["descr"].DrToString(),
                    create_tm = dr["create_tm"].DrToDateStr(),
                    create_emp_id = dr["create_emp_id"].DrToString(),
                    is_use = dr["is_use"].DrToInt()
                };
            });
            return actlist;
        }

protected Plan GetPlanStartAndEndTM(string planId)
        {
            sql = @"select * from vote_plan where plan_id=@planId";
            MySqlParameter planParam = new MySqlParameter("@planId", planId);
            Plan planObj = mysql.ExecuteReader(sql, delegate (MySqlDataReader reader) {
                return new Plan
                {
                    start_tm = reader["start_tm"].DrToDateStr(),
                    end_tm = reader["end_tm"].DrToDateStr(),
                    do_start_tm = reader["do_start_tm"].DrToDateStr(),
                    do_end_tm = reader["do_end_tm"].DrToDateStr()
                };
            }, planParam);
            return planObj;
        }

新增：

public int Add(string actionName, string controllerName, string descr, bool chkIsUse, string sessionEmpId)
        {
            sql = @"insert into action (id, controller_id, descr, create_tm, create_emp_id, is_use) values (@id, @controllerName, @descr, now(), @sessionEmpId, @chkIsUse)";
            MySqlParameter idParam = new MySqlParameter("@id", actionName);
            MySqlParameter conParam = new MySqlParameter("@controllerName", controllerName);
            MySqlParameter descrParam = new MySqlParameter("@descr", descr);
            MySqlParameter sessionEmpParam = new MySqlParameter("@sessionEmpId", sessionEmpId);
            MySqlParameter isUseParam = new MySqlParameter("@chkIsUse", chkIsUse);
            int rs = mysql.ExecuteNonQuery(sql, idParam, conParam, descrParam, sessionEmpParam, isUseParam);
            return rs;
        }

像这样使用参数化查询，需要一个一个封装参数，查询出来的结果，也需要一个字段一个字段处理，

使用原始的ADO.Net速度理论上会比使用ORM框架要快，因为ORM本来就是在ADO的基础上再做了封装

使用dapper编写代码会方便很多，与EF不同，这是一个轻量级的ORM，Model对象要自己编写，有说这个性能接近于ADO.Net。

关于dapper，可查看另一篇：

 https://www.cnblogs.com/SasaL/p/11223929.html