Dapper简单入门案例

备注：以WebAPI下项目为例，数据库根据下面的实体类手动创建

一、项目准备

NuGet为项目安装如下两个包

1.Dapper.dll

2.Dapper.Contrib.dll

二、准备实体类

项目目录下建立Entities文件夹，存放如下实体类

约束实体BaseEntity

 public class BaseEntity
    {
        [Key]
        public int Id { get; set; }
    }

用户实体Users

 [Table("Users")]
    public class Users: BaseEntity
    {
        public string UserNo { get; set; }
        public string UserName { get; set; }
        public int UserLevel { get; set; }
        public string Password { get; set; }
    }

发表实体类Posts

[Table("Posts")]
public class Posts:BaseEntity
{
    public string PostTitle { get; set; }
    public string PostIcon { get; set; }
    public string PostType { get; set; }
    public string PostContent { get; set; }
    public int Clicks { get; set; }
    public int Replys { get; set; }
    public DateTime CreateTime { get; set; }
    public int CreateUser { get; set; }
    public DateTime EditTime { get; set; }
    public int EditUser { get; set; }
    public DateTime LastReplyTime { get; set; }
    public int LastReplyUser { get; set; }
    public Users CreateUserInfo { get; set; }
    public Users EditUserInfo { get; set; }
    public Users LastReplyUserInfo { get; set; }
}

三、数据库上下文类以及查询帮助类

项目下建立SqlContext文件夹，存放如下类

数据库连接类ConnectionOption

  public class ConnectionOption
    {
        private static IDbConnection _dbConnection = new SqlConnection();
        public static IDbConnection DbConnection
        {
            get
            {
                if (string.IsNullOrEmpty(_dbConnection.ConnectionString))
                {
                    _dbConnection.ConnectionString = ConnectionString;
                }
                return _dbConnection;
            }
        }


        private static string _connectionString;
        public static string ConnectionString { get => _connectionString; set => _connectionString = value; }

    }

Dapper增删改查的帮助类DapperExtHelper

public class DapperExtHelper<T> where T:BaseEntity
{
    public T Get(int id) {
        return ConnectionOption.DbConnection.Get<T>(id);
    }
    public IEnumerable<T> GetAll()
    {
        return ConnectionOption.DbConnection.GetAll<T>();
    }
    public long Insert(T t)
    {
        return ConnectionOption.DbConnection.Insert(t);
    }
    public bool Update(T t)
    {
        return ConnectionOption.DbConnection.Update(t);
    }
    public bool Delete(T t)
    {
        return ConnectionOption.DbConnection.Delete(t);
    }
    public bool DeleteAll(int id)
    {
        return ConnectionOption.DbConnection.DeleteAll<T>();
    }
}

单个数据与多个数据查询帮助类DapperHelper

public class DapperHelper
{
    //static IDbConnection _dbConnection = new SqlConnection();
    //public string ConnectionString => ConnectionOption.ConnectionString;
    //public DapperHelper() {
    //    if (string.IsNullOrEmpty(_dbConnection.ConnectionString))
    //    {
    //        _dbConnection.ConnectionString = ConnectionString;
    //    }

    //}
    /// <summary>
    /// 单个查询
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="sql">sql语句</param>
    /// <param name="param">参数</param>
    /// <param name="transaction">事务</param>
    /// <param name="commandTimeout">超时时间</param>
    /// <param name="commandType">command类型</param>
    /// <returns></returns>
    public T QueryFirst<T>(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null) {
        ConnectionOption.DbConnection.Open();
        using (transaction = ConnectionOption.DbConnection.BeginTransaction())
        {
            var user = ConnectionOption.DbConnection.QueryFirstOrDefault<T>(sql, param, transaction, commandTimeout, commandType);
            transaction.Commit();
            ConnectionOption.DbConnection.Close();
            return user;
        }
    }
    /// <summary>
    /// 多个查询
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="sql">sql语句</param>
    /// <param name="param">参数</param>
    /// <param name="transaction">事务</param>
    /// <param name="buffered">缓冲/缓存</param>
    /// <param name="commandTimeout">超时时间</param>
    /// <param name="commandType">command类型</param>
    /// <returns></returns>
    public IEnumerable<T> Query<T>(string sql, object param = null, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = null, CommandType? commandType = null)
    {
        return ConnectionOption.DbConnection.Query<T>(sql, param, transaction, buffered,commandTimeout, commandType);
    }

