public static void Update <T>(this ISqlConnectionProvider sqlConnectionProvider,
T objectToUpdate,
Expression <Func <T, object> > keyProperties = null,
string tableName = null,
string schemaName = null,
bool processColumnNames = true)
{
tableName = SqlTextFunctions.GetTableName(objectToUpdate.GetType(), tableName, schemaName);
using (var myConnection = sqlConnectionProvider.ToSqlConnection())
{
using (var myCommand = new SqlCommand {
Connection = myConnection
})
{
var setClauseProperties = SqlClrHelpers.GetRelevantPropertyInfos(objectToUpdate, null);
var whereClauseProperties = SqlClrHelpers.GetPropertiesFromObject(objectToUpdate, keyProperties);
SqlTextFunctions.UnUnderscoreColumnNames = processColumnNames;
BuildOutUpdateCommand(objectToUpdate,
tableName,
setClauseProperties,
whereClauseProperties,
myCommand,
processColumnNames);
SqlTextFunctions.UnUnderscoreColumnNames = true;
myConnection.Open();
SqlLogger.LogSqlCommand(myCommand);
myCommand.ExecuteNonQuery();
myConnection.Close();
}
}
}
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})";
}
public static IEnumerable <T> MyQuery <T>(this ISqlConnectionProvider sqlConnectionProvider,
string whereClause = null,
string tableName = null,
string schemaName = null)
{
whereClause = whereClause ?? "1 = 1";
var selectClause = GenerateSelectClause <T>();
tableName = SqlTextFunctions.GetTableName(typeof(T), tableName, schemaName);
var sqlStatement = $"SELECT {selectClause} FROM {tableName} WHERE {whereClause}";
SqlLogger.LogSqlStatement(sqlStatement);
var results = sqlConnectionProvider.Query <T>(sqlStatement).ToList();
return(results);
}
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}";
}
public static void Delete <T>(this ISqlConnectionProvider sqlConnectionProvider, T objToDelete,
Expression <Func <T, object> > keyProperties = null, string tableName = null, string schemaName = null, bool processColumnNames = true)
{
tableName = SqlTextFunctions.GetTableName(objToDelete.GetType(), tableName, schemaName);
using (var myConnection = sqlConnectionProvider.ToSqlConnection())
{
using (var myCommand = new SqlCommand()
{
Connection = myConnection
})
{
SqlTextFunctions.UnUnderscoreColumnNames = processColumnNames;
BuildOutMyCommand(objToDelete, keyProperties, tableName, myCommand);
SqlTextFunctions.UnUnderscoreColumnNames = true;
myConnection.Open();
SqlLogger.LogSqlStatement(myCommand.CommandText);
myCommand.ExecuteNonQuery();
myConnection.Close();
}
}
}
public static void Insert(this ISqlConnectionProvider sqlConnectionProvider,
object obj,
string tableName = null,
string schemaName = null,
IEnumerable <string> columnsToIgnore = null,
bool processColumnNames = true,
bool useIdentityInsert = false)
{
tableName = SqlTextFunctions.GetTableName(obj.GetType(), tableName, schemaName);
var propertyInfos = SqlClrHelpers.GetRelevantPropertyInfos(obj, columnsToIgnore);
if (propertyInfos.Count == 0)
{
return;
}
using (var myConnection = sqlConnectionProvider.GetSqlConnectionWithTimeout(60))
{
using (var myCommand = new SqlCommand {
Connection = myConnection
})
{
SqlTextFunctions.UnUnderscoreColumnNames = processColumnNames;
BuildOutMyCommand(obj, tableName, propertyInfos, myCommand, processColumnNames);
SqlTextFunctions.UnUnderscoreColumnNames = true;
myConnection.Open();
SqlLogger.LogSqlCommand(myCommand);
// if (useIdentityInsert)
// myConnection.Execute("set identity_insert on;");
myCommand.ExecuteNonQuery();
myConnection.Close();
}
}
}
