/// <summary>
/// Provides low level functionality to delete entities from a given table
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="hopper"></param>
/// <param name="tableName"></param>
/// <param name="whereClause"></param>
/// <exception cref="HopWhereClauseParseException"></exception>
public static void Delete <T>(this IHop hopper, string tableName = "", string whereClause = "")
{
tableName = string.IsNullOrWhiteSpace(tableName) ? HopBase.GetTypeToTableNameService(typeof(T)) : tableName;
var cmdText = string.Format("DELETE FROM {0}", tableName);
if (!string.IsNullOrWhiteSpace(whereClause))
{
cmdText += string.Format(" WHERE {0}", whereClause);
}
using (var dbCommand = hopper.Connection.CreateCommand())
{
dbCommand.CommandText = cmdText;
try
{
dbCommand.Connection.Open();
dbCommand.ExecuteNonQuery();
}
catch (SqlException sqlException)
{
throw new HopWhereClauseParseException(dbCommand.CommandText, sqlException);
}
finally
{
dbCommand.Connection.Close();
}
}
}
/// <summary>
/// Insert a custom query into the database
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="hopper"></param>
/// <param name="intoClause">Tablename and optional column names to insert to</param>
/// <param name="insertClause">Values part of the query</param>
/// <returns>Last inserted Id</returns>
/// <exception cref="HopInsertClauseParseException"></exception>
public static int Insert <T>(this IHop hopper, string intoClause = "", string insertClause = "")
{
using (var dbCommand = hopper.Connection.CreateCommand())
{
intoClause = string.IsNullOrWhiteSpace(intoClause) ? HopBase.GetTypeToTableNameService(typeof(T)) : intoClause;
dbCommand.CommandText = string.Format("INSERT INTO {0} VALUES {1}; SELECT @@IDENTITY AS 'Identity'", intoClause, insertClause);
var lastId = 0;
try
{
hopper.Connection.Open();
lastId = (int)(decimal)dbCommand.ExecuteScalar();
}
catch (SqlException exception)
{
throw new HopInsertClauseParseException(exception)
{
InsertClause = dbCommand.CommandText
};
}
finally
{
if (hopper.Connection.State == ConnectionState.Open)
{
hopper.Connection.Close();
}
}
return(lastId);
}
}
public static void AddTypeToCache <T>(IDbConnection connection) where T : new()
{
if (VerifiedTypesCache.Contains(typeof(T)))
{
return;
}
var sqlConnection = connection as SqlConnection;
if (sqlConnection == null)
{
return;
}
VerifiedTypesCache.Add(typeof(T));
var tableName = HopBase.GetTypeToTableNameService(typeof(T));
var columns =
connection
.Hop()
.ReadTuples <Tuple <string, int>, T>("column_name, ordinal_position", string.Format("table_name = '{0}'", tableName), "information_schema.columns")
.ToList();
var unMatchedProperties = TypeCache.Get <T>().Properties.Where(x => columns.All(y => y.Item1 != x.Name)).ToList();
if (!unMatchedProperties.Any())
{
return;
}
var sb = new StringBuilder();
//create table if no column found
if (!columns.Any())
{
var propertyInfo = TypeCache.Get <T>().IdProperty;
unMatchedProperties = unMatchedProperties.Where(x => x != TypeCache.Get <T>().IdProperty).ToList();
sb.AppendLine(string.Format("CREATE TABLE {0}({1} {2} PRIMARY KEY {3});", tableName, propertyInfo.Name, GetSqlString(GetSqlType(propertyInfo.PropertyType)), propertyInfo.PropertyType == typeof(int) ? "IDENTITY" : string.Empty));
}
foreach (var source in unMatchedProperties)
{
sb.Append(string.Format("ALTER TABLE {0} ", tableName));
var sqlDbType = GetSqlType(source.PropertyType);
sb.AppendLine(string.Format(" ADD {0} {1}", source.Name, GetSqlString(sqlDbType)));
}
using (var dbCommand = connection.CreateCommand())
{
dbCommand.CommandText = sb.ToString();
connection.Open();
dbCommand.ExecuteNonQuery();
connection.Close();
}
}
public static void Update <T>(this IHop hopper, IEnumerable <T> instances) where T : new()
{
SchemaVerifierService.AddTypeToCache <T>(hopper.Connection);
IIdExtractorService idExtractorService = HopBase.GetIdExtractorService();
List <T> instanceList = instances as List <T> ?? instances.ToList();
var sb = new StringBuilder();
int paramCounter = 0;
using (var dbCommand = new SqlCommand())
{
dbCommand.Connection = (SqlConnection)hopper.Connection;
foreach (T inst in instanceList)
{
sb.AppendLine(string.Format("UPDATE {0} SET ", HopBase.GetTypeToTableNameService(typeof(T))));
sb.AppendLine(
TypeCache.Get <T>().PropertiesWithoutId
.Select((x, i) =>
{
string paramName = string.Format("@param{0}{1}", paramCounter, i);
object value = x.GetValue(inst, null);
if (value == null)
{
return(string.Empty);
}
dbCommand.Parameters.Add(new SqlParameter(paramName, value));
return(string.Format("{0} = {1}", x.Name, paramName));
})
.Where(x => x != string.Empty)
.Aggregate((set1, set2) => set1 + ", " + set2));
object instanceId = idExtractorService.GetId(inst);
if (instanceId == null || HopBase.GetDefault(instanceId.GetType()).Equals(instanceId))
{
throw new HopUpdateWithoutKeyException(inst);
}
sb.AppendLine(string.Format(" WHERE {0} = {1}", idExtractorService.GetIdField <T>(), instanceId));
paramCounter++;
}
dbCommand.CommandText = sb.ToString();
dbCommand.Connection.Open();
dbCommand.ExecuteNonQuery();
dbCommand.Connection.Close();
}
}
/// <summary>
/// Deletes a collection of instances in the database
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="hopper"></param>
/// <param name="instances"></param>
/// <exception cref="ArgumentNullException"></exception>
public static void Delete <T>(this IHop hopper, ICollection <T> instances)
{
if (instances == null)
{
throw new ArgumentNullException("instances", "Please provide a non null value for parameter instances");
}
var tableName = HopBase.GetTypeToTableNameService(typeof(T));
var idExtractorService = HopBase.GetIdExtractorService();
var idField = idExtractorService.GetIdField <T>();
var inClause =
idExtractorService
.GetIds <T, int>(instances)
.Select(x => x.ToString(CultureInfo.InvariantCulture))
.Aggregate((id1, id2) => id1 + ", " + id2);
Delete <T>(hopper, tableName, string.Format("{0} IN({1})", idField, inClause));
}
/// <summary>
/// Inserts a collection of entities to the database
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="hopper"></param>
/// <param name="instances">Collection to insert</param>
/// <exception cref="ArgumentNullException"></exception>
public static void Insert <T>(this IHop hopper, ICollection <T> instances) where T : new()
{
if (instances == null)
{
throw new ArgumentNullException("instances", "Please provide a non null value to parameter instances");
}
if (!instances.Any())
{
return;
}
SchemaVerifierService.AddTypeToCache <T>(hopper.Connection);
var propertyInfos = TypeCache.Get <T>().PropertiesWithoutId;
var objects =
instances
.Select(objToInsert =>
propertyInfos
.Select(prop => prop.GetValue(objToInsert, null).ToSqlString())
.Aggregate((field1, field2) => field1 + ", " + field2)
);
var tableName = HopBase.GetTypeToTableNameService(typeof(T));
var columnList =
propertyInfos
.Select(x => x.Name)
.Aggregate((field1, field2) => field1 + ", " + field2);
var intoClause = string.Format("{0} ({1})", tableName, columnList);
var valuesClause =
objects
.Select(x => "(" + x + ")")
.Aggregate((obj1, obj2) => obj1 + ", " + obj2);
var lastId = hopper.Insert <T>(intoClause, valuesClause);
foreach (T source in instances.Reverse())
{
HopBase.GetIdExtractorService().SetId(source, lastId--);
}
}
public static void Update <T>(this IHop hopper, string where = "", string setClause = "") where T : new()
{
SchemaVerifierService.AddTypeToCache <T>(hopper.Connection);
using (var dbCommand = hopper.Connection.CreateCommand())
{
where = string.IsNullOrWhiteSpace(where) ? " 1 = 1" : where;
dbCommand.CommandText = string.Format("UPDATE {0} SET {1} WHERE {2}", HopBase.GetTypeToTableNameService(typeof(T)), setClause, where);
dbCommand.Connection.Open();
dbCommand.ExecuteNonQuery();
dbCommand.Connection.Close();
}
}
private static string SelectFrom <T>(string columnsToSelect = "*", string fromTable = null) where T : new()
{
var tableName = HopBase.GetTypeToTableNameService(typeof(T));
return(string.Format("SELECT {1} FROM {0} ", fromTable ?? tableName, columnsToSelect));
}