Dapper的使用

前文：

ORM框架Dapper，数据库常用方式与Dapper的关联查询

1.Commet.cs

using System;
using System.Collections.Generic;
using System.Text;

namespace Sample05
{
    public class Commet
    {
        /// <summary>
        /// 主键
        /// </summary>
        public int id { get; set; }
        /// <summary>
        /// 文章id
        /// </summary>
        public int content_id { get; set; }
        /// <summary>
        /// 评论内容
        /// </summary>
        public string content { get; set; }
        /// <summary>
        /// 添加时间
        /// </summary>
        public DateTime add_time { get; set; } = DateTime.Now;
    }
}

2.content.cs

using System;
using System.Collections.Generic;
using System.Text;

namespace Sample05
{
    public class Content
    {
        /// <summary>
        /// 主键
        /// </summary>
        public int id { get; set; }

        /// <summary>
        /// 标题
        /// </summary>
        public string title { get; set; }
        /// <summary>
        /// 内容
        /// </summary>
        public string content { get; set; }
        /// <summary>
        /// 状态 1正常 0删除
        /// </summary>
        public int status { get; set; }
        /// <summary>
        /// 创建时间
        /// </summary>
        public DateTime add_time { get; set; } = DateTime.Now;
        /// <summary>
        /// 修改时间
        /// </summary>
        public DateTime? modify_time { get; set; }
    }
}

3.ContentWithComment.cs

using System;
using System.Collections.Generic;
using System.Text;

namespace Sample05
{
    public class ContentWithComment
    {
        /// <summary>
        /// 主键
        /// </summary>
        public int id { get; set; }

        /// <summary>
        /// 标题
        /// </summary>
        public string title { get; set; }
        /// <summary>
        /// 内容
        /// </summary>
        public string content { get; set; }
        /// <summary>
        /// 状态 1正常 0删除
        /// </summary>
        public int status { get; set; }
        /// <summary>
        /// 创建时间
        /// </summary>
        public DateTime add_time { get; set; } = DateTime.Now;
        /// <summary>
        /// 修改时间
        /// </summary>
        public DateTime? modify_time { get; set; }
        /// <summary>
        /// 文章评论
        /// </summary>
        public IEnumerable<Commet> comments { get; set; }
    }
}

4.App.config

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <connectionStrings>
    <add name="master" connectionString="Server=.;Database=CmsDate;User Id=sa;Password=123;Connection Timeout=3000;"/>
  </connectionStrings>
</configuration>

5.sql表脚本

IF EXISTS(select * from sysobjects where name='content')
drop table content
go

