In the colourful land of .NET, the database gods have bestowed upon us lowly peasants (atleast) 3 ways to access ye olde SQL databases.  Each has their pros and their cons.   Some more so than others.  Below are some of those options.  As a developer, it is up to you to choose which is right for (a) you, and (b) your situation.

1. DataSource, DataTable, DataProvider, and the visual editor

Microsoft used to promote this quite a bit.  It’s the primary means to access SQL databases from some of the out-of-the-box .NET controls (for both ASP.NET and Windows Forms).  Using this method, you drag database objects onto your forms and configure them using the visual SQL editor.  As I understand it, this is very good for RAD (Rapid Application Development) developers.  However, for me, it’s quite slow to use because of the visual editing elements.

2. SQLConnection and SQLCommand classes

This used to be my favourite way to work with SQL databases.  Not being a fan of the DataSource classes, I found it easier to use the SQLConnection and SQLCommand classes directly.  I also felt that I had more control over the commands and the data.  Using this method, I was able to setup my own connections and do my own calls without needing to hide behind a data abstraction.  The only problem with this was that there was alot of repeated code.  Often, I’d just have to copy and paste an existing connection and change a couple of lines here or there.  It was different enough that I couldn’t make a general purpose handler though.  But I had my control.  And that’s how the SQL world was to me until…

3.  LINQ to SQL

In my opinion, this is the database gods’ gift to mankind.  Using LINQ to SQL, I can use a visual editor to define the relationships between my SQL tables (notice that I’m back on the visual editor bandwagon).  Afterwards, I have a strongly-typed set of classes that I can use to access my data.  No more casting data out of the database classes. Instead, it’s already in the proper types.  No longer do I have to create SQL statements and be required to run my program to find out how incorrect the statement is.  LINQ to SQL is done natively in the programming language (C# for me), so incorrect syntax is caught by the compiler.  My latest project to access a database took me 2 hours to complete using LINQ.  If I had been using method #2, I think it would have taken me closer to a day, but it would have worked just as a well in the end.  LINQ let me complete the job faster.

In summary, LINQ to SQL == goodness.  Others are still good, just not as good (for me anyways).