Gosh I hate SPs/SQL (a.k.a. LLBLGen Rocks!)

...well at least in my C# code. That stuff belongs in the world of reporting, integration services, and other places which are NOT IN CODE. You couldn't get me to use ADO.NET anymore; it's such a pain for large projects. I love it for mass importing and when I do extremely dynamics such as report generation and a few other things, but for a static data access teir it's PATHETIC.

Not only is everything you type not strongly typed and therefore prone to error and has no color syntax; SQL (including SP calls) is just abrupt. You are doing this beautiful array of C# 2.0 greatness. Generic collections all over and BAM you see "select * from Person where..." Yuck!

Long live LLBLGen! If you don't know what LLBLGen is, you are completely missing out. I've been using it for months on all my projects and it's just downright awesome. It's basically LINQ...NOW. It allows you to use strongly typed .NET code to access your data WITHOUT writing any data tier code yourself. Not only that, the full blown enterprise edition is only $300. There is NO reason to ever write any data access layer yourself. It will pay for itself in about a DAY. I recommending getting a copy of LLBLGen even for your HOME projects!

All you have to do is point the LLBLGen app to the database, select the tables (and other entities) you want, and hit generate. It automatically creates a Visual Studio 2005 (or 2003 if you HAVE to use that icky thing) project for you. You can either include that project into your solution or compile the assembly and just us the compiled files. You only need to use LLBLGen again when you schema changes, which, as every architect knows...shouldn't be happening that often.

This rant is brought to you by the following sponsor...

DataSet dataSet1 = new DataSet( );
SqlDataAdapter sqlDataAdapter1 = new SqlDataAdapter( );

SqlConn1 = new SqlConnection(connString);
SqlConn1.Open( );

SqlCommData = new SqlCommand("GetDraftBox", SqlConn1);
SqlCommData.CommandType = CommandType.StoredProcedure;

sqlDataAdapter1.SelectCommand = SqlCommData;
sqlDataAdapter1.Fill(dataSet1, "MailList");

return dataSet1;
I was porting this old app from .NET 1.x to .NET 2.0 and I kept seeing stupid garbage like this. I got SO sick of it I decided to disconnect the logic tier and connect it to the LLBLGen access layer. Here's what I have now... 
DraftMailCollection draftMailBox = new DraftMailCollection( );
draftMailBox.GetMulti(null);
return draftMailBox;

THAT'S IT!!! Short, sweet, strongly-typed, intellisense LOVES it, it's pretty (you gotta LOVE the VS2005 color scheme), and it's VERY easy to extend.

As another quick example here's how you would translate the following code into LLBLGEN code. First, the SQL code...

select *
from Person
where
PersonUserName = username and
PersonPassword = password;

Now the LLBLGen code...

   
PersonCollection people = new PersonCollection( );
IPredicateExpression filter = new PredicateExpression( );
filter.Add(PredicateFactory.CompareValue(PersonFieldIndex.PersonUserName, ComparisonOperator.Equal, username));
filter.AddWithAnd(PredicateFactory.CompareValue(PersonFieldIndex.PersonPassword, ComparisonOperator.Equal, password));
people.GetMulti(filter);

Yes, joining is also BEYOND easy and, yes, it databinds to GridViews with no extra work. I use it all the time in my ObjectDataSources.

OK one last example... well two. This one should be the straw that breaks...something. Look at this ADO.NET code (I'll leave some of it out though)

sqlCommand = new SqlCommand();
sqlCommand.Connection = sqlConnection;
sqlCommand.CommandText = "insert into Customers (CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax )values(@CompanyName, @ContactName, @ContactTitle, @Address, @City, @Region, @PostalCode, @Country, @Phone, @Fax)";
sqlCommand.Parameters.AddWithValue("@CompanyName", companyName);
sqlCommand.Parameters.AddWithValue("@ContactName", contactName);
sqlCommand.Parameters.AddWithValue("@ContactTitle", contactTitle);
sqlCommand.Parameters.AddWithValue("@Address", address);
sqlCommand.Parameters.AddWithValue("@City", city);
sqlCommand.Parameters.AddWithValue("@Region", region);
sqlCommand.Parameters.AddWithValue("@PostalCode", postalCode);
sqlCommand.Parameters.AddWithValue("@Country", country);
sqlCommand.Parameters.AddWithValue("@Phone", phone);
sqlCommand.Parameters.AddWithValue("@Fax", fax);

sqlConnection.Open();

string id = (string)sqlCommand.ExecuteScalar();

sqlConnection.Close();

Some would say that it would not look so bad if the insert was in an SP. Um, ok...so you want to code in more than one place? No thank you. Also, what happens if there is no id to return? I've noticed that .NET 2.0 doesn't like that too much.

Now heres the LLBLGen code...

CustomersEntity customer = CustomersEntity( );
customer.CompanyName = companyName;
customer.ContactName = contactName;
customer.ContactTitle = contactTitle;
customer.Address = address;
customer.City = city;
customer.Region = region;
customer.PostalCode = postalCode;
customer.Country = country;
customer.Phone = phone;
customer.Fax = fax;
customer.Save( );

string id = customer.CustomerId;

Yeah...wow. Not only that, but in the SQL code you also have to do all that ADO.NET stuff. Not with LLBLGen, it's all in one!

I've been using LLBLGen for almost a year now and I have to say that my productivity has SOARED because of it. LLBLGen + .NET 2.0 + Firefox is a smart, er..., GENIUS match that should not be ignored, but rather ran towards!

To get a 30-day trial copy of LLBLGen follow the below link. I'm sure you will love it and for only $300, your manager will as well.