CREATE TABLE [dbo].[content](
 [id] [int] IDENTITY(1,1) NOT NULL,
 [title] [nvarchar](50) NOT NULL,
 [content] [nvarchar](max) NOT NULL,
 [status] [int] NOT NULL,
 [add_time] [datetime] NOT NULL,
 [modify_time] [datetime] NULL,
 CONSTRAINT [PK_Content] PRIMARY KEY CLUSTERED 
(
 [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

ALTER TABLE [dbo].[content] ADD  CONSTRAINT [DF_Content_status]  DEFAULT ((1)) FOR [status]
GO

ALTER TABLE [dbo].[content] ADD  CONSTRAINT [DF_content_add_time]  DEFAULT (getdate()) FOR [add_time]
GO

IF EXISTS(select * from sysobjects where name='comment')
drop table content
go

CREATE TABLE [dbo].[comment](
 [id] [int] IDENTITY(1,1) NOT NULL,
 [content_id] [int] NOT NULL,
 [content] [nvarchar](512) NOT NULL,
 [add_time] [datetime] NOT NULL,
 CONSTRAINT [PK_comment] PRIMARY KEY CLUSTERED 
(
 [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[comment] ADD  CONSTRAINT [DF_comment_add_time]  DEFAULT (getdate()) FOR [add_time]
GO

6.Program.cs

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Linq;
using Dapper;

namespace Sample05
{
    class Program
    {
        public static string connectionStr = ConfigurationManager.ConnectionStrings["master"].ConnectionString;
        static void Main(string[] args)
        {
            //Test_Insert();
            //Test_Mult_Insert();
            //Tsst_mult_Del();
            //Test_Update();
            //Tset_mult_Update();
            //Test_Select();
           // Test_mult_Select();
            //Test_Comment_insert();
            Test_select_content_with_comment();
            //Tset_Del();
            Console.ReadKey();
        }

        /// <summary>
        /// 测试单条数据导入数据库
        /// </summary>
        public static void Test_Insert()
        {
            
            //Content content = new Content;
            //content.title = "标题1";
            //content.content = "内容1";
            var content = new Content { title = "标题1",content="内容1", };
            using (var conn =new SqlConnection(connectionStr))
            {
                conn.Open();
                string sql_insert = @"INSERT INTO [Content](title, [content], status, add_time, modify_time) VALUES (@title,@content,@status,@add_time,@modify_time)";
                var result = conn.Execute(sql_insert,content);
                Console.WriteLine($"Test_Insert：插入了{result}条数据！");
                conn.Close();
            }
        }

        /// <summary>
        /// 测试批量数据导入数据库
        /// </summary>
        public static void Test_Mult_Insert()
        {

            List<Content> content = new List<Content>()
            {
                new Content
                            { title="批次插入标题1",content="批量插入内容1" },
                new Content
                            { title = "批次插入标题2", content = "批量插入内容2" } };

            using (var conn =new SqlConnection(connectionStr))
            {
                conn.Open();
                string sql_insert = @"INSERT INTO [Content](title, [content], status, add_time, modify_time)
                                    VALUES   (@title,@content,@status,@add_time,@modify_time)";

                var result = conn.Execute(sql_insert,content);
                Console.WriteLine($"Test_Mult_Insert：批量导入{result}条数据");

                conn.Close();
            }

        }
        /// <summary>
        /// 测试单条删除数据
        /// </summary>
        public static void Tset_Del()
        {
            var content = new Content { id = 2 };

            using (var conn =new SqlConnection(connectionStr))
            {
                string sql_Del = @"DELETE [Content] WHERE (id = @id)";
                conn.Open();
                var result = conn.Execute(sql_Del, content);
                Console.WriteLine($"Test_Del：共删除了{result}条数据");
                conn.Close();
                
            }
        }
        /// <summary>
        /// 数据批量删除
        /// </summary>
        public static void Tsst_mult_Del()
        {
            List<Content> content = new List<Content>()
            {
                new Content { id = 4 },
                new Content { id = 5 }

            };

            using (var conn=new SqlConnection(connectionStr))
            {
                string sql_Del = @"DELETE [Content] WHERE (id = @id)";
                conn.Open();
                var result = conn.Execute(sql_Del,content);
                Console.WriteLine($"Tsst_mult_Del：共删除了{result}条数据");
                conn.Close();
            }
        }

        /// <summary>
        /// 单条数据更新
        /// </summary>
        public static void Test_Update()
        {
            var content = new Content { id = 6 ,title="被更新的数据。",content="被更新的内容。"};

            using (var conn = new SqlConnection(connectionStr))
            {
                string sql_Update = @"UPDATE [Content] SET title = @title, [content] = @content, modify_time = GETDATE() WHERE (id = @id)";
                conn.Open();
                var result = conn.Execute(sql_Update,content);
                Console.WriteLine($"Test_Update：更新了{result}条数据");
                conn.Close();
            }
        }

        /// <summary>
        /// 批量更新多条数据
        /// </summary>
        public static void Tset_mult_Update()
        {
            List<Content> content = new List<Content>()
            {
                new Content{ id = 7 ,title="被更新的数据1。",content="被更新的内容1。"},
                new Content{ id = 8 ,title="被更新的数据2。",content="被更新的内容2。"},
            };
            using (var conn=new SqlConnection(connectionStr))
            {
                string sql_Update = @"UPDATE [Content] SET title = @title, [content] = @content, modify_time = GETDATE() WHERE (id = @id)";
                conn.Open();
                var result = conn.Execute(sql_Update,content);
                Console.WriteLine($"Tset_mult_Update：批量更新了{result}条数据");
                conn.Close();
            }
        }

        /// <summary>
        /// 单条数据查询
        /// </summary>
        public static void Test_Select()
        {
            using (var conn =new SqlConnection(connectionStr))
            {
                string sql_select = @"SELECT * FROM [Content] WHERE id = @id";
                conn.Open();
                var result = conn.QueryFirstOrDefault<Content>(sql_select, new { id = 8 });
                Console.WriteLine($"Test_Select：查到数据比编号为{result.id}");
                conn.Close();
            }
        }

        /// <summary>
        /// 查询多条数据
        /// </summary>
        public static void Test_mult_Select()
        {
            using (var conn = new SqlConnection(connectionStr))
            {
                string sql_select = @"SELECT * FROM [Content] WHERE id in @i";
                var result = conn.Query<Content>(sql_select, new { i = new int[] { 6, 7, 8 } });
                conn.Open();
                Console.WriteLine($"Test_mult_Select：共查询到{result.Count()}条数据");
                conn.Close();
            }
        }

        /// <summary>
        /// 给comment表插入数据
        /// </summary>
        public static void Test_Comment_insert()
        {
            List<Commet> commet = new List<Commet>()
            {
                new Commet{ content_id=1, content="测试添加0" },
                new Commet{ content_id=2, content="测试添加1" },
                new Commet{ content_id=3, content="测试添加3" },
                new Commet{ content_id=5, content="测试评价5" },
                new Commet{ content_id=5, content="测试评价5" }
            };
            using (var conn = new SqlConnection(connectionStr))
            {
                string sql_insert = @"INSERT INTO [comment](content_id, content, add_time) VALUES (@content_id, @content, @add_time)";
                conn.Open();
                var result = conn.Execute(sql_insert,commet);
                Console.WriteLine($"Test_Comment_insert：插入了{result}条数据");
                conn.Close();
            }
        }

        /// <summary>
        /// Dapper的关联查询
        /// </summary>
        public static void Test_select_content_with_comment()
        {
            using (var conn = new SqlConnection(connectionStr))
            {
                string sql_select_count = @"SELECT * FROM [content] WHERE id = @id; 
                                            SELECT * FROM [comment] WHERE id = @id;";
                conn.Open();
                using (var result = conn.QueryMultiple(sql_select_count, new { id = 1 }))
                {
                    var cotent = result.ReadFirstOrDefault<ContentWithComment>();
                    cotent.comments = result.Read<Commet>();
                    foreach (var item in cotent.comments)
                    {
                        Console.WriteLine($"Test_select_content_with_comment：内容编号为{item.id}的评论数量为{cotent.comments.Count()}");
                    }
                   
                }

                conn.Close();
            }
        }
    }
}

 

*注*：材料整理来源于大佬依乐祝博客（https://www.cnblogs.com/yilezhu/p/10024091.html）

*再注*：个人笔记，不喜勿喷