public void GenerateMetadataForDatabase()
{
Guid g = StopWatch.Start();
SqlMetadataDatabase meta = SqlMetadataDatabase.FromConnection(SqlBuilder.DefaultConnection);
MetadataDatabase mdb = meta.BuildMetadata();
Console.WriteLine(StopWatch.Stop(g, StopWatch.WatchTypes.Seconds, "Metadata generated in {0}s"));
Console.WriteLine("Database contains {0} tables and a total of {1} columns", mdb.Tables.Count, mdb.Tables.Values.SelectMany(x => x.Columns).Count());
string FileName = System.IO.Path.Combine(System.IO.Path.GetTempPath(), System.IO.Path.GetRandomFileName() + ".json");
string FileName2 = System.IO.Path.Combine(System.IO.Path.GetTempPath(), System.IO.Path.GetRandomFileName() + ".bson");
g = StopWatch.Start();
mdb.ToFile(FileName);
Console.WriteLine("Metadata persisted as {0} in {1}ms", FileName, StopWatch.Stop(g, StopWatch.WatchTypes.Milliseconds));
g = StopWatch.Start();
mdb = SerializationExtensions.FromFile(FileName);
Console.WriteLine("Metadata read from file '{0}' in {1}ms", FileName, StopWatch.Stop(g, StopWatch.WatchTypes.Milliseconds));
g = StopWatch.Start();
SerializationExtensions.ToFile <MetadataDatabase>(mdb, FileName2, true, false, SerializerFormats.Bson);
Console.WriteLine("Metadata persisted as bson {0} in {1}ms", FileName2, StopWatch.Stop(g, StopWatch.WatchTypes.Milliseconds));
g = StopWatch.Start();
mdb = SerializationExtensions.FromFile <MetadataDatabase>(FileName2, SerializerFormats.Bson);
Console.WriteLine("Metadata read from file '{0}' in {1}ms", FileName2, StopWatch.Stop(g, StopWatch.WatchTypes.Milliseconds));
FileInfo fi = new FileInfo(FileName);
Console.WriteLine("The File {1} is {0:0.00}MB in size", (double)fi.Length / (double)(1024 * 1024), FileName);
fi = new FileInfo(FileName2);
Console.WriteLine("The File {1} is {0:0.00}MB in size", (double)fi.Length / (double)(1024 * 1024), FileName2);
File.Delete(FileName);
File.Delete(FileName2);
Assert.IsTrue(!File.Exists(FileName));
Assert.IsTrue(!File.Exists(FileName2));
}
public void UpdateAccountWithJoinAndOutputResults()
{
string NewTitle = Guid.NewGuid().ToString();
SqlBuilder builder = SqlBuilder.Update()
.Table("Account")
.Output()
.Column("AccountID", System.Data.SqlDbType.Decimal)
.Column("Name", System.Data.SqlDbType.VarChar, 50)
.UpdateTable()
.Set <string>("Name", NewTitle, System.Data.SqlDbType.VarChar)
.InnerJoin("Systemuser").On("OwningUserID", SqlOperators.Equal, "SystemUserID")
.ToTable()
.Where <decimal>("Account", "AccountID", SqlOperators.Equal, 526)
.And <decimal>("SystemUser", "SystemUserID", SqlOperators.Equal, 2)
//.ToTable()
//.Where<decimal>("SystemUser", "SystemUserID", SqlOperators.Equal, 1)
.Builder;
Console.WriteLine(builder.ToSql());
Guid g = StopWatch.Start();
List <Account> Accounts = builder.List <Account>();
Console.WriteLine(StopWatch.Stop(g, StopWatch.WatchTypes.Milliseconds, "One account updated and retrieved as List<T> in {0}ms"));
Assert.IsTrue(Accounts.Count == 1);
Assert.AreEqual <string>(NewTitle, Accounts.First().Name);
}
public void UpdateAccountFromObject()
{
SqlBuilder builder = SqlBuilder.Select()
.From("Account")
.AllColumns()
.Where <decimal>("account", "AccountID", SqlOperators.Equal, 526)
.Builder();
Account account = builder.FirstOrDefault <Account>();
account.ModifiedOn = DateTime.Now;
string OldName = account.Name;
account.Name = Guid.NewGuid().ToString();
Console.WriteLine("Updating account ID 526 from Name {0} to {1}", OldName, account.Name);
Guid g = StopWatch.Start();
builder = TypeBuilder.Update <Account>(account, "Account");
Console.WriteLine(builder.ToSql());
int i = builder.ExecuteNonQuery();
Assert.IsTrue(i == 1, "Account 526 was NOT updated");
Console.WriteLine(StopWatch.Stop(g, StopWatch.WatchTypes.Milliseconds, "Account 526 updated to the new name in {0}ms"));
g = StopWatch.Start();
account.Name = OldName;
builder = TypeBuilder.Update <Account>(account, Properties: new string[] { "Name", "ModifiedOn" });
i = builder.ExecuteNonQuery();
Assert.IsTrue(i == 1, "Account 526 was NOT updated back to the original name again");
Console.WriteLine(StopWatch.Stop(g, StopWatch.WatchTypes.Milliseconds, "Account 526 updated back to the original name again in {0}ms"));
}
public void InsertOneAccountAsStoredProcedureText()
{
Guid g = StopWatch.Start();
SqlBuilder builder = GetInsertUpdateBuilder();
ResultTable result = builder.Execute(30, false);
Assert.IsTrue(result.Count == 1, "The insert procedure did not return 1 row");
decimal ID = Convert.ToDecimal(builder.Procedure.Parameters.First(x => x.Name.Equals("retval")).Value);
Console.WriteLine(StopWatch.Stop(g, StopWatch.WatchTypes.Milliseconds, "One account inserted in {0}ms"));
Assert.IsTrue(ID > 0, "The Account was not inserted");
Console.WriteLine(SerializationExtensions.ToJson <RowData>(result[0], true));
g = StopWatch.Start();
builder = GetInsertUpdateBuilder(ID, "Nørregade 28D");
result = builder.Execute(30, false);
Assert.IsTrue(result.Count == 1, "The update procedure did not return 1 row");
decimal ID2 = Convert.ToDecimal(builder.Procedure.Parameters.First(x => x.Name.Equals("retval")).Value);
Console.WriteLine(StopWatch.Stop(g, StopWatch.WatchTypes.Milliseconds, "One account updated in {0}ms"));
Assert.AreEqual <decimal>(ID, ID2, "The Insert/update IDs do not match {0} != {1}", ID, ID2);
builder = SqlBuilder.Select()
.From("Account").AllColumns(false)
.Where <decimal>("Account", "AccountID", SqlOperators.Equal, ID2)
.Builder();
result = builder.Execute(30, false);
Assert.IsTrue(result.Count == 1, "The updated account {0} could not be retrieved", ID2);
Console.WriteLine(SerializationExtensions.ToJson <RowData>(result[0], true));
DeleteOneAccount(ID2);
}
public void AutoUpdateFromRowDataObject()
{
Guid g = StopWatch.Start();
SqlBuilder builder = SqlBuilder.Select().WithMetadata(true, SetupData.MetadataFileName)
.From("Account")
.AllColumns(false)
.Where <decimal>("Account", "AccountID", SqlOperators.Equal, 526)
.Builder;
Console.WriteLine(builder.ToSql());
ResultTable r = builder.Execute();
Console.WriteLine(StopWatch.Stop(g, StopWatch.WatchTypes.Milliseconds, "1 Account selected in {0}ms"));
g = StopWatch.Start();
Assert.IsTrue(r.Count == 1, "Executed 1 account");
RowData row = r.First();
row.Column("Name", Guid.NewGuid().ToString());
builder = SqlBuilder.Update().Update(row, new string[] { "AccountID", "Name" });
Console.WriteLine(builder.ToSql());
r = builder.Execute();
Console.WriteLine(StopWatch.Stop(g, StopWatch.WatchTypes.Milliseconds, "1 Account updated in {0}ms"));
row.AcceptChanges();
Assert.IsTrue(r.First().Column <string>("Name") == row.Column <string>("Name"), "Names are equal");
Assert.IsFalse(row.HasChanges, "The row does not have changes");
}
public void ParallelSelectWithSubquery()
{
Guid g = StopWatch.Start();
int loops = 80;
SqlBuilder builder = SqlBuilder.Select()
.From("Account")
.AllColumns(false)
.SubSelect("Contact", "AccountID", "AccountID", null)
.AllColumns(false)
.Builder();
Console.WriteLine(builder.ToSql());
ResultTable result = builder.Execute();
double one = StopWatch.Stop(g, StopWatch.WatchTypes.Milliseconds);
int count = result.Count + result.SelectMany(x => x.Column <ResultTable>("ContactList")).Count();
g = StopWatch.Start();
int total = 0;
ParallelLoopResult r = Parallel.For(0, loops, new ParallelOptions()
{
MaxDegreeOfParallelism = 8
}, a => { total += SelectWithSubqueryInternal(); });
double twenty = StopWatch.Stop(g, StopWatch.WatchTypes.Milliseconds);
Console.WriteLine("{2} selects parallel executed with a total of {3} rows in {0:0.00}ms. Estimated {1:0.00}ms", twenty, one * loops, loops, loops * count);
Assert.IsTrue(r.IsCompleted);
}
public void InsertOneAccountAsStoredProcedure()
{
Guid g = StopWatch.Start();
SqlBuilder builder = GetInsertUpdateBuilder();
Console.WriteLine(builder.ToSql());
int i = new SqlBuilder[] { builder }.ExecuteNonQuery();
Assert.IsTrue(i == 1, "The insert procedure did not return 1 row");
decimal ID = Convert.ToDecimal(builder.Procedure.Parameters.First(x => x.Name.Equals("retval")).Value);
Assert.IsTrue(ID > 0, "The Account was not inserted");
Console.WriteLine(string.Format("An account with the ID {0} was inserted in {1}ms", ID, StopWatch.Stop(g, StopWatch.WatchTypes.Milliseconds)));
g = StopWatch.Start();
SqlBuilder select = SqlBuilder.Select()
.From("Account")
.AllColumns(false)
.Where <decimal>("Account", "AccountID", SqlOperators.Equal, ID)
.Builder();
Console.WriteLine(select.ToSql());
ResultTable result = select.Execute();
Assert.IsTrue(result.Count == 1, "The Account could not be retrieved after insert");
dynamic row = result.First();
Console.WriteLine("Account ID {0}: {1} {2} retrieved in {3}ms", row.AccountID, row.Name, row.Address1, StopWatch.Stop(g, StopWatch.WatchTypes.Milliseconds));
g = StopWatch.Start();
builder = GetInsertUpdateBuilder(ID, "Nørregade 28D");
i = new SqlBuilder[] { builder }.ExecuteNonQuery();
Assert.IsTrue(i == 1, "The update procedure did not return 1 row");
decimal ID2 = Convert.ToDecimal(builder.Procedure.Parameters.First(x => x.Name.Equals("retval")).Value);
Assert.AreEqual <decimal>(ID, ID2);
Console.WriteLine(StopWatch.Stop(g, StopWatch.WatchTypes.Milliseconds, "The Account was updated in {0}ms"));
g = StopWatch.Start();
result = select.Execute();
Assert.IsTrue(result.Count == 1, "The Account could not be retrieved after update");
row = result.First();
Console.WriteLine("Account ID {0}: {1} {2} retrieved in {3}ms", row.AccountID, row.Name, row.Address1, StopWatch.Stop(g, StopWatch.WatchTypes.Milliseconds));
builder = SqlBuilder.Delete()
.From("Account")
.Where <decimal>("Account", "AccountID", SqlOperators.Equal, ID2)
.Builder();
Console.WriteLine(builder.ToSql());
i = new SqlBuilder[] { builder }.ExecuteNonQuery();
Assert.IsTrue(i == 1, "The Account could not be deleted");
}
public void Insert1000Accounts()
{
int num = 0;
Guid g = StopWatch.Start();
for (int i = 0; i < 1000; i++)
{
num += InsertOneAccountInternal(i < 1);
}
Console.WriteLine("{0} accounts inserted in {1:0.00}s", num, StopWatch.Stop(g, StopWatch.WatchTypes.Seconds));
Assert.IsTrue(num == 1000);
DeleteInsertedAccounts();
}
public void JoinAccountsAndContactsWithMetadata()
{
Guid g = StopWatch.Start();
SqlBuilder builder = SqlBuilder.Select()
.From("Account")
.AllColumns()
.WithMetadata().InnerJoin("Contact", null)
.AllColumns()
.Builder();
Console.WriteLine(builder.ToSql());
ResultTable result = builder.Execute(30, false);
Console.WriteLine("{0} rows selected in {1}ms", result.Count, StopWatch.Stop(g, StopWatch.WatchTypes.Milliseconds));
Console.WriteLine(SerializationExtensions.ToJson <dynamic>(result.First(), true));
}
public void SerializeSqlBuilder()
{
Guid g = StopWatch.Start();
SqlBuilder builder = SqlBuilder.Select()
.From("Account")
.AllColumns(false)
.SubSelect("Contact", "AccountID", "AccountID", null, null, "Contacts")
.AllColumns(false)
.SubSelect("Activity", "ContactID", "ContactID", null, null, "Activities")
.AllColumns(false)
.InnerJoin("Checkkode").On("ActivityTypeID", SqlOperators.Equal, "CheckID")
.And <decimal>("CheckGroup", SqlOperators.Equal, 5)
.ToTable().Column("BeskrivelseDK", "ActivityType")
.Builder.ParentBuilder.From("Contact")
.SubSelect("CampaignActivity", "ContactID", "ContactID", null, null)
.AllColumns(false)
.InnerJoin("Checkkode").On("CampaignActivityTypeID", SqlOperators.Equal, "CheckID")
.And <decimal>("CheckGroup", SqlOperators.Equal, 4)
.ToTable().Column("BeskrivelseDK", "ActivityType")
.Builder();
string before = builder.ToSql();
Console.WriteLine(before);
string file = Path.Combine(Path.GetTempPath(), Path.GetRandomFileName() + ".json");
File.WriteAllText(file, TinySql.Serialization.SerializationExtensions.ToJson <SqlBuilder>(builder));
Console.WriteLine(string.Format("Results serialized to {0} in {1}ms", file, StopWatch.Stop(g, StopWatch.WatchTypes.Milliseconds)));
FileInfo fi = new FileInfo(file);
Console.WriteLine("The File is {0:0.00}MB in size", (double)fi.Length / (double)(1024 * 1024));
g = StopWatch.Start();
builder = TinySql.Serialization.SerializationExtensions.FromJson <SqlBuilder>(File.ReadAllText(file));
Console.WriteLine(string.Format("Results deserialized from {0} in {1}ms", file, StopWatch.Stop(g, StopWatch.WatchTypes.Milliseconds)));
string after = builder.ToSql();
Console.WriteLine(after);
g = StopWatch.Start();
ResultTable result = builder.Execute();
Console.WriteLine(StopWatch.Stop(g, StopWatch.WatchTypes.Milliseconds, "builder executed in {0}ms"));
fi.Delete();
Assert.IsFalse(File.Exists(file));
Assert.AreEqual(before, after, "The SQL is identical");
}
public int InsertOneAccountInternal(bool WriteSql = true)
{
Guid g = StopWatch.Start();
string id = DateTime.Now.Ticks.ToString();
SqlBuilder builder = SqlBuilder.Insert()
.Into("account")
.Value <string>("Name", "Test Account " + id, System.Data.SqlDbType.VarChar)
.Value <string>("Address1", "Address1 " + id, System.Data.SqlDbType.VarChar)
.Value <string>("Address2", "Address2 " + id, System.Data.SqlDbType.VarChar)
.Value <string>("Address3", "Address3 " + id, System.Data.SqlDbType.VarChar)
.Value <string>("PostalCode", "1165", System.Data.SqlDbType.VarChar)
.Value <string>("City", "City " + id, System.Data.SqlDbType.VarChar)
.Value <string>("Telephone", "500-500-2015", System.Data.SqlDbType.VarChar)
.Value <string>("Telefax", "500-500-2015", System.Data.SqlDbType.VarChar)
.Value <string>("Web", "http://www.company.com", System.Data.SqlDbType.VarChar)
.Value <decimal>("AccountTypeID", 1, System.Data.SqlDbType.Decimal)
.Value <decimal>("DataSourceID", 1, System.Data.SqlDbType.Decimal)
.Value <decimal>("StateID", 1, System.Data.SqlDbType.Decimal)
.Value <decimal>("CreatedBy", 1, System.Data.SqlDbType.Decimal)
.Value <decimal>("ModifiedBy", 1, System.Data.SqlDbType.Decimal)
.Value <DateTime>("CreatedOn", DateTime.Now, System.Data.SqlDbType.DateTime)
.Value <DateTime>("ModifiedOn", DateTime.Now, System.Data.SqlDbType.DateTime)
.Value <decimal>("OwningUserID", 1, System.Data.SqlDbType.Decimal)
.Value <decimal>("OwningBusinessUnitID", 1, System.Data.SqlDbType.Decimal)
.Output()
.Column("AccountID", System.Data.SqlDbType.Decimal)
.Builder;
if (WriteSql)
{
Console.WriteLine(builder.ToSql());
}
g = StopWatch.Start();
ResultTable result = builder.Execute();
if (FirstInsertedId == 99999999)
{
FirstInsertedId = result.First().Column <decimal>("AccountID");
}
if (WriteSql)
{
Console.WriteLine(StopWatch.Stop(g, StopWatch.WatchTypes.Milliseconds, "One account inserted in {0}ms"));
}
Assert.IsTrue(result.Count == 1);
return(result.Count);
}
public void GenerateMetadataTwoTimesUsingCaching()
{
Guid g = StopWatch.Start();
SqlBuilder b1 = SqlBuilder.Select().WithMetadata();
Console.WriteLine(StopWatch.Stop(g, StopWatch.WatchTypes.Seconds, "Metadata Pass 1/2: Metadata serialized to cache and SqlBuilder ready in {0}s"));
g = StopWatch.Start();
SqlBuilder b2 = SqlBuilder.Select().WithMetadata();
Console.WriteLine(StopWatch.Stop(g, StopWatch.WatchTypes.Milliseconds, "Metadata Pass 2/2: Metadata serialized from cache and SqlBuilder ready in {0}ms"));
Console.WriteLine("Removing Metadata from cache");
bool b = SqlMetadataDatabase.FromBuilder(b2).ClearMetadata();
Assert.IsTrue(b1.Metadata != null);
Assert.IsTrue(b2.Metadata != null);
Assert.IsTrue(b);
}
public void ExecuteAndSerialize()
{
Guid g = StopWatch.Start();
SqlBuilder sb = SqlBuilder.Select(5)
.From("Account")
.AllColumns(false)
.Builder;
Console.WriteLine(sb.ToSql());
ResultTable result = sb.Execute();
string s = SerializationExtensions.ToJson <ResultTable>(result, true);
Console.WriteLine("{0} rows executed and serialized in {1}ms", result.Count, StopWatch.Stop(g, StopWatch.WatchTypes.Milliseconds));
g = StopWatch.Start();
result = SerializationExtensions.FromJson <ResultTable>(s);
Console.WriteLine("{0} rows de-serialized in {1}ms", result.Count, StopWatch.Stop(g, StopWatch.WatchTypes.Milliseconds));
Console.WriteLine(s);
}
public void ExecuteDeepAndSerialize()
{
ResultTable result = ExecuteDeepInternal();
string file = Path.Combine(Path.GetTempPath(), Path.GetRandomFileName() + ".json");
Guid g = StopWatch.Start();
File.WriteAllText(file, TinySql.Serialization.SerializationExtensions.ToJson <ResultTable>(result));
Console.WriteLine(StopWatch.Stop(g, StopWatch.WatchTypes.Milliseconds, "Results serialized in {0}ms"));
g = StopWatch.Start();
ResultTable deserialized = SerializationExtensions.FromJson <ResultTable>(File.ReadAllText(file));
Console.WriteLine(StopWatch.Stop(g, StopWatch.WatchTypes.Milliseconds, "Results deserialized in {0}ms"));
FileInfo fi = new FileInfo(file);
Console.WriteLine("The File is {0:0.00}MB in size", (double)fi.Length / (double)(1024 * 1024));
fi.Delete();
Assert.IsFalse(File.Exists(file));
Assert.IsTrue(result.Count == deserialized.Count);
}
public void Insert1000AccountsInOneBatch()
{
Guid g = StopWatch.Start();
List <SqlBuilder> Builders = new List <SqlBuilder>();
InsertOneAccountInternal(false);
for (int i = 0; i < 1000; i++)
{
string id = DateTime.Now.Ticks.ToString();
SqlBuilder builder = SqlBuilder.Insert()
.Into("account")
.Value <string>("Name", "Test Account " + id, System.Data.SqlDbType.VarChar)
.Value <string>("Address1", "Address1 " + id, System.Data.SqlDbType.VarChar)
.Value <string>("Address2", "Address2 " + id, System.Data.SqlDbType.VarChar)
.Value <string>("Address3", "Address3 " + id, System.Data.SqlDbType.VarChar)
.Value <string>("PostalCode", "1165", System.Data.SqlDbType.VarChar)
.Value <string>("City", "City " + id, System.Data.SqlDbType.VarChar)
.Value <string>("Telephone", "500-500-2015", System.Data.SqlDbType.VarChar)
.Value <string>("Telefax", "500-500-2015", System.Data.SqlDbType.VarChar)
.Value <string>("Web", "http://www.company.com", System.Data.SqlDbType.VarChar)
.Value <decimal>("AccountTypeID", 1, System.Data.SqlDbType.Decimal)
.Value <decimal>("DataSourceID", 1, System.Data.SqlDbType.Decimal)
.Value <decimal>("StateID", 1, System.Data.SqlDbType.Decimal)
.Value <decimal>("CreatedBy", 1, System.Data.SqlDbType.Decimal)
.Value <decimal>("ModifiedBy", 1, System.Data.SqlDbType.Decimal)
.Value <DateTime>("CreatedOn", DateTime.Now, System.Data.SqlDbType.DateTime)
.Value <DateTime>("ModifiedOn", DateTime.Now, System.Data.SqlDbType.DateTime)
.Value <decimal>("OwningUserID", 1, System.Data.SqlDbType.Decimal)
.Value <decimal>("OwningBusinessUnitID", 1, System.Data.SqlDbType.Decimal)
.Output()
.Column("AccountID", System.Data.SqlDbType.Decimal)
.Builder;
Builders.Add(builder);
}
Console.WriteLine(StopWatch.Stop(g, StopWatch.WatchTypes.Milliseconds, "1000 Builders created in {0}ms"));
g = StopWatch.Start();
int result = Builders.ToArray().ExecuteNonQuery();
Console.WriteLine("{0} Rows affected in {1}ms", result, StopWatch.Stop(g, StopWatch.WatchTypes.Milliseconds));
Assert.IsTrue(result == 1000);
DeleteInsertedAccounts();
}
public void SelectAllAccountsIntoCustomDictionary()
{
SqlBuilder builder = SqlBuilder.Select().From("Account").AllColumns(false)
.InnerJoin("State", null).On("StateID", SqlOperators.Equal, "StateID")
.ToTable()
.Column("Description", "State")
.From("Account")
.InnerJoin("Checkkode", null).On("DatasourceID", SqlOperators.Equal, "CheckID")
.And <decimal>("CheckGroup", SqlOperators.Equal, 7).ToTable()
.Column("BeskrivelseDK", "Datasource")
.Builder();
Console.WriteLine(builder.ToSql());
Guid g = StopWatch.Start();
MyDictionary result = builder.Dictionary <decimal, Account, MyDictionary>("AccountID");
Console.WriteLine("MyDictionary<int, Account> with {0} rows executed in {1}ms", result.Count, StopWatch.Stop(g, StopWatch.WatchTypes.Milliseconds));
g = StopWatch.Start();
result = null;
}
public void SelectAllBusinessEntitiesWithNestedSubQueriesAndJoin()
{
SqlBuilder builder = SqlBuilder.Select()
.From("Account")
.Columns("AccountID", "Name", "Address1", "PostalCode", "City")
.SubSelect("Contact", "AccountID", "AccountID")
.Columns("ContactID", "Name", "Title", "Telephone", "Mobile", "WorkEmail", "PrivateEmail")
.InnerJoin("ListMember").On("ContactID", SqlOperators.Equal, "ContactID").ToTable()
.Columns("ListMemberID", "ListMemberStatusID")
.From("Contact")
.SubSelect("ListMember", "ContactID", "ContactID")
.Columns("ContactID", "ListMemberID", "ListmemberStatusID", "ListID")
.SubSelect("List", "ListID", "ListID")
.Columns("ListID", "Name")
.Builder();
Console.WriteLine(builder.ToSql());
Guid g = StopWatch.Start();
System.Data.DataSet result = builder.DataSet();
Console.WriteLine("Dataset with {0} tables executed in {1}ms", result.Tables.Count, StopWatch.Stop(g, StopWatch.WatchTypes.Milliseconds));
for (int i = 0; i < result.Tables.Count; i++)
{
Console.WriteLine("Table {0} contains {1} rows", i, result.Tables[i].Rows.Count);
}
}
public void SelectWithAliasInWhereClause()
{
SqlBuilder builder = SqlBuilder.Select()
.From("account", null)
.Into("tempPerson")
.Column("AccountID", "ID")
.Columns("Name", "Address1", "PostalCode", "City")
.OrderBy("ID", OrderByDirections.Desc)
.Where <int>("account", "ID", SqlOperators.LessThan, 1000)
.AndGroup()
.And <string>("account", "Name", SqlOperators.StartsWith, "A")
.Or <string>("account", "City", SqlOperators.StartsWith, "A")
.Builder;
Console.WriteLine(builder.ToSql());
Guid g = StopWatch.Start();
ResultTable result = builder.Execute(120);
Console.WriteLine("ResultTable with {0} rows executed in {1}s", result.Count, StopWatch.Stop(g, StopWatch.WatchTypes.Milliseconds));
}
public void SelectAllColumnsFromOneTable()
{
SqlBuilder builder = SqlBuilder.Select().From("Account").AllColumns(false).Builder;
Console.WriteLine(builder.ToSql());
Guid g = StopWatch.Start();
ResultTable result = builder.Execute();
Console.WriteLine("ResulTable with {0} rows executed in {1}s", result.Count, StopWatch.Stop(g, StopWatch.WatchTypes.Seconds));
g = StopWatch.Start();
List <Account> list = builder.List <Account>(null, 30, true, true);
Console.WriteLine("List<Account> with {0} rows executed in {1}s", list.Count, StopWatch.Stop(g, StopWatch.WatchTypes.Seconds));
list = null;
result = null;
}
public void Select2000AccountsIntoTempTableWithOutput()
{
SqlBuilder builder = SqlBuilder.Select(2000)
.From("Account")
.Into("tempPerson")
.Columns("AccountID", "Name", "Address1")
.ConcatColumns("Address", "\r\n", "Address1", "PostalCode", "City")
.OrderBy("AccountID", OrderByDirections.Desc)
.Builder;
Console.WriteLine(builder.ToSql());
Guid g = StopWatch.Start();
ResultTable result = builder.Execute(120);
Console.WriteLine("ResultTable with {0} rows executed in {1}s", result.Count, StopWatch.Stop(g, StopWatch.WatchTypes.Milliseconds));
}
public void SelectPeopleInMarketingListsWithoutEmail()
{
List <string> domains = new List <string>()
{
"@hotmail",
"@yahoo",
"@live",
"@msn",
"@outlook",
"@mail.tele",
"@gmail",
"@post"
};
Guid g = StopWatch.Start();
SqlBuilder builder = SqlBuilder.Select()
.From("Contact")
.AllColumns(false)
.WhereExists("ListMember").And("ContactID", SqlOperators.Equal, "ContactID")
.EndExists()
.And <List <string> >("Contact", "WorkEmail", SqlOperators.In, domains)
.Builder();
Console.WriteLine(builder.ToSql());
ResultTable results = builder.Execute();
Console.WriteLine("{0} contacts executed in {1}ms, that are listmembers and have a public domain email address", results.Count, StopWatch.Stop(g, StopWatch.WatchTypes.Milliseconds));
}
public void SelectWithSubQuery()
{
SqlBuilder builder = SqlBuilder.Select(100)
.From("Account")
.AllColumns(false)
.SubSelect("Contact", "AccountID", "AccountID", null)
.Columns("ContactID", "Name", "Title")
.Builder();
Console.WriteLine(builder.ToSql());
Guid g = StopWatch.Start();
System.Data.DataSet result = builder.DataSet();
Console.WriteLine("Dataset with {0} tables executed in {1}ms", result.Tables.Count, StopWatch.Stop(g, StopWatch.WatchTypes.Milliseconds));
for (int i = 0; i < result.Tables.Count; i++)
{
Console.WriteLine("Table {0} contains {1} rows", i, result.Tables[i].Rows.Count);
}
}
public void SelectContactAndAccountAllColumns()
{
Guid g = StopWatch.Start();
SqlBuilder builder = SqlBuilder.Select().WithMetadata <Contact>()
.AllColumns()
.InnerJoin(x => x.AccountID)
.AllColumns()
.Builder();
List <Contact> result = builder.List <Contact>();
Console.WriteLine("{0} contacts selected as List<T> in {1}ms\r\n\r\n", result.Count, StopWatch.Stop(g, StopWatch.WatchTypes.Milliseconds));
foreach (Contact c in result.Take(5))
{
Console.WriteLine("{0},{1} works with {1} @ {2}, {3} {4}", c.ContactID, c.Name, c.AccountName, c.Address1, c.PostalCode, c.City);
}
Console.WriteLine("");
Console.WriteLine(builder.ToSql());
}
public void PopulateListClassFromResultTable()
{
Guid g = StopWatch.Start();
SqlBuilder builder = SqlBuilder.Select()
.From("Account")
.AllColumns(false)
.SubSelect("Contact", "AccountID", "AccountID", null, null, "Contacts")
.AllColumns(false)
.Builder();
Console.WriteLine(builder.ToSql());
ResultTable result = builder.Execute();
Console.WriteLine("ResulTable with {0} rows executed in {1}ms", result.Count, StopWatch.Stop(g, StopWatch.WatchTypes.Milliseconds));
g = StopWatch.Start();
List <Account> accounts = TypeBuilder.PopulateObject <Account>(result);
Console.WriteLine("List<Account> with {0} rows created from ResultTable executed in {1}ms", accounts.Count, StopWatch.Stop(g, StopWatch.WatchTypes.Milliseconds));
Console.WriteLine("List<Account> has a total of {0} contacts", accounts.SelectMany(x => x.Contacts).Count());
}
public void SelectFunctionColumnsFromOneTable()
{
SqlBuilder builder = SqlBuilder.Select(20).From("Account", null).AllColumns(false)
.Fn()
.GetDate("Today")
.Concat("My Name",
ConstantField <string> .Constant("Michael"),
ConstantField <string> .Constant(" "),
ConstantField <string> .Constant("Randrup")
)
.ToTable()
.Column <string>(Guid.NewGuid().ToString(), "UniqueID")
.Builder;
Console.WriteLine(builder.ToSql());
Guid g = StopWatch.Start();
ResultTable result = builder.Execute(120);
Console.WriteLine("ResulTable with {0} rows executed in {1}s", result.Count, StopWatch.Stop(g, StopWatch.WatchTypes.Seconds));
g = StopWatch.Start();
List <Account> list = builder.List <Account>(null, 30, true, true);
Console.WriteLine("List<Account> with {0} rows executed in {1}s", list.Count, StopWatch.Stop(g, StopWatch.WatchTypes.Seconds));
list = null;
result = null;
}
public void SelectAllColumnsFromOneTableOrderedByTwoFields()
{
SqlBuilder builder = SqlBuilder.Select(2000)
.From("Account")
.AllColumns(false)
.OrderBy("City", OrderByDirections.Desc).OrderBy("Name", OrderByDirections.Asc)
.Builder;
Console.WriteLine(builder.ToSql());
Guid g = StopWatch.Start();
ResultTable result = builder.Execute(120);
Console.WriteLine("ResulTable with {0} rows executed in {1}ms", result.Count, StopWatch.Stop(g, StopWatch.WatchTypes.Milliseconds));
foreach (dynamic row in result.Take(10))
{
Console.WriteLine("{0} {1} - Ordered by City DESC, Name ASC", row.Name, row.City);
}
}
public void SelectAccountsWithCustomAliasesIntoResultTable()
{
SqlBuilder builder = SqlBuilder.Select().From("Account", "a").AllColumns(false)
.InnerJoin("State", "b").On("StateID", SqlOperators.Equal, "StateID")
.ToTable()
.Column("Description", "State")
.From("a")
.InnerJoin("Checkkode", "c").On("DatasourceID", SqlOperators.Equal, "CheckID")
.And <decimal>("CheckGroup", SqlOperators.Equal, 7).ToTable()
.Column("BeskrivelseDK", "Datasource")
.Builder;
Console.WriteLine(builder.ToSql());
Guid g = StopWatch.Start();
ResultTable result = builder.Execute();
Console.WriteLine("ResultTable with {0} rows executed in {1}ms", result.Count, StopWatch.Stop(g, StopWatch.WatchTypes.Milliseconds));
g = StopWatch.Start();
result = null;
}
public void PopulateClassFromResultTable()
{
Guid g = StopWatch.Start();
SqlBuilder builder = SqlBuilder.Select(1)
.From("Account")
.AllColumns(false)
.SubSelect("Contact", "AccountID", "AccountID", null, null, "Contacts")
.AllColumns(false)
.Builder();
Console.WriteLine(builder.ToSql());
ResultTable result = builder.Execute();
Console.WriteLine("ResulTable with {0} rows executed in {1}ms", result.Count, StopWatch.Stop(g, StopWatch.WatchTypes.Milliseconds));
g = StopWatch.Start();
Account account = TypeBuilder.PopulateObject <Account>(result.First());
Console.WriteLine("Account {0}-{1} with {2} Contacts created from RowData executed in {3}ms", account.AccountID, account.Name, account.Contacts.Count, StopWatch.Stop(g, StopWatch.WatchTypes.Milliseconds));
}
public void SelectValueColumnFromOneTable()
{
SqlBuilder builder = SqlBuilder.Select().From("Account", null).AllColumns(false)
.Column <int>(22, "Age")
.Column <string>(Guid.NewGuid().ToString(), "UniqueID")
.Builder;
Console.WriteLine(builder.ToSql());
Guid g = StopWatch.Start();
ResultTable result = builder.Execute(120);
Console.WriteLine("ResulTable with {0} rows executed in {1}s", result.Count, StopWatch.Stop(g, StopWatch.WatchTypes.Seconds));
g = StopWatch.Start();
List <Account> list = builder.List <Account>(null, 30, true, true);
Console.WriteLine("List<Account> with {0} rows executed in {1}s", list.Count, StopWatch.Stop(g, StopWatch.WatchTypes.Seconds));
list = null;
result = null;
}
public void SelectContacts()
{
Guid g = StopWatch.Start();
DataTable dt = Data.All <Account>("Account", null, false, null, 30);
List <Account> list = Data.All <Account>(null, null, true, null, 30, false, true);
Dictionary <decimal, Account> dict = Data.All <decimal, Account>("AccountID");
SqlBuilder builder = TypeBuilder.Select <Account>();
builder.From("Account").FieldList.Remove(builder.From("Account").FieldList.First(x => x.Name.Equals("State")));
builder.From("Account").FieldList.Remove(builder.From("Account").FieldList.First(x => x.Name.Equals("Datasource")));
builder
.From("Account")
.InnerJoin("State").On("StateID", SqlOperators.Equal, "StateID").ToTable()
.Column("Description", "State")
.From("Account")
.InnerJoin("Checkkode").On("DatasourceID", SqlOperators.Equal, "CheckID").And <decimal>("CheckGroup", SqlOperators.Equal, 7)
.ToTable()
.Column("BeskrivelseDK", "Datasource")
.From("Account").OrderBy("Name", OrderByDirections.Asc)
.Builder();
list = builder.List <Account>();
Console.WriteLine("All accounts selected with 5 different methods in {0}ms", StopWatch.Stop(g, StopWatch.WatchTypes.Milliseconds));
}
