C# MySql Transaction Async
18171 ワード
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Configuration;
using MySql.Data.MySqlClient;
using System.Data.Common;
using ConsoleApplication22.Model;
using System.Reflection;
namespace ConsoleApplication22
{
class Program
{
static void Main(string[] args)
{
ReadAsyncDemo();
Console.ReadLine();
}
static async void ReadAsyncDemo()
{
string selectSQL = "select * from country";
IList countryList = await MySqlRead2Async(selectSQL);
}
static string MySqlConnectionString = ConfigurationManager.ConnectionStrings["MySqlConnectionString"].ConnectionString;
static async void MySqlReadAsync(string readSQL,Dictionary<string,object> parametersDic=null)
{
using (MySqlConnection conn = GetMySqlConnection())
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
using (MySqlCommand cmd = new MySqlCommand(readSQL, conn))
{
using (DbDataReader dataReader = await cmd.ExecuteReaderAsync())
{
StringBuilder selectBuilder = new StringBuilder();
while (await dataReader.ReadAsync())
{
for(int i=0;i)
{
selectBuilder.Append(dataReader[i]+"\t");
}
selectBuilder.AppendLine();
}
Console.WriteLine(selectBuilder.ToString());
}
}
}
}
static async Task> MySqlRead2Async(string selectSQL,Dictionary<string,object> parametersDic=null)where T:class
{
IList dataList = new List();
using (MySqlConnection conn = GetMySqlConnection())
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
using(MySqlCommand selectCmd=new MySqlCommand(selectSQL, conn))
{
using (DbDataReader dataReaderAsync = await selectCmd.ExecuteReaderAsync())
{
if (dataReaderAsync.HasRows)
{
DataTable dt = new DataTable();
dt.Load(dataReaderAsync);
dataList = dt.ToDataList();
}
}
}
}
return dataList;
}
static async Task<int> MySqlWriteAsync(string writeSQL,Dictionary<string,object> parametersDic=null)
{
int executeResult = -1;
using (MySqlConnection conn = GetMySqlConnection())
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
using (MySqlCommand cmd = new MySqlCommand(writeSQL, conn))
{
using (MySqlTransaction myTrans = await conn.BeginTransactionAsync())
{
try
{
if (parametersDic != null && parametersDic.Any())
{
foreach (var pDic in parametersDic)
{
cmd.Parameters.AddWithValue(pDic.Key, pDic.Value);
}
}
cmd.Transaction = myTrans;
executeResult = await cmd.ExecuteNonQueryAsync();
myTrans.Commit();
}
catch (Exception ex)
{
myTrans.Rollback();
Console.WriteLine(ex.Message);
}
}
}
}
return executeResult;
}
static MySqlConnection GetMySqlConnection()
{
MySqlConnection conn = new MySqlConnection();
conn.ConnectionString = MySqlConnectionString;
return conn;
}
}
static class ExtendClass
{
public static List ToDataList(this DataTable dt)
{
var list = new List();
var plist = new List(typeof(T).GetProperties());
foreach (DataRow item in dt.Rows)
{
T s = Activator.CreateInstance();
for (int i = 0; i < dt.Columns.Count; i++)
{
PropertyInfo info = plist.Find(p => p.Name == dt.Columns[i].ColumnName);
if (info != null)
{
try
{
if (!Convert.IsDBNull(item[i]))
{
object v = null;
if (info.PropertyType.ToString().Contains("System.Nullable"))
{
v = Convert.ChangeType(item[i], Nullable.GetUnderlyingType(info.PropertyType));
}
else
{
v = Convert.ChangeType(item[i], info.PropertyType);
}
info.SetValue(s, v, null);
}
}
catch (Exception ex)
{
throw new Exception(" [" + info.Name + "] ," + ex.Message);
}
}
}
list.Add(s);
}
return list;
}
}
}
static async Task<int> MySqlWriteAsync(string writeSQL,Dictionary<string,object> parametersDic=null)
{
int executeResult = -1;
using (MySqlConnection conn = GetMySqlConnection())
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
using (MySqlCommand cmd = new MySqlCommand(writeSQL, conn))
{
using (MySqlTransaction myTrans = await conn.BeginTransactionAsync())
{
try
{
if (parametersDic != null && parametersDic.Any())
{
foreach (var pDic in parametersDic)
{
cmd.Parameters.AddWithValue(pDic.Key, pDic.Value);
}
}
cmd.Transaction = myTrans;
executeResult = await cmd.ExecuteNonQueryAsync();
myTrans.Commit();
}
catch (Exception ex)
{
myTrans.Rollback();
Console.WriteLine(ex.Message);
}
}
}
}
return executeResult;
}