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