C#利用Dapper实现对SQLite的操作

旧城等待， 2022-04-18 05:34 1405阅读 0赞

## 前言 ##

近几天借助C\#对SQLite的学习，算是对数据库刚入门吧，三天前写了一篇[C\#利用System.Data.SQLite实现对SQLite的操作][C_System.Data.SQLite_SQLite]，其中方法是基于System.Data.SQLite.dll的程序包，后来在youtube和infoworld上看到利用Dapper程序包对数据库操作更简单、快捷，在其基础之上自己又增加了一些用法，以下是参考视频和文档：  
参考视频：[https://www.youtube.com/watch?v=ayp3tHEkRc0][https_www.youtube.com_watch_v_ayp3tHEkRc0]  
参考文档：[https://www.infoworld.com/article/3025784/application-development/how-to-work-with-dapper-in-c.html][https_www.infoworld.com_article_3025784_application-development_how-to-work-with-dapper-in-c.html]

## ORM介绍 ##

介绍Dapper之前不得不提及ORM，ORM是什么呢？  
从字面理解，O是Object，对象；R是Relation，关系；M是Mapping，映射。所以，用一句话概括就是：**ORM是一种对象关系映射的技术。**  
我们平时所使用的大多数编程模型都是面向对象编程，就是把我们研究的问题抽象出来分解成一个个对象，对象有属性，方法等等，我们操作的是对象。  
而我们平时所使用的数据库是关系型数据库，他是从集合论等数学知识发展过来的，他讲究集合，映射，六大范式等等，跟我们的面向对象思想的封装继承多态不同，这两者之间有着根本性的差异。  
所以我们就存在一个问题，怎么去调和二者，如果能有一种技术把对象与数据库对应起来，我们编程就会方便许多。于是，ORM应运而生，他就是为了解决对象与数据库之间的差异所产生的一门技术，用他可以把对象与数据库建立一种映射，对于业务层的程序员，他不用管数据库的结构，他只需要专注于他的业务逻辑去操作对象。至于对象怎么去变成元组进入数据库，以及怎么从数据库取数据变成对象，那是ORM自动解决的。  
所以，ORM为对象关系映射器，它直接将数据库映射到C\#对象。  
有很多ORM框架可用，Dapper是其中之一，被称为ORM之王。

## Dapper介绍与安装 ##

**功能：**  
1、速度快，性能好;  
2、更少的代码行  
3、对象映射  
4、静态对象绑定  
5、动态对象绑定  
6、易于处理Sql语句  
7、易于处理存储过程  
8、直接操作IDBConnection类，该类直接向数据库提供平滑性和运行查询，而不是像在EF和ADO.NET中那样使用各种对象传递数据。  
9、多个查询支持  
10、支持存储过程  
11、批量处理数据插入  
12、允许基于多个输入获取多个数据

**安装：**  
鼠标右击项目下面的引用选项，进入NuGet，输入Dapper安装。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTczMjQzMA_size_16_color_FFFFFF_t_70]  
如果环境是在.NET4.5环境下进行的编译，最新版的Dapper可能与之不兼容，会出现以下报错：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTczMjQzMA_size_16_color_FFFFFF_t_70 1]  
你可以安装一个低版本的Dapper来规避此问题，我采用的是1.40.0版本的Dapper，亲测可用。

## 一、创建与数据库表对应的实体类 ##

*此前的文章忘记介绍此步骤，特意补充。*  
利用SQLite的可视化工具[DB Browser for SQLite][] 创建数据库文件SQLDB.db，并创建以“people”为名称的表，配置好数据类型，以ID作为主键。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTczMjQzMA_size_16_color_FFFFFF_t_70 2]  
然后我们需要创建一个实体类（POCO类），下面是与数据库SQLDB中people表相对应的实体类PeopleModel，其中包含表中含有的几个字段：

public class PeopleModel
        {
            public int ID { get; set; }
            public string MName { get; set; }
            public int Age { get; set; }
            public string Sex { get; set; }
            public string Phone { get; set; }
        }

## 二、配置路径 ##

App.config下配置数据库路径

<?xml version="1.0" encoding="utf-8" ?>
    <configuration>
        <startup> 
            <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
        </startup>
        <connectionStrings>
          <add name="sqlDB" connectionString="data source=H:\C#\DemoDB\DemoDB\bin\Debug\SQLDB.db;version=3;"/>
        </connectionStrings>
    </configuration>

在项目中访问connectionStrings标签

private static string LoadConnectString(string id = "sqlDB")
     {
         return ConfigurationManager.ConnectionStrings[id].ConnectionString;
     }

## 三、Dapper对于SQLite的基本操作 ##

**Dapper如何工作**  
主要包含三个步骤  
第一步：使用连接字符串创建一个IDBConnection对象；  
第二步：编写一个查询并将其存储在一个普通的字符串变量中；  
第三步：调用db.execute()并传递查询，完成。

**查询数据**  
Dapper中的扩展方法Query()能够让你从数据库中检索数据并填充到对象模型中。

public static List<PeopleModel> LoadPeople()
            {
                using (IDbConnection cnn = new SQLiteConnection(LoadConnectString()))
                {
                    cnn.Open();
                    var output = cnn.Query<PeopleModel>("select * from people", new DynamicParameters());
                    return output.ToList();
                }
            }

**保存数据**  
Dapper中的Execute()方法可以用于向数据库中插入，更新，删除数据。这个方法会返回一个整数，表示在执行查询时受到影响的行数。

public static void SavePerson(PeopleModel people)
            {
                using (IDbConnection cnn = new SQLiteConnection(LoadConnectString()))
                {
                    cnn.Execute("insert into people (MName,Age,Sex,Phone) values(@MName,@Age,@Sex,@Phone)", people);
                }
            }

**修改数据**

public static void UpdatePerson(PeopleModel people,int id)
            {
                using (IDbConnection cnn = new SQLiteConnection(LoadConnectString()))
                {
                    cnn.Execute("update people set MName = @MName,Age = @Age,Sex = @Sex,Phone = @Phone where ID="+id,people);
                }
            }

**删除数据**

public static int Delete(int id)
            {
                using (IDbConnection cnn = new SQLiteConnection(LoadConnectString()))
                {
                   return cnn.Execute("delete from people where ID="+id);
                }
            }

**批量操作**  
批量操作就把对象由单个改为多个即可，即把`PeopleModel people`改为`List<PeopleModel> peoples`。  
删除的操作是根据其主键ID，所以删除的批量操作视情况而定，根据DataGridViewRow的单元格属性选择需要进行的批量操作。

## 四、窗体代码 ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTczMjQzMA_size_16_color_FFFFFF_t_70 3]  
建立窗体如上图所示，右侧为DataGridViewRow控件。

**新建泛型集合**

List<PeopleModel> people = new List<PeopleModel>();

**绑定数据源**

private void WireUpPeopleList()
            {
                dataGridView1.DataSource = null;
                dataGridView1.DataSource = people;      
            }

**数据刷新函数**

private void LoadPeopleList()
            {
                people = SqliteHelper.LoadPeople();  
                WireUpPeopleList();
            }

**添加/修改**  
其中双击特定行会把数据返回至填单里，根据txtID里的信息判断是否进行修改行为。

private void btn_Add_Click(object sender, EventArgs e)
            {
                PeopleModel p = new PeopleModel();
                
                p.MName = txtName.Text;
                p.Age = Convert.ToInt32(txtAge.Text);
                p.Sex = btnBoy.Checked ? "男" : "女";
                p.Phone = txtPhone.Text;
                if (txtID.Text.Equals("添加时无编号"))
                {
                    SqliteHelper.SavePerson(p);
                }
                else
                {
                    SqliteHelper.UpdatePerson(p,Convert.ToInt32(txtID.Text));
                }
                txtName.Text = "";
                txtAge.Text = "";
                txtPhone.Text = "";
                txtID.Text = "添加时无编号";
            }

**双击操作**  
返回信息至填单，“添加”按钮变为“修改”

private void dataGridView1_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
            {
                DataGridViewRow row = dataGridView1.Rows[e.RowIndex];
                txtID.Text = row.Cells[0].Value.ToString();
                txtName.Text = row.Cells[1].Value.ToString();
                txtAge.Text = row.Cells[2].Value.ToString();
                txtPhone.Text = row.Cells[4].Value.ToString();
                if (row.Cells[3].Value.ToString().Equals("男"))
                {
                    btnBoy.Checked = true;
                }
                else
                {
                    btnGirl.Checked = true;
                }
                btn_Add.Text = "修改";
            }

**删除操作**

private void btn_Delete_Click(object sender, EventArgs e)
            {
                try
                {
                    if (this.dataGridView1.SelectedRows.Count > 0)
                    {
                        DialogResult result = MessageBox.Show("确定要删除吗？", "提示", MessageBoxButtons.OKCancel);
                        if (result == DialogResult.Cancel)
                        {
                            return;
                        }
                        int x = int.Parse(dataGridView1.SelectedRows[0].Cells[0].Value.ToString());
                        SqliteHelper.Delete(x);
                        LoadPeopleList();
                    }
                    else
                    {
                        MessageBox.Show("请选择需要删除的行", "提示", MessageBoxButtons.OK);
                    }
                }
                catch(Exception x)
                {
                    MessageBox.Show(this, x.Message, "Error", MessageBoxButtons.OK);
                }
                
            }

## 五、Dapper的复杂操作 ##

**查询的In操作**

public static List<PeopleModel> QueryIn()
    {
        using (IDbConnection connection = new SqlConnection(connectionString))
        {
            var sql = "select * from people where id in @ids";
            //参数类型是Array的时候，dappper会自动将其转化
            return connection.Query<Person>(sql, new { ids = new int[2] { 1, 2 }, }).ToList();
        }
    }
    
    public static List<PeopleModel> QueryIn(int[] ids)
    {
        using (IDbConnection connection = new SqlConnection(connectionString))
        {
            var sql = "select * from people where id in @ids";
            //参数类型是Array的时候，dappper会自动将其转化
            return connection.Query<Person>(sql, new { ids }).ToList();
        }
    }

**处理多个表**  
我们也会遇到一次需要处理多个表的需求，例如一次查询多个表或者以外键获取数据  
1、我们可以用Dapper同时访问多个表，并且十分平滑  
2、传递对象列表，Dapper本事可以识别出是批量插入  
3、根据各种参数获取数据，Dapper会自动将数组转换成CSV,并返回一个List对象  
![在这里插入图片描述][20181109022439941.png]  
在两个表上同时执行CRUD操作，使用相同的ContactId,即Contacts表中的主键，Address表中的外键，现在让我们通过传递Id从Contacts表和Address表中获取多条数据：

static IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLDB"].ConnectionString);
    string query = "select * from contacts where id = @id ; select * from address where ContactId = @id;";
    using (var multipleresult = db.QueryMultiple(query, new { id = 1 }))
                {
                    var contact = multipleresult.Read<Contact>().SingleOrDefault();
                    var Addresses = multipleresult.Read<Address>().ToList();
                    if (contact != null && Addresses != null)
                    {
                        contact.Addresses.AddRange(Addresses);
                    }
                }

**使用存储过程访问数据库**  
以下为sql的一个存储过程：

Use ContactDB
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    CREATE PROCEDURE [dbo].[sp_GetContact_Address]  
        @Id int 
    AS
    BEGIN
    select * from contacts where id = @Id ;   
    select * from Address where ContactId = @Id; 
    END
    GO

用Dapper调用存储过程

第一步同上

在这里不需要写任何的query语句，相反的我们将调用数据库内的存储过程  
在这里将做的更改是传递一个存储过程的名称而不是查询语句，并传递一个名称为command type的参数。

using (var multipleresult = db.QueryMultiple("sp_GetContact_Address", new { id = 1 }, commandType: CommandType.StoredProcedure))
                {
                    var contact = multipleresult.Read<Contact>().SingleOrDefault();
                    var Addresses = multipleresult.Read<Address>().ToList();
                    if (contact != null && Addresses != null)
                    {
                        contact.Addresses.AddRange(Addresses);
                    }
                }

[C_System.Data.SQLite_SQLite]: https://blog.csdn.net/weixin_41732430/article/details/83753628
[https_www.youtube.com_watch_v_ayp3tHEkRc0]: https://www.youtube.com/watch?v=ayp3tHEkRc0
[https_www.infoworld.com_article_3025784_application-development_how-to-work-with-dapper-in-c.html]: https://www.infoworld.com/article/3025784/application-development/how-to-work-with-dapper-in-c.html
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTczMjQzMA_size_16_color_FFFFFF_t_70]: /images/20220418/591aa3f86ebf42f8b0a5c873ba633e77.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTczMjQzMA_size_16_color_FFFFFF_t_70 1]: /images/20220418/bea668cd756e48879a3abce229264929.png
[DB Browser for SQLite]: http://sqlitebrowser.org/
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTczMjQzMA_size_16_color_FFFFFF_t_70 2]: /images/20220418/d3dafce6c53b47cebb0600e9fadd7c3e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTczMjQzMA_size_16_color_FFFFFF_t_70 3]: /images/20220418/010a3b8ff83c41989765e86c485c912c.png
[20181109022439941.png]: /images/20220418/98b6a851444444dd98e4749c4b343fce.png