/// <summary>
/// Generates a batch that will get a specific page of data that satisfies the parameters specified
/// </summary>
/// <param name="PageSize">Page size</param>
/// <param name="CurrentPage">The current page (starting at 0)</param>
/// <param name="OrderBy">The order by portion of the query</param>
/// <param name="Parameters">Parameters</param>
/// <returns>Batch with the appropriate commands</returns>
public IBatch Paged(int PageSize, int CurrentPage, string OrderBy, params IParameter[] Parameters)
{
string WhereCommand = "";
var FinalOrderBy = Mapping.IDProperties.ToString(x => x.Name);
if (!string.IsNullOrEmpty(OrderBy))
{
FinalOrderBy = OrderBy;
}
int PageStart = CurrentPage * PageSize;
if (Parameters != null && Parameters.Length > 0)
{
WhereCommand += " WHERE " + Parameters.ToString(x => x.ToString(), " AND ");
}
return(QueryProvider
.Batch(Source)
.AddCommand(null,
null, string.Format(CultureInfo.InvariantCulture,
"SELECT Paged.* FROM (SELECT ROW_NUMBER() OVER (ORDER BY {0}) AS Row, Query.* FROM (SELECT {1} FROM {2} {3}) as Query) AS Paged WHERE Row>{4} AND Row<={5}",
FinalOrderBy,
GetColumns(Mapping),
Mapping.TableName,
WhereCommand,
PageStart,
PageStart + PageSize),
CommandType.Text,
Parameters));
}
/// <summary>
/// Generates a batch that will get the specific property for the object
/// </summary>
/// <typeparam name="P">Property type</typeparam>
/// <param name="Object">Object to get the property for</param>
/// <param name="Property">Property to get</param>
/// <returns>Batch with the appropriate commands</returns>
public IBatch LoadProperty <P>(T Object, IProperty Property)
{
return(QueryProvider.Batch(Source)
.AddCommand(null, null,
Property.LoadCommand,
Property.LoadCommandType,
Mapping.IDProperties.FirstOrDefault().GetValue(Object)));
}
/// <summary>
/// Generates a batch that will insert the data from the objects
/// </summary>
/// <param name="Objects">Objects to insert</param>
/// <returns>Batch with the appropriate commands</returns>
public IBatch Insert(IEnumerable <T> Objects)
{
var TempBatch = QueryProvider.Batch(Source);
foreach (T Object in Objects)
{
TempBatch.AddCommand(Insert(Object));
}
return(TempBatch);
}
/// <summary>
/// Generates a batch that will delete the object
/// </summary>
/// <param name="Object">Object to delete</param>
/// <returns>Batch with the appropriate commands</returns>
public IBatch Delete(T Object)
{
return(QueryProvider
.Batch(Source)
.AddCommand(null,
null,
Mapping.DeleteCommand,
Mapping.DeleteCommandType,
Mapping.IDProperties.ToArray(x => x.GetParameter(Object))));
}
/// <summary>
/// Generates a batch that will update the data from the objects
/// </summary>
/// <param name="Objects">Objects to update</param>
/// <returns>Batch with the appropriate commands</returns>
public IBatch Update(IEnumerable <T> Objects)
{
IBatch TempBatch = QueryProvider.Batch(Source);
foreach (T Object in Objects)
{
TempBatch.AddCommand(Update(Object));
}
return(TempBatch);
}
/// <summary>
/// Generates a batch that will update the data from the object
/// </summary>
/// <param name="Object">Object to update</param>
/// <returns>Batch with the appropriate commands</returns>
public IBatch Update(T Object)
{
return(QueryProvider.Batch(Source)
.AddCommand(null,
null, Mapping.UpdateCommand, Mapping.UpdateCommandType,
Mapping.Properties
.Where(x => x is IMap || x is IReference)
.Where(x => !x.AutoIncrement)
.Concat(Mapping.IDProperties)
.ToArray(x => x.GetParameter(Object))));
}
/// <summary>
/// Generates a batch that will insert the data from the object
/// </summary>
/// <param name="Object">Object to insert</param>
/// <returns>Batch with the appropriate commands</returns>
public IBatch Insert(T Object)
{
return(QueryProvider.Batch(Source)
.AddCommand((x, y) => y[0].CopyTo(x.Object),
Object,
Mapping.InsertCommand,
Mapping.InsertCommandType,
Mapping.Properties
.Where(x => x is IMap || x is IReference)
.Concat(Mapping.IDProperties)
.Where(x => !x.AutoIncrement)
.ToArray(x => x.GetParameter(Object))));
}
/// <summary>
/// Generates a batch that will get the first item that satisfies the parameters specified
/// </summary>
/// <param name="Parameters">Parameters</param>
/// <returns>Batch with the appropriate commands</returns>
public IBatch Any(params IParameter[] Parameters)
{
if (Mapping == null)
{
return(QueryProvider.Batch(Source));
}
Parameters = Parameters.Check(new IParameter[] { });
return(QueryProvider.Batch(Source)
.AddCommand(null,
null, string.Format(CultureInfo.InvariantCulture,
"{0}{1}",
Mapping.SelectAnyCommand,
Parameters != null && Parameters.Length > 0 ? " WHERE " + Parameters.ToString(x => x.ToString(), " AND ") : ""),
Parameters != null && Parameters.Length > 0 ? CommandType.Text : Mapping.SelectAnyCommandType,
Parameters));
}
/// <summary>
/// Deletes an object from the database
/// </summary>
/// <typeparam name="ObjectType">Object type</typeparam>
/// <param name="Object">Object to delete</param>
public void Delete <ObjectType>(ObjectType Object)
where ObjectType : class
{
Cache.RemoveByTag(typeof(ObjectType).GetName());
foreach (ISourceInfo Source in SourceProvider.Where(x => x.Writable).OrderBy(x => x.Order))
{
IMapping Mapping = MapperProvider[typeof(ObjectType), Source];
if (Mapping != null)
{
var Generator = QueryProvider.Generate <ObjectType>(Source, MapperProvider[typeof(ObjectType), Source], MapperProvider.GetStructure(Mapping.DatabaseConfigType));
var TempBatch = QueryProvider.Batch(Source);
CascadeDelete <ObjectType>(Object, Source, Mapping, TempBatch, new List <object>());
TempBatch.AddCommand(Generator.Delete(Object));
TempBatch.Execute();
}
}
}
/// <summary>
/// Generates a batch that will get the number of pages for a given page size given the
/// parameters specified
/// </summary>
/// <param name="Parameters">Parameters</param>
/// <param name="PageSize">Page size</param>
/// <returns>Batch with the appropriate commands</returns>
public IBatch PageCount(int PageSize, params IParameter[] Parameters)
{
string WhereCommand = "";
if (Parameters != null && Parameters.Length > 0)
{
WhereCommand += " WHERE " + Parameters.ToString(x => x.ToString(), " AND ");
}
return(QueryProvider
.Batch(Source)
.AddCommand(null, null, string.Format(CultureInfo.InvariantCulture,
"SELECT COUNT(*) as Total FROM (SELECT {0} FROM {1} {2}) as Query",
Mapping.IDProperties.ToString(x => x.FieldName),
Mapping.TableName,
WhereCommand),
CommandType.Text,
Parameters));
}
/// <summary>
/// Creates a batch.
/// </summary>
/// <param name="source">The source.</param>
/// <param name="dynamoFactory">The dynamo factory.</param>
/// <returns>Creates a batch</returns>
/// <exception cref="ArgumentNullException">source</exception>
/// <exception cref="ArgumentException">Provider not found</exception>
public SQLHelper CreateBatch(IDatabase source, DynamoFactory dynamoFactory)
{
if (source is null)
{
throw new ArgumentNullException(nameof(source));
}
if (!Providers.TryGetValue(source.Provider, out var QueryProvider))
{
throw new ArgumentException("Provider not found: " + source.Provider);
}
if (IsDebug)
{
Logger.Debug("Creating batch for data source {SourceName:l}", source.Name);
}
return(QueryProvider.Batch(source, dynamoFactory));
}
/// <summary>
/// Generates a batch that will get all items for the given type the parameters specified
/// </summary>
/// <param name="Parameters">Parameters</param>
/// <returns>Batch with the appropriate commands</returns>
public IBatch All(params IParameter[] Parameters)
{
if (Mapping == null)
{
return(QueryProvider.Batch(Source));
}
Parameters = Parameters.Check(new IParameter[] { });
string Command = "(*)";
Parameters.ForEach(x =>
{
Command = string.Format(CultureInfo.InvariantCulture, "(&({0}={1})({2}))", x.ID, x.InternalValue.ToString(), Command);
});
return(QueryProvider.Batch(Source)
.AddCommand(null,
null,
Command,
CommandType.Text,
Parameters));
}
/// <summary>
/// Generates a batch that will get all items for the given type the parameters specified
/// </summary>
/// <param name="Parameters">Parameters</param>
/// <param name="Limit">Max number of items to return</param>
/// <returns>Batch with the appropriate commands</returns>
public IBatch All(int Limit, params IParameter[] Parameters)
{
if (Limit < 1)
{
return(All(Parameters));
}
if (Mapping == null)
{
return(QueryProvider.Batch(Source));
}
Parameters = Parameters.Check(new IParameter[] { });
return(QueryProvider.Batch(Source)
.AddCommand(null,
null, string.Format(CultureInfo.InvariantCulture,
"SELECT TOP {0} {1} FROM {2}{3}",
Limit,
GetColumns(Mapping),
Mapping.TableName,
Parameters != null && Parameters.Length > 0 ? " WHERE " + Parameters.ToString(x => x.ToString(), " AND ") : ""),
CommandType.Text,
Parameters));
}
/// <summary>
/// Saves an object to the database
/// </summary>
/// <typeparam name="ObjectType">Object type</typeparam>
/// <typeparam name="PrimaryKeyType">Primary key type</typeparam>
/// <param name="Object">Object to save</param>
public void Save <ObjectType, PrimaryKeyType>(ObjectType Object)
where ObjectType : class, new()
{
Cache.RemoveByTag(typeof(ObjectType).GetName());
foreach (ISourceInfo Source in SourceProvider.Where(x => x.Writable).OrderBy(x => x.Order))
{
IMapping Mapping = MapperProvider[typeof(ObjectType), Source];
if (Mapping != null)
{
IGenerator <ObjectType> Generator = QueryProvider.Generate <ObjectType>(Source, MapperProvider[typeof(ObjectType), Source]);
IBatch TempBatch = QueryProvider.Batch(Source);
CascadeSave <ObjectType>(Object, Source, Mapping, TempBatch, new List <object>());
TempBatch.Execute();
TempBatch = QueryProvider.Batch(Source);
TempBatch.AddCommand(Generator.Save <PrimaryKeyType>(Object));
TempBatch.Execute();
TempBatch = QueryProvider.Batch(Source);
JoinsDelete <ObjectType>(Object, Source, Mapping, TempBatch, new List <object>());
JoinsSave <ObjectType>(Object, Source, Mapping, TempBatch, new List <object>());
TempBatch.RemoveDuplicateCommands().Execute();
}
}
}
/// <summary>
/// Deletes items from the joining table for the property
/// </summary>
/// <param name="Property">Property</param>
/// <param name="Object">Object</param>
/// <typeparam name="P">Property type</typeparam>
/// <returns>The batch with the appropriate commands</returns>
public IBatch JoinsDelete <P>(IProperty <T, P> Property, T Object)
{
var ReturnValue = QueryProvider.Batch(Source);
if (Object == null)
{
return(ReturnValue);
}
var List = (P)Property.GetValue(Object);
if (List == null)
{
return(ReturnValue);
}
var CurrentID = Mapping.IDProperties.FirstOrDefault().GetValue(Object);
IMapping ForeignMapping = Property.ForeignMapping;
if (string.Compare(Mapping.TableName, ForeignMapping.TableName, StringComparison.Ordinal) == 0 &&
Property as IManyToOne != null)
{
ReturnValue.AddCommand(null,
Object,
"DELETE FROM " + Property.TableName + " WHERE " + Mapping.TableName + Mapping.IDProperties.FirstOrDefault().FieldName + "2=@0",
CommandType.Text,
CurrentID);
}
else
{
ReturnValue.AddCommand(null,
Object,
"DELETE FROM " + Property.TableName + " WHERE " + Mapping.TableName + Mapping.IDProperties.FirstOrDefault().FieldName + "=@0",
CommandType.Text,
CurrentID);
}
return(ReturnValue);
}
/// <summary>
/// Saves the object to the source
/// </summary>
/// <typeparam name="PrimaryKeyType">Primary key type</typeparam>
/// <param name="Object">Object to save</param>
public IBatch Save <PrimaryKeyType>(T Object)
{
var TempBatch = QueryProvider.Batch(Source);
IProperty <T> IDProperty = ((IProperty <T>)Mapping.IDProperties.FirstOrDefault());
var IDValue = IDProperty.GetValue(Object).To(default(PrimaryKeyType));
var Comparer = new GenericEqualityComparer <PrimaryKeyType>();
IParameter Param1 = null;
if (Comparer.Equals(IDValue, default(PrimaryKeyType)))
{
return(TempBatch.AddCommand(Insert(Object)));
}
if (IDProperty.AutoIncrement)
{
return(TempBatch.AddCommand(Update(Object)));
}
Param1 = typeof(PrimaryKeyType).Is(typeof(string)) ? (IParameter) new StringEqualParameter(IDValue.ToString(), IDProperty.FieldName, IDValue.ToString().Length, IDProperty.FieldName, Source.ParameterPrefix) : (IParameter) new EqualParameter <PrimaryKeyType>(IDValue, IDProperty.FieldName, IDProperty.FieldName, Source.ParameterPrefix);
if (Any(Param1).Execute()[0].Count == 0)
{
return(TempBatch.AddCommand(Insert(Object)));
}
return(TempBatch.AddCommand(Update(Object)));
}
public static void Main(string[] args)
{
var StopWatch = new StopWatch();
StopWatch.Start();
IBootstrapper Bootstrapper = Utilities.IoC.Manager.Bootstrapper;
StopWatch.Stop();
Console.WriteLine("Start up: " + StopWatch.ElapsedTime);
using (IDisposable StartProfiler = Utilities.Profiler.Profiler.StartProfiling())
{
var Rand = new System.Random();
SingleSourceCached(Rand);
MultipleSourceCached(Rand);
MultipleSourceCachedCascade(Rand);
}
Console.WriteLine(Utilities.Profiler.Profiler.StopProfiling(false).ToString());
QueryProvider.Batch("Data Source=localhost;Initial Catalog=SpeedTest;Integrated Security=SSPI;Pooling=false")
.AddCommand(null, null, CommandType.Text, "ALTER DATABASE SpeedTest SET OFFLINE WITH ROLLBACK IMMEDIATE")
.AddCommand(null, null, CommandType.Text, "ALTER DATABASE SpeedTest SET ONLINE")
.AddCommand(null, null, CommandType.Text, "DROP DATABASE SpeedTest")
.Execute();
Console.ReadKey();
}
/// <summary>
/// Saves items to the joining table for the property
/// </summary>
/// <param name="Property">Property</param>
/// <param name="Object">Object</param>
/// <typeparam name="P">Property type</typeparam>
/// <typeparam name="ItemType">Item type</typeparam>
/// <returns>The batch with the appropriate commands</returns>
public IBatch JoinsSave <P, ItemType>(IProperty <T, P> Property, T Object)
{
var ReturnValue = QueryProvider.Batch(Source);
if (Object == null)
{
return(ReturnValue);
}
if (Property as IManyToOne != null)
{
var Item = (P)Property.GetValue(Object);
if (Item == null)
{
return(ReturnValue);
}
var CurrentID = ((IProperty <T>)Mapping.IDProperties.FirstOrDefault()).GetValue(Object);
IMapping ForeignMapping = Property.ForeignMapping;
var ForeignID = ForeignMapping.IDProperties.FirstOrDefault().GetValue(Item);
string Parameters = "";
object[] Values = new object[2];
if (string.Compare(Mapping.TableName, ForeignMapping.TableName, StringComparison.InvariantCulture) == 0)
{
Parameters = Mapping.TableName + Mapping.IDProperties.FirstOrDefault().FieldName + "," + ForeignMapping.TableName + ForeignMapping.IDProperties.FirstOrDefault().FieldName + "2";
Values[1] = CurrentID;
Values[0] = ForeignID;
}
else if (string.Compare(Mapping.TableName, ForeignMapping.TableName, StringComparison.InvariantCulture) <= 0)
{
Parameters = Mapping.TableName + Mapping.IDProperties.FirstOrDefault().FieldName + "," + ForeignMapping.TableName + ForeignMapping.IDProperties.FirstOrDefault().FieldName;
Values[0] = CurrentID;
Values[1] = ForeignID;
}
else
{
Parameters = ForeignMapping.TableName + ForeignMapping.IDProperties.FirstOrDefault().FieldName + "," + Mapping.TableName + Mapping.IDProperties.FirstOrDefault().FieldName;
Values[1] = CurrentID;
Values[0] = ForeignID;
}
ReturnValue.AddCommand(null, Object,
"INSERT INTO " + Property.TableName + "(" + Parameters + ") VALUES (@0,@1)",
CommandType.Text,
Values);
return(ReturnValue);
}
var List = (IEnumerable <ItemType>)Property.GetValue(Object);
if (List == null)
{
return(ReturnValue);
}
foreach (ItemType Item in List)
{
if (Item != null)
{
var CurrentID = Mapping.IDProperties.FirstOrDefault().GetValue(Object);
IMapping ForeignMapping = Property.ForeignMapping;
var ForeignID = ForeignMapping.IDProperties.FirstOrDefault().GetValue(Item);
string Parameters = "";
object[] Values = new object[2];
if (string.Compare(Mapping.TableName, ForeignMapping.TableName, StringComparison.InvariantCulture) < 0)
{
Parameters = Mapping.TableName + Mapping.IDProperties.FirstOrDefault().FieldName + "," + ForeignMapping.TableName + ForeignMapping.IDProperties.FirstOrDefault().FieldName;
Values[0] = CurrentID;
Values[1] = ForeignID;
}
else if (string.Compare(Mapping.TableName, ForeignMapping.TableName, StringComparison.InvariantCulture) == 0)
{
Parameters = Mapping.TableName + Mapping.IDProperties.FirstOrDefault().FieldName + "," + ForeignMapping.TableName + ForeignMapping.IDProperties.FirstOrDefault().FieldName + "2";
Values[0] = CurrentID;
Values[1] = ForeignID;
}
else
{
Parameters = ForeignMapping.TableName + ForeignMapping.IDProperties.FirstOrDefault().FieldName + "," + Mapping.TableName + Mapping.IDProperties.FirstOrDefault().FieldName;
Values[1] = CurrentID;
Values[0] = ForeignID;
}
ReturnValue.AddCommand(null,
Object,
"INSERT INTO " + Property.TableName + "(" + Parameters + ") VALUES (@0,@1)",
CommandType.Text,
Values);
}
}
return(ReturnValue);
}
/// <summary>
/// Generates a batch that will insert the data from the object
/// </summary>
/// <param name="Object">Object to insert</param>
/// <returns>Batch with the appropriate commands</returns>
public IBatch Insert(T Object)
{
return(QueryProvider.Batch(Source));
}
/// <summary>
/// Deletes items from the joining table for the property
/// </summary>
/// <param name="Property">Property</param>
/// <param name="Object">Object</param>
/// <typeparam name="P">Property type</typeparam>
/// <returns>The batch with the appropriate commands</returns>
public IBatch JoinsDelete <P>(IProperty <T, P> Property, T Object)
{
return(QueryProvider.Batch(Source));
}
/// <summary>
/// Sets up the specified database schema
/// </summary>
/// <param name="Mappings">The mappings.</param>
/// <param name="Database">The database.</param>
/// <param name="QueryProvider">The query provider.</param>
public void Setup(ListMapping<IDatabase, IMapping> Mappings, IDatabase Database, QueryProvider.Manager QueryProvider)
{
ISourceInfo TempSource = SourceProvider.GetSource(Database.Name);
var TempDatabase = new Schema.Default.Database.Database(Regex.Match(TempSource.Connection, "Initial Catalog=(.*?;)").Value.Replace("Initial Catalog=", "").Replace(";", ""));
SetupTables(Mappings, Database, TempDatabase);
SetupJoiningTables(Mappings, Database, TempDatabase);
SetupAuditTables(Database, TempDatabase);
foreach (ITable Table in TempDatabase.Tables)
{
Table.SetupForeignKeys();
}
List<string> Commands = GenerateSchema(TempDatabase, SourceProvider.GetSource(Database.Name)).ToList();
IBatch Batch = QueryProvider.Batch(SourceProvider.GetSource(Database.Name));
for (int x = 0; x < Commands.Count; ++x)
{
if (Commands[x].ToUpperInvariant().Contains("CREATE DATABASE"))
{
QueryProvider.Batch(SourceProvider.GetSource(Regex.Replace(SourceProvider.GetSource(Database.Name).Connection, "Initial Catalog=(.*?;)", ""))).AddCommand(null, null, CommandType.Text, Commands[x]).Execute();
}
else if (Commands[x].Contains("CREATE TRIGGER") || Commands[x].Contains("CREATE FUNCTION"))
{
if (Batch.CommandCount > 0)
{
Batch.Execute();
Batch = QueryProvider.Batch(SourceProvider.GetSource(Database.Name));
}
Batch.AddCommand(null, null, CommandType.Text, Commands[x]);
if (x < Commands.Count - 1)
{
Batch.Execute();
Batch = QueryProvider.Batch(SourceProvider.GetSource(Database.Name));
}
}
else
{
Batch.AddCommand(null, null, CommandType.Text, Commands[x]);
}
}
Batch.Execute();
}
/// <summary>
/// Saves items to the joining table for the property
/// </summary>
/// <param name="Property">Property</param>
/// <param name="Object">Object</param>
/// <typeparam name="P">Property type</typeparam>
/// <typeparam name="ItemType">Item type</typeparam>
/// <returns>The batch with the appropriate commands</returns>
public IBatch JoinsSave <P, ItemType>(IProperty <T, P> Property, T Object)
{
return(QueryProvider.Batch(Source));
}
/// <summary>
/// Generates a batch that will get the specific property for the object
/// </summary>
/// <typeparam name="P">Property type</typeparam>
/// <param name="Object">Object to get the property for</param>
/// <param name="Property">Property to get</param>
/// <returns>Batch with the appropriate commands</returns>
public IBatch LoadProperty <P>(T Object, IProperty Property)
{
return(QueryProvider.Batch(Source));
}
/// <summary>
/// Generates a batch that will update the data from the object
/// </summary>
/// <param name="Object">Object to update</param>
/// <returns>Batch with the appropriate commands</returns>
public IBatch Update(T Object)
{
return(QueryProvider.Batch(Source));
}
/// <summary>
/// Saves the object to the source
/// </summary>
/// <typeparam name="PrimaryKeyType">Primary key type</typeparam>
/// <param name="Object">Object to save</param>
public IBatch Save <PrimaryKeyType>(T Object)
{
return(QueryProvider.Batch(Source));
}
/// <summary>
/// Generates a batch that will get the number of pages for a given page size given the
/// parameters specified
/// </summary>
/// <param name="Parameters">Parameters</param>
/// <param name="PageSize">Page size</param>
/// <returns>Batch with the appropriate commands</returns>
public IBatch PageCount(int PageSize, params IParameter[] Parameters)
{
return(QueryProvider.Batch(Source));
}
