SQL CLR – Appropriate Use in C# Frameworks

SQL CLR integration was developed mainly because, implementing logic through T-SQL was really hard. .NET Framework if filled with thousands of useful libraries, to which you have no access at SQL engine level. Thus, any logic should be implemented from scratch. For example, a simple foreach loop should be implemented with cursors, which are honestly, far less productive.

I have experience of working with SQL CRL integration. I did it for date-time conversion at database engine level. Date-time conversion at the level of database engine is really really hard, while .NET, has System.Globalization which facilitates the work.

The main point of this method, is to follow the step exactly as described (like extension methods in which, methods should be static functions inside static classes inside first-level namespace). If you fail to do something exactly as you're told, things simply fail.

In modern SQL Server versions (> 2008 IIRC) you can use User defined types. So your sproc can actually take an array of user types:

http://blogs.msdn.com/b/felixmar/archive/2010/10/27/how-to-create-and-execute-a-stored-procedure-using-a-table-as-a-parameter.aspx

In our app we actually use the repository pattern, but hack it a bit to get around slowness issues like you describe.

So, in your example, I'd do something like this:

using (CustomerBulkContext ctx = customerCollection.GetSaveContext())
{

foreach(Customer c in customerCollection)
{
   if(c.ShouldSave())
   {
       c.Save(ctx);
   }
}
ctx.Save();
}

The idea is that all the customers are added to an in-memory list inside the context, and then the actual commit to the DB is handled via the save context's .Save() method. That method then creates a list of SQL objects and hands them to a sproc that does the bulk update/insert/delete.