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 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);
}
private ResultTable InsertOrUpdate(decimal Id)
{
Guid g = StopWatch.Start();
SqlBuilder builder = SqlBuilder.If()
.Conditions.AndExists("Account")
.And <decimal>("Account", "AccountID", SqlOperators.Equal, Id)
.Begin(SqlBuilder.StatementTypes.Update).Table("Account")
.Output()
.Column("AccountID", System.Data.SqlDbType.Decimal)
.UpdateTable()
.Set <string>("Name", "Name Updated " + DateTime.Now.ToString(), System.Data.SqlDbType.VarChar, 200)
.Where <decimal>("Account", "AccountID", SqlOperators.Equal, Id)
.Builder.End()
.Else().Begin(SqlBuilder.StatementTypes.Insert).Into("Account")
.Value <string>("Name", "Test Account", System.Data.SqlDbType.VarChar)
.Value <string>("Address1", "Address1", System.Data.SqlDbType.VarChar)
.Value <string>("Address2", "Address2", System.Data.SqlDbType.VarChar)
.Value <string>("Address3", "Address3", System.Data.SqlDbType.VarChar)
.Value <string>("PostalCode", "1165", System.Data.SqlDbType.VarChar)
.Value <string>("City", "City", 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.End();
Console.WriteLine(builder.ToSql());
ResultTable result = builder.Execute();
Console.WriteLine(string.Format("{0} rows inserted or updated in {1}ms", result.Count, StopWatch.Stop(g, StopWatch.WatchTypes.Milliseconds)));
Assert.IsTrue(result.Count == 1);
return(result);
}
public void TestSimpleIf()
{
Guid g = StopWatch.Start();
SqlBuilder builder = SqlBuilder.If()
.Conditions.AndExists("Account")
.And <decimal>("Account", "AccountID", SqlOperators.Equal, 526)
.Begin(SqlBuilder.StatementTypes.Select)
.From("Account")
.AllColumns(false)
.Where <decimal>("Account", "AccountID", SqlOperators.Equal, 526)
.Builder.End();
Console.WriteLine(builder.ToSql());
ResultTable result = builder.Execute();
Console.WriteLine(string.Format("{0} rows retrieved in {1}ms", result.Count, StopWatch.Stop(g, StopWatch.WatchTypes.Milliseconds)));
Assert.IsTrue(result.Count == 1);
}
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 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 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 CrossJoinAccountsAndContactsWithMetadata()
{
Guid g = StopWatch.Start();
SqlBuilder builder = SqlBuilder.Select(100000)
.From("Account")
.AllColumns()
.WithMetadata().CrossJoin("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));
g = StopWatch.Start();
result = builder.Execute(30, false);
Console.WriteLine("Executed a second time in {0}ms", StopWatch.Stop(g, StopWatch.WatchTypes.Milliseconds));
Console.WriteLine(SerializationExtensions.ToJson <dynamic>(result.First(), true));
}
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 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 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 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 UpdateAccountWithOutputResults()
{
string NewName = 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", NewName, System.Data.SqlDbType.VarChar)
.Where <decimal>("account", "AccountID", SqlOperators.Equal, 526)
.Builder;
Console.WriteLine(builder.ToSql());
Guid g = StopWatch.Start();
List <Account> Accounts = builder.List <Account>();
Console.WriteLine(StopWatch.Stop(g, StopWatch.WatchTypes.Milliseconds, "One person updated and retrieved as List<T> in {0}ms"));
Assert.IsTrue(Accounts.Count == 1);
Assert.AreEqual <string>(NewName, Accounts.First().Name);
}
public void InsertAccountFromObject()
{
Guid g = StopWatch.Start();
Account acc = new Account()
{
AccountID = 88888888,
Name = "Test Account",
AccountTypeID = 1,
DatasourceID = 1,
StateID = 1,
CreatedBy = 1,
CreatedOn = DateTime.Now,
ModifiedBy = 1,
ModifiedOn = DateTime.Now,
OwningUserID = 1,
OwningBusinessUnitID = 1
};
SqlBuilder builder = TypeBuilder.Insert <Account>(acc);
Console.WriteLine(builder.ToSql());
ResultTable result = builder.Execute();
Assert.IsTrue(result.Count == 1);
decimal i = result.First().Column <decimal>("AccountID");
Console.WriteLine("Inserted Account {0}", i);
SqlBuilder b1 = SqlBuilder.Delete()
.From("account", null)
.Where <decimal>("account", "AccountID", SqlOperators.Equal, i)
.Builder;
i = new SqlBuilder[] { b1 }.ExecuteNonQuery();
Console.WriteLine("{0} Accounts deleted", i);
Assert.IsTrue(i == 1);
}
public void TestFieldToFieldWhereClauseMultipleJoins()
{
Guid g = StopWatch.Start();
SqlBuilder builder = SqlBuilder.Select()
.From("Account")
.Columns("Name")
.LeftOuterJoin("SystemUser", "CreatedUser")
.On("CreatedBy", SqlOperators.Equal, "SystemUserID")
.FromTable()
.LeftOuterJoin("SystemUser", "ModifiedUser")
.On("ModifiedBy", SqlOperators.Equal, "SystemUserID")
.FromTable()
.Where("Account", "ModifiedBy", "SystemUser", "SystemuserID", SqlOperators.Equal)
.Builder();
//.AllColumns()
//.Where("Contact", "FirstName", "Contact", "LastName", SqlOperators.Equal)
//.Builder();
Console.WriteLine(builder.ToSql());
}
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));
}
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 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 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 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 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 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 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 ExecuteDeepParent()
{
Guid g = StopWatch.Start();
SqlBuilder builder = SqlBuilder.Select()
.From("Contact")
.AllColumns(false)
.SubSelect("Account", "AccountID", "AccountID", null, null, "Account")
.AllColumns(false)
.ConcatColumns("Address", ", ", "Address1", "PostalCode", "City")
.InnerJoin("Contact").On("AccountID", SqlOperators.Equal, "AccountID")
.ToTable().Column("ContactID")
.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));
foreach (dynamic row in result.Where(x => x.Column <ResultTable>("Account").Count > 0).Take(50))
{
dynamic Parent = row.Column <ResultTable>("Account");
if (Parent.Count > 0)
{
Console.WriteLine("The Contact {0} is connected to the account {1} - {2}", row.Name, Parent[0].Name, Parent[0].Address);
}
else
{
Console.WriteLine("The Contact {0} is not connected to an account", row.Name);
}
}
}
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 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 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));
}
private ResultTable ExecuteDeepInternal()
{
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();
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));
return(result);
}
