Friday, March 30, 2012

Linq to SQL: The 5 minute overview

Nothing could be simpler than establishing a Linq connection to your SQL Server.  There are a few skills that you need to have mastered before attempting the connection.  For instance, the mouse click and dragging and dropping.  If you have not mastered these two skillsets, you have no hope in Linq.
However, if you are proficient – setting up a Linq connection should take you the better part of 2 minutes.  So, without sugar-coating it anymore… We’ll dive right in.

It is assumed that you have Visual Studio 2010 and the appropriate project open where you want to establish your Linq to SQL connection. 

In your Server Explorer Tab Right Click the Data Connections and click Add Connection.

You will presented with the following dialog:

Obviously you will want to fill out the appropriate fields with the information needed to connect to a database.
Data Source: If you are connecting to a SQL Server, you will want to choose the default.
Server Name: The SQL Server instance name that you want to connect to.  In my current environment, I have a SQL Server Production, Testing, Development, and an Express version on my local machine that I use as a playground.  In the screenshot below, I am using the Express version for security reasons. 
You will then want to configure your security credentials.  Typically this will be SQL Server Authentication with the logins that are allowed for the particular database, however (again) due to this being an Express version I am just using Windows Authentication.
Finally, you will want to identify the particular database that you want to connect to.  This will be the main resource that you are going to use to provide data for the application to use.

Before clicking OK.  There is a Test Connection button that allows you to connect to the resource using the server and credentials provided to make sure everything is configured correctly (and you haven’t fat fingered any part of it).
Clicking OK creates the connection in your Server Explorer Tab.

As you can see here, we have access to all our objects in the database.

From there, you will want to go back to your Solution Explorer.  Right click the name of your project and click Add New.  This will bring up the Add New Item modal dialog.  In the objects list, choose Linq to SQL Class:

We’ll rename the class to NorthwindDataClasses.

Click OK and the system automatically creates your datacontext class for you.  This is how you will refer to the Linq object throughout your application.  If you receive the following error, this is telling you that Visual Studio likes to organize files in a certain way and it wants to put the file in a special folder that it keeps for class files.  Click OK.

From there you will see the class added to your Solution Explorer:

Notice that the file type is dbml.  You will also notice that a new tab has been opened in your editor pane with the follow instructions:


From here you will need to open your Server Explorer, and drag and drop table objects to the left pane (big) on the screen.  You will see the table objects listed as you would in SQL.

Notice that if you drop objects that have relationships, that it will create those relationships for you in Linq. 

Here you can see the relationship between the Employee and Order table.
You can also Linq SQL Methods (otherwise known as Stored Procedures).  In the Server Explorer, drag and drop the Method (or Stored Procedure) to the right pane (small):

You will see it listed in the right pane.  It will also tell you the parameters that are required for the Method.  If you decide to get lazy and drag a table to the right pane or a stored procedure to the left one, it will place it in the correct place, so it is more of a best practice rule to drag it to the correct pane.

Why Microsoft internally can not use the same technical jargon is beyond me.  You would think that the Linq people would walk down the hall to see if the SQL people call it a Method or a Stored Procedure.  Of course, this oversight is not limited to Linq.  Perhaps in the future, I will complain to Mr. Gates about his lack of attention to detail……

Once we added the first table, we had established our first connection with Linq to SQL.  However, if you were going to utilize more SQL objects, I simply demonstrated the method here.

As another caveat to Linq, you must SAVE the dbml before you can correctly utilize it.  This includes if you add objects later.  Linq automatically writes the class for you, but it does not write the class until you save the Linq object.  Simply either click the Save, Save All, or close the dbml object and it will ask if you wish to save the object.

Now we can utilize the Linq object that we have just created.  In order to correctly connect to the object, we must import the correct namespace.  So:
We have to add the Imports System.Data.Linq command to the declarations of our page.  In this instance I am using the default page.  So open the Default.aspx.vb page and add it to the declarations.

Another caveat of Linq is that when it builds your Linq class, it names it the name you gave it in the Add New Item window plus the words DATACONTEXT.  So in order to instantiate my class I will need to the dimension the following variable to the class as so:

Obviously, for this instance, I made the variable global to the page.  This is so that I do not have to repeatedly dimension the variable in the different methods.  Now that we have instantiated the class, we can use it like we did in the Linq to Objects article I wrote:

In this example, we are dimension a results variable of no particular datatype (thus object) that will be instantiated with the Select statement.  However, our From statement identifies where we expect to get the data from.  I created a local variable called table as a placeholder, thus our select statement references it and we selected which columns we wanted from the table.

Now we need somewhere to place the data that we get.  Right Click the App_Data Folder in your Solution Explorer, and click Add New and then select class.  Name the class ResultList:


For simplicity we will use Response.Write to directly write to the outgoing HTML.
Back on our Default Page, we need to add the following For Each Loop:

You could easily use it to bind to a datagrid/gridview or populate dropdownlists… However this is beyond the scope of this article.
In the For Each Loop what we have done is used our resultlist class object and populated the public properties with the values that are returned in the table.  From there we added them to the HTML output stream.

To utilize a Linq Method (once again, a Stored Procedure in the SQL world) we simply identify it by name and the Intellisense will actually read the Method and help us out with what parameters we need to populate:

Really cool thing is that it even tells us what type it is looking for.

Well that should be enough to get you started on your Linq journey…

Happy .Netting… Saleh
Hack The Gibson!

No comments:

Post a Comment