ASP.NET Core 1.0 使用 Dapper 操作 MySql（包含事务）-阿里云开发者社区

ASP.NET Core 1.0 使用 Dapper 操作 MySql（包含事务）

2015-10-20 1243

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

本文涉及的产品

云数据库 RDS MySQL Serverless，0.5-2RCU 50GB

简介：

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