Linq to SQL?

I'm at a bit of a crossroads. I've gotten pretty comfortable with VB .Net and I do a lot of development work with VB .Net and SQL Server databases. Having been self-taught I can get things done "my way" pretty quickly and customers have been very happy with the results. But the more I learn, the more I realize there are different, maybe better, ways the same application can be built. Meaning the end result from the users' perspective may be no different, but from the developers looking at two sets of code could be worlds apart.

My general method up until now is I use SQLClient class along with CommandText to send SQL INSERT, UPDATE, DELETE statements dorectly to the SQL Server from the VB app. I have a function I use that will escape any special characters within any string variables. I put a lot of vb database functions in a module that all of my forms can use. When I need to pass data around I send/add arguments to functions using regular variables, etc... I log all of my SQL queries sent to the server to a log file for troubleshooting.

So far this has worked well. I know there are better ways to do this, Stored Procedures for example. But I just have not made that leap yet. I'm about to consider turning my world upside down!

I'm MOSTLY interested in creating custom Classes that represent the various objects in my database (Customers, Projects, Invoices, etc...). Then defining all of the properties each of these classes should have: Customer.ID, Customer.Name, Customer.Status, etc... and would like to "map" that Customer object to the database I'm interfacing with. I figure I can wrap all of the SQL commands into methods within each class, i.e.: myCustomer.Name = "Joe Smith"

Then I realize that is going to be a lot of work to setup all of the classes, along with their properties and methods. And then how do I log or troubleshoot the SQL that is actually happening, etc...?

Then I come across Linq to SQL. And there is a utillity called "SQLMetal.exe" which basically reads the schema of a database and does everything I'm talking about automatically? Is it really as simple as that? I mean I have never found the "easy way" to work very well. For example, I never use data designer UI (DataAdapters, DataTables, etc ) at design time. I am always binding my grids and controls at runtime because I find the simple bound-control mechanism isn't flexible enough for real world apps. So is that also the case with Linq to SQL and this SQLMetal thing?

Looking for some experienced advice and thoughts here. Thank you.

I have not tried this util as it is command line but instead use DataContext by starting here.

So let's say I use NorthWind SQL Database and do a query against Customers.

Code:

---

Dim db As New DataClasses1DataContext
Dim Result = (From Cust In db.Customers Where Cust.CompanyName = "Around the Horn").FirstOrDefault
If Result IsNot Nothing Then
    Console.WriteLine(Result.Country)
End If

---

If I want to tweak the customer class we simply right click on the dataset file in the solution explorer and write some code as shown below

Code:

---

Partial Class Customer
    Public Overrides Function ToString() As String
        Return Me.CompanyName & " " & Me.ContactName
    End Function
    Public Sub MyProcedure()
        MessageBox.Show(Me.ToString)
    End Sub
End Class

---

So now we have

Code:

---

Dim db As New DataClasses1DataContext
Dim Result = (From Cust In db.Customers Where Cust.CompanyName = "Around the Horn").FirstOrDefault
If Result IsNot Nothing Then
    Result.MyProcedure()
End If

---

To see a video step by step doing custom validation go here.

I would tend to recommend using the Entity Framework. It's fully integrated into VS and it has far wider support than LINQ to SQL. You can use the same Data Source wizard that you would use to generate a typed DataSet but simply choose EF as the model. EF also supports database first or code first.