protected override async Task <Dict[]> DoSelectRowsAsync(string executableSql, Dict executableParams, int limit)
{
SelectStatement statement = new SelectStatement(this, executableSql);
MySqlParameter[] mySqlParams = executableParams.Select(keyValuePair => new MySqlParameter(keyValuePair.Key, keyValuePair.Value)).ToArray();
List <Dict> rows = new List <Dict>();
try {
var sql = limit > 0 ? $"{executableSql} LIMIT {limit}" : executableSql;
using (MySqlDataReader reader = await ExecuteReaderAsync(this.ConnectionString, sql, mySqlParams)) {
ReadOnlyCollection <DbColumn> columns = null;
while (reader.Read())
{
if (columns == null)
{
columns = reader.GetColumnSchema();
}
Dict row = new Dictionary <string, object>();
foreach (var column in columns)
{
row[column.ColumnName] = ConvertValue(reader[column.ColumnName]);
}
rows.Add(row);
}
}
}
catch (Exception e) {
logger.Error(e, $"Error executing {statement.Sql}...");
throw;
}
return(rows.ToArray());
}
/// <summary>
/// Metoda pentru generarea unui sir de parametri pentru operatiile de insert/update/delete
/// </summary>
/// <param name="_dt">Tabela in care se efectueaza operatia</param>
/// <param name="_object">obiect cu valorile ce vor fi updatate</param>
/// <returns>vector de MySqlParameters</returns>
public object[] GenerateMySqlParameters(MySqlDataReader _dt, object[] _object)
{
ArrayList _alist = new ArrayList();
#if NET461
for (int i = 0; i < _dt.FieldCount; i++)
{
string dcName = _dt.GetName(i).ToString();
if (dcName.ToLower() != "id" && dcName.ToLower() != "extension")
{
_alist.Add(new MySqlParameter("_" + dcName, _object[i].ToString()));
}
/*
* Console.Write(SqlReader.GetName(col).ToString()); // Gets the column name
* Console.Write(SqlReader.GetFieldType(col).ToString()); // Gets the column type
* Console.Write(SqlReader.GetDataTypeName(col).ToString()); // Gets the column database type
*/
}
#else
System.Collections.ObjectModel.ReadOnlyCollection <DbColumn> _columns = _dt.GetColumnSchema();
for (int i = 0; i < _columns.Count; i++)
{
string dcName = _columns[i].ColumnName;
if (dcName.ToLower() != "id" && dcName.ToLower() != "extension")
{
_alist.Add(new MySqlParameter("_" + dcName, _object[i].ToString()));
}
}
#endif
return(_alist.ToArray());
}
IEnumerable <T> ForEachFirstCol <T>(IWrappedDataReader p_wrappedReader)
{
// yield无法使用await，无法在含catch的try内
// 一定要使用using 或 finally方式释放资源，不然foreach内部break时资源无法释放！！！
try
{
using (p_wrappedReader)
{
MySqlDataReader reader = (MySqlDataReader)p_wrappedReader.Reader;
var cols = reader.GetColumnSchema();
if (cols[0].DataType == typeof(T))
{
while (reader.Read())
{
yield return(reader.GetFieldValue <T>(0));
}
}
else
{
while (reader.Read())
{
yield return((T)Convert.ChangeType(reader.GetValue(0), typeof(T)));
}
}
}
}
finally
{
ReleaseConnection();
}
}
/// <summary>
/// Executes a Query
/// </summary>
/// <param name="context">Database Context</param>
/// <param name="query">SQL</param>
/// <param name="parameters">Parameters</param>
public static IReadOnlyDictionary <string, object>[] MySqlQuery(DatabaseContext context, string query, MySqlParameter[] parameters = null)
{
//Place to store Results
List <IReadOnlyDictionary <string, object> > results = new();
//Create and Open Connection
MySqlConnection connection = new(context.GetMySqlConnectionString());
connection.Open();
//Create command with `query` Command Text
MySqlCommand cmd = connection.CreateCommand();
cmd.CommandText = query;
//Add Parameters if present
if (parameters != null)
{
cmd.Parameters.AddRange(parameters);
}
//Execute Data Reader
using MySqlDataReader reader = cmd.ExecuteReader();
//Read Columns
ReadOnlyCollection <DbColumn> columns = reader.GetColumnSchema();
//Read Results
while (reader.Read())
{
//Place to store Result
Dictionary <string, object> result = new();
//Create Object Array with the size of the amount of columns
object[] values = new object[columns.Count];
//Get Values
reader.GetValues(values);
//Iterate over each column
for (int i = 0; i < columns.Count; i++)
{
//Get Column
DbColumn column = columns[i];
//Add Result
if (!result.ContainsKey(column.ColumnName))
{
result.Add(column.ColumnName, values[i]);
}
}
//Add Result to Results
results.Add(result);
}
//Close Connection
connection.Close();
//Return Results
return(results.ToArray());
}
/// <summary>
/// Metoda pentru selectarea valorilor existente in baza de date inainte de efectuarea unei operatii de insert/update/delete/import
/// </summary>
/// <param name="_tabela">tabela in care urmeaza sa se efectueze operatia</param>
/// <param name="_id">ID-ul uni al inregistrarii asupra careia se efectueaza operatia</param>
/// <returns></returns>
public string GetDetaliiBefore(string _tabela, int _id)
{
string toReturn = "";
MySqlConnection mc = new MySqlConnection();
mc.ConnectionString = ConnectionString;
MySqlCommand m = new MySqlCommand();
m.Connection = mc;
m.CommandType = CommandType.StoredProcedure;
m.CommandText = _tabela.ToUpper() + "sp_GetById";
MySqlParameter _AUTHENTICATED_USER_ID = new MySqlParameter("_AUTHENTICATED_USER_ID", this.ID_UTILIZATOR);
m.Parameters.Add(_AUTHENTICATED_USER_ID);
MySqlParameter _ID = new MySqlParameter("_ID", _id);
m.Parameters.Add(_ID);
mc.Open();
MySqlDataReader mdr = m.ExecuteReader();
#if NET461
while (mdr.Read())
{
for (int i = 0; i < mdr.FieldCount; i++)
{
string dcName = mdr.GetName(i).ToString();
toReturn += (dcName.ToUpper() + " = " + mdr[dcName].ToString() + ", ");
}
break;
}
#else
System.Collections.ObjectModel.ReadOnlyCollection <DbColumn> _columns = mdr.GetColumnSchema();
while (mdr.Read())
{
for (int i = 0; i < _columns.Count; i++)
{
string dcName = _columns[i].ColumnName;
toReturn += (dcName.ToUpper() + " = " + mdr[dcName].ToString() + ", ");
}
break;
}
#endif
mc.Close();
return(toReturn);
}
/// <summary>
/// 以参数值方式执行Sql语句，返回符合条件的第一列数据，并转换为指定类型
/// </summary>
/// <param name="p_type">第一列数据类型</param>
/// <param name="p_keyOrSql">Sql字典中的键名(无空格) 或 Sql语句</param>
/// <param name="p_params">参数值，支持Dict或匿名对象，默认null</param>
/// <returns>返回第一列数据的泛型列表</returns>
public async Task <object> FirstCol(Type p_type, string p_keyOrSql, object p_params = null)
{
Throw.IfNull(p_type);
var cmd = CreateCommand(p_keyOrSql, p_params, false);
try
{
await OpenConnection();
using (var wrappedReader = (IWrappedDataReader)await _conn.ExecuteReaderAsync(cmd))
{
// Dapper2.0 改版
MySqlDataReader reader = (MySqlDataReader)wrappedReader.Reader;
Type tp = typeof(List <>).MakeGenericType(p_type);
var ls = Activator.CreateInstance(tp) as IList;
var cols = reader.GetColumnSchema();
if (cols[0].DataType == p_type)
{
while (await reader.ReadAsync())
{
ls.Add(reader.GetValue(0));
}
}
else
{
while (await reader.ReadAsync())
{
ls.Add(Convert.ChangeType(reader.GetValue(0), p_type));
}
}
return(ls);
}
}
catch (Exception ex)
{
throw GetSqlException(cmd, ex);
}
finally
{
ReleaseConnection();
}
}
IEnumerable <TRow> ForEachRow <TRow>(IWrappedDataReader p_wrappedReader)
where TRow : Row
{
// yield无法使用await，无法在含catch的try内
// 一定要使用using 或 finally方式释放资源，不然foreach内部break时资源无法释放！！！
try
{
using (p_wrappedReader)
{
MySqlDataReader reader = (MySqlDataReader)p_wrappedReader.Reader;
var cols = reader.GetColumnSchema();
// Entity类型
Type tpEntity = null;
if (typeof(TRow).IsSubclassOf(typeof(Entity)))
{
tpEntity = typeof(TRow);
}
while (reader.Read())
{
// 无参数构造方法可能为private，如实体类型
TRow row = (TRow)Activator.CreateInstance(typeof(TRow), true);
for (int i = 0; i < reader.FieldCount; i++)
{
var col = cols[i];
Type colType = col.DataType;
if (col.AllowDBNull.HasValue && col.AllowDBNull.Value && col.DataType.IsValueType)
{
// 可为null的值类型
colType = typeof(Nullable <>).MakeGenericType(col.DataType);
}
else if (colType == typeof(byte) && tpEntity != null)
{
// Entity 时根据属性类型将 byte 自动转为 enum 类型
var prop = tpEntity.GetProperty(col.ColumnName, BindingFlags.Public | BindingFlags.Instance | BindingFlags.DeclaredOnly | BindingFlags.IgnoreCase);
if (prop != null)
{
colType = prop.PropertyType;
}
}
if (reader.IsDBNull(i))
{
new Cell(row, col.ColumnName, colType);
}
else
{
new Cell(row, col.ColumnName, colType, reader.GetValue(i));
}
}
yield return(row);
}
}
}
finally
{
ReleaseConnection();
}
}
async Task QueryInternal <TRow>(Table p_tbl, string p_keyOrSql, object p_params = null)
where TRow : Row
{
var cmd = CreateCommand(p_keyOrSql, p_params, false);
try
{
await OpenConnection();
using (var wrappedReader = (IWrappedDataReader)await _conn.ExecuteReaderAsync(cmd))
{
// Dapper2.0 改版
MySqlDataReader reader = (MySqlDataReader)wrappedReader.Reader;
// Entity类型
Type tpEntity = null;
if (typeof(TRow).IsSubclassOf(typeof(Entity)))
{
tpEntity = typeof(TRow);
}
// 参见github上的MySqlDataReader.cs
// 获取列定义
var cols = reader.GetColumnSchema();
foreach (var col in cols)
{
if (col.AllowDBNull.HasValue && col.AllowDBNull.Value && col.DataType.IsValueType)
{
// 可为null的值类型
p_tbl.Add(col.ColumnName, typeof(Nullable <>).MakeGenericType(col.DataType));
}
else if (col.DataType == typeof(byte) && tpEntity != null)
{
// Entity 时根据属性类型将 byte 自动转为 enum 类型
var prop = tpEntity.GetProperty(col.ColumnName, BindingFlags.Public | BindingFlags.Instance | BindingFlags.DeclaredOnly | BindingFlags.IgnoreCase);
p_tbl.Add(col.ColumnName, prop != null ? prop.PropertyType : col.DataType);
}
else
{
p_tbl.Add(col.ColumnName, col.DataType);
}
}
while (await reader.ReadAsync())
{
// 整行已读到内存，官方推荐使用同步方法获取值，比异步性能更好！
// 无参数构造方法可能为private，如实体类型
var row = (TRow)Activator.CreateInstance(typeof(TRow), true);
for (int i = 0; i < reader.FieldCount; i++)
{
var col = p_tbl.Columns[i];
if (reader.IsDBNull(i))
{
new Cell(row, col.ID, col.Type);
}
else
{
new Cell(row, col.ID, col.Type, reader.GetValue(i));
}
}
p_tbl.Add(row);
}
}
}
catch (Exception ex)
{
throw GetSqlException(cmd, ex);
}
finally
{
ReleaseConnection();
}
}
