public static List <object> ReloadSqlParameters(DbContext context, List <object> sqlParameters)
{
DbServer databaseType = GetDatabaseType(context);
if (databaseType == DbServer.Sqlite)
{
var sqlParametersReloaded = new List <object>();
foreach (var parameter in sqlParameters)
{
var sqlParameter = (IDbDataParameter)parameter;
sqlParametersReloaded.Add(new SqliteParameter(sqlParameter.ParameterName, sqlParameter.Value));
}
return(sqlParametersReloaded);
}
else if (databaseType == DbServer.SqlServer)
{
// if SqlServer, might need to convert
// Microsoft.Data.SqlClient to System.Data.SqlClient
var sqlParametersReloaded = new List <object>();
var c = context.Database.GetDbConnection();
foreach (var parameter in sqlParameters)
{
var sqlParameter = (IDbDataParameter)parameter;
sqlParametersReloaded.Add(SqlClientHelper.CorrectParameterType(c, sqlParameter));
}
return(sqlParametersReloaded);
}
else // for SqlServer return original
{
return(sqlParameters);
}
}
protected void CheckAndSetParametersForConvertibles(DbContext context, IEnumerable <object> innerParameters, TableInfo tableInfo) // fix for enum 'int' Conversion to nvarchar
{
foreach (var innerParameter in innerParameters)
{
string parameterColumnName = ((Microsoft.Data.SqlClient.SqlParameter)innerParameter).ParameterName.Replace("@__", ""); // @__column_N..
parameterColumnName = parameterColumnName.Contains("_") ? parameterColumnName.Substring(0, parameterColumnName.IndexOf("_")) : parameterColumnName; // column
parameterColumnName = parameterColumnName.ToLower();
foreach (var convertibleProperty in tableInfo.ConvertibleProperties)
{
if (convertibleProperty.Key.ToLower() == parameterColumnName)
{
if (convertibleProperty.Value.ProviderClrType.Name == nameof(String))
{
if (SqlClientHelper.IsSystemConnection(context.Database.GetDbConnection()))
{
((System.Data.SqlClient.SqlParameter)innerParameter).DbType = System.Data.DbType.String;
}
else
{
((Microsoft.Data.SqlClient.SqlParameter)innerParameter).DbType = System.Data.DbType.String;
}
}
}
}
}
}
protected int GetNumberUpdated(DbContext context)
{
var resultParameter = SqlClientHelper.CreateParameter(context.Database.GetDbConnection());
resultParameter.ParameterName = "@result";
resultParameter.DbType = DbType.Int32;
resultParameter.Direction = ParameterDirection.Output;
string sqlQueryCount = SqlQueryBuilder.SelectCountIsUpdateFromOutputTable(this);
context.Database.ExecuteSqlRaw($"SET @result = ({sqlQueryCount});", resultParameter);
return((int)resultParameter.Value);
}
protected async Task <int> GetNumberUpdatedAsync(DbContext context, CancellationToken cancellationToken)
{
var resultParameters = new List <IDbDataParameter>();
var p = SqlClientHelper.CreateParameter(context.Database.GetDbConnection());
p.ParameterName = "@result";
p.DbType = DbType.Int32;
p.Direction = ParameterDirection.Output;
resultParameters.Add(p);
string sqlQueryCount = SqlQueryBuilder.SelectCountIsUpdateFromOutputTable(this);
await context.Database.ExecuteSqlRawAsync($"SET @result = ({sqlQueryCount});", resultParameters, cancellationToken).ConfigureAwait(false); // TODO cancellationToken if Not
return((int)resultParameters.FirstOrDefault().Value);
}
private static string GetSqlSetSegment(DbContext context, TableInfo tableInfo, Type updateValuesType, object updateValues, object defaultValues, List <string> updateColumns, List <object> parameters)
{
string sql = string.Empty;
foreach (var propertyNameColumnName in tableInfo.PropertyColumnNamesDict)
{
string propertyName = propertyNameColumnName.Key;
string columnName = propertyNameColumnName.Value;
var pArray = propertyName.Split(new char[] { '.' });
Type lastType = updateValuesType;
PropertyInfo property = lastType.GetProperty(pArray[0]);
if (property != null)
{
object propertyUpdateValue = property.GetValue(updateValues);
object propertyDefaultValue = property.GetValue(defaultValues);
for (int i = 1; i < pArray.Length; i++)
{
lastType = property.PropertyType;
property = lastType.GetProperty(pArray[i]);
propertyUpdateValue = propertyUpdateValue != null?property.GetValue(propertyUpdateValue) : propertyUpdateValue;
var lastDefaultValues = lastType.Assembly.CreateInstance(lastType.FullName);
propertyDefaultValue = property.GetValue(lastDefaultValues);
}
if (tableInfo.ConvertibleProperties.ContainsKey(columnName))
{
propertyUpdateValue = tableInfo.ConvertibleProperties[columnName].ConvertToProvider.Invoke(propertyUpdateValue);
}
bool isDifferentFromDefault = propertyUpdateValue != null && propertyUpdateValue?.ToString() != propertyDefaultValue?.ToString();
if (isDifferentFromDefault || (updateColumns != null && updateColumns.Contains(propertyName)))
{
sql += $"[{columnName}] = @{columnName}, ";
propertyUpdateValue = propertyUpdateValue ?? DBNull.Value;
var p = SqlClientHelper.CreateParameter(context.Database.GetDbConnection());
p.ParameterName = $"@{columnName}";
p.Value = propertyUpdateValue;
parameters.Add(p);
}
}
}
if (String.IsNullOrEmpty(sql))
{
throw new InvalidOperationException("SET Columns not defined. If one or more columns should be updated to theirs default value use 'updateColumns' argument.");
}
sql = sql.Remove(sql.Length - 2, 2); // removes last excess comma and space: ", "
return($"SET {sql}");
}
