上篇文章我们讲了如在在实际项目开发中使用Git来进行代码的版本控制,当然介绍的都是比较常用的功能。今天我再带着大家一起熟悉下一个ORM框架Dapper,实例代码的演示编写完成后我会通过Git命令上传到GitHub上,正好大家可以再次熟悉下Git命令的使用,来巩固上篇文章的知识。本篇文章已经收入.NET Core实战项目之CMS 第一章 入门篇-开篇及总体规划 有兴趣的朋友可以加入.NET Core项目实战交流群637326624 进行交流。
作者:依乐祝 原文地址:https://cloud.tencent.com/developer/article/1367126
Dapper是.NET下一个轻量级的ORM框架,它和Entity Framework或Nhibnate不同,属于轻量级的,并且是半自动的。也就是说实体类都要自己写。它没有复杂的配置文件,一个单文件就可以了。Dapper通过扩展你的IDbConnection来进行工作的。如果你想了解更多内容的话请点击这里。
前面几篇文章我们进行介绍的时候都是手动在代码里面创建的模拟数据,这篇文章我们就结合Dapper来从数据库进行相关的操作。为了演示的方便,这里的实例代码我们就使用一个简单地asp.net core控制台程序来进行。
git checkout Master
切换回Mater分支,然后输入git checkout -b Sample05
创建一个新的名为“Sample05”的分支,如下所示:
CREATE TABLE dbo.content(
id IDENTITY(1,1) NOT NULL,
title(50) NOT NULL,
content(max) NOT NULL,
status NOT NULL,
add_time NOT NULL,
modify_time 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
CREATE TABLE dbo.comment(
id IDENTITY(1,1) NOT NULL,
content_id NOT NULL,
content(512) NOT NULL,
add_time 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
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; }
}
public class Comment
{
/// <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;
}
/// <summary>
/// 测试插入单条数据
/// </summary>
static void test_insert()
{
var content = new Content
{
title = "标题1",
content = "内容1",
};
using (var conn = new SqlConnection("Data Source=127.0.0.1;User ID=sa;Password=1;Initial Catalog=Czar.Cms;Pooling=true;Max Pool Size=100;"))
{
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}条数据!");
}
}
/// <summary>
/// 测试一次批量插入两条数据
/// </summary>
static void test_mult_insert()
{
List<Content> contents = new List<Content>() {
new Content
{
title = "批量插入标题1",
content = "批量插入内容1",
},
new Content
{
title = "批量插入标题2",
content = "批量插入内容2",
},
};
using (var conn = new SqlConnection("Data Source=127.0.0.1;User ID=sa;Password=1;Initial Catalog=Czar.Cms;Pooling=true;Max Pool Size=100;"))
{
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, contents);
Console.WriteLine($"test_mult_insert:插入了{result}条数据!");
}
}
然后到数据库查看下表中的数据如下:
/// <summary>
/// 测试删除单条数据
/// </summary>
static void test_del()
{
var content = new Content
{
id = 2,
};
using (var conn = new SqlConnection("Data Source=127.0.0.1;User ID=sa;Password=1;Initial Catalog=Czar.Cms;Pooling=true;Max Pool Size=100;"))
{
string sql_insert = @"DELETE FROM [Content]
WHERE (id = @id)";
var result = conn.Execute(sql_insert, content);
Console.WriteLine($"test_del:删除了{result}条数据!");
}
}
/// <summary>
/// 测试一次批量删除两条数据
/// </summary>
static void test_mult_del()
{
List<Content> contents = new List<Content>() {
new Content
{
id=3,
},
new Content
{
id=4,
},
};
using (var conn = new SqlConnection("Data Source=127.0.0.1;User ID=sa;Password=1;Initial Catalog=Czar.Cms;Pooling=true;Max Pool Size=100;"))
{
string sql_insert = @"DELETE FROM [Content]
WHERE (id = @id)";
var result = conn.Execute(sql_insert, contents);
Console.WriteLine($"test_mult_del:删除了{result}条数据!");
}
}
然后去数据库里查看,发现主键为2,3,4的数据都已经被删除了,如下图所示:
/// <summary>
/// 测试修改单条数据
/// </summary>
static void test_update()
{
var content = new Content
{
id = 5,
title = "标题5",
content = "内容5",
};
using (var conn = new SqlConnection("Data Source=127.0.0.1;User ID=sa;Password=1;Initial Catalog=Czar.Cms;Pooling=true;Max Pool Size=100;"))
{
string sql_insert = @"UPDATE [Content]
SET title = @title, content = @content, modify_time = GETDATE()
WHERE (id = @id)";
var result = conn.Execute(sql_insert, content);
Console.WriteLine($"test_update:修改了{result}条数据!");
}
}
/// <summary>
/// 测试一次批量修改多条数据
/// </summary>
static void test_mult_update()
{
List<Content> contents = new List<Content>() {
new Content
{
id=6,
title = "批量修改标题6",
content = "批量修改内容6",
},
new Content
{
id =7,
title = "批量修改标题7",
content = "批量修改内容7",
},
};
using (var conn = new SqlConnection("Data Source=127.0.0.1;User ID=sa;Password=1;Initial Catalog=Czar.Cms;Pooling=true;Max Pool Size=100;"))
{
string sql_insert = @"UPDATE [Content]
SET title = @title, content = @content, modify_time = GETDATE()
WHERE (id = @id)";
var result = conn.Execute(sql_insert, contents);
Console.WriteLine($"test_mult_update:修改了{result}条数据!");
}
}
现在我们执行下测试代码看下结果吧
再到数据库中查看下数据,上步骤5中最后一张图相比较
/// <summary>
/// 查询单条指定的数据
/// </summary>
static void test_select_one()
{
using (var conn = new SqlConnection("Data Source=127.0.0.1;User ID=sa;Password=1;Initial Catalog=Czar.Cms;Pooling=true;Max Pool Size=100;"))
{
string sql_insert = @"select * from [dbo].[content] where id=@id";
var result = conn.QueryFirstOrDefault<Content>(sql_insert, new { id=5});
Console.WriteLine($"test_select_one:查到的数据为:");
}
}
/// <summary>
/// 查询多条指定的数据
/// </summary>
static void test_select_list()
{
using (var conn = new SqlConnection("Data Source=127.0.0.1;User ID=sa;Password=1;Initial Catalog=Czar.Cms;Pooling=true;Max Pool Size=100;"))
{
string sql_insert = @"select * from [dbo].[content] where id in @ids";
var result = conn.Query<Content>(sql_insert, new { ids=new int[] { 6,7} });
Console.WriteLine($"test_select_one:查到的数据为:");
}
}
然后我们打上断点然后去看下结果吧!这里图片我没有截成功,所以就不贴了。
public class ContentWithCommnet
{
/// <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<Comment> comments { get; set; }
}
然后就是测试代码,运行的查询测试代码如下:查询id为5的文章,文章是包含评论列表的
代码如下:
static void test_select_content_with_comment()
{
using (var conn = new SqlConnection("Data Source=127.0.0.1;User ID=sa;Password=1;Initial Catalog=Czar.Cms;Pooling=true;Max Pool Size=100;"))
{
string sql_insert = @"select * from content where id=@id;
select * from comment where content_id=@id;";
using (var result = conn.QueryMultiple(sql_insert, new { id = 5 }))
{
var content = result.ReadFirstOrDefault<ContentWithComment>();
content.comments = result.Read<Comment>();
Console.WriteLine($"test_select_content_with_comment:内容5的评论数量{content.comments.Count()}");
}
}
}
结果如下所示,调试的代码没法截图我也很无奈。
GitHub的测试源码已经上传,https://github.com/yilezhu/Czar.Cms/tree/Sample05 放在Czar.Cms的Sample05分支上面。大家可以参考下,觉得有用的话记得star哦!
本文给大家演示了Dapper的常用方法,不过都是通过同步的方式进行操作的,如果你想使用异步的话可以自行进行测试。文中的大部分内容都有截图,个别调试无法截图的大伙可以自行调试查看!相信通过本文的实例讲解,大伙应该能够使用dapper进行相应的开发!下一篇文章我们将进行vue的讲解!当然也只是进行很浅层次的讲解。因为我是一个后端,也是抱着学习的态度来进行vue的记录的!主要是以快速上为主。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。