Protect Database Design – SQL

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.

In general, if you are using this model, you should not use any access method except stored procs and the rights shoud be on the procs themselves and never directly on a table or view. This means no dynamic SQL of any kind (including in procs). That way users who directly access the database can still only do the things they are allowed to do by the application. This is one place where using an ORM would be a disaster. There shoud be no SQL Server logins. Instead the users should be in windows groups that are given rights based on what they are allowed to do. These rights could consist of read rights for a table but no insert/updates or deletes that are not done through executing a stored proc. They would get exec rights only to the procs that their user group should be able to run in the application.