/// <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);
}
}
/// <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 single entity of type T to the database
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="hopper"> </param>
/// <param name="instance">Instance to insert to database</param>
/// <exception cref="ArgumentNullException">thrown when instance is null</exception>
public static void InsertSingle <T>(this IHop hopper, T instance) where T : new()
{
if (instance == null)
{
throw new ArgumentNullException("instance", "Please provide a non null value to parameter instace ");
}
hopper.Insert(new[] { instance });
}
public RecipeHop(IHop hop, Weight weight, int boilTime, decimal alphaAcid, Recipe recipe)
: base(hop.Name)
{
this.hop = hop;
this.Weight = weight;
this.BoilTime = boilTime;
this.alphaAcid = alphaAcid;
this.Recipe = recipe;
}
/// <summary>
/// Deletes a single instance in the database
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="hopper"></param>
/// <param name="instance">Instance to delete</param>
/// <exception cref="ArgumentNullException"></exception>
public static void DeleteSingle <T>(this IHop hopper, T instance)
{
if (instance == null)
{
throw new ArgumentNullException("instance", "Please provide a non null value for parameter instance");
}
hopper.Delete(new[] { instance });
}
public static T ReadSingle <T>(this IHop hopper, T instance) where T : class, new()
{
if (instance == null)
{
throw new ArgumentNullException("instance", "ReadSingle method expects an instance to read, not null.");
}
return(hopper.Read(new[] { instance }).FirstOrDefault());
}
private static IEnumerable <T> ReadInternal <T>(this IHop hopper, IDbCommand command) where T : new()
{
using (command)
{
command.Connection = hopper.Connection;
command.Connection.Open();
return(HopBase.GetMaterializerService().ReadObjects <T>(command.ExecuteReader(CommandBehavior.CloseConnection)).ToList());
}
}
public RecipeHop(IHop hop, Weight weight, int boilTime, Recipe recipe)
: base(hop.Name)
{
//this.hop = hop;
this.Name = hop.Name;
this.Description = hop.Description;
this.AddOilCharacteristics(hop.GetCharacteristics());
this.Weight = weight;
this.BoilTime = boilTime;
this.Recipe = recipe;
}
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();
}
}
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();
}
}
/// <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));
}
public static IEnumerable <T> Read <T>(this IHop hopper, string whereClause) where T : new()
{
string cmdText = null;
try
{
cmdText = string.Format("{0} WHERE {1}", SelectFrom <T>(), whereClause);
using (var sqlCommand = new SqlCommand(cmdText))
{
return(hopper.ReadInternal <T>(sqlCommand));
}
}
catch (SqlException sqlException)
{
throw new HopWhereClauseParseException(cmdText, sqlException);
}
}
/// <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 IEnumerable <T> Read <T>(this IHop hopper, IEnumerable <T> instances) where T : new()
{
if (instances == null)
{
throw new ArgumentNullException("instances", "Read extension method expects an array of instances to read");
}
var listInstances = instances as List <T> ?? instances.ToList();
if (!listInstances.Any())
{
return(Enumerable.Empty <T>());
}
var idExtractorService = HopBase.GetIdExtractorService();
var ids = idExtractorService.GetIds(listInstances).Select((x, i) =>
{
if (HopBase.GetDefault(x.GetType()).Equals(x))
{
throw new HopReadWithoutKeyException(listInstances[i]);
}
return(new SqlParameter(string.Format("@param{0}", i), x));
}).ToArray();
var idField = idExtractorService.GetIdField <T>();
var whereClause = ids.Select(p => p.ParameterName).Aggregate((p1, p2) => p1 + " , " + p2);
var selectFrom = SelectFrom <T>();
var cmdText = string.Format("{0} WHERE {2} IN ( {1} )", selectFrom, whereClause, idField);
using (var command = new SqlCommand())
{
command.CommandText = cmdText;
command.Parameters.AddRange(ids);
return(hopper.ReadInternal <T>(command));
}
}
public Guid Save(IHop hop)
{
using (var tran = Session.BeginTransaction())
{
try
{
_session.SaveOrUpdate(hop);
tran.Commit();
}
catch (Exception ex)
{
tran.Rollback();
_session.Close();
_session.Dispose();
_session = null;
throw ex;
}
}
return hop.Id;
}
public static IEnumerable <T> ReadTuples <T, TEntity>(this IHop hop, string columnSelectQuery, string whereQuery = null, string fromTable = null) where T : class, IStructuralEquatable, IStructuralComparable, IComparable where TEntity : new()
{
var selectFrom = SelectFrom <TEntity>(columnSelectQuery, fromTable);
using (var dbCommand = hop.Connection.CreateCommand())
{
dbCommand.CommandText = selectFrom + " WHERE " + (whereQuery ?? "1=1");
dbCommand.Connection.Open();
var genericArguments = typeof(T).GetGenericArguments();
var openGenericTupleCreator =
typeof(Tuple)
.GetMethods()
.FirstOrDefault(x => x.GetGenericArguments().Count() == genericArguments.Count());
if (openGenericTupleCreator == null)
{
yield break;
}
var tupleCreationFactory = openGenericTupleCreator.MakeGenericMethod(genericArguments);
using (var executeReader = dbCommand.ExecuteReader(CommandBehavior.CloseConnection))
{
var parameterStubList = Enumerable.Range(0, genericArguments.Count());
while (executeReader.Read())
{
var factoryParameters = parameterStubList
.Select(x => executeReader.Get(genericArguments[x], x))
.ToArray();
yield return(tupleCreationFactory.Invoke(null, factoryParameters) as T);
}
}
}
}
public static T ReadSingle <T>(this IHop hopper, string whereClause = "") where T : new()
{
return(hopper.Read <T>(whereClause).FirstOrDefault());
}
public virtual void AddHop(IHop hop, Weight weight, int boilTime)
{
hops.Add(new RecipeHop(hop, weight, boilTime, this));
}
public virtual void AddHop(IHop hop, Weight weight, int boilTime, decimal alphaAcid)
{
hops.Add(new RecipeHop(hop, weight, boilTime, alphaAcid, this));
}
public static IEnumerable <T> ReadAll <T>(this IHop hopper) where T : new()
{
return(hopper.ReadInternal <T>(new SqlCommand(SelectFrom <T>())));
}
public static void UpdateSingle <T>(this IHop hopper, T instance) where T : new()
{
hopper.Update(new[] { instance });
}
public static T ReadTupleSingle <T, TEntity>(this IHop hop, string columnSelectQuery, string whereQuery = null, string fromTable = null) where T : class, IStructuralEquatable, IStructuralComparable, IComparable where TEntity : new()
{
return(ReadTuples <T, TEntity>(hop, columnSelectQuery, whereQuery, fromTable).FirstOrDefault());
}