    public int Execute<T>(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
    {
        return ConnectionOption.DbConnection.Execute(sql, param, transaction, commandTimeout, commandType);
    }
}

四、缓存帮助类

项目下建立Utility文件夹，存放如下类

  public class MemoryHelper
    {
        private static IMemoryCache _memoryCache = null;
        static MemoryHelper() {
            if (_memoryCache ==null)
            {
                _memoryCache = new MemoryCache(new MemoryCacheOptions());
            }
        }
        public static void SetMemory(string key, object value) {
            _memoryCache.Set(key, value, TimeSpan.FromSeconds(60));
        }
        public static object GetMemory(string key)
        {
            if (!_memoryCache.TryGetValue(key, out object value)) {
                value = new DapperExtHelper<Posts>().GetAll();
                SetMemory(key, value);
            }
            return value;
        }
    }

五、数据库访问层

项目下建立DAL文件夹，存放如下类

UserDAL

public class UserDAL
{
    DapperHelper _db = new DapperHelper();
    public Users GetUserByLogin(string userName, string password) {
        string sql = "SELECT * FROM Users WHERE UserName=@userName AND Password = @password";
        var user = _db.QueryFirst<Users>(sql, new { userName, password });
        if (user == null)
        {
            return default;
        }
        else {
            return user;
        }
    }
    public Users GetUserById(int id)
    {
        var user = new DapperExtHelper<Users>().Get(id);
        if (user == null)
        {
            return default;
        }
        else
        {
            return user;
        }
    }
}

PostDAL

public class PostDAL
{
    DapperExtHelper<Posts> dapperExtHelper = new DapperExtHelper<Posts>();
    public List<Posts> GetPosts()
    {
        return dapperExtHelper.GetAll().ToList();
    }
    public Posts GetPost(int id)
    {
        return dapperExtHelper.Get(id);
    }
    public long Insert(Posts post)
    {
        return dapperExtHelper.Insert(post);
    }
}

六、控制层

项目下建立Controllers文件夹，存放如下类

LoginController

[EnableCors("any")]
[Route("[controller]")]
//[ApiController]
public class LoginController : ControllerBase
{
    [HttpPost]
    public Users Get(string n, string p) {
        var user =  new UserDAL().GetUserByLogin(n, p);
        return user;
    }
    [HttpGet("{id}")]
    public Users Get(int id)
    {
        var user = new UserDAL().GetUserById(id);
        return user;
    }
}

PostController

[EnableCors("any")]
[Route("[controller]")]
[ApiController]
public class PostController : ControllerBase
{
    [HttpGet]
    public List<Posts> Get() {
        var posts = (List<Posts>)MemoryHelper.GetMemory("post");
        posts.ForEach(p =>
                      {
                          p.CreateUserInfo = new UserDAL().GetUserById(p.CreateUser);
                          p.EditUserInfo = new UserDAL().GetUserById(p.EditUser);
                          p.LastReplyUserInfo = new UserDAL().GetUserById(p.LastReplyUser);
                      });
        return posts;
    }
}

七、解决服务注册与跨域请求

Startup类

public class Startup
{
    public Startup(IConfiguration configuration)
    {
        Configuration = configuration;
    }

    public IConfiguration Configuration { get; }

    // This method gets called by the runtime. Use this method to add services to the container.
    public void ConfigureServices(IServiceCollection services)
    {
        ConnectionOption.ConnectionString = "Data Source=.;database=MyBBSDb;uid=sa;pwd=1qaz2wsx";//添加数据库链接
        services.AddResponseCaching();
        services.AddCors(ac => ac.AddPolicy("any", ap => ap.AllowAnyOrigin().AllowAnyMethod().AllowAnyHeader()));
        services.AddControllers();
    }

    // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
    public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
    {
        if (env.IsDevelopment())
        {
            app.UseDeveloperExceptionPage();
        }

        app.UseHttpsRedirection();

        app.UseRouting();

        app.UseResponseCaching();

        app.UseAuthorization();

        app.UseCors();

        app.UseEndpoints(endpoints =>
                         {
                             endpoints.MapControllers();
                         });
    }
}

八、前端

文件布局如下：

html

存放于根目录

<!DOCTYPE html>
<html lang="en">

<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <link rel="stylesheet" href="css/Top.css">
    <script src="https://vuejs.org/js/vue.min.js"></script>
    <script src="https://unpkg.com/axios/dist/axios.min.js"></script>
    <script src="components/Top.js"></script>
    <title>Document</title>
</head>

<body>
    <div id="app">
        <top @showloginpad="showLogin" :username="userName"></top>
        <div id="showPad">
            <div class="nav">
                <a class="main-page"></a>
                <em></em>
                <a>单机游戏</a>
                <em></em>
                <a>《全面战争：三国》</a>
            </div>
            <div class="backgroud">
                <img src="http://att.3dmgame.com/att/forum/201905/19/120255ccxhvvggvtvconrr.png" alt="">
                <h2>《全面战争：三国》等你来战</h2>
            </div>
        </div>
        <div id="edit">
            <button>发帖</button>
        </div>
        <div id="post">
            <table>
                <thead>
                    <tr>
                        <td colspan="2">
                            <span>全部主题</span>
                            <span>最新</span>
                            <span>热门</span>
                            <span>热帖</span>
                            <span>精华</span>
                        </td>
                        <td>作者</td>
                        <td>回复/查看</td>
                        <td>最后发表</td>
                    </tr>
                </thead>
                <tbody>
                    <tr v-for="post in posts">
                        <td class="post-icon">
                            <img src="https://bbs.3dmgame.com/static/image/common/folder_new.gif" alt="">
                        </td>
                        <td class="post-name">
                            <em>[{{post.postType}}]</em>
                            <a href="">{{post.postTitle}}</a>
                        </td>
                        <td><span>{{post.createUserInfo.userName}}</span><span>{{post.createTime|time}}</span></td>
                        <td><span>{{post.replys}}</span><span>{{post.clicks}}</span></td>
                        <td><span>{{post.lastReplyUserInfo.userName}}</span><span>{{post.lastReplyTime|time}}</span></td>
                    </tr>
                </tbody>

            </table>
        </div>
        <div id="loginPad" v-show="isShowLogin">
            <span @click="showLogin(false)">×</span>
            <h3>用户登录</h3>
            <p>
                <input type="text" name="" id="" v-model="userNameInput" placeholder="输入用户名">
            </p>
            <p>
                <input type="password" name="" id="" v-model="passwordInput" placeholder="输入密码">
            </p>
            <p>
                <button @click="login">登录</button>
            </p>
            <p><i v-show="isShowWarn">*账号或密码错误</i></p>
        </div>
    </div>
    <script>



        var vm = new Vue({
            el:"#app",
            data:{
                posts:[]  ,
                isShowLogin:false,
                userNameInput:"",
                passwordInput:"",
                userName:"",
                isShowWarn:false,
            },
            mounted() {
                this.getPosts();
                if(localStorage["userName"]!=null && localStorage["userName"]!=""){
                    this.userName = localStorage["userName"];
                }
            },
            methods: {
                getPosts(){
                    axios.get("https://localhost:44398/post").then(res=>{
                        this.posts = res.data;
                    })
                },
                transTime(time){
                    time = time.replace("T"," ");
                    return time.substring(0,time.lastIndexOf(":"))
                },
                showLogin(isShow){
                    this.isShowLogin = isShow;
                },
                login(){
                    var data = new URLSearchParams();
                    data.append("n",this.userNameInput);
                    data.append("p",this.passwordInput);
                    axios.post("https://localhost:44398/login",data).then(res=>{
                        if (res.data!=""){
                            this.userName = res.data.userName;
                            this.isShowWarn = false;
                            this.isShowLogin = false;
                            localStorage["userName"] = this.userName;
                        }else {
                            this.isShowWarn = true;
                        }
                    })
                }
            },
            filters:{
                time(time){
                    time = time.replace("T"," ");
                    return time.substring(0,time.lastIndexOf(":"))
                }
            }
        })
    </script>
    <style>
        * {
            margin: 0;
            padding: 0;
        }
       

