Dapper 常规用法
安装Neget包
安装Dapper和其他的数据库
.Net FX
Install-Package Dapper 1.50.2
Install-Package MySql.Data 6.9.12
Install-Package Oracle.ManagedDataAccess 19.10.0
Install-Package System.Data.SQLite
.Net Core
Install-Package Dapper
Install-Package MySql.Data
Install-Package Oracle.ManagedDataAccess.Core
Install-Package System.Data.SQLite.Core
操作数据库
查询数据
using (var conn = new SqlConnection("Data Source=127.0.0.1;Initial Catalog=TestDB;User Id=sa;Password=123456;"))
{
var data = conn.Query<User>("SELECT * from User");
}
增删改获取影响行数
using (var conn = new SqlConnection("Data Source=127.0.0.1;Initial Catalog=TestDB;User Id=sa;Password=123456;"))
{
int a = conn.Execute("delete from User");
}
事务
using (var conn = new SqlConnection("Data Source=127.0.0.1;Initial Catalog=TestDB;User Id=sa;Password=123456;"))
{
IDbTransaction transaction = conn.BeginTransaction();
var result = conn.Execute("delete from User", null, transaction);
//回滚
transaction.Rollback();
//提交
transaction.Commit();
}
其他数据库类型
using Dapper;
using MySql.Data.MySqlClient;
using Oracle.ManagedDataAccess.Client;
using System;
using System.Data.SqlClient;
using System.Data.SQLite;
namespace DBDapperNetCore
{
class Program
{
static void Main(string[] args)
{
using (var conn = new SqlConnection("Data Source=127.0.0.1;Initial Catalog=TestDB;User Id=sa;Password=123456;"))
{
var data = conn.Query<User>("SELECT * from User");
int a = conn.Execute("delete from User");
}
using (var conn = new OracleConnection("Data Source=127.0.0.1:1521/orcl;User Id=root;Password=123456;"))
{
}
using (var conn = new MySqlConnection("Database=TestDB;Data Source=127.0.0.1;User Id=root;Password=123456;port=30001;pooling=false;SslMode=None;old Guids=true;Charset=utf8;"))
{
}
using (var conn = new SQLiteConnection(@"Data Source=F:\test.db;database=main;"))
{
}
Console.ReadKey();
}
}
}
连接不同类型的数据库
public class UserRepository : DbConnectionMSSQLRepositoryBase
{
public UserRepository(IDbConnectionFactory dbConnectionFactory)
: base(dbConnectionFactory) { }
public IEnumerable<int> GetAll()
{
const string sql = @"SELECT * FROM TABLE";
return base.DbConnection.Query<int>(sql);
}
}
public class DapperDbConnectionFactory : IDbConnectionFactory
{
public IDbConnection CreateDbConnection(DatabaseConnectionName connectionName)
{
string connectionString = null;
if (connectionName.Equals(DatabaseConnectionName.MSSQL))
{
return new SqlConnection(connectionString);
}
if (connectionName.Equals(DatabaseConnectionName.MYSQL))
{
return new MySqlConnection(connectionString);
}
throw new ArgumentNullException();
}
}
public abstract class DbConnectionMSSQLRepositoryBase
{
public IDbConnection DbConnection { get; private set; }
public DbConnectionMSSQLRepositoryBase(IDbConnectionFactory dbConnectionFactory)
{
// Now it's the time to pick the right connection string!
// Enum is used. No magic string!
this.DbConnection = dbConnectionFactory.CreateDbConnection(DatabaseConnectionName.MSSQL);
}
}
public abstract class DbConnectionMYSQLRepositoryBase
{
public IDbConnection DbConnection { get; private set; }
public DbConnectionMYSQLRepositoryBase(IDbConnectionFactory dbConnectionFactory)
{
this.DbConnection = dbConnectionFactory.CreateDbConnection(DatabaseConnectionName.MYSQL);
}
}
public interface IDbConnectionFactory
{
IDbConnection CreateDbConnection(DatabaseConnectionName connectionName);
}
public enum DatabaseConnectionName
{
MSSQL,
MYSQL
}
实体类操作数据库
需要安装Dapper.Contrib包
Install-Package Dapper.Contrib
--.NET FX
Install-Package Dapper.Contrib 1.50.0
在Dapper.Contrib中一共以下扩展方法可以调用
T Get<T>(id);
IEnumerable<T> GetAll<T>();
int Insert<T>(T obj);
int Insert<T>(Enumerable<T> list);
bool Update<T>(T obj);
bool Update<T>(Enumerable<T> list);
bool Delete<T>(T obj);
bool Delete<T>(Enumerable<T> list);
bool DeleteAll<T>();
特性
- Table：指定实体对应地数据库表名
- Key：指定此列为主键（自动增长主键），可忽略，忽略后默认查找以实体类
Id（不区分大小写）作为主键 - ExplicitKey：指定此列为主键（不自动增长类型例如guid，ExplicitKey与Key地区别下面会详细讲）
- Computed：计算属性，打上此标签，对象地insert，update等操作会忽略此列
- Write：需穿一个bool值，false时insert，update等操作会忽略此列
主键属性必须存在，都是通过主键去增删改查的。
- 默认以实体类
Id（不区分大小写）作为主键 - 可以通过
[Key]属性标位主键，代表此值是数据库自动生成的数据，一般是int或GUID类型 - 可以通过
[ExplicitKey]属性标位主键，代表此值需要手动赋值，不会自动生成
增删改查
using Dapper.Contrib.Extensions;
using System;
using System.Data.SqlClient;
namespace ConsoleApp1
{
class Program
{
static void Main(string[] args)
{
using (var conn = new SqlConnection("Data Source=127.0.0.1;Initial Catalog=TestDB;User Id=sa;Password=123456;"))
{
Note entity = new Note();
entity.Text = "564457674";
entity.Date = DateTime.Now;
var result = conn.Insert(entity);
var list = conn.GetAll<Note>();
conn.Update(new Note() { ID = 1, Text = "Maruti" });
//SqlMapperExtensions.Insert(conn, entity);
}
}
}
[Table("Note")]
public class Note
{
//主键
[Key]
public int ID { get; set; }
//不可写
[Write(false)]
public string Text { get; set; }
public DateTime Date { get; set; }
}
}
通过数据库Table生成实体类
需要安装PocoClassGenerator包
支持以下类型的数据库
- sqlserver
- oracle
- mysql,
- postgresql
- SqLite
生成案例代码
using System.Data.SqlClient;
namespace ConsoleApp2
{
class Program
{
static void Main(string[] args)
{
using (var connection = new SqlConnection("Data Source=127.0.0.1;Initial Catalog=TestDB;User Id=sa;Password=123456;"))
{
//生成所有表的实体
var str = connection.GenerateAllTables();
//生成summary注释实体
str = connection.GenerateAllTables(GeneratorBehavior.Comment);
//生成DapperContrib特性实体
str = connection.GenerateAllTables(GeneratorBehavior.DapperContrib);
//生成所有视图实体
str = connection.GenerateAllTables(GeneratorBehavior.View);
//生成前面三个所有的实体
str = connection.GenerateAllTables(GeneratorBehavior.Comment
| GeneratorBehavior.DapperContrib
| GeneratorBehavior.View);
//获取具体表的实体
var classCode = connection.GenerateClass("select * from Table");
classCode = connection.GenerateClass("select * from Table", GeneratorBehavior.Comment
| GeneratorBehavior.DapperContrib
| GeneratorBehavior.View);
}
}
}
}
