public static IEnumerable <ColumnInfo> ColumsOfType(DbClassInfoCache dbClassInfoCache,
QueryIdentifier alias,
QueryIdentifier sourceReference,
IQueryContainer container)
{
return(DbAccessLayer.GetSelectableColumnsOf(dbClassInfoCache)
.Select(e =>
{
switch (container.AccessLayer.DbAccessType)
{
case DbAccessType.MsSql:
case DbAccessType.MySql:
return new ColumnInfo(e, alias, container);
case DbAccessType.Experimental:
case DbAccessType.Unknown:
case DbAccessType.OleDb:
case DbAccessType.Obdc:
case DbAccessType.SqLite:
return new ColumnInfo(e, sourceReference, null);
default:
throw new ArgumentOutOfRangeException();
}
}).ToArray());
}
/// <summary>
/// Creates a new Query
/// </summary>
/// <param name="database"></param>
protected QueryBuilderContainer(DbAccessLayer database) : this(new InternalContainerContainer(database))
{
if (database == null)
{
throw new ArgumentNullException("database", "Please use a valid DbAccess Layer");
}
}
/// <summary>
/// Loads the PageSize into CurrentPageItems
/// </summary>
/// <param name="dbAccess"></param>
void IDataPager <T> .LoadPage(DbAccessLayer dbAccess)
{
SyncHelper(CurrentPageItems.Clear);
TotalItemCount = CommandQuery.CountInt().FirstOrDefault();
MaxPage = (int)Math.Ceiling((decimal)TotalItemCount / PageSize);
RaiseNewPageLoading();
var elements = CommandQuery.AsPagedQuery(CurrentPage, PageSize).ToArray();
//var elements = new SelectQuery<T>(dbAccess.Query()
// .WithCte(new ElementProducer<T>(CommandQuery
// .AsPagedQuery(CurrentPage, PageSize)),
// out var commandCte)
// .Select
// .Identifier<T>(commandCte))
// .ToArray();
foreach (var item in elements)
{
var item1 = item;
SyncHelper(() => CurrentPageItems.Add(item1));
}
RaiseNewPageLoaded();
}
public static int[] AddBooksWithImageAndUser(int number, int imagesPerBook, DbAccessLayer mgr)
{
mgr.RaiseEvents = false;
var books = new List <Book>();
mgr.Database.RunInTransaction(d =>
{
for (var i = 0; i < number; i++)
{
var book = new Book();
book.Text = "BOOK_" + Guid.NewGuid().ToString();
var usr = mgr.InsertWithSelect(new Users()
{
UserName = "******" + Guid.NewGuid().ToString()
});
book.IdUser = (int?)usr.User_ID;
books.Add(book = mgr.InsertWithSelect(book));
for (var j = 0; j < imagesPerBook; j++)
{
mgr.Insert(new Image
{
Text = "IMG_" + Guid.NewGuid().ToString(),
IdBook = book.BookId
});
}
}
});
mgr.RaiseEvents = true;
return(books.Select(f => f.BookId).ToArray());
}
public static object[] AddEntity(DbAccessLayer mgr, int number, Type poco, Action <object> defaulting = null)
{
mgr.RaiseEvents = false;
var typeCache = mgr.GetClassInfo(poco);
if (typeCache.PrimaryKeyProperty == null)
{
throw new NotSupportedException("Please provide a PK for that Entity");
}
var users = new List <object>();
mgr.Database.RunInTransaction((dd) =>
{
for (var i = 0; i < number; i++)
{
var user = typeCache.New();
if (defaulting != null)
{
defaulting(user);
}
users.Add(mgr.InsertWithSelect(poco, user));
}
});
mgr.RaiseEvents = true;
return(users.Select(f => typeCache.PrimaryKeyProperty.Getter.Invoke(f)).ToArray());
}
/// <summary>
/// Creates a new Instance based on possible Ctor's and the given
/// <paramref name="reader" />
/// </summary>
/// <returns></returns>
public static object SetPropertysViaReflection(
this DbClassInfoCache type,
IDataRecord reader,
DbAccessType?accessType = null,
DbConfig config = null)
{
if (reader == null)
{
return(null);
}
bool created;
var source = DbAccessLayer.CreateInstance(type, reader, out created);
if (created)
{
return(source);
}
if (config == null)
{
config = new DbConfig(true);
}
#pragma warning disable 618
return(DbAccessLayer.ReflectionPropertySet(config, source, type, reader, null, accessType));
#pragma warning restore 618
}
public List <Task> Get()
{
//Read the query string to know wheter to load all tasks or a selected task
var UrlKeyValues = ControllerContext.Request.GetQueryNameValuePairs();
string TaskCode = UrlKeyValues.SingleOrDefault(x => x.Key == "TaskCode").Value;
DbAccessLayer dal = new DbAccessLayer();
List <Task> TaskAll = new List <Task>();//Empty list for the first time
if (TaskCode != null)
{
TaskAll = (from t in dal.Tasks
where t.TaskCode == TaskCode
select t).ToList <Task>();
}
else
{
TaskAll = dal.Tasks.ToList <Task>();
}
// Thread.Sleep(2000);//Prnv: this daley was put just to check ajax behaviour of the appln
return(TaskAll);
}
/// <summary>
/// Loads the PageSize into CurrentPageItems
/// </summary>
/// <param name="dbAccess"></param>
public void LoadPage(DbAccessLayer dbAccess)
{
SyncHelper(CurrentPageItems.Clear);
MaxPage = (int)Math.Ceiling((decimal)_localDbRepository.Count / PageSize);
if (RaiseEvents)
{
var handler = NewPageLoading;
handler?.Invoke();
}
TotalItemCount = _localDbRepository.Count;
var items = _localDbRepository.Skip((int)((CurrentPage - 1) * PageSize)).Take(PageSize).ToArray();
foreach (var item in items)
{
SyncHelper(() => { CurrentPageItems.Add(item); });
}
if (CurrentPage > MaxPage)
{
CurrentPage = MaxPage;
}
if (RaiseEvents)
{
var handler = NewPageLoaded;
handler?.Invoke();
}
}
/// <summary>
/// Sync the Changes to this Collection to the Database
/// </summary>
public void SaveChanges(DbAccessLayer layer)
{
var bulk = layer.Database.CreateCommand("");
var removed = new List <T>();
foreach (var pair in _internalCollection)
{
IDbCommand tempCommand;
switch (pair.Value)
{
case CollectionStates.Added:
tempCommand = layer.CreateInsertWithSelectCommand(typeof(T), pair.Key);
break;
case CollectionStates.Removed:
tempCommand = DbAccessLayer.CreateDelete(layer.Database, layer.GetClassInfo(typeof(T)), pair.Key);
removed.Add(pair.Key);
break;
case CollectionStates.Unchanged:
tempCommand = null;
break;
case CollectionStates.Changed:
tempCommand = layer.CreateUpdate(pair.Key, layer.Database);
break;
default:
throw new ArgumentOutOfRangeException();
}
if (tempCommand != null)
{
bulk = layer.Database.MergeCommands(bulk, tempCommand, true);
}
}
var results = layer.ExecuteMARS(bulk, typeof(T)).SelectMany(s => s).Cast <T>().ToArray();
//Added
var added = _internalCollection.Where(s => s.Value == CollectionStates.Added).ToArray();
for (var i = 0; i < added.Length; i++)
{
var addedOne = added[i];
var newId = results[i];
DataConverterExtensions.CopyPropertys(addedOne.Value, newId, layer.Config);
}
//Removed
foreach (var item in removed)
{
_internalCollection.Remove(item);
}
foreach (var collectionStatese in _internalCollection.Keys.ToArray())
{
ChangeState(collectionStatese, CollectionStates.Unchanged);
}
}
public ConditionalEvalQuery <T> UpdateEntity <T>(T obj)
{
return(new ConditionalEvalQuery <T>(new UpdateQuery <T>(this
.QueryCommand(
DbAccessLayer
.CreateUpdate(ContainerObject
.AccessLayer.Database, ContainerObject.AccessLayer.GetClassInfo(typeof(T)), obj)))));
}
public UpdateQuery <T> UpdateStatement <T>(T obj)
{
return(new UpdateQuery <T>(this
.QueryCommand(
DbAccessLayer
.CreateUpdateSimple(ContainerObject
.AccessLayer.Database, ContainerObject.AccessLayer.GetClassInfo(typeof(T)), obj))));
}
public DeleteQuery <T> Delete <T>()
{
return(new DeleteQuery <T>(this
.QueryCommand(
DbAccessLayer
.CreateDelete(ContainerObject
.AccessLayer.Database, ContainerObject.AccessLayer.GetClassInfo(typeof(T))))));
}
public ActionResult GetTasks() //returns Json collection of Task records to jQuery get method
{
DbAccessLayer dal = new DbAccessLayer();
List <Task> TaskAll = dal.Tasks.ToList <Task>();
Thread.Sleep(2000); // this delay is included just to demostrate the Ajax behaviour
return(Json(TaskAll, JsonRequestBehavior.AllowGet));
}
private DbAccessLayer GenerateAccessLayer()
{
var accessLayer = new DbAccessLayer(new MsSql(ConnectionString));
accessLayer.Async = true;
accessLayer.LoadCompleteResultBeforeMapping = false;
accessLayer.ThreadSave = true;
return(accessLayer);
}
public void CreateEntrys(string connection, string outputPath, string database)
{
TargetDir = outputPath;
Manager = new DbAccessLayer(DbAccessType.MsSql, connection);
bool checkDatabase;
try
{
checkDatabase = Manager.CheckDatabase();
}
catch (Exception)
{
checkDatabase = false;
}
if (!checkDatabase)
{
throw new Exception("Database not accessible. Maybe wrong Connection or no Selected Database?");
}
var databaseName = string.IsNullOrEmpty(Manager.Database.DatabaseName) ? database : Manager.Database.DatabaseName;
if (string.IsNullOrEmpty(databaseName))
{
throw new Exception("Database not exists. Maybe wrong Connection or no Selected Database?");
}
WinConsole.WriteLine("Connection OK ... Reading Server Version ...");
SqlVersion = Manager.RunSelect <string>(Manager.Database.CreateCommand("SELECT SERVERPROPERTY('productversion')")).FirstOrDefault();
WinConsole.WriteLine("Server version is {0}", SqlVersion);
WinConsole.WriteLine("Reading Tables from {0} ...", databaseName);
Tables = Manager.Select <TableInformations>()
.ToArray()
.AsParallel()
.Select(s => new TableInfoModel(s, databaseName, new DbAccessLayer(DbAccessType.MsSql, connection)))
.ToList();
Views = Manager.Select <ViewInformation>()
.ToArray()
.AsParallel()
.Select(s => new TableInfoModel(s, databaseName, new DbAccessLayer(DbAccessType.MsSql, connection)))
.ToList();
StoredProcs = Manager.Select <StoredProcedureInformation>()
.Select(s => new StoredPrcInfoModel(s))
.ToList();
WinConsole.WriteLine(
"Found {0} Tables, {1} Views, {2} Procedures ... select a Table to see Options or start an Action", Tables.Count(),
Views.Count(), StoredProcs.Count());
Enums = new List <Dictionary <int, string> >();
RenderMenu();
}
public override void LoadPage(DbAccessLayer dbAccess)
{
base.LoadPage(dbAccess);
CurrentPageItems.Clear();
foreach (T currentPageItem in base.CurrentPageItems)
{
CurrentPageItems.Add(Converter(currentPageItem));
}
}
/// <summary>
/// Creates a new Instance of an QueryText Builder that creates Database aware querys
/// </summary>
public InternalContainerContainer(DbAccessLayer database)
{
AccessLayer = database;
_parts = new List <IQueryPart>();
QueryInfos = new Dictionary <string, object>();
Interceptors = new List <IQueryCommandInterceptor>();
PostProcessors = new List <IEntityProcessor>();
TableAlias = new Dictionary <string, QueryIdentifier>();
Identifiers = new List <QueryIdentifier>();
Joins = new List <JoinParseInfo>();
}
internal QueryBuilderContainer(DbAccessLayer database, Type type) : this(new InternalContainerContainer(database, type))
{
if (database == null)
{
throw new ArgumentNullException("database", "Please use a valid Database");
}
if (database == null)
{
throw new ArgumentNullException("type", "Please use a valid Type");
}
}
public DbAccessLayer GetWrapper()
{
DbAccessLayer expectWrapper = null;
var elementType = GetElementType();
expectWrapper = _managers[elementType].GetWrapper();
//if (elementType == DbAccessType.MsSql)
//{
// expectWrapper = new MsSqlManager().GetWrapper();
//}
//else if (elementType == DbAccessType.SqLite)
//{
// expectWrapper = new SqLiteManager().GetWrapper();
//}
_errorData = new StringBuilder();
expectWrapper.RaiseEvents = true;
expectWrapper.OnSelect += (sender, eventArg) =>
{
_errorData.AppendFormat(@"SELECT: \r\n{0}", eventArg.QueryDebugger);
_errorData.AppendLine();
};
expectWrapper.OnDelete += (sender, eventArg) =>
{
_errorData.AppendFormat(@"DELETE: \r\n{0}", eventArg.QueryDebugger);
_errorData.AppendLine();
};
expectWrapper.OnInsert += (sender, eventArg) =>
{
_errorData.AppendFormat(@"INSERT: \r\n{0}", eventArg.QueryDebugger);
_errorData.AppendLine();
};
expectWrapper.OnUpdate += (sender, eventArg) =>
{
_errorData.AppendFormat(@"Update: \r\n{0}", eventArg.QueryDebugger);
_errorData.AppendLine();
};
Assert.NotNull(expectWrapper, "This test cannot run as no Database Variable is defined");
bool checkDatabase = expectWrapper.CheckDatabase();
Assert.IsTrue(checkDatabase);
expectWrapper.Config.ConstructorSettings.FileCollisonDetection = CollisonDetectionMode.Pessimistic;
expectWrapper.Config.ConstructorSettings.CreateDebugCode = false;
expectWrapper.Multipath = true;
QueryDebugger.UseDefaultDatabase = expectWrapper.DatabaseStrategy;
return(expectWrapper);
}
// GET: ToDo
public ActionResult Enter()
{
DbAccessLayer db = new DbAccessLayer();
//select all the users and pass it to the view using viewbag
ViewBag.users = db.Users.Select(m => m.UserName).ToList();
TasksVM vm = new TasksVM();
vm.Tasks = new Task();
return(View("EnterToDo", vm));//first time pass empty object
}
internal QueryLazyEnumerator(IQueryContainer queryContainer, Type type, bool async)
{
_type = queryContainer.AccessLayer.GetClassInfo(type);
_accessLayer = queryContainer.AccessLayer;
if (async)
{
_startupTask = new Task(() => OpenReader(queryContainer));
_startupTask.Start();
}
else
{
OpenReader(queryContainer);
}
}
public SelectTableQueryPart(string source,
DbClassInfoCache tableInfo,
QueryIdentifier alias,
IQueryContainer queryContainer)
{
_source = source;
_tableInfo = tableInfo;
Alias = alias;
_columns = new List <ColumnInfo>();
_joins = new List <JoinParseInfo>();
_columns = DbAccessLayer.GetSelectableColumnsOf(_tableInfo)
.Select(e => new ColumnInfo(e, Alias, queryContainer))
.ToList();
}
public List <Task> Delete(Task t)
{
//delete the selected task n reload the entries
DbAccessLayer db = new DbAccessLayer();
Task taskDelete = (from temp in db.Tasks
where temp.TaskCode == t.TaskCode
select temp).ToList <Task>()[0];
db.Tasks.Remove(taskDelete);
db.SaveChanges();
//return refreshed list
List <Task> tasks = db.Tasks.ToList <Task>();
return(tasks);
}
public void CreateNStatements(int runs)
{
var timeForEach = new Stopwatch();
for (int i = 0; i < runs; i++)
{
var dbaAccess = new DbAccessLayer(DbAccessType.MsSql, "");
timeForEach.Start();
var query = dbaAccess.Query().Select.Table <Users>().Where.Column(f => f.UserID).Is.EqualsTo(10);
timeForEach.Stop();
}
TestContext.Out.WriteLine($"Test took {timeForEach.Elapsed}");
TestContext.Out.WriteLine($"That is {TimeSpan.FromMilliseconds(timeForEach.Elapsed.TotalMilliseconds / (runs * 1D))} per call");
}
public static void AddUsersFast(int number, DbAccessLayer mgr)
{
mgr.RaiseEvents = false;
var users = new List <Users>();
mgr.Database.RunInTransaction(d =>
{
for (var i = 0; i < number; i++)
{
var user = new Users();
user.UserName = Guid.NewGuid().ToString();
mgr.Insert(user);
}
});
mgr.RaiseEvents = true;
}
public ActionResult Register(User u)
{
DbAccessLayer dal = new DbAccessLayer();
if (ModelState.IsValid)
{
string RePass = Request.Form["RetypePassword"];
if (RePass == u.Password)
{
dal.Users.Add(u);
dal.SaveChanges();
TempData["RegistrationSucess"] = "User registered sucessfully..";//tempdata to retain the value
return(RedirectToAction("Index"));
}
}
return(View("Register", null));
}
public ActionResult Validate(User u)
{
DbAccessLayer db = new DbAccessLayer();
var check = db.Users.Where(m => m.UserName == u.UserName && m.Password == u.Password).FirstOrDefault();
if (check == null)
{
ViewBag.InvalidLogIn = "******";
return(View("Index", null));
}
else
{
FormsAuthentication.SetAuthCookie("Cookie", true); //Set cookie for form authentication
Session["UserName"] = u.UserName;
return(RedirectToAction("Enter", "ToDoUI", null));
}
}
public static int[] AddBooks(int number, DbAccessLayer mgr)
{
mgr.RaiseEvents = false;
var books = new List <Book>();
mgr.Database.RunInTransaction(d =>
{
for (var i = 0; i < number; i++)
{
var book = new Book();
book.Text = Guid.NewGuid().ToString();
books.Add(mgr.InsertWithSelect(book));
}
});
mgr.RaiseEvents = true;
return(books.Select(f => f.BookId).ToArray());
}
public static long[] AddUsers(int number, DbAccessLayer mgr)
{
mgr.RaiseEvents = false;
var users = new List <Users>();
mgr.Database.RunInTransaction(d =>
{
for (var i = 0; i < number; i++)
{
var user = new Users();
user.UserName = Guid.NewGuid().ToString();
users.Add(mgr.InsertWithSelect(user));
}
});
mgr.RaiseEvents = true;
return(users.Select(f => f.User_ID).ToArray());
}
/// <summary>
/// Returns an enumerator that iterates through the collection.
/// </summary>
/// <returns>
/// An enumerator that can be used to iterate through the collection.
/// </returns>
IEnumerator <TEntity> IEnumerable <TEntity> .GetEnumerator()
{
CheckCreatedElseThrow();
return(Base.Values.ToArray().Select(s =>
{
if (_keepOriginalObject)
{
return s;
}
bool fullyLoaded;
return (TEntity)DbAccessLayer.CreateInstance(
_typeInfo,
new ObjectDataRecord(s, _config, 0),
out fullyLoaded,
DbAccessType.Unknown);
}).GetEnumerator());
}