SQL Queries

Level Separator

Sample of Queries

//SELECT LastMaintainedBy, EvidenceOfFundingRequired, DateStamp FROM BusinessUnit WHERE ID = 5
BusinessUnitQuery q = new q.LevelSeparator = LevelSeparator.AND; //If you want to change it to q.AddFilter(BusinessUnitQuery.ID, QueryComparison.Equals, 5,

q.AddFilter(BusinessUnitQuery.LastMaintainedBy, QueryComparison.Equals, "someUserID", q.AddFilter(BusinessUnitQuery.PaymentSummaryYTD, QueryComparison.Greater, 100, DataTable dt = this.dal.GetDataTable(q);

Not adding any columns selects all (select * from BusinessUnit WHERE ID = 5) 

            BusinessUnitQuery q = new BusinessUnitQuery();
            q.AddFilter(BusinessUnitQuery.ID, QueryComparison.Equals, 5);
            DataTable dt = this.dal.GetDataTable(q);

Quicker

            BusinessUnitQuery q = new BusinessUnitQuery(BusinessUnitQuery.ID, QueryComparison.Equals, 5);
            DataTable dt = this.dal.GetDataTable(q);

multiple filters (WHERE'S)

            //SELECT * FROM BusinessUnit
            //WHERE (ID=5 AND LastMaintainedBy='someUserID') OR (PaymentSummaryYTD > 100)
            BusinessUnitQuery q = new BusinessUnitQuery();
            q.AddFilter(BusinessUnitQuery.ID, QueryComparison.Equals, 5, 1);
            q.AddFilter(BusinessUnitQuery.LastMaintainedBy, QueryComparison.Equals, "someUserID", 1);
            q.AddFilter(BusinessUnitQuery.PaymentSummaryYTD, QueryComparison.Greater, 100, 2);
            DataTable dt = this.dal.GetDataTable(q);


Select RevisionNo From BusinessUnit Where ID = 2

            BusinessUnitQuery q = new BusinessUnitQuery();
            int revNo = this.dal.DataGet(q).SingleValue<int>(BusinessUnitQuery.RevisionNo, BusinessUnitQuery.ID, QueryComparison.Equals, 2);

Select Count(*) From BusinessUnit Where ID = 2

            BusinessUnitQuery q = new BusinessUnitQuery();
            bool exists = this.dal.DataGet(q).HasRecords(BusinessUnitQuery.ID, QueryComparison.Equals, 2);