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