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