/// <summary> /// Add parameters to SqlCommand /// </summary> /// <param name="cmd"></param> /// <param name="attrName"></param> /// <param name="value"></param> /// <param name="dbType"></param> public IRepository AddInnerParameter(DbCommandExtended cmd, string attrName, object value, SqlDbType dbType = SqlDbType.NVarChar) { if (attrName != null && attrName[0] != '@') { attrName = "@" + attrName; } if (value?.GetType().GetTypeInfo().IsEnum ?? false) { value = value.ConvertValue <long>(); } var sqlDbTypeValue = value ?? DBNull.Value; if (DataBaseTypes == DataBaseTypes.Mssql) { var param = new SqlParameter { SqlDbType = dbType, Value = sqlDbTypeValue, ParameterName = attrName }; cmd.Command.Parameters.Add(param); } else if (DataBaseTypes == DataBaseTypes.Sqllight) { (cmd.Command as SQLiteCommand).Parameters.AddWithValue(attrName, value ?? DBNull.Value); } else { (cmd.Command as NpgsqlCommand).Parameters.AddWithValue(attrName, value ?? DBNull.Value); } return(this); }
/// <summary> /// return LightDataTable e.g. DataTable /// </summary> /// <param name="cmd">sqlCommand that are create from GetSqlCommand</param> /// <param name="primaryKey">Table primaryKeyId, so LightDataTable.FindByPrimaryKey could be used </param> /// <returns></returns> public ILightDataTable GetLightDataTable(DbCommandExtended cmd, string primaryKey = null) { ValidateConnection(); var reader = cmd.Command.ExecuteReader(); return(new LightDataTable().ReadData(DataBaseTypes, reader, cmd, primaryKey)); }
/// <inheritdoc /> public int ExecuteNonQuery(DbCommandExtended cmd) { ValidateConnection(); var o = cmd.Command.ExecuteNonQuery(); CloseifPassible(); return(o); }
/// <summary> /// /// </summary> /// <param name="cmd"></param> /// <returns></returns> public object ExecuteScalar(DbCommandExtended cmd) { ValidateConnection(); var o = cmd.Command.ExecuteScalar(); CloseifPassible(); return(o); }
/// <summary> /// return LightDataTable e.g. DataTable /// </summary> /// <param name="cmd">sqlCommand that are create from GetSqlCommand</param> /// <param name="primaryKey">Table primaryKeyId, so LightDataTable.FindByPrimaryKey could be used </param> /// <returns></returns> public ILightDataTable GetLightDataTable(DbCommandExtended cmd, string primaryKey = null) { GlobalConfiguration.Logg?.Info("Execute", cmd); ValidateConnection(); var reader = cmd.Command.ExecuteReader(); return(new LightDataTable().ReadData(DataBaseTypes, reader, cmd, primaryKey)); }
/// <summary> /// return a list of LightDataTable e.g. DataSet /// </summary> /// <param name="cmd">sqlCommand that are create from GetSqlCommand</param> /// <param name="primaryKeyId"> Table primaryKeyId, so LightDataTable.FindByPrimaryKey could be used </param> /// <returns></returns> protected List <ILightDataTable> GetLightDataTableList(DbCommandExtended cmd, string primaryKeyId = null) { var returnList = new List <ILightDataTable>(); var reader = cmd.Command.ExecuteReader(); returnList.Add(new LightDataTable().ReadData(DataBaseTypes, reader, cmd, primaryKeyId, false)); while (reader.NextResult()) { returnList.Add(new LightDataTable().ReadData(DataBaseTypes, reader, cmd, primaryKeyId, false)); } reader.Close(); reader.Dispose(); return(returnList); }
/// <summary> /// Convert to unknown type /// </summary> /// <param name="command"></param> /// <param name="type"></param> /// <returns></returns> public IList DataReaderConverter(DbCommandExtended command, Type type) { IList result; ValidateConnection(); try { var o = command.Command.ExecuteReader(); OpenedDataReaders.GetOrAdd(o, true); result = Extension.DataReaderConverter(this, o, command, type); } catch (Exception e) { throw e; } finally { CloseifPassible(); } return(result); }
/// <summary> /// Convert to unknown type /// </summary> /// <param name="command"></param> /// <param name="type"></param> /// <returns></returns> public IList DataReaderConverter(DbCommandExtended command, Type type) { IList result; ValidateConnection(); GlobalConfiguration.Log?.Info("Execute", command); try { var o = command.Command.ExecuteReader(); OpenedDataReaders.GetOrAdd(o, true); result = Extension.DataReaderConverter(this, o, command, type); } catch (Exception e) { throw new EntityException(e.Message); } finally { CloseifPassible(); } return(result); }
internal static List <T> DataReaderConverter <T>(Transaction.Transaction repository, IDataReader reader, DbCommandExtended command) { return((List <T>)DataReaderConverter(repository, reader, command, typeof(T))); }
/// <summary> /// Convert to known object /// </summary> /// <typeparam name="T"></typeparam> /// <param name="command"></param> /// <returns></returns> public ISqlQueryable <T> DataReaderConverter <T>(DbCommandExtended command) { GlobalConfiguration.Log?.Info("Execute", command); return(new SqlQueryable <T>(this, ((List <T>)DataReaderConverter(command, typeof(T))))); }
internal static ISqlCommand ProcessSql(this IRepository repository, TransactionSqlConnection connection, IDbTransaction tran, string sql) { var i = 1; var dicCols = new SafeValueType <string, Tuple <object, SqlDbType> >(); MatchCollection matches = null; while ((matches = stringExp.Matches(sql)).Count > 0) { var exp = matches[0]; var col = "@CO" + i + "L"; object str = exp.Value.TrimEnd(']').Substring(@"String\[".Length - 1); sql = sql.Remove(exp.Index, exp.Value.Length); sql = sql.Insert(exp.Index, col); var v = str.ConvertValue <string>(); if (string.Equals(v, "null", StringComparison.OrdinalIgnoreCase)) { v = null; } dicCols.TryAdd(col, new Tuple <object, SqlDbType>(v, SqlDbType.NVarChar)); i++; } while ((matches = dateExp.Matches(sql)).Count > 0) { var exp = matches[0]; var col = "@CO" + i + "L"; object str = exp.Value.TrimEnd(']').Substring(@"Date\[".Length - 1); sql = sql.Remove(exp.Index, exp.Value.Length); sql = sql.Insert(exp.Index, col); dicCols.TryAdd(col, new Tuple <object, SqlDbType>(str.ConvertValue <DateTime?>(), SqlDbType.DateTime)); i++; } while ((matches = guidExp.Matches(sql)).Count > 0) { var exp = matches[0]; var col = "@CO" + i + "L"; object str = exp.Value.TrimEnd(']').Substring(@"Guid\[".Length - 1); sql = sql.Remove(exp.Index, exp.Value.Length); sql = sql.Insert(exp.Index, col); dicCols.TryAdd(col, new Tuple <object, SqlDbType>(str.ConvertValue <Guid?>(), SqlDbType.UniqueIdentifier)); i++; } sql = sql.CleanValidSqlName(repository.DataBaseTypes); DbCommand cmd = null; try { switch (repository.DataBaseTypes) { case DataBaseTypes.Mssql: cmd = tran != null ? "System.Data.SqlClient.SqlCommand".GetObjectType("System.Data.SqlClient").CreateInstance(new object[] { sql, connection.DBConnection, tran }) as DbCommand : "System.Data.SqlClient.SqlCommand".GetObjectType("System.Data.SqlClient").CreateInstance(new object[] { sql, connection.DBConnection }) as DbCommand; break; case DataBaseTypes.PostgreSql: cmd = tran != null ? "Npgsql.NpgsqlCommand".GetObjectType("Npgsql").CreateInstance(new object[] { sql, connection.DBConnection, tran }) as DbCommand : "Npgsql.NpgsqlCommand".GetObjectType("Npgsql").CreateInstance(new object[] { sql, connection.DBConnection }) as DbCommand; break; case DataBaseTypes.Sqllight: cmd = tran != null ? "System.Data.SQLite.SQLiteCommand".GetObjectType("System.Data.SQLite").CreateInstance(new object[] { sql, connection.DBConnection, tran }) as DbCommand : "System.Data.SQLite.SQLiteCommand".GetObjectType("System.Data.SQLite").CreateInstance(new object[] { sql, connection.DBConnection }) as DbCommand; break; } } catch (Exception e) { switch (repository.DataBaseTypes) { case DataBaseTypes.Mssql: throw new EntityException($"Please make sure that nuget 'System.Data.SqlClient' is installed \n orginal exception: \n {e.Message}"); case DataBaseTypes.PostgreSql: throw new EntityException($"Please make sure that nuget 'Npgsql' is installed \n orginal exception: \n {e.Message}"); case DataBaseTypes.Sqllight: throw new EntityException($"Please make sure that nuget 'System.Data.SQLite' is installed \n orginal exception: \n {e.Message}"); } } var dbCommandExtended = new DbCommandExtended(cmd, repository); foreach (var dic in dicCols) { dbCommandExtended.AddInnerParameter(dic.Key, dic.Value.Item1); } return(dbCommandExtended); }
internal static IList DataReaderConverter(Transaction.Transaction repository, IDataReader reader, DbCommandExtended command, Type type) { var tType = type.GetActualType(); var attachable = tType.GetPrimaryKey() != null; var baseListType = typeof(List <>); var listType = baseListType.MakeGenericType(tType); var iList = DeepCloner.CreateInstance(listType) as IList; var props = DeepCloner.GetFastDeepClonerProperties(tType); try { var colNames = new Custom_ValueType <int, string>(); var pp = new Custom_ValueType <int, FastDeepCloner.IFastDeepClonerProperty>(); while (reader.Read()) { object item = null; object clItem = null; item = DeepCloner.CreateInstance(tType); clItem = attachable ? DeepCloner.CreateInstance(tType) : null; var col = 0; while (col < reader.FieldCount) { string columnName; if (colNames.ContainsKey(col)) { columnName = colNames[col]; } else { columnName = reader.GetName(col); colNames.TryAdd(col, columnName); } var value = reader[columnName]; IFastDeepClonerProperty prop; if (!pp.ContainsKey(col)) { prop = DeepCloner.GetProperty(tType, columnName); if (prop == null) { prop = props.FirstOrDefault(x => string.Equals(x.GetPropertyName(), columnName, StringComparison.CurrentCultureIgnoreCase) || x.GetPropertyName().ToLower() == columnName); } pp.TryAdd(col, prop); } else { prop = pp[col]; } if (prop != null && value != DBNull.Value && value != null && prop.CanRead) { if (value as byte[] != null && prop.PropertyType.FullName.Contains("Guid")) { value = new Guid(value as byte[]); } var dataEncode = prop.GetCustomAttribute <DataEncode>(); if (prop.ContainAttribute <ToBase64String>()) { if (value.ConvertValue <string>().IsBase64String()) { value = MethodHelper.DecodeStringFromBase64(value.ConvertValue <string>()); } else { value = MethodHelper.ConvertValue(value, prop.PropertyType); } } else if (dataEncode != null) { value = new DataCipher(dataEncode.Key, dataEncode.KeySize).Decrypt(value.ConvertValue <string>()); } else if (prop.ContainAttribute <JsonDocument>()) { value = value?.ToString().FromJson(prop.PropertyType); } else if (prop.ContainAttribute <XmlDocument>()) { value = value?.ToString().FromXml(); } else { value = MethodHelper.ConvertValue(value, prop.PropertyType); } prop.SetValue(item, value); if (attachable) { prop.SetValue(clItem, value); } } col++; } if (clItem != null && !(repository?.IsAttached(clItem) ?? true)) { repository?.AttachNew(clItem); } iList.Add(item); } } catch (Exception e) { throw e; } finally { reader.Close(); reader.Dispose(); if (repository.OpenedDataReaders.ContainsKey(reader)) { repository.OpenedDataReaders.Remove(reader); } } return(iList); }
/// <summary> /// Convert to known object /// </summary> /// <typeparam name="T"></typeparam> /// <param name="command"></param> /// <returns></returns> public ISqlQueryable <T> DataReaderConverter <T>(DbCommandExtended command) { return(new SqlQueryable <T>(this, ((List <T>)DataReaderConverter(command, typeof(T))))); }
internal static IList DataReaderConverter(Transaction.Transaction repository, IDataReader reader, DbCommandExtended command, Type type) { var tType = type.GetActualType(); var baseListType = typeof(List <>); var listType = baseListType.MakeGenericType(tType); var iList = DeepCloner.CreateInstance(listType) as IList; //#if (NETSTANDARD2_0 || NETSTANDARD1_3 || NETSTANDARD1_5) var props = DeepCloner.GetFastDeepClonerProperties(tType); //#endif try { while (reader.Read()) { object item = null; object clItem = null; //#if (NETSTANDARD2_0 || NETSTANDARD1_3 || NETSTANDARD1_5) item = DeepCloner.CreateInstance(tType); clItem = DeepCloner.CreateInstance(tType); var col = 0; while (col < reader.FieldCount) { var columnName = reader.GetName(col); var value = reader[columnName]; var prop = DeepCloner.GetProperty(tType, columnName); if (prop == null) { prop = props.FirstOrDefault(x => string.Equals(x.GetPropertyName(), columnName, StringComparison.CurrentCultureIgnoreCase) || x.GetPropertyName().ToLower() == columnName); } if (value != DBNull.Value && value != null && prop != null && prop.CanRead) { if (value as byte[] != null && prop.PropertyType.FullName.Contains("Guid")) { value = new Guid(value as byte[]); } var dataEncode = prop.GetCustomAttribute <DataEncode>(); var toBase64String = prop.GetCustomAttribute <ToBase64String>(); if (toBase64String != null) { if (value.ConvertValue <string>().IsBase64String()) { value = MethodHelper.DecodeStringFromBase64(value.ConvertValue <string>()); } else { value = MethodHelper.ConvertValue(value, prop.PropertyType); } } else if (dataEncode != null) { value = new DataCipher(dataEncode.Key, dataEncode.KeySize).Decrypt(value.ConvertValue <string>()); } else if (prop.ContainAttribute <JsonDocument>()) { value = value?.ToString().FromJson(prop.PropertyType); } else { value = MethodHelper.ConvertValue(value, prop.PropertyType); } prop.SetValue(item, value); prop.SetValue(clItem, value); } col++; } //#else // var cmReader = new DataRecordExtended(reader); // if (!CachedDataRecord.ContainsKey(tType)) // CachedDataRecord.GetOrAdd(tType, DynamicBuilder.CreateBuilder(cmReader, tType)); // var x = CachedDataRecord[tType]; // item = x.Build(cmReader); // clItem = !(repository?.IsAttached(item) ?? true) ? x.Build(cmReader) : null; //#endif if (clItem != null && !(repository?.IsAttached(clItem) ?? true)) { repository?.AttachNew(clItem); } iList.Add(item); } } catch (Exception e) { throw e; } finally { reader.Close(); reader.Dispose(); if (repository.OpenedDataReaders.ContainsKey(reader)) { repository.OpenedDataReaders.Remove(reader); } } return(iList); }
internal static DbCommandExtended ProcessSql(this IRepository repository, IDbConnection connection, IDbTransaction tran, string sql) { var i = 1; var dicCols = new Custom_ValueType <string, Tuple <object, SqlDbType> >(); MatchCollection matches = null; while ((matches = stringExp.Matches(sql)).Count > 0) { var exp = matches[0]; var col = "@CO" + i + "L"; object str = exp.Value.TrimEnd(']').Substring(@"String\[".Length - 1); sql = sql.Remove(exp.Index, exp.Value.Length); sql = sql.Insert(exp.Index, col); dicCols.TryAdd(col, new Tuple <object, SqlDbType>(str.ConvertValue <string>(), SqlDbType.NVarChar)); i++; } while ((matches = dateExp.Matches(sql)).Count > 0) { var exp = matches[0]; var col = "@CO" + i + "L"; object str = exp.Value.TrimEnd(']').Substring(@"Date\[".Length - 1); sql = sql.Remove(exp.Index, exp.Value.Length); sql = sql.Insert(exp.Index, col); dicCols.TryAdd(col, new Tuple <object, SqlDbType>(str.ConvertValue <DateTime>(), SqlDbType.DateTime)); i++; } while ((matches = guidExp.Matches(sql)).Count > 0) { var exp = matches[0]; var col = "@CO" + i + "L"; object str = exp.Value.TrimEnd(']').Substring(@"Guid\[".Length - 1); sql = sql.Remove(exp.Index, exp.Value.Length); sql = sql.Insert(exp.Index, col); dicCols.TryAdd(col, new Tuple <object, SqlDbType>(str.ConvertValue <Guid?>(), SqlDbType.UniqueIdentifier)); i++; } sql = sql.CleanValidSqlName(repository.DataBaseTypes); DbCommand cmd = null; if (repository.DataBaseTypes == DataBaseTypes.Mssql) { cmd = tran != null ? new SqlCommand(sql, connection as SqlConnection, tran as SqlTransaction) : new SqlCommand(sql, connection as SqlConnection); } else if (repository.DataBaseTypes == DataBaseTypes.Sqllight) { cmd = tran == null ? new SQLiteCommand(sql, connection as SQLiteConnection) : new SQLiteCommand(sql, connection as SQLiteConnection, tran as SQLiteTransaction); } else { cmd = tran == null ? new NpgsqlCommand(sql, connection as NpgsqlConnection) : new NpgsqlCommand(sql, connection as NpgsqlConnection, tran as NpgsqlTransaction); } var dbCommandExtended = new DbCommandExtended(cmd, repository); foreach (var dic in dicCols) { repository.AddInnerParameter(dbCommandExtended, dic.Key, dic.Value.Item1, dic.Value.Item2); } return(dbCommandExtended); }
internal static ILightDataTable ReadData(this ILightDataTable data, DataBaseTypes dbType, IDataReader reader, DbCommandExtended command, string primaryKey = null, bool closeReader = true) { var i = 0; if (reader.FieldCount <= 0) { return(data); } data.TablePrimaryKey = primaryKey; if (reader.FieldCount <= 0) { if (closeReader) { reader.Close(); reader.Dispose(); } return(data); } if (command.TableType == null) { try { var key = command?.TableType != null ? command.TableType.FullName : command.Command.CommandText; if (!CachedSqlException.ContainsKey(command.Command.CommandText)) { if (!CachedGetSchemaTable.ContainsKey(key)) { CachedGetSchemaTable.Add(key, new LightDataTable(reader.GetSchemaTable())); } foreach (var item in CachedGetSchemaTable[key].Rows) { var columnName = item.Value <string>("ColumnName"); data.TablePrimaryKey = data.TablePrimaryKey == null && item.Columns.ContainsKey("IsKey") && item.TryValueAndConvert <bool>("IsKey", false) ? columnName : data.TablePrimaryKey; var dataType = TypeByTypeAndDbIsNull(item["DataType"] as Type, item.TryValueAndConvert <bool>("AllowDBNull", true)); if (data.Columns.ContainsKey(columnName)) { columnName = columnName + i; } data.AddColumn(columnName, dataType); i++; } } else { for (var col = 0; col < reader.FieldCount; col++) { var columnName = reader.GetName(col); var dataType = TypeByTypeAndDbIsNull(reader.GetFieldType(col) as Type, true); if (data.Columns.ContainsKey(columnName)) { columnName = columnName + i; } data.AddColumn(columnName, dataType); i++; } } } catch (Exception e) { if (!string.IsNullOrEmpty(command.Command.CommandText)) { CachedSqlException.Add(command.Command.CommandText, e); } return(ReadData(data, dbType, reader, command, primaryKey)); } } else { foreach (var c in command.DataStructure.Columns.Values) { data.AddColumn(c.ColumnName, c.DataType, c.DefaultValue); } } while (reader.Read()) { var row = data.NewRow(); reader.GetValues(row._itemArray); data.AddRow(row); } if (closeReader) { reader.Close(); reader.Dispose(); } return(data); }
/// <summary> /// Convert to known object /// </summary> /// <typeparam name="T"></typeparam> /// <param name="command"></param> /// <returns></returns> public List <T> DataReaderConverter <T>(DbCommandExtended command) { return((List <T>)DataReaderConverter(command, typeof(T))); }