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

參考資料：

• Use Dapper ORM With ASP.NET Core
• .NET Core 使用 Dapper 操作 MySQL