        #showPad {
            width: 98%;
            border: 1px solid #ccc;
            margin: 20px auto;
        }

        #showPad .nav {
            padding-top: 6px;
            padding-bottom: 6px;
            padding-left: 10px;
        }

        #showPad .nav .main-page {
            display: inline-block;
            width: 16px;
            background: url(https://bbs.3dmgame.com/static/image/common/search.png) no-repeat 0 0;
            overflow: hidden;
        }

        #showPad .nav a {
            display: inline-block;
            height: 30px;
            line-height: 30px;
            color: #666;
            font-size: 14px;
            overflow: hidden;
        }

        #showPad .nav a:active {
            color: #666;
        }

        #showPad .nav em {
            display: inline-block;
            width: 16px;
            height: 30px;
            line-height: 30px;
            overflow: hidden;
            background: url(https://bbs.3dmgame.com/static/image/common/pt_item.png) no-repeat 3px 10px;
        }

        #showPad .backgroud {
            width: 100%;
            text-align: center;
        }

        #showPad .backgroud h2{
          color:blue;
          margin-top: 10px;
          margin-bottom: 10px;
        }
        #edit
        {
            width: 98%;
            margin: auto;
            border:1px solid #fff;
            margin-bottom: 10px;
        }
        #edit button{
            background-color: rgb(19, 73, 175);
            color:#fff;
            border:0 none;
            width: 80px;
            height: 32px;
        }
        #post{
            width: 98%;
            margin: auto;
            border:1px solid #ccc;
            margin-bottom: 20px;
        }
        #post table {
            width: 100%;
            border-collapse: collapse;
        }
        #post table thead td:first-child{
            text-align: left;
        }
        #post table thead td{
            background-color: #eee;
            height: 40px;
            font-size: 14px;
            text-align: center;
        }
        #post table thead td span{
            margin-left: 20px;
            color:cornflowerblue;
        }
        #post table tbody td {
            height: 40px;
            font-size: 14px;
        }
        #post table tbody td.post-icon{
            width: 20px;
            padding: 0 6px;
        }
        #post table tbody td.post-name em{
            color:cornflowerblue;
            font-style: normal;
        }
        #post table tbody td.post-name a{
            color:#333;
            text-decoration: none;
        }
        #post table tbody td.post-name a:active{
            color:#333;
        }
        #post table tbody tr{
            border-bottom:1px solid #ccc;
        }
        #post table tbody tr:last-child{
            border-bottom:0 none;
            margin: 0 6px;
           
        }
        #post table tbody td span{
            display: block;
            text-align: center;
            font-size:12px;
        }
        #post table tbody td span:last-child{
            color:#999;
        }
        #loginPad{
            position: fixed;
            left: 40%;
            top: 20%;
            height: 266px;
            width: 388px;
            background-color: #fff;
            border:5px solid #ccc;
            border-radius: 3px;
        }
        #loginPad p{
            width: 80%;
            margin: auto;
        }
        #loginPad input{
            width: 100%;
            margin-top: 20px;
            height: 30px;
            border-radius: 3px;
            border:1px solid #666;
            display: block;
        }
        #loginPad button{
            width: 100%;
            margin-top: 20px;
            height: 36px;
            border-radius: 3px;
            border:1px solid brown;
            background-color: brown;
            display: block;
            color:#fff;
            cursor: pointer;
        }
        #loginPad h3{
            text-align: center;
            margin-top: 20px;
        }
        
        #loginPad > span{
            float: right;
            border:1px solid #ccc;
            display: block;
            width: 20px;
            height: 20px;
            text-align: center;
            line-height: 20px;
            cursor: pointer;
        }
        #loginPad p i{
            color:red;
            font-size: 14px;

        }
    </style>
</body>

</html>

存放于components文件中

Vue.component("top",{
    template:`
     <div id="header">
         <img src="https://ss0.bdstatic.com/70cFuHSh_Q1YnxGkpoWK1HF6hhy/it/u=3689032668,2645360714&fm=11&gp=0.jpg" alt="">
         <div class="user-login" v-if="username == '' || username == null ">
             <span @click="showLoginPad">登录</span>
             <span>注册</span>
         </div>
         <div class="user-login" v-else>
             <span>{{username}}</span>
             <span>注销</span>
         </div>
     </div>
    `,
    props:["username"],
    methods: {
        showLoginPad(){
            this.$emit("showloginpad",true);
        }
    },
 })

CSS

存放于css文件中

#header{
    height: 40px;
    width: 100%;
    text-align: right;
    background-color: #333;
    color: #fff;
    line-height: 40px;
   
}
#header img{
    width: 280px;
    height: 38px;
    margin-top: 1px;
    margin-left: 1px;
    float: left;
}

#header .user-login span {
    margin-right: 16px;
    cursor: pointer;
}

posted @ 2022-07-10 23:01 码农阿亮阅读(83) 评论(0) 编辑收藏举报

刷新页面返回顶部

码农阿亮

您知道的越多，您的思维越开阔！