ASP.NET Core 1.0 使用 Dapper 操作 MySql(包含事务)
2023-02-18 15:32:27 时间
操作 MySql 数据库使用MySql.Data
程序包(MySql 开发,其他第三方可能会有些问题)。
project.json 代码:
{
"version": "1.0.0-*",
"buildOptions": {
"emitEntryPoint": true
},
"dependencies": {
"Microsoft.NETCore.App": {
"type": "platform",
"version": "1.0.1"
},
"Dapper": "1.50.2",
"MySql.Data": "7.0.6-IR31"
},
"frameworks": {
"netcoreapp1.0": {
"imports": "dnxcore50"
}
}
}
测试数据库脚本:
CREATE TABLE `products` (
`ProductID` int(11) NOT NULL AUTO_INCREMENT,
`Name` varchar(255) DEFAULT NULL,
`Quantity` int(11) DEFAULT NULL,
`Price` int(11) DEFAULT NULL,
PRIMARY KEY (`ProductID`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=gbk;
Product 代码:
public class Product
{
[Key]
public int ProductId { get; set; }
public string Name { get; set; }
public int Quantity { get; set; }
public double Price { get; set; }
}
ProductRepository 代码(数据访问操作):
public class ProductRepository
{
private string connectionString;
public ProductRepository()
{
connectionString = @"server=localhost;database=dapperdemo;uid=root;pwd=123456;";
}
public IDbConnection Connection
{
get
{
return new MySqlConnection(connectionString);
}
}
public void Add(Product prod)
{
using (IDbConnection dbConnection = Connection)
{
string sQuery = "INSERT INTO Products (Name, Quantity, Price)"
+ " VALUES(@Name, @Quantity, @Price)";
dbConnection.Open();
dbConnection.Execute(sQuery, prod);
}
}
public IEnumerable<Product> GetAll()
{
using (IDbConnection dbConnection = Connection)
{
dbConnection.Open();
return dbConnection.Query<Product>("SELECT * FROM Products");
}
}
public Product GetByID(int id)
{
using (IDbConnection dbConnection = Connection)
{
string sQuery = "SELECT * FROM Products"
+ " WHERE ProductId = @Id";
dbConnection.Open();
return dbConnection.Query<Product>(sQuery, new { Id = id }).FirstOrDefault();
}
}
public void Delete(int id)
{
using (IDbConnection dbConnection = Connection)
{
string sQuery = "DELETE FROM Products"
+ " WHERE ProductId = @Id";
dbConnection.Open();
dbConnection.Execute(sQuery, new { Id = id });
}
}
public void Update(Product prod)
{
using (IDbConnection dbConnection = Connection)
{
string sQuery = "UPDATE Products SET Name = @Name,"
+ " Quantity = @Quantity, Price= @Price"
+ " WHERE ProductId = @ProductId";
dbConnection.Open();
dbConnection.Execute(sQuery, prod);
}
}
public void TransactionTest()
{
using (IDbConnection dbConnection = Connection)
{
string sQuery = "UPDATE Products SET Name = 'xishuai222'"
+ " WHERE ProductId = 1";
dbConnection.Open();
using (var transaction = dbConnection.BeginTransaction())
{
dbConnection.Execute(sQuery);
///to do throw exception
transaction.Commit();
}
}
}
}
调用代码:
public class Program
{
public static void Main(string[] args)
{
var productRepository = new ProductRepository();
var product = new Product() { Name = "xishuai" };
productRepository.Add(product);
var products = productRepository.GetAll();
foreach (var item in products)
{
Console.WriteLine($"id: {item.ProductId}; name: {item.Name}");
}
productRepository.TransactionTest();
Console.ReadKey();
}
}
参考资料:
相关文章
- [TCP/IP] TCP的报文头
- [PHP] 重回基础(date函数和strtotime函数)
- [PHP]垃圾回收机制
- [HTML5] Canvas绘制简单图片
- [PHP] 广度优先搜索匹配网站所有链接
- [PHP] 最简单的权限控制设计
- [PHP] substr占用内存谨慎使用
- [PHP] 使用ftell和fseek函数直接定位文件位置获取部分数据
- [HTML5] Canvas绘制简单形状
- [PHP]socket的连接超时 与 读取/写入超时
- [PHP]引用返回与节省内存
- [PHP]实体类基类和序列化__sleep问题
- [PHP]日志处理error_log()函数和配置使用
- [PHP] 使用反射实现的控制反转
- [PHP] debug_backtrace()可以获取到代码的调用路径追踪
- [Redis] redis数据备份恢复与持久化
- [PHP] 商品类型规格属性后台管理(代码流程备忘)
- [TCP/IP] TCP的传输连接管理
- [PHP] sys_get_temp_dir()和tempnam()函数报错与环境变量的配置问题
- [PHP] ubuntu下使用uuid扩展获取uuid