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)); }