Linq Inserts

Contents

Summary

You can use the goodness that is the Linq syntax to insert data into your database. It's important to note that SubSonic IS NOT AN ORM - it's a query tool (subtle difference) and therefore it doesn't do object tracking. There's nothing stopping you from creating a T4 template to do just this - but out of the box SubSonic is not designed this way.

Typically, ORM's will track the changes to your object and will "flush" these changes to the database when you tell it to (this is the way Linq to Sql works). This is fine and dandy if you like working that way - but we think this is a Big Lie.

The web is a stateless medium - this sort of "contextual tracking" is just not the Real World and we don't drink that beer.

Setup

Make sure you setup SubSonic by adding a reference, setting up your DB connection, and then adding the templates to your project.

Simple Inserts

You can perform inserts very simply by using the Query Surface, which is generated for you when you add the T4 templates to your site:

var DB=Northwind.DB.CreateDB();
db.Insert.Into<Northwind.Region>(
         x => x.RegionID, 
         x => x.RegionDescription)
        .Values(6, "Hawaii").Execute();

This will execute an INSERT statement thus:

INSERT INTO [Region](RegionID,RegionDescription)
 VALUES (@ins_RegionID,@ins_RegionDescription)

Using the Repository

Many people don't want to write this stuff by hand and would rather use objects. You can definitely do this by using our Linq IRepository, which is generated for you if you using our default T4 templates.

Simple Insert

This will add a Product to the Products table (using Northwind)

var repo=new NorthwindRepository<Product>();
 
var p = new Product();
p.ProductName = "Test";
p.UnitPrice = 100;
//...//
repo.Add(p);

Multiple Inserts

You can also insert an IEnumerable collection of items to the Repository, and they will be saved in a transaction:

var repo=new NorthwindRepository<Product>();
List<Product> queue = new List<Product>();
for (int i = 1; i < 100; i++) {
    var p = new Product();
    p.ProductName = "Test";
    p.UnitPrice = 100;
    queue.Add(p);
}
repo.Add(queue);

This latter example uses SubSonic's Batch Query to execute multiple items at once inside a transaction.