安装使用Dapper和拓展QueryToDataTable方法

1、首先在解决方案里右键点击项目选择【管理NuGet程序包】，在里面搜索并安装Dapper。

 

 2、在项目中引用Dapper

 

 3、创建一个DbHelper帮助类，顺便拓展一下toDataTable 的方法

using Dapper;
namespace NetFramework.Model
{
    public static class DbHelper
    {
        private static readonly string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["defaultNew"].ToString();

        //创建连接对象
        public static IDbConnection GetConn()
        {
            var conn = new OracleConnection(connectionString);
            return conn;
        }

        public static IDbConnection GetConnEmr()
        {
            throw new NotImplementedException();
        }
/// <summary>
        ///  返回查询结果集
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="type"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public static DataTable QueryToDataTable(this IDbConnection connection, string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            using (var dataReader = connection.ExecuteReader(sql, param, transaction, commandTimeout, commandType))
            {
                var datatable = new DataTable();
                for (int i = 0; i < dataReader.FieldCount; i++)
                {
                    DataColumn mydc = new DataColumn();    //关键的一步
                    mydc.DataType = dataReader.GetFieldType(i);
                    mydc.ColumnName = dataReader.GetName(i);
                    datatable.Columns.Add(mydc);          //关键的第二步
                }

                while (dataReader.Read())
                {
                    DataRow myDataRow = datatable.NewRow();
                    for (int i = 0; i < dataReader.FieldCount; i++)
                    {
                        myDataRow[i] = dataReader[i].ToString();
                    }
                    datatable.Rows.Add(myDataRow);
                }
                ///关闭数据读取器
                dataReader.Close();
                return datatable;
            }

        }
    }
}

4、使用的话也很简单

public static bool DealWithMatters(int status, string tfdh)
        {

            using (var conn = DbHelper.GetConn())
            {
                conn.Open();//用事务的话需要手动打开
                using (var transaction = conn.BeginTransaction())
                {
                    try
                    {
                        string sql = $"UPDATE NET_MZTF SET STATUS='{status}' WHERE TFDH={tfdh}";
                        bool i = conn.Execute(sql, transaction) > 0;
                        sql = $"insert into NET_MZTF_1()values()";
                        conn.Execute(sql, transaction);
                        transaction.Commit();
                        return true;
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback();
                        MessageBox.Show(ex.Message);
                    }
                    finally
                    {
                        conn.Close();//记得关闭
                    }
                    return false;
                }
            }
        }

public static DataTable GetFlowStatus(string operCode)
        {
            using (var conn = DbHelper.GetConn())
            {
                string sql = $"select * from TabeleA where OPERCODE=:operCode";//连的是oracle的数据库，其他的数据库sql参数化不一样
                var parameters = new DynamicParameters();
                parameters.Add("operCode", operCode);
                //自己拓展的ToDataTable方法
                DataTable dt = conn.QueryToDataTable(sql, parameters);
                return dt;
            }
        }

5、Dapper通过扩展IDbConnection提供一些有用的扩展方法去查询您的数据库。详细的自行去看看Dapper的方法介绍。