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