• 自己整理了一个 Dapper的Helper助手类


    链接字符串配置:

    <connectionStrings>
        <add name="db" connectionString="server=.;database=db;uid=sa;pwd=123456;integrated security=false;"/>
      </connectionStrings>

    DapperHelper.cs :

    using Dapper;
    using System;
    using System.Collections.Generic;
    using System.Configuration;
    using System.Data;
    using System.Data.SqlClient;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    
    namespace PullChargeData.Helper
    {
        public class DapperHelper<T>
        {
            /// <summary>
            /// 数据库连接字符串
            /// </summary>
            private static readonly string connectionString = ConfigurationManager.ConnectionStrings["db"].ConnectionString;
    
            /// <summary>
            /// 查询列表(返回DataTable)
            /// </summary>
            /// <returns></returns>
            public static DataTable QueryToDataTable(string sql)
            {
                DataTable table = new DataTable("MyTable");
                using (SqlConnection con = new SqlConnection(connectionString))
                {
                    var reader = con.ExecuteReader(sql);
                    table.Load(reader);
                    return table;
                }
            }
    
            /// <summary>
            /// 查询列表
            /// </summary>
            /// <param name="sql">查询的sql</param>
            /// <param name="param">替换参数</param>
            /// <returns></returns>
            public static List<T> Query(string sql, object param = null)
            {
                using (SqlConnection con = new SqlConnection(connectionString))
                {
                    return con.Query<T>(sql, param).ToList();
                }
            }
    
            /// <summary>
            /// 查询第一个数据
            /// </summary>
            /// <param name="sql"></param>
            /// <param name="param"></param>
            /// <returns></returns>
            public static T QueryFirst(string sql, object param = null)
            {
                using (SqlConnection con = new SqlConnection(connectionString))
                {
                    return con.Query<T>(sql, param).ToList().First();
                }
            }
    
            /// <summary>
            /// 查询第一个数据没有返回默认值
            /// </summary>
            /// <param name="sql"></param>
            /// <param name="param"></param>
            /// <returns></returns>
            public static T QueryFirstOrDefault(string sql, object param = null)
            {
                using (SqlConnection con = new SqlConnection(connectionString))
                {
                    return con.Query<T>(sql, param).ToList().FirstOrDefault();
                }
            }
    
            /// <summary>
            /// 查询单条数据
            /// </summary>
            /// <param name="sql"></param>
            /// <param name="param"></param>
            /// <returns></returns>
            public static T QuerySingle(string sql, object param = null)
            {
                using (SqlConnection con = new SqlConnection(connectionString))
                {
                    return con.Query<T>(sql, param).ToList().Single();
                }
            }
    
            /// <summary>
            /// 查询单条数据没有返回默认值
            /// </summary>
            /// <param name="sql"></param>
            /// <param name="param"></param>
            /// <returns></returns>
            public static T QuerySingleOrDefault(string sql, object param = null)
            {
                using (SqlConnection con = new SqlConnection(connectionString))
                {
                    return con.Query<T>(sql, param).ToList().SingleOrDefault();
                }
            }
    
            /// <summary>
            /// 增删改
            /// </summary>
            /// <param name="sql"></param>
            /// <param name="param"></param>
            /// <returns>Number of rows affected</returns>
            public static int Execute(string sql, object param)
            {
                using (SqlConnection con = new SqlConnection(connectionString))
                {
                    return con.Execute(sql, param);
                }
            }
    
            /// <summary>
            /// Reader获取数据
            /// </summary>
            /// <param name="sql"></param>
            /// <param name="param"></param>
            /// <returns></returns>
            public static IDataReader ExecuteReader(string sql, object param)
            {
                using (SqlConnection con = new SqlConnection(connectionString))
                {
                    return con.ExecuteReader(sql, param);
                }
            }
    
            /// <summary>
            /// Scalar获取数据
            /// </summary>
            /// <param name="sql"></param>
            /// <param name="param"></param>
            /// <returns></returns>
            public static object ExecuteScalar(string sql, object param)
            {
                using (SqlConnection con = new SqlConnection(connectionString))
                {
                    return con.ExecuteScalar(sql, param);
                }
            }
    
            /// <summary>
            /// Scalar获取数据
            /// </summary>
            /// <param name="sql"></param>
            /// <param name="param"></param>
            /// <returns></returns>
            public static T ExecuteScalarForT(string sql, object param)
            {
                using (SqlConnection con = new SqlConnection(connectionString))
                {
                    return con.ExecuteScalar<T>(sql, param);
                }
            }
    
            /// <summary>
            /// 带参数的存储过程
            /// </summary>
            /// <param name="sql"></param>
            /// <param name="param"></param>
            /// <returns></returns>
            public static List<T> ExecutePro(string proc, object param)
            {
                using (SqlConnection con = new SqlConnection(connectionString))
                {
                    List<T> list = con.Query<T>(proc,
                        param,
                        null,
                        true,
                        null,
                        CommandType.StoredProcedure).ToList();
                    return list;
                }
            }
    
    
            /// <summary>
            /// 事务1 - 全SQL
            /// </summary>
            /// <param name="sqlarr">多条SQL</param>
            /// <param name="param">param</param>
            /// <returns></returns>
            public static int ExecuteTransaction(string[] sqlarr)
            {
                using (SqlConnection con = new SqlConnection(connectionString))
                {
                    using (var transaction = con.BeginTransaction())
                    {
                        try
                        {
                            int result = 0;
                            foreach (var sql in sqlarr)
                            {
                                result += con.Execute(sql, null, transaction);
                            }
    
                            transaction.Commit();
                            return result;
                        }
                        catch (Exception ex)
                        {
                            transaction.Rollback();
                            return 0;
                        }
                    }
                }
            }
    
            /// <summary>
            /// 事务2 - 声明参数
            ///demo:
            ///dic.Add("Insert into Users values (@UserName, @Email, @Address)",
            ///        new { UserName = "jack", Email = "380234234@qq.com", Address = "上海" });
            /// </summary>
            /// <param name="Key">多条SQL</param>
            /// <param name="Value">param</param>
            /// <returns></returns>
            public static int ExecuteTransaction(Dictionary<string, object> dic)
            {
                using (SqlConnection con = new SqlConnection(connectionString))
                {
                    using (var transaction = con.BeginTransaction())
                    {
                        try
                        {
                            int result = 0;
                            foreach (var sql in dic)
                            {
                                result += con.Execute(sql.Key, sql.Value, transaction);
                            }
    
                            transaction.Commit();
                            return result;
                        }
                        catch (Exception ex)
                        {
                            transaction.Rollback();
                            return 0;
                        }
                    }
                }
            }
        }
    }
    

    调用方法

    //查 无参数
    var list = DapperHelper<T_User>.Query("select * from T_User ").ToList();
    //查 带参数
    var list = DapperHelper<T_User>.Query("select * from T_User where uid=@uid", new { uid = 1, }).ToList();
    //
    int ins = DapperHelper<T_User>.Execute("insert into T_User (uid,username) value(@uid,@username)", new { uid = 1, username = "张三" });
    //
    int upd = DapperHelper<T_User>.Execute("update T_User set username=@username where uid=@uid", new { username = "李四", uid = 1});
    //
    int del = DapperHelper<T_User>.Execute("delete from T_User where uid=@uid", new { uid = 1 });
  • 相关阅读:
    TextView控件中使用android:drawableLeft图片和文字使用gravity只能使文字居中,无法使图片随文字同时居中, 让图片随文字同时居中
    Android Studio sdk无法下载的问题
    CollapsingToolbarLayout的使用,实现向上滑动时,图片自动压缩消失,下拉到最上面图片显示出来
    android studio开发app时,运行时百度地图显示合适,app打包后地图不显示
    两个日期相差的天数
    Android实现定点任务(定时定点做某事)
    Conversion to Dalvik format failed: Unable to execute dex: Cannot merge new index 69457 into a non-jumbo instruction!
    JSP易错点及常用标签
    Java异常机制总结
    java内存区域简单介绍
  • 原文地址:https://www.cnblogs.com/codedisco/p/12974582.html
Copyright © 2020-2023  润新知