public string Update(ISqlResourceConfiguration config)
{
ISqlPropertyConfiguration[] properties = config.PropertyConfigurationsForUpdate
.Where(x => x.ValueComputationFunc == null || (x.ValueComputationFunc?.EndpointTriggers & HttpVerbs.PUT) == HttpVerbs.PUT)
.ToArray();
StringBuilder stringBuilder = new StringBuilder("UPDATE [");
stringBuilder.Append(config.ModelConfiguration.TableName);
stringBuilder.Append("] SET ");
for (int i = 0; i < properties.Length; i++)
{
stringBuilder.Append("[");
stringBuilder.Append(properties[i].ColumnName);
stringBuilder.Append("] = ");
stringBuilder.Append(_sqlParamaterProvider.GenerateParameterName(properties[i].PropertyName));
if (i != properties.Length - 1)
{
stringBuilder.Append(",");
}
stringBuilder.Append(" ");
}
return(stringBuilder
.AppendWhereClause(config.PrimaryIdentifier.ColumnName, _sqlParamaterProvider.GetPrimaryKeyParameterName())
.Append(SelectByIdBuilder(config)).ToString());
}
private static StringBuilder SelectAllBuilder(ISqlResourceConfiguration config)
{
StringBuilder stringBuilder = new StringBuilder("SELECT ");
ISqlPropertyConfiguration[] properties = config.PropertyConfigurationsForRead.ToArray();
for (int i = 0; i < properties.Length; i++)
{
stringBuilder.Append("[");
stringBuilder.Append(properties[i].ColumnName);
stringBuilder.Append("] AS [");
stringBuilder.Append(properties[i].PropertyName);
stringBuilder.Append("]");
if (i != properties.Length - 1)
{
stringBuilder.Append(",");
}
stringBuilder.Append(" ");
}
stringBuilder.Append("FROM [");
stringBuilder.Append(config.ModelConfiguration.TableName);
stringBuilder.Append("]");
return(stringBuilder);
}
private Func <object, List <IDatabaseCommandParameter> > GetSqlParameters(
ISqlResourceConfiguration resourceConfig,
ISqlPropertyConfiguration[] propertyConfigs)
{
var paramResource = Expression.Parameter(typeof(object), "resourceObject");
var typedParam = Expression.Convert(paramResource, resourceConfig.ResourceType);
var result = Expression.Variable(typeof(List <IDatabaseCommandParameter>), "databaseCommandParameter");
var typedParamVar = Expression.Variable(resourceConfig.ResourceType, "typedParam");
var assignTypedParamVar = Expression.Assign(typedParamVar, typedParam);
var instantiateResultObj = Expression.Assign(result, Expression.New(typeof(List <IDatabaseCommandParameter>)));
List <Expression> expressions = new List <Expression>
{
assignTypedParamVar,
instantiateResultObj
};
for (int i = 0; i < propertyConfigs.Length; i++)
{
var property = Expression.Property(typedParamVar, propertyConfigs[i].PropertyName);
var sqlParam = GetSqlParameter(propertyConfigs[i], _sqlParamaterProvider.GenerateParameterName(propertyConfigs[i].PropertyName));
var getSqlParam = Expression.Invoke(sqlParam, Expression.Convert(property, typeof(object)));
var generateNameAndAddToList = Expression.Call(
result,
nameof(List <IDatabaseCommandParameter> .Add),
null,
getSqlParam);
expressions.Add(generateNameAndAddToList);
}
// Return the result from the expression
expressions.Add(result);
var lambda = Expression.Lambda <Func <object, List <IDatabaseCommandParameter> > >(
Expression.Block(
new[] { result, typedParamVar },
expressions),
paramResource);
return(lambda.Compile());
}
public string Insert(ISqlResourceConfiguration config)
{
ISqlPropertyConfiguration[] properties = config.PropertyConfigurationsForCreate.ToArray();
StringBuilder stringBuilder = new StringBuilder("INSERT INTO [");
stringBuilder.Append(config.ModelConfiguration.TableName);
stringBuilder.Append("] (");
for (int i = 0; i < properties.Length; i++)
{
stringBuilder.Append("[");
stringBuilder.Append(properties[i].ColumnName);
stringBuilder.Append("]");
if (i != properties.Length - 1)
{
stringBuilder.Append(", ");
}
}
stringBuilder.Append(") VALUES (");
for (int i = 0; i < properties.Length; i++)
{
stringBuilder.Append(_sqlParamaterProvider.GenerateParameterName(properties[i].PropertyName));
if (i != properties.Length - 1)
{
stringBuilder.Append(", ");
}
}
stringBuilder.Append(") ");
stringBuilder.Append(
SelectAllBuilder(config)
.AppendWhereClause(config.PrimaryIdentifier.ColumnName, "SCOPE_IDENTITY()"));
return(stringBuilder.ToString());
}
// This is created by reflection, so be careful when changing/adding parameters
public SqlRuntimeConfiguration(
ISqlResourceConfiguration resourceConfiguration,
Func <IDatabaseResultReader, TResource> getSqlMapToResource,
Func <object, IDatabaseCommandParameter> getPrimaryKeySqlParameter,
Func <object, List <IDatabaseCommandParameter> > getSqlParametersForCreationFunc,
Func <object, List <IDatabaseCommandParameter> > getSqlParametersForUpdatingFunc,
string selectAll,
string selectById,
string deleteById,
string insert,
string updateById)
: base(resourceConfiguration)
{
GetSqlMapToResource = getSqlMapToResource;
GetPrimaryKeySqlParameter = getPrimaryKeySqlParameter;
GetSqlParametersForCreation = getSqlParametersForCreationFunc;
GetSqlParametersForUpdating = getSqlParametersForUpdatingFunc;
SelectAll = selectAll;
SelectById = selectById;
DeleteById = deleteById;
Insert = insert;
UpdateById = updateById;
}
public Func <IDatabaseResultReader, T> CreateObjectRelationalMap <T>(ISqlResourceConfiguration config)
where T : class, IRestResource
{
ISqlPropertyConfiguration[] orderedConfigs = config.PropertyConfigurationsForRead.OrderBy(prop => prop.Index).ToArray();
var paramDataReader = Expression.Parameter(typeof(IDatabaseResultReader), "databaseResultReader");
var result = Expression.Variable(typeof(T), "result");
var instantiateResultObj = Expression.Assign(result, Expression.New(typeof(T)));
List <Expression> expressions = new List <Expression>
{
instantiateResultObj
};
// Add assignment expression for each property that uses correct read method on SqlDataReader for the property type
for (int i = 0; i < orderedConfigs.Length; i++)
{
var property = Expression.Property(result, orderedConfigs[i].PropertyName);
var assignProperty = Expression.Assign(
Expression.MakeMemberAccess(result, property.Member),
GetMethodCallForType(paramDataReader, Expression.Constant(i), property));
expressions.Add(assignProperty);
}
// Return the result from the expression
expressions.Add(result);
var lambda = Expression.Lambda <Func <IDatabaseResultReader, T> >(
Expression.Block(
new[] { result },
expressions),
paramDataReader);
return(lambda.Compile());
}
public StringBuilder SelectByIdBuilder(ISqlResourceConfiguration config)
{
return(SelectAllBuilder(config)
.AppendWhereClause(config.PrimaryIdentifier.ColumnName, _sqlParamaterProvider.GetPrimaryKeyParameterName()));
}
public string SelectById(ISqlResourceConfiguration config)
{
return(SelectByIdBuilder(config).ToString());
}
public string DeleteById(ISqlResourceConfiguration config)
{
return(new StringBuilder($"DELETE FROM [{config.ModelConfiguration.TableName}]")
.AppendWhereClause(config.PrimaryIdentifier.ColumnName, _sqlParamaterProvider.GetPrimaryKeyParameterName())
.ToString());
}
public Func <object, List <IDatabaseCommandParameter> > GetSqlParametersForCreation(ISqlResourceConfiguration config)
{
ISqlPropertyConfiguration[] configs = config.PropertyConfigurationsForCreate.ToArray();
return(GetSqlParameters(config, configs));
}
public Func <object, List <IDatabaseCommandParameter> > GetSqlParametersForUpdating(ISqlResourceConfiguration config)
{
// Get configs that either do not have a computed value, or their computed value is allowed for PUT. If the computation is
// not allowed for PUT, then we don't want to try and update the data
ISqlPropertyConfiguration[] configs = config.PropertyConfigurationsForUpdate
.Where(x => x.ValueComputationFunc == null || (x.ValueComputationFunc?.EndpointTriggers & HttpVerbs.PUT) == HttpVerbs.PUT)
.ToArray();
return(GetSqlParameters(config, configs));
}
