private static void BuildOutMyCommand(object obj,
string tableName,
IList <PropertyInfo> propertyInfos,
SqlCommand myCommand,
bool processColumnNames = true)
{
var columnList = string.Join(", ", propertyInfos.Select(SqlTextFunctions.GetColumnNameFromPropertyInfo));
var sqlParameterNames = string.Join(", ", propertyInfos.Select(SqlTextFunctions.GetParameterName));
var sqlParameters = propertyInfos.Select(pi => SqlTextFunctions.GetParameter(obj, pi)).ToList();
myCommand.Parameters.AddRange(sqlParameters.ToArray());
myCommand.CommandText = $"INSERT INTO {tableName} ({columnList}) values ({sqlParameterNames})";
}
private static void BuildOutUpdateCommand(object objectToUpdate, string tableName,
IList <PropertyInfo> setClauseProperties,
IList <PropertyInfo> whereClauseProperties,
SqlCommand myCommand,
bool processColumnNames)
{
var setClauseColumnNames =
setClauseProperties.Select(SqlTextFunctions.GetColumnNameFromPropertyInfo).ToList();
var setClauseParameterNames =
setClauseProperties.Select(SqlTextFunctions.GetSetClauseParameterName).ToList();
var setClauseParts = Enumerable
.Range(0, setClauseColumnNames.Count)
.Select(index => $"{setClauseColumnNames[index]} = {setClauseParameterNames[index]}");
var setClause = string.Join(", ", setClauseParts);
var whereClauseColumnNames =
whereClauseProperties.Select(SqlTextFunctions.GetColumnNameFromPropertyInfo).ToList();
var whereClauseParameterNames =
whereClauseProperties.Select(SqlTextFunctions.GetWhereClauseParameterName).ToList();
var whereClauseParts = Enumerable
.Range(0, whereClauseColumnNames.Count())
.Select(index => $"{whereClauseColumnNames[index]} = {whereClauseParameterNames[index]}");
var whereClause = string.Join(" AND ", whereClauseParts);
var setClauseParameters = Enumerable
.Range(0, setClauseProperties.Count)
.Select(
index =>
SqlTextFunctions.GetParameter(objectToUpdate, setClauseProperties[index],
setClauseParameterNames[index]))
.ToList();
var whereClauseParameters = Enumerable
.Range(0, whereClauseProperties.Count)
.Select(
index =>
SqlTextFunctions.GetParameter(objectToUpdate, whereClauseProperties[index],
whereClauseParameterNames[index]))
.ToList();
myCommand.Parameters.AddRange(setClauseParameters.ToArray());
myCommand.Parameters.AddRange(whereClauseParameters.ToArray());
myCommand.CommandText = $"UPDATE {tableName} SET {setClause} WHERE {whereClause}";
}
private static void BuildOutMyCommand <T>(T objToDelete, Expression <Func <T, object> > keyProperties, string tableName, SqlCommand myCommand)
{
var whereClauseProperties = SqlClrHelpers.GetPropertiesFromObject(objToDelete, keyProperties);
var whereClauseColumnNames = whereClauseProperties.Select(SqlTextFunctions.GetColumnNameFromPropertyInfo).ToList();
var whereClauseParameterNames = whereClauseProperties.Select(SqlTextFunctions.GetParameterName).ToList();
var whereClauseParts = Enumerable
.Range(0, whereClauseColumnNames.Count)
.Select(index => $"{whereClauseColumnNames[index]} = {whereClauseParameterNames[index]}");
var whereClause = string.Join(" AND ", whereClauseParts);
var whereClauseParameters = Enumerable
.Range(0, whereClauseProperties.Count)
.Select(
index => SqlTextFunctions.GetParameter(objToDelete, whereClauseProperties[index], whereClauseParameterNames[index]))
.ToList();
myCommand.Parameters.AddRange(whereClauseParameters.ToArray());
myCommand.CommandText = $"DELETE FROM {tableName} WHERE {whereClause}";
}