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