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 Dapper

{

    public class DapperConn

    {

        public static IDbConnection GetConnection()

        {

            string connStr = ConfigurationManager.AppSettings["conn"];

            return new SqlConnection(connStr);

        }

    }

}

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

namespace Dapper

{

    public class UserModel

    {

        public Int32 Id { get; set; }

        public String Key { get; set; }

        public String Value { get; set; }

    }

}

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

namespace Dapper

{

    public class UserLoginLog

    {

        public Int32 Id { get; set; }

        public Int32 UserId { get; set; }

        public DateTime CreateTime { get; set; }

    }

}

using System;

using System.Collections.Generic;

using System.Data;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

namespace Dapper

{

    public class DB

    {

        public int Add(UserModel model)

        {

            using (var conn = DapperConn.GetConnection())

            {

                string sql = "Insert into User (Key,Value) Value (@Key,@Value)";

                return conn.Execute(sql, model);

            }

        }

        public int Add1(UserModel model)

        {

            using (var conn = DapperConn.GetConnection())

            {

                DynamicParameters dp = new DynamicParameters();

                dp.Add("@Key", model.Key);

                dp.Add("@Value", model.Value);

                return conn.Execute("User", dp, null, null, CommandType.TableDirect);

            }

        }

        public int Update(UserModel model)

        {

            using (var conn = DapperConn.GetConnection())

            {

                string sql = "update User set Key=@Key,Value=@Value where Id=@Id";

                return conn.Execute(sql, model);

            }

        }

        public int Del(UserModel model)

        {

            using (var conn = DapperConn.GetConnection())

            {

                string sql = "Delete from User where Id=@Id";

                return conn.Execute(sql, model);

            }

        }

        public UserModel GetModel()

        {

            using (var conn = DapperConn.GetConnection())

            {

                var sql = "Select Id,Key,Value from User";

                return conn.QueryFirstOrDefault<UserModel>(sql);

            }

        }

        public IEnumerable<UserModel> GetModels()

        {

            using (var conn = DapperConn.GetConnection())

            {

                var sql = "Select Id,Key,Value from User";

                return conn.Query<UserModel>(sql);

            }

        }

        public void ImplementAffair(UserModel userModel, UserLoginLog userLogModel)

        {

            using (var conn = DapperConn.GetConnection())

            {

                IDbTransaction tran = conn.BeginTransaction();

                try

                {

                    string query = "Update User set Key='测试' where ID=@ID";//更新一条记录

                    conn.Execute(query, userModel, tran, null, null);

                    query = "insert into UserLoginLog (userId,CreateTime) value (@userId,@CreateTime)";//删除一条记录

                    conn.Execute(query, userLogModel, tran, null, null);

                    //提交

                    tran.Commit();

                }

                catch (Exception ex)

                {

                    //提交错误

                    //回滚事务

                    tran.Rollback();

                }

            }

        }

        /// <summary>

        /// 执行无参数存储过程 返回列表

        /// </summary>

        /// <returns></returns>

        private IEnumerable<UserModel> ExecuteStoredProcedureNoParms()

        {

            using (IDbConnection con = DapperConn.GetConnection())

            {

                var userList = new List<UserModel>();

                userList = con.Query<UserModel>("QueryRoleNoParms",

                                        null,

                                        null,

                                        true,

                                        null,

                                        CommandType.StoredProcedure).ToList();

                return userList;

            }

        }

        /// <summary>

        /// 执行无参数存储过程 返回int

        /// </summary>

        /// <returns></returns>

        private int ExecutePROC()

        {

            using (IDbConnection con = DapperConn.GetConnection())

            {

               return con.Execute("QueryRoleWithParms", null, null, null, CommandType.StoredProcedure);

            }

        }

        /// <summary>

        /// 执行带参数的存储过程

        /// </summary>

        /// <param name="model"></param>

        /// <returns></returns>

        private string ExecutePROC(UserModel model)

        {

            DynamicParameters dp = new DynamicParameters();

            dp.Add("@ID", "");

            dp.Add("@msg", "", DbType.String, ParameterDirection.Output);

            using (IDbConnection con = DapperConn.GetConnection())

            {

                con.Execute("Proc", dp, null, null, CommandType.StoredProcedure);

                string roleName = dp.Get<string>("@msg");

                return roleName;

            }

        }

    }

}