/// <summary>
/// Constructor to create the instance.
/// </summary>
public DataMigrationForm()
{
InitializeComponent();
_dataMigrationHelper = new DataMigrationHelper(ShowMessage);
VisibleChanged += OnVisibleChangedEvent;
_timer.Tick += OnTimerTick;
}
public void OrderBy()
{
var maxItems = 250;
DataMigrationHelper.AddUsers(maxItems, DbAccess);
var elementProducer =
CreateQuery().Select.Table <Users>().Order.By(s => s.UserName)
.ThenBy(f => f.UserID)
.ForResult(_asyncEnumeration).ToArray();
var directQuery =
DbAccess.SelectNative <Users>(UsersMeta.SelectStatement + " ORDER BY UserName, User_ID");
Assert.That(directQuery.Length, Is.EqualTo(elementProducer.Length));
for (var index = 0; index < directQuery.Length; index++)
{
var userse = directQuery[index];
var userbe = elementProducer[index];
Assert.That(userbe.UserID, Is.EqualTo(userse.UserID));
Assert.That(userbe.UserName, Is.EqualTo(userse.UserName));
}
}
public void JoinInCte()
{
DataMigrationHelper.AddBooksWithImage(250, 10, DbAccess);
var books = Measure(() => CreateQuery()
.WithCte(e => e
.Select
.Table <ImageWithFkBooks>()
.Join(f => f.Book),
out var cteId)
.Select
.Identifier <ImageWithFkBooks>(cteId)
.ToArray());
foreach (var imageWithFkBookse in books)
{
Assert.That(imageWithFkBookse.Book, Is.Not.Null);
Assert.That(imageWithFkBookse.Book.Text, Is.Not.Null);
Assert.That(imageWithFkBookse.Book.BookId, Is.EqualTo(imageWithFkBookse.IdBook));
}
Assert.That(books, Is.Not.Null);
}
public void SelectBase()
{
DataMigrationHelper.AddUsers(1, DbAccess);
var refSelect = DbAccess.Select <Users>();
Assert.IsTrue(refSelect.Length > 0);
var testInsertName = Guid.NewGuid().ToString();
var testUser = DbAccess.InsertWithSelect(new Users {
UserName = testInsertName
});
Assert.IsNotNull(testUser);
Assert.AreNotEqual(testUser.UserID, default(long));
var selTestUser =
DbAccess.Select <Users_PK_IDFM_FUNCSELECTFACWITHPARAM>(new object[] { testUser.UserID }).FirstOrDefault();
Assert.That(selTestUser, Is.Not.Null);
Assert.AreEqual(selTestUser.UserName, testUser.UserName);
Assert.AreEqual(selTestUser.UserId, testUser.UserID);
}
public void NestedTransactionTestRollback()
{
//DbAccess.Database.AllowNestedTransactions = Type == DbAccessType.SqLite;
DataMigrationHelper.AddUsers(250, DbAccess);
var count =
DbAccess.SelectNative(typeof(long), "SELECT COUNT(1) FROM " + UsersMeta.TableName).FirstOrDefault();
DbAccess.Database.RunInTransaction(dd =>
{
DbAccess.Database.RunInTransaction(ddx =>
{
DbAccess.Delete <Users>();
ddx.TransactionRollback();
});
});
var countAfter =
DbAccess.SelectNative(typeof(long), "SELECT COUNT(1) FROM " + UsersMeta.TableName).FirstOrDefault();
Assert.That(count, Is.EqualTo(countAfter));
}
public void CheckFactory()
{
DataMigrationHelper.AddUsers(1, DbAccess);
Assert.That(() => DbAccess.Select <Users_StaticQueryFactoryForSelect>(), Is.Not.Empty);
var testInsertName = Guid.NewGuid().ToString();
Users_StaticQueryFactoryForSelect testUser = null;
Assert.That(
() =>
testUser =
DbAccess.InsertWithSelect(new Users_StaticQueryFactoryForSelect {
UserName = testInsertName
}),
Is.Not.Null
.And.Property("UserId").Not.EqualTo(0));
var selTestUser = DbAccess.Select <Users_StaticQueryFactoryForSelect>(testUser.UserId);
Assert.AreEqual(selTestUser.UserName, testUser.UserName);
Assert.AreEqual(selTestUser.UserId, testUser.UserId);
}
public void NestedTransactionTestRollback()
{
//DbAccess.Database.AllowNestedTransactions = Type == DbAccessType.SqLite;
DataMigrationHelper.AddUsers(250, DbAccess);
var count =
DbAccess.SelectNative(typeof(long), DbAccess.Database.CreateCommand(SelectCountOneFromUsers)).FirstOrDefault();
DbAccess.Database.RunInTransaction(dd =>
{
DbAccess.Database.RunInTransaction(ddx =>
{
DbAccess.Delete <Users>();
ddx.TransactionRollback();
});
});
var countAfter =
DbAccess.SelectNative(typeof(long), DbAccess.Database.CreateCommand(SelectCountOneFromUsers)).FirstOrDefault();
Assert.That(count, Is.EqualTo(countAfter));
}
public void RefIn()
{
var addBooksWithImage = DataMigrationHelper.AddBooksWithImage(250, 2, DbAccess);
foreach (var id in addBooksWithImage)
{
var countOfImages = -1;
if (DbAccess.DbAccessType == DbAccessType.MsSql)
{
countOfImages =
DbAccess.RunPrimetivSelect <int>(
string.Format("SELECT COUNT(1) FROM {0} WHERE {0}.{1} = {2}",
ImageMeta.TableName, ImageMeta.ForgeinKeyName,
id))[0];
}
if (DbAccess.DbAccessType == DbAccessType.SqLite)
{
countOfImages =
(int)
DbAccess.RunPrimetivSelect <long>(
string.Format("SELECT COUNT(1) FROM {0} WHERE {0}.{1} = {2}",
ImageMeta.TableName, ImageMeta.ForgeinKeyName,
id))[0];
}
Assert.That(countOfImages, Is.EqualTo(2));
var deSelect =
DbAccess.SelectNative <Image>(string.Format("{2} AS b WHERE b.{0} = {1}",
ImageMeta.ForgeinKeyName, id, ImageMeta.SelectStatement));
Assert.That(deSelect, Is.Not.Empty);
Assert.That(deSelect.Length, Is.EqualTo(countOfImages));
var book = DbAccess.Select <Book>(id);
var forResult = CreateQuery().Select.Table <Image>().In(book).ForResult(_asyncEnumeration).ToArray();
Assert.That(forResult, Is.Not.Empty);
Assert.That(forResult.Count, Is.EqualTo(countOfImages));
}
}
public void TransactionAsyncTestExceptional()
{
DbAccess.Database.AllowNestedTransactions = Type == DbAccessType.SqLite;
DataMigrationHelper.AddUsers(250, DbAccess);
var count =
DbAccess.SelectNative(typeof(long), "SELECT COUNT(1) FROM " + UsersMeta.TableName).FirstOrDefault();
Assert.That(() =>
{
DbAccess.Database.RunInTransactionAsync(async dd =>
{
DbAccess.Delete <Users>();
await Task.FromResult("");
throw new Exception();
}).Wait();
}, Throws.Exception);
var countAfter =
DbAccess.SelectNative(typeof(long), "SELECT COUNT(1) FROM " + UsersMeta.TableName).FirstOrDefault();
Assert.That(count, Is.EqualTo(countAfter));
}
public void UpdateExplicit()
{
var addUsers = DataMigrationHelper.AddUsers(1, DbAccess)[0];
var user = DbAccess.Select <Users>(addUsers);
var userIdPre = user.UserID;
var usernamePre = user.UserName;
user.UserName = Guid.NewGuid().ToString();
CreateQuery().Update.Table <Users>().Set
.Column(f => f.UserName).Value(user.UserName)
.ExecuteNonQuery();
user = DbAccess.Select <Users>(addUsers);
Assert.That(user.UserID, Is.EqualTo(userIdPre));
Assert.That(user.UserName, Is.Not.EqualTo(usernamePre));
CreateQuery().Update.Table <Users>().Set
.Column(f => f.UserName).Value(null)
.ExecuteNonQuery();
user = DbAccess.Select <Users>(addUsers);
Assert.That(user.UserID, Is.EqualTo(userIdPre));
Assert.That(user.UserName, Is.Null);
}
public void TransactionAsyncTestExceptional()
{
DbAccess.Database.AllowNestedTransactions = Type.HasFlag(DbAccessType.SqLite);
DataMigrationHelper.AddUsersFast(250, DbAccess);
var count =
DbAccess.SelectNative(typeof(long), DbAccess.Database.CreateCommand(SelectCountOneFromUsers)).FirstOrDefault();
Assert.That(() =>
{
DbAccess.Database.RunInTransactionAsync(async dd =>
{
DbAccess.Delete <Users>();
await Task.FromResult("");
throw new Exception();
}).Wait();
}, Throws.Exception);
var countAfter =
DbAccess.SelectNative(typeof(long), DbAccess.Database.CreateCommand(SelectCountOneFromUsers)).FirstOrDefault();
Assert.That(count, Is.EqualTo(countAfter));
}
public void SelectPrimitivSelect()
{
DataMigrationHelper.AddUsers(1, DbAccess);
var refSelect = DbAccess.RunPrimetivSelect <long>(UsersMeta.SelectPrimaryKeyStatement);
Assert.IsTrue(refSelect.Any());
var anyId = refSelect.FirstOrDefault();
Assert.AreNotEqual(anyId, 0);
refSelect =
DbAccess.RunPrimetivSelect <long>(
UsersMeta.SelectPrimaryKeyStatement + " WHERE " + UsersMeta.PrimaryKeyName + " = @paramA",
new QueryParameter("paramA", anyId));
Assert.IsTrue(refSelect.Length > 0);
refSelect =
DbAccess.RunPrimetivSelect <long>(
UsersMeta.SelectPrimaryKeyStatement + " WHERE " + UsersMeta.PrimaryKeyName + " = @paramA",
new { paramA = anyId });
Assert.IsTrue(refSelect.Length > 0);
}
public void Pager()
{
var maxItems = 250;
DataMigrationHelper.AddUsers(maxItems, DbAccess);
var basePager = DbAccess.Database.CreatePager <Users>();
basePager.PageSize = 10;
basePager.LoadPage(DbAccess);
Assert.That(basePager.CurrentPage, Is.EqualTo(1));
Assert.That(basePager.MaxPage, Is.EqualTo(maxItems / basePager.PageSize));
var queryPager = CreateQuery()
.Select.Table <Users>()
.Order.By(f => f.UserID)
.ForPagedResult(1, basePager.PageSize);
queryPager.LoadPage(DbAccess);
Assert.That(basePager.CurrentPage, Is.EqualTo(queryPager.CurrentPage));
Assert.That(basePager.MaxPage, Is.EqualTo(queryPager.MaxPage));
}
public void JoinMultipleParent()
{
DataMigrationHelper.AddBooksWithImageAndUser(250, 10, DbAccess);
var books = Measure(() => CreateQuery()
.Select.Table <BookWithFkImages>()
.Join(nameof(BookWithFkImages.User))
.Join(nameof(BookWithFkImages.User1))
.ToArray());
foreach (var book in books)
{
Assert.That(book.User, Is.Not.Null);
Assert.That(book.User.UserName, Is.Not.Null);
Assert.That(book.User.User_ID, Is.Not.Zero);
Assert.That(book.User1, Is.Not.Null);
Assert.That(book.User1.UserName, Is.Not.Null);
Assert.That(book.User1.User_ID, Is.Not.Zero);
}
Assert.That(books, Is.Not.Null);
}
public void JoinParentAndThenChild()
{
DataMigrationHelper.AddBooksWithImage(250, 10, DbAccess);
var images = Measure(() => CreateQuery()
.Select.Table <ImageWithFkBooks>()
.Join(f => f.Book.Images)
.ToArray());
Assert.That(images, Is.Not.Null);
var allBooks = DbAccess.Select <Book>();
var allImages = DbAccess.Select <Image>();
foreach (var imageWithFkBookse in images)
{
Assert.That(imageWithFkBookse.Text, Is.Not.Null);
Assert.That(imageWithFkBookse.Book, Is.Not.Null);
var book = allBooks.FirstOrDefault(e => e.BookId == imageWithFkBookse.IdBook);
Assert.That(imageWithFkBookse.Book.Text, Is.EqualTo(book.Text));
Assert.That(imageWithFkBookse.Book.Images, Is.Not.Null);
Assert.That(imageWithFkBookse.Book.Images, Is.Not.Empty);
var imgs = allImages.Where(f => f.IdBook == imageWithFkBookse.IdBook);
foreach (var image in imageWithFkBookse.Book.Images)
{
var img = imgs.FirstOrDefault(e => e.ImageId == image.ImageId);
Assert.That(image.Book, Is.Null);
Assert.That(image.Text, Is.Not.Null);
Assert.That(image.IdBook, Is.EqualTo(imageWithFkBookse.Book.BookId));
Assert.That(img, Is.Not.Null);
Assert.That(img.Text, Is.EqualTo(image.Text));
}
}
}
public void SelectPrimitivSelect()
{
DataMigrationHelper.AddUsersFast(1, DbAccess);
var refSelect = DbAccess
.RunSelect <long>(DbAccess.Database.CreateCommand(UsersMeta.SelectPrimaryKeyStatement));
Assert.IsTrue(refSelect.Any());
var anyId = refSelect.FirstOrDefault();
Assert.AreNotEqual(anyId, 0);
refSelect =
DbAccess.RunSelect <long>(
DbAccess.Database.CreateCommandWithParameterValues($"{UsersMeta.SelectPrimaryKeyStatement} WHERE {UsersMeta.PrimaryKeyName} = @paramA",
new QueryParameter("paramA", anyId)));
Assert.IsTrue(refSelect.Length > 0);
refSelect =
DbAccess.RunSelect <long>(
DbAccess.Database.CreateCommandWithParameterValues(UsersMeta.SelectPrimaryKeyStatement + " WHERE " + UsersMeta.PrimaryKeyName + " = @paramA",
new QueryParameter("paramA", anyId)));
Assert.IsTrue(refSelect.Length > 0);
}
public void In()
{
var maxItems = 250;
var addUsers = DataMigrationHelper.AddUsers(maxItems, DbAccess);
var rand = new Random(54541117);
var elementsToRead = new List <long>();
for (var i = 0; i < 25; i++)
{
elementsToRead.Add(addUsers[rand.Next(0, addUsers.Length)]);
}
var elementProducer =
CreateQuery()
.Select.Table <Users>()
.Where.Column(f => f.UserID)
.Is.In(elementsToRead.ToArray())
.ExecutionMode(_asyncEnumeration)
.ToArray();
var directQuery = DbAccess.RunSelect <Users>(DbAccess.Database.CreateCommand(UsersMeta.SelectStatement
+ string.Format(" WHERE User_ID IN ({0})",
elementsToRead.Select(f => f.ToString())
.Aggregate((e, f) => e + "," + f))));
Assert.That(directQuery.Length, Is.EqualTo(elementProducer.Length));
for (var index = 0; index < directQuery.Length; index++)
{
var userse = directQuery[index];
var userbe = elementProducer[index];
Assert.That(userbe.UserID, Is.EqualTo(userse.UserID));
Assert.That(userbe.UserName, Is.EqualTo(userse.UserName));
}
}
public void PagerConditionalCall()
{
var testUsers = DataMigrationHelper.AddUsers(250, DbAccess);
var refSelect =
DbAccess.Database.Run(
s => s.GetSkalar(string.Format("SELECT COUNT(*) FROM {0}", UsersMeta.TableName)));
Assert.That(testUsers.Length, Is.EqualTo(refSelect));
using (var pager = DbAccess.Database.CreatePager <Users>())
{
pager.AppendedComands.Add(
DbAccess.Database.CreateCommand(string.Format("WHERE User_ID = {0}", testUsers[0])));
Assert.That(pager, Is.Not.Null);
pager.CurrentPage = 1;
pager.PageSize = 25;
Assert.That(pager.MaxPage, Is.EqualTo(0));
#region CheckEvents
var triggeredNewPageLoaded = false;
var triggeredNewPageLoading = false;
pager.NewPageLoaded += () => triggeredNewPageLoaded = true;
pager.NewPageLoading += () => triggeredNewPageLoading = true;
pager.LoadPage(DbAccess);
Assert.That(pager.MaxPage, Is.Not.EqualTo(0));
Assert.That(triggeredNewPageLoaded, Is.False);
Assert.That(triggeredNewPageLoading, Is.False);
pager.RaiseEvents = true;
pager.LoadPage(DbAccess);
Assert.That(pager.MaxPage, Is.Not.EqualTo(0));
Assert.That(triggeredNewPageLoaded);
Assert.That(triggeredNewPageLoading);
#endregion
#region CheckPage Size
var oldPageSize = pager.PageSize;
var newPageSize = 20;
Assert.That(pager.CurrentPageItems.Count, Is.EqualTo(1));
pager.PageSize = newPageSize;
Assert.That(pager.PageSize, Is.EqualTo(newPageSize));
pager.LoadPage(DbAccess);
Assert.That(pager.MaxPage, Is.Not.EqualTo(0));
Assert.That(pager.CurrentPageItems.Count, Is.EqualTo(1));
#endregion
}
}
public void SelectPropertyLessPoco()
{
DataMigrationHelper.AddUsersFast(1, DbAccess);
Assert.That(() => DbAccess.Select <UsersWithoutProperties>(), Is.Not.Null.And.Not.Empty);
}
public void SharedTransactionCounter()
{
var dbOne = new DefaultDatabaseAccess(new ThreadConnectionController());
dbOne.Attach(new MsSql(DbAccess.Database.ConnectionString));
var rootAccess = new DbAccessLayer(dbOne, DbAccess.Config);
Assert.That(rootAccess.Database.ConnectionController.InstanceCounter, Is.EqualTo(0));
Assert.That(rootAccess.Database.ConnectionController.LockRoot, Is.Not.Null);
Assert.That(rootAccess.Database.ConnectionController.Transaction, Is.Null);
var dbTwo = new DefaultDatabaseAccess(new ThreadConnectionController());
dbTwo.Attach(new MsSql(DbAccess.Database.ConnectionString));
var nestedAccess = new DbAccessLayer(dbTwo, DbAccess.Config);
Assert.That(nestedAccess.Database.ConnectionController.InstanceCounter, Is.EqualTo(0));
Assert.That(nestedAccess.Database.ConnectionController.LockRoot, Is.EqualTo(dbOne.ConnectionController.LockRoot));
Assert.That(nestedAccess.Database.ConnectionController.Transaction, Is.Null);
Assert.That(DbAccess.Database.ConnectionController, Is.Not.EqualTo(dbOne.ConnectionController));
Assert.That(DbAccess.Database.ConnectionController.LockRoot, Is.Not.EqualTo(dbOne.ConnectionController.LockRoot));
Assert.That(rootAccess.Database, Is.Not.EqualTo(nestedAccess.Database));
Assert.That(rootAccess.Database.ConnectionController, Is.Not.EqualTo(nestedAccess.Database.ConnectionController));
if (Type.HasFlag(DbAccessType.MsSql))
{
DbAccess.ExecuteGenericCommand("ALTER DATABASE " + DbAccess.Database.DatabaseName +
" SET ALLOW_SNAPSHOT_ISOLATION ON");
}
DbAccess.Database.RunInTransaction((de) =>
{
Assert.That(DbAccess.Database.ConnectionController.InstanceCounter, Is.EqualTo(1));
Assert.That(DbAccess.Select <Users>().Length, Is.EqualTo(0));
}, IsolationLevel.Snapshot);
rootAccess.Database.RunInTransaction(d =>
{
Assert.That(rootAccess.Database.ConnectionController.InstanceCounter, Is.EqualTo(nestedAccess.Database.ConnectionController.InstanceCounter));
Assert.That(rootAccess.Database.ConnectionController.Transaction, Is.EqualTo(nestedAccess.Database.ConnectionController.Transaction));
Assert.That(rootAccess.Database.ConnectionController.InstanceCounter, Is.EqualTo(1));
DataMigrationHelper.AddUsersFast(10, rootAccess);
Assert.That(rootAccess.Database.ConnectionController.InstanceCounter, Is.EqualTo(1));
//the nested database runs with the same TransactionCounter as the RootAccess so it should use
//the same transaction and connection
nestedAccess.Database.RunInTransaction((de) =>
{
Assert.That(rootAccess.Database.ConnectionController.InstanceCounter, Is.EqualTo(2));
Assert.That(nestedAccess.Select <Users>().Length, Is.EqualTo(10));
DataMigrationHelper.AddUsersFast(10, nestedAccess);
Assert.That(rootAccess.Database.ConnectionController.InstanceCounter, Is.EqualTo(2));
});
Assert.That(nestedAccess.Select <Users>().Length, Is.EqualTo(20));
DbAccess.Database.RunInTransaction((de) =>
{
Assert.That(DbAccess.Database.ConnectionController.InstanceCounter, Is.EqualTo(1));
Assert.That(DbAccess.Select <Users>().Length, Is.EqualTo(0));
}, IsolationLevel.Snapshot);
}, IsolationLevel.Snapshot);
DbAccess.Database.RunInTransaction((de) =>
{
Assert.That(DbAccess.Database.ConnectionController.InstanceCounter, Is.EqualTo(1));
Assert.That(DbAccess.Select <Users>().Length, Is.EqualTo(20));
}, IsolationLevel.Snapshot);
}
