使用Dapper访问SQL Server数据库

 

对应Demo程序名：DapperDemo

 

准备工作：为项目安装Dapper类库

方法一：项目中添加：项目名右键；Manage NuGet Packages；搜索Dappe；点击安装

方法二：在NuGet官网（https://www.nuget.org/）查询类库的安装命令：

              搜索Dapper；找到安装命令（Install-Package Dapper -Version 1.60.6）；在Visual Studio中打开包管理工具控制台（Tools→NuGget Package Manager→Packages Manager Console）；

              选中默认的项目（DapperDemo）并输入安装命令；回车

             

 

一、创建实体类，例如Person.cs

作用：Person类对应数据库中的Person表，各字段对应数据库表的各列。

    public class Person
    {
        public int Id { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string EmailAddress { get; set; }
        public DateTime CreatedOn { get; set; }
        public string IpAddress { get; set; }
        
        //定义一个属性，用来计算所有属性
        /// <summary>
        /// 计算属性，返回用户的详细信息
        /// </summary>
        public string Display
        {
            get
            {
                return $"{FirstName} {LastName} ({EmailAddress}) {IpAddress}";
            }
        }
    }

 

二、创建数据库连接帮助类DbHelper.cs

作用：从配置文件中读取数据库连接字符串。

    public class DbHelper
    {
        /// <summary>
        /// 从配置文件中读取数据库连接字符串
        /// </summary>
        public static string ConnectionString
        {
            get { return ConfigurationManager.ConnectionStrings["Dbconn"].ConnectionString; }
        }
    }

注：解决ConfigurationManager标红：添加引用Reference→Add Reference→System.Configuration，再在程序中引用using System.Configuration;

 

三、数据库连接配置（App.config）

作用：在配置文件中存放数据库连接信息，如需修改，只修改配置文件而不用修改源代码，符合开闭原则。

在<configuration>节点下添加<connectionStrings>节点，存放数据库连接信息：

<configuration>
    <connectionStrings>
      <add name="Dbconn" connectionString="Server=127.0.0.1;Database=master;User Id=sa;Password=Phoenix@2019;" />
    </connectionStrings>
</configuration>

 

四、创建数据库访问类PersonService.cs

作用：连接并访问数据库，读取或写入到数据库。

    public class PersonService
    {
        /// <summary>
        /// 根据用户姓氏查询用户集合 
        /// </summary>
        /// <param name="lastName">姓氏</param>
        /// <returns></returns>
        public List<Person> FindListByLastName(string lastName)
        {
            //SELECT * FROM Person WHERE LastName='Pye'
            using (IDbConnection db = new SqlConnection(DbHelper.ConnectionString))
            {
                //where后直接跟变量来查询，不能避免SQL注入，应使用后边一种写法
                //string sql = $"SELECT * FROM Person WHERE LastName='{lastName}'";
                //IEnumerable<Person> list = db.Query<Person>(sql);

                //Dapper应对SQL注入
                //where后边限定属性LastName的必须是@LastName格式，且Query中添加匿名对象new { LastName = lastName}，lastName在这里传入
                string sql = $"SELECT * FROM Person WHERE LastName=@LastName";
                IEnumerable<Person> list = db.Query<Person>(sql, new { LastName = lastName});
                return list.ToList();
            }
            //using语句块结束后自动释放资源，释放数据库连接。
        }
        
        /// <summary>
        /// 写入一条用户数据
        /// </summary>
        /// <param name="person">用户信息实体</param>
        /// <returns></returns>
        public bool Insert(Person person)
        {
            using (IDbConnection db = new SqlConnection(DbHelper.ConnectionString))
            {
                string sql = "INSERT INTO Person(FirstName, LastName, EmailAddress, CreatedOn) VALUES(@FirstName, @LastName, @EmailAddress, @CreatedOn)";
                int result = db.Execute(sql, person);
                return result > 0;  //简化的转换写法
            }
        }

        /// <summary>
        /// 删除一条用户数据
        /// </summary>
        /// <param name="personId">用户Id</param>
        /// <returns></returns>
        public bool Delete(int personId)
        {
            using (IDbConnection db = new SqlConnection(DbHelper.ConnectionString))
            {
                string sql = "DELETE FROM Person WHERE Id = @Id";  //where很重要！！！
                int result = db.Execute(sql, new { Id = personId });
                return result > 0;  //简化的转换写法
            }
        }

        /// <summary>
        /// 更新一条用户数据
        /// </summary>
        /// <param name="person">用户信息实体</param>
        /// <returns></returns>
        public bool Update(Person person)
        {
            using (IDbConnection db = new SqlConnection(DbHelper.ConnectionString))
            {
                string sql =
                    "UPDATE Person SET FirstName = @FirstName, LastName = @LastName, EmailAddress = @EmailAddress WHERE Id = @Id";  //where很重要！！！
                int result = db.Execute(sql, person);
                return result > 0;  //简化的转换写法
            }
        }

        /// <summary>
        /// 根据用户Id查询用户
        /// </summary>
        /// <param name="personId">用户Id</param>
        /// <returns></returns>
        public Person FindByPersonId(int personId)
        {
            using (IDbConnection db = new SqlConnection(DbHelper.ConnectionString))
            {
                string sql = $"SELECT * FROM Person WHERE Id=@Id";
                IEnumerable<Person> list = db.Query<Person>(sql, new { Id = personId });
                return list.FirstOrDefault();
            }
        }
    }

 

五、Dashboard界面

作用：分别实现增删改查方法。

后台代码中结合具体数据，调用PersonService中的访问数据库的方法，实现相关业务功能。

        private void btnSearch_Click(object sender, EventArgs e)
        {
            PersonService ps = new PersonService();
            List<Person> result = ps.FindListByLastName(txtSearchLastName.Text);
            lstSearchResult.DataSource = result;
            lstSearchResult.DisplayMember = "Display";
        }

        private void btnCreate_Click(object sender, EventArgs e)
        {
            PersonService ps = new PersonService();
            Person person = new Person()
            {
                FirstName = txtFirstName.Text,
                LastName = txtLastName.Text,
                EmailAddress = txtEmailAddress.Text,
                CreatedOn = DateTime.Now
            };

            var success = ps.Insert(person);
            MessageBox.Show(success ? "操作成功" : "操作失败");
        }

        private void btnDelete_Click(object sender, EventArgs e)
        {
            PersonService ps = new PersonService();
            int personId = Convert.ToInt32(txtIndexPersonId.Text);
            var success = ps.Delete(personId);
            MessageBox.Show(success ? "操作成功" : "操作失败");
        }

        private void btnUpdate_Click(object sender, EventArgs e)
        {
            PersonService ps = new PersonService();
            Person person = new Person()
            {
                Id = Convert.ToInt32(txtIndexPersonId.Text),
                FirstName = txtFirstName.Text,
                LastName = txtLastName.Text,
                EmailAddress = txtEmailAddress.Text,
                CreatedOn = DateTime.Now
            };

            var success = ps.Update(person);
            MessageBox.Show(success ? "操作成功" : "操作失败");
        }

        private void btnIndex_Click(object sender, EventArgs e)
        {
            PersonService ps = new PersonService();
            int personId = Convert.ToInt32(txtIndexPersonId.Text);
            var person = ps.FindByPersonId(personId);

            txtFirstName.Text = person.FirstName;
            txtLastName.Text = person.LastName;
            txtEmailAddress.Text = person.EmailAddress;
        }

前台界面如图：

 

六、拓展TODO

可以不使用SQL语句，但要使用Dapper的扩展工具。

 

使用Dapper连接MySQL数据库

要另外使用MySql.Data库

数据库连接字符串格式为

 

 

 

 

使用Dapper连接SQLite数据库

要另外使用System.Data.SQLite库

对应的DEMO程序名：

 

 

 

 

通过IP地址连接远程共享文件夹中的SQLite数据库时，调试遇到的两个问题：

1.远程IP地址解析为C盘的地址，例如"\\192.168.1.10\MES\test.db"解析为："C:\192.168.1.10\MES\test.db"

解决方法：字符串格式问题，应写为@"\\192.168.1.10\MES\test.db"

2.能够访问到远程共享文件夹后，可以新建数据库，但_dbConnection.Open();打开数据库时报错：unable to open database file

解决方法：首先尝试将远程文件夹的权限设为EveryOne拥有全部权限，未能解决；

然后尝试_dbConnection = new SQLiteConnection($"Data Source={dbFilePath};Version=3;", true); 设置parseViaFramework参数为true，问题解决。

parseViaFramework：Non-zero to parse the connection string using the built-in (i.e. framework provided) parser when opening the connection.非零，以在打开连接时使用内置（即提供的框架）解析器解析连接字符串。

 

 

 

 

 

 

 

 

七、参考

https://study.163.com/course/courseLearn.htm?courseId=1004855012#/learn/video?lessonId=1050368325&courseId=1004855012