Introduction
DynamicToSql is a lightweight API that maps dynamically typed entities to a database and vice versa. The API strives to generate database agnostic SQL and can be used against any database, such as Microsoft SQL Server, MySQL, Oracle, SQLite, etc, that has support for ADO.NET. The API can be easily extended and used to develop custom repository and data access patterns. To get started read the Quick Start Guide below and download the VS 2010 project, which contains the API's source and a lot more examples than the guide.

Quick Start Guide
Creating a session object
To start working with the API, you first need to create a Session object. Session objects are created with the help of the SessionFactory. At a minimum, you need to set the Connection property of the SessionFactory. Optionally, you may provider a logger or use the API's loggers (Debug and Console), a parameter prefix (the default is '@'), and a naming converter, which can be used to translate between database tables and colums and entities and properties.

var conn = new SqlConnection(connectionString)
var logger = new ConsoleLogger()
Session session = SessionFactory.Create(conn, logger)

Creating a dynamically typed entity
To create a dynamically typed entity you can use the dynamic keyword, Entity object, and add properties accordingly.

dynamic person = new Entity()
person.Name = "John"
person.Age = 40
person.CreatedDate = DateTime.Now

Performing CRUD operations
You can perform CRUD operations through the Session object as shown below .

 //save person
session.Save("Person", person)

//update all persons with name "John"
session.Update("Person", person, new { Name = "John" }) 

 //delete all persons with name "John"
session.Delete("Person", new { Name = "John" })

//count all persons
session.Count("Person") 

 //find all persons with name "John"
session.Find("Person", new { Name = "John" })

Using criteria
You can use criteria to update, delete, find, and count entities from the database. There are two ways of specifying criteria: Anonymous Classes and Criteria Objects. In addition, the Criteria object can be used to dynamically create criteria as needed by combining expressions of properties. See examples below.

 //find all persons with name "John" and age 40
session.Find("Person", new { Name = "John", Age = 40})

//find all persons with name "John" or name that starts with "M" and age between 30 and 50 
var criteria = Entity.Property("Name") = "John" | Entity.Property("Name").Like("M%")
criteria &= Entity.Property("Age").Between(30, 50)
session.Find("Person", criteria) 

Using the property selector
The property selector can be used to include or exclude properties as needed. It can be used when converting, saving, updating, and finding entities. Included properties take precedence over excluded properties. See examples below.

//update the entity with name "John" with the person's properties but don't update its Id
session.Update("Person", person, new { Name = "John" }, new PropertySelector().Exclude("Id"))

//select only the Id and Name properties
var persons = session.Find("Person", new PropertySelector().Include("Id", "Name"))
foreach(var person in persons)
      Console.Write(person.Id + " " + person.Name)

Using transactions
To use transactions call the StartTransaction method on a session object and then wrap it with a using statement. You can have more manual control over the transaction's life-cycle by using the Commit and Rollback methods as needed.

using(var transaction = session.StartTransaction())
{
      //Perform operations here
}

Using native SQL
Whenever the API does not provide what you need and/or you want to do something complex, you can always revert to native SQL and use your knowledge of SQL as needed. See examples below.

//get single list
IList<dynamic> slist = session.Sql("select * from Person").ToList()

//get multiple list
IList<IList<dynamic>> mlist = session.Sql("select * from Person; select * from Address;").ToMultipleList()

//get scalar value
int count = session.Sql("select count(*) from Person").ToScalarValue<int>()

//get list by supplying parameters
var plist = session.Sql("select * from Person where Name=@Name and Age=@Age")
                   .Parameter("Name", "John")
                   .Parameter("Age", 40)
                   .ToList()

Using stored procedures and functions
In addition to native SQL, you can also call stored procedures and functions in a similar way. See examples below.

//update the age of all persons with name "John" and print the number of records updated
var proc = session.Procedure("UpdatePersonAge")
.Parameter("Name", "John", DbType.String, ParameterDirection.Input)
.Parameter("Age", 41, DbType.Int32, ParameterDirection.Input)
.Parameter("RecordsUpdated", null, DbType.Int32, ParameterDirection.Output)
proc.Execute()
Console.WriteLine(proc["RecordsUpdated"].Value)

//get the number of persons with name "John"
var func = session.Function("CountPersons")
.Parameter("Name", "John")
.Parameter("ReturnValue", null, DbType.Int32, ParameterDirection.ReturnValue)
func.Execute()
Console.WriteLine(func["ReturnValue"].Value)

Integrating with ASP.NET MVC framework
There are two ways of integrating the DynamicToSql API with the ASP.NET MVC framework: You can either write your own custom model binder for the dynamic and Entity types or you can use strongly typed models in the controller's actions and then use the entity converter as shown below to convert strongly types entities into dynamically type entities.

var person = new Person()
person.Name = "John"
person.Age = 40

//use the ToEntity method to convert to an Entity or dynamic type.
Entity dynoPerson = Entity.ToEntity<Person>(person)
dynamic dynoPerson = Entity.ToEntity<Person>(person)

Last edited Aug 25, 2012 at 3:39 PM by tanitm, version 45