97 lines
3.2 KiB
C#
97 lines
3.2 KiB
C#
using System.Data;
|
|
using MySqlConnector;
|
|
|
|
namespace MesETL.Shared.Helper;
|
|
|
|
public static class DatabaseHelper
|
|
{
|
|
/// <summary>
|
|
/// 创建一个MySql连接
|
|
/// </summary>
|
|
/// <param name="connStr"></param>
|
|
/// <returns></returns>
|
|
public static MySqlConnection CreateConnection(string connStr)
|
|
{
|
|
var newConnStr = new MySqlConnectionStringBuilder(connStr)
|
|
{
|
|
ConnectionTimeout = 30,
|
|
DefaultCommandTimeout = 0,
|
|
}.ConnectionString;
|
|
return new MySqlConnection(newConnStr);
|
|
}
|
|
|
|
/// <summary>
|
|
/// 使用语句查询数据库
|
|
/// </summary>
|
|
/// <param name="connStr"></param>
|
|
/// <param name="sql"></param>
|
|
/// <param name="ct"></param>
|
|
/// <returns></returns>
|
|
public static async Task<DataSet> QueryTableAsync(string connStr, string sql, CancellationToken ct = default)
|
|
{
|
|
await using var conn = CreateConnection(connStr);
|
|
if(conn.State is not ConnectionState.Open)
|
|
await conn.OpenAsync(ct);
|
|
await using var cmd = conn.CreateCommand();
|
|
cmd.CommandText = sql;
|
|
var ds = new DataSet();
|
|
new MySqlDataAdapter(cmd).Fill(ds);
|
|
return ds;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 使用语句进行标量查询
|
|
/// </summary>
|
|
/// <param name="connStr"></param>
|
|
/// <param name="sql"></param>
|
|
/// <param name="ct"></param>
|
|
/// <returns></returns>
|
|
public static async Task<object?> QueryScalarAsync(string connStr, string sql, CancellationToken ct = default)
|
|
{
|
|
await using var conn = CreateConnection(connStr);
|
|
if(conn.State is not ConnectionState.Open)
|
|
await conn.OpenAsync(ct);
|
|
await using var cmd = conn.CreateCommand();
|
|
cmd.CommandText = sql;
|
|
return await cmd.ExecuteScalarAsync(ct);
|
|
}
|
|
|
|
/// <summary>
|
|
/// 执行非查询语句
|
|
/// </summary>
|
|
/// <param name="connStr"></param>
|
|
/// <param name="sql"></param>
|
|
/// <param name="ct"></param>
|
|
/// <returns></returns>
|
|
public static async Task<int> NonQueryAsync(string connStr, string sql, CancellationToken ct = default)
|
|
{
|
|
await using var conn = CreateConnection(connStr);
|
|
if(conn.State is not ConnectionState.Open)
|
|
await conn.OpenAsync(ct);
|
|
await using var cmd = conn.CreateCommand();
|
|
cmd.CommandText = sql;
|
|
return await cmd.ExecuteNonQueryAsync(ct);
|
|
}
|
|
|
|
/// <summary>
|
|
/// 在事务中执行语句
|
|
/// </summary>
|
|
/// <param name="connStr"></param>
|
|
/// <param name="sql"></param>
|
|
/// <param name="parameters"></param>
|
|
/// <returns></returns>
|
|
public static async Task<int> TransactionAsync(string connStr, string sql, params MySqlParameter[] parameters)
|
|
{
|
|
await using var conn = CreateConnection(connStr);
|
|
if(conn.State is not ConnectionState.Open)
|
|
await conn.OpenAsync();
|
|
await using var trans = await conn.BeginTransactionAsync();
|
|
await using var cmd = conn.CreateCommand();
|
|
cmd.CommandText = sql;
|
|
cmd.Transaction = trans;
|
|
cmd.Parameters.AddRange(parameters);
|
|
var rows = await cmd.ExecuteNonQueryAsync();
|
|
await trans.CommitAsync();
|
|
return rows;
|
|
}
|
|
} |