Rational.DB

Create objects

Populating objects from a recordset is tedious. With Rational.DB, simply construct a query and call a function to create objects.

var connstring = "Server=(local); Database=AdventureWorks; Trusted_Connection=true;";
var db = new Rational.DB.Database(new DbConnectionInfo(connstring, eDbType.SqlServer));
 
//create list of objects
var stmt1 = db.CreateStatement("SELECT * FROM Person");
var people = db.SelectObjectList<Person>(stmt1);
 
//create single object
var stmt2 = db.CreateSelectStatement("Person", "*");
stmt2.ResultMax = 1;
var person = db.SelectObject<Person>(stmt2);
 
//get list of scalars
var stmt3 = db.CreateSelectStatement("Person", "LastName");
var allNames = db.SelectScalarList<string>(stmt3);
 
//paging
var stmt4 = db.CreateSelectStatement("Person", "*");
var pgStmt = new DbSelectPager { SelectStatement = stmt4, ResultMax = 100, ResultPage = 5 };
var peoplePage = db.SelectObjectList<Person>(pgStmt);
 
//select objects into an action for processing
db.SelectObjectsIntoAction<Person>(stmt1, (p) =>
{
    Debug.Print(p.LastName);
});

Manage parameters

We all know we need to parameterize queries. But handling parameters in ADO.NET is verbose and cumbersome. The query builders in Rational.DB simplify using parameters, including multi-value parameters.

var connstring = "Server=(local); Database=AdventureWorks; Trusted_Connection=true;";
var db = new Database(new DbConnectionInfo(connstring, eDbType.SqlServer));
 
//simple positional parameter
var stmt1 = db.CreateStatement("SELECT * FROM Person WHERE ID = @0", 123);
 
//parameters from object properties
var criteria = new PersonSearchCriteria { FirstName = "Bill", LastName = "Smith" };
var stmt2 = db.CreateSelectStatement("Person", "*");
stmt2.Where.And("FirstName = @FirstName AND LastName = @LastName", criteria);
 
//parameters from anonymous object
var stmt3 = db.CreateStatement("SELECT * FROM Person WHERE ID = @ID", new { ID = 123 });
 
//multi-value parameters
var idList = new List<int> { 123, 245, 356 };
var stmt4 = db.CreateStatement("SELECT * FROM Person WHERE ID IN ( @0 )", idList);
 
//explicit parameters
var stmt5 = db.CreateStatement("SELECT * FROM Person WHERE ID = @ID AND LastName = @LastName");
stmt5.Parameters.Add("ID", 123);
stmt5.Parameters.Add("LastName", "Smith", System.Data.DbType.String);

Simple CRUD

Often, you just need entity CRUD operations (Create Read Update Delete). Rational.DB lets you associate table and column metadata with an Enum, enabling simple CRUD while keeping your POCO classes clean.

[Table()]
public enum PersonTable
{
    [Column(PrimaryKey = true)]
    ID,
 
    [Column()]
    FirstName,
 
    [Column(NotNull = true, SqlName = "Last Name")]
    LastName
}

//==================================================================================

var connstring = "Server=(local); Database=sample; Trusted_Connection=true;";
var db = new Database(new DbConnectionInfo(connstring, eDbType.SqlServer));
 
//simple CRUD operations, requires an integer key field
db.Insert<PersonTable>(new Person { ID = 100, LastName = "Smith", FirstName = "Bill" });
var aPerson = db.SelectByKey<PersonTable, Person>(123);
db.Update<PersonTable>(aPerson);
db.Delete<PersonTable>(aPerson);

//strong-typing select using the table enum
var stmt1 = db.CreateSelectStatement<PersonTable>();
stmt1.Where.AndWithIdentifiers("{LastName} = @LastName AND {FirstName} = @FirstName", 
    new { LastName = PersonTable.LastName, FirstName = PersonTable.FirstName },
    new { LastName = "Smith", FirstName = "Bill" });
aPerson = db.SelectObject<Person>(stmt1);

Multiple DBMS

Using a different DBMS with ADO.NET can require a lot of changes. From the obvious changes to every Connection, Command, and Parameter reference, to more obscure requirements like Disposable and data type differences. Even using the factories and interfaces from System.Data does not alleviate all of these. Rational.DB provides a consistent interface for all supported databases (currently SQL Server, SQL Server CE, Oracle, and MySQL). It won't rewrite your SQL, but if you Keep It Simple, you might be able to switch DBMS with a single line of code.