asp.net mysql 事务_ASP.NET Core 1.0 使用 Dapper 操作 MySql（包含事务）

操作 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 GetAll()

{

using (IDbConnection dbConnection = Connection)

{

dbConnection.Open();

return dbConnection.Query("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(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();

}

}

参考资料：