Simple.Data and bulk inserts

Simple.Data allows you to pass Lists (or IEnumerables) of dynamically or statically typed objects (ExpandoObjects) to the Insert, Update and UpdateBy methods. This is great if you need to perform any kind of bulk insert/update.

var db = Database.OpenNamedConnection("dbConnection");
var list = new List<User>();
for (var i = 0; i < 10; i++)
  list.Add(new User { 
      Id = i + 10000, Username = "User" + i, 
      Password = "Pwd" + i, 
      DateCreated = DateTime.Now.AddDays(-i), 
      RoleId = (i % 3) 
   }
  );
//All users are inserted into the database 
//  with this single call
db.User.Insert(list);

When bulk inserting into SQL Server Simple.Data makes a call to the ADO.NET DbCommand.Prepare() which actually creates a compiled version of the insert statement on the server itself . Then insert statements are then run one by one which should improve performance. However you may not see and performance gain if you are only bulk inserting 2 or 3 rows as there is a small upfront overhead. This compiled temporary stored procedure will be destroyed when the current connection is closed. The SQL profiler shows us:-

declare @p1 int
set @p1=-1
exec sp_prepexec @p1 output,N'
  @p0 int,
  @p1 varchar(50),
  @p2 varchar(50),
  @p3 datetime,@p4 int',
  N'insert into [dbo].[User] ([Id],[Username],[Password],
    [DateCreated],[RoleId]) values (@p0,@p1,@p2,@p3,@p4)',
    @p0=10000,@p1='User0',@p2='Pwd0',
    @p3='Nov 29 2011  8:59:44:897PM',@p4=0
select @p1

and then sends each insert as:-

exec sp_execute 1,@p0=10001,@p1='User1',@p2='Pwd1',
  @p3='Nov 28 2011  8:59:44:897PM',@p4=1
exec sp_execute 1,@p0=10002,@p1='User2',@p2='Pwd2',
  @p3='Nov 27 2011  8:59:44:897PM',@p4=2
exec sp_execute 1,@p0=10003,@p1='User3',@p2='Pwd3',
  @p3='Nov 26 2011  8:59:44:897PM',@p4=0

Note: In the case of Insert you get back a new list of objects with any database-assigned default values such as identity values or timestamps.

The following code is just a check to see that the inserts worked:-

foreach (var item in db.User.All())
  Console.WriteLine(string.Concat(
    item.Id, " ", 
    item.Username, " ", 
    item.Password, " ", 
    item.DateCreated, " ", 
    item.RoleId)
  );

I believe before version 1 release Mark is going to create an Upsert which will either update or insert your entity based on whether the record exists in the database.

Since writng this post, Mark has also done some macro optimisations using Bulk insert which makes use of SqlBulkCopy. Read this blog post to find out more.

Simple.Data Ranges

In this blog I am going to show you how you can use FindAll using ranges that makes use of the BETWEEN operator. The BETWEEN operator (in SQL) is used in a WHERE clause to select a range of data between two values.

Lets look at finding users by Id:-

var db = Database.OpenNamedConnection("dbConnection");
var list = db.User.FindAllById(10002.to(10005));

foreach (var item in list)
  Console.WriteLine(string.Concat(item.Id, " ", item.Username,
    " ", item.Password, " ", item.DateCreated, " ", 
    item.RoleId));

This actually produces the following SQL:-

select
  User.Id,
  User.Username,
  User.Password,
  User.DateCreated,
  User.RoleId
from
  User
WHERE
  User.Id BETWEEN 10002 AND 10005

and returns the following data:-

OK so far so good but what about if we want to get all users that have a RoledId of 1 to 2. This is where the dyanamic features of .net4 and Simple.Data comes alive. All I need to do is change FindAllById to FindAllByRoleId:-

var list = db.User.FindAllByRoleId(1.to(2));

foreach (var item in list)
  Console.WriteLine(string.Concat(item.Id, " ", item.Username,
    " ", item.Password, " ", item.DateCreated, " ", 
    item.RoleId));

The SQL where clause will now be

WHERE User.RoleId BETWEEN 1 AND 2

and returns the following data:-

OK great but what about date ranges?

var list = db.User
  .FindAllByDateCreated("2011-11-20".to("2011-11-22 17:00"));

foreach (var item in list)
  Console.WriteLine(string.Concat(item.Id, " ", item.Username,
    " ", item.Password, " ", item.DateCreated, " ", 
    item.RoleId));

The SQL where clause will now be

WHERE User.DateCreated
  BETWEEN '2011-11-20  00:00:00' 
    AND '2011-11-22  17:00:00'

and returns the following data:-

Great can we also make user of using the BETWEEN operator for strings? you bet:-

var list = db.User.FindAllByUsername("User2".to("User4"));

The SQL where clause will now be

WHERE User.Username BETWEEN 'User2' AND 'User4'

You can also use FindAllBy... range for arrays, this will make use of the IN operator (in SQL) which allows you to specify multiple values in a WHERE clause, more to follow...

Simple.Data explicit joins

I am delving a bit more into Simple.Data and starting to look at joins. I am going to begin with a classic one-to-many relationship between the tables cmsmeta and cmspage:-

So how does simple.data handle joins? In this blog I am going to show you explicit joins.

The following code shows how we can project 2 columns from both tables into a DTO using the on(...) form.

var db = Database.OpenConnection(...);

var pages = db.CmsPage
  .Query()
  .Join(db.CmsMeta).On(db.CmsPage.MetaId == db.CmsMeta.Id)
  .Select(
    db.CmsPage.Id, 
    db.CmsPage.PageName, 
    db.CmsMeta.Keywords.As("MetaKeywords"),
    db.CmsMeta.Title.As("MetaTitle")
  ).Cast<PageDto>();

foreach (var page in pages)
  Console.WriteLine(string.Concat(page.Id, " ", page.PageName, ",", page.MetaKeywords, "-", page.MetaTitle));

The DTO looks like this:-

internal class PageDto { {
  public long Id { get; set; }
  public string PageName { get; set; }
  public string MetaTitle { get; set; }
  public string MetaKeywords { get; set; }
}

As you can see we are joining CmsPage with CmsMeta using on(...).

There is also another way to describe an explicit join and that is to use named parameters, that is describe the join as Id: db.CmsPageMetaId, whichever you choose is down to personal taste. For example:-

var pages = db.CmsPage
  .Query()
  .Join(db.CmsMeta, Id: db.CmsPage.MetaId)
  .Select(...)

Both pieces of code produces the following:-

and in case you are wondering the generated SQL looks like this:-

select 
  cmspage.Id,
  cmspage.PageName,
  cmsmeta.Keywords AS MetaKeywords,
  cmsmeta.Title AS MetaTitle
from 
  cmspage
JOIN cmsmeta ON (cmsmeta.Id = cmspage.MetaId)

There is another type of join that is called Natural Joins which requires referential integrity and uses the Foreign Key constraints on the tables. Read more about natural joins. Unless you are using the innoDb engine in MySql then natural joins are not supported.

Simple.Data a projection into a dto

Sorry for the long delay since my last post but my wife gave birth to our son Nico a few weeks ago and to honest this has taken up all my time....

Anyway back to the blog, today I decided to have a quick look into Simple.Data. First I created a new console application and then installed the package via nuget:-

PM> Install-Package Simple.Data.Mysql

If you are using Sql Server then just use:-

PM> Install-Package Simple.Data.SqlServer

The first thing I decided to try was projecting a couple of columns from a single table into a DTO. My schema looks like this (note mysql):-

var db = Database.OpenConnection("server=1.1.1.1;user=usera;database=wildesoft;password=*********;");

IEnumerable<CmsPageDto> pages = db.CmsPage
  .All()
  .Select(db.CmsPage.PageId.As("Id"), db.CmsPage.PageName)
  .Cast<CmsPageDto>();

foreach (var page in pages) {
  Console.WriteLine(page.Id + " " + page.PageName);
}

The dto looks like this:-

public class CmsPageDto {
  public int Id { get; set; }
  public string PageName { get; set; }
}

One thing that I could not fathom out was how to change the column PageId to just Id. After downloading the Simple.Data source code and looked at the tests I worked out that I needed to use the .As("NewColumnName")

and volia...

The generated SQL looks like this:-

select
  cmspage.PageId AS Id,
  cmspage.PageName 
from
  cmspage

A sweet spot is that preserves the case of the table and columns names, very important for MySql server.

Boy is Simple.Data well just simple to use! I am going to use Simple.Data in my next project and hopefully be able to blog about it a bit more..