Dapper .NET ORM神器，增删改查(Mysql,SqlServer)

1.安装Dapper，Mysql.Data

NuGet搜索Dapper,点击安装即可

NuGet搜索Mysql,点击安装即可

2.创建工具类

using Dapper;
public  class SqlConnectService
    {
	    //楼主同时不用Mysql和Sqlserver 两种数据库，暂时不封装这两种数据库的查询 ，需要的自己进行封装
	    //Mysql和Sqlserver查询的区别仅仅只连接方式的区别
        /// <summary>
        /// 创建数据库连接对象Sqlserver
        /// </summary>
        public static IDbConnection sqlConnection()
        {
            string sqlServerConnectString = "server=***;database=***;User=***;password=***;Connect Timeout=10000";
         
            IDbConnection connection = new SqlConnection(sqlServerConnectString );
            connection.Open();
            return connection;
        }
   		 /// <summary>
        /// 创建数据库连接对象Mysql
        /// </summary>
        public static IDbConnection sqlConnection()
        {
            
            string mysqlConnectString = "server=localhost;database=attendance;userid=root;password=root";
            IDbConnection connection = new MySqlConnection(sqlServerConnectString);
            connection.Open();
            return connection;
        }
       
        /// <summary>
        /// Dapper查询返回List<T>
        /// </summary>
        /// <typeparam name="T">需要返回的对象类型</typeparam>
        /// <param name="sql">Sql语句</param>
        /// <param name="parm"></param>
        /// <returns></returns>
        public static List<T> select<T>(string sql,Object parm) where T : new()
        {
           
            List<T> list = null;
            using (IDbConnection conn = sqlConnection())
            {
                list = conn.Query<T>(sql, parm).ToList<T>();
            }
            return list;
        }
         /// <summary>
        /// Dapper查询返回List字典对象  无需手动Wapper对象了 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parm"></param>
        /// <returns></returns>
        public static List<Dictionary<string, Object>> selectToDict(string sql, Object parm)
        {

            List<Dictionary<string, Object>> result = null;
            using (IDbConnection conn = sqlConnection())
            {
                var menus = conn.Query(sqlName, parm).ToList();
                result = JsonConvert.DeserializeObject<List<Dictionary<string, Object>>>(JsonConvert.SerializeObject(menus));
            }
            return result;
        }
        /// <summary>
        /// Dapper查询返回对象非List集合
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sqlName"></param>
        /// <param name="sqlFileName"></param>
        /// <param name="parm"></param>
        /// <returns></returns>
        public static T selectToObject<T>(string sql Object parm) where T : new()
        {
            T t = new T();
            //sql语句
            using (IDbConnection conn = sqlConnection())
            {
                t = conn.Query<T>(sql, parm).Single<T>();
            }
            return t;
        }
               
        /// <summary>
        /// Dapper增删改
        /// </summary>
        /// <param name="sqlName"></param>
        /// <param name="sqlFileName"></param>
        /// <param name="parameter"></param>
        /// <returns></returns>
        public static int SQL(string sql, Object parametere)
        {
            //sql语句
            
            int result = 0;
            using (IDbConnection conn = sqlConnection())
            {
                result = conn.Execute(sql, parameter);
            }
            return result;
        }
       
        /// <summary>
        /// Dapper插入 返回自增主键Id
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameter"></param>
        /// <param name="tableName">待插入数据的表名</param>
        /// <returns></returns>
        public static int insertReturnId(string sql, Object parameter, String tableName)
        {
          
            int result = 0;
            using (IDbConnection conn = sqlConnection(type))
            {
                result = conn.Execute(sql, parameter);
                if (result != 0)
                {
                    result = conn.Query<int>("select ident_current(@table)", new { table = tableName }).Single<int>();
                }
            }
            return result;
        }

3.Sql语句,以及参数Parmeter示例书写示例

//sql
string insertSql="insert into table1 (prop1,prop2,prop3) values (@prop1,@prop2,@prop3)";
//参数对象
public class Test{
public String prop1{get;set;}
public String prop1{get;set;}
public String prop1{get;set;}
}
//以上仅以插入语句纹为例，其他语句请自行仿照书写

若有疑问，可在评论区留言

关于条件查询、以及分页查询可按照个人习惯进行封装，需要的也可以咨询我如何进行封装的