Home.NET Introducing LINQ to SQL Designer using Vis...
Introducing LINQ to SQL Designer using Visual Studio 2008
This is an introductory article that focuses on designing LINQ to SQL objects using the LINQ to SQL Designer in Visual Studio 2008. Apart from designing, we will concentrate on fetching and updating databases using the model created with Designer.
If you are a bit new to programming LINQ, please consider reading my previous article titled "Beginning 'LINQ to SQL' using Visual Studio 2008."
The entire source code for this article is available in the form of a free downloadable zip file. The solution was developed using Microsoft Visual Studio 2008 Team Edition on Microsoft Windows Server 2003 Standard Edition with Microsoft SQL Server 2005 Developer Edition. I didn't really test it in any other environment. I request that you post in the discussion area, if you have any problems in execution.
Creating a simple "LINQ to SQL" application: creating the project
In my previous article, we focused on "LINQ to SQL" development without working with a designer. "LINQ to SQL" designer is a great choice and has lots of features that we can take advantage of. We can take full advantage of it, if we know what it is doing internally.
Before going into complete internal details, let us start by creating a 'LINQ to SQL' application using a designer.
To make this article simple, I created two tables, "emp" and "dept," as follows:
The following are the steps used to create an ASP.NET 3.5 application with a "LINQ to SQL" designer:
Open Visual Studio 2008
Go to File || New || Project
In the "New Project" dialog, make sure ".NET Framework 3.5" is selected. In the "Project Type," select either "Visual Basic || Web" or "Visual C# || Web".
Select "ASP.NET Web Application" in the templates, provide the name of the application (Fig 3), and finally hit OK.
Creating a simple "LINQ to SQL" application: using "LINQ to SQL Designer"
The following steps are continued from previous section.
Right click on solution and go to Add || New Item (Fig 03)
Select "LINQ to SQL Classes" from the templates, provide the DataContext name and hit "Add". It is always recommended to provide a database name (unless you are working with multiple databases). The following is the screenshot (Fig 04):
If not already open, open "Server Explorer" (go to View || Server Explorer) and connect to your database. Make sure that you have both tables (along with their relationship) as mentioned in the previous section. The following gives you a better idea (Fig 05):
Drag and drop "dept" and "emp" from "Server Explorer" on to the designer (Fig 06).
Make sure "Web.Config" is modified with the connection string (should be automatic).
Now that you are familiar with fetching and updating database tables using LINQ (in previous sections), it is time to work with transactions.
"LINQ to SQL" has built-in support for transactions. If we use "LINQ to SQL Designer," we don't even have to worry about transactions. If multiple rows are modified (irrespective of any number of tables), "LINQ to SQL" automatically encloses them in a transaction.
Let us start with another web page. Follow the steps below:
Add a new web page (.aspx) to your project. Call it "TransactionSample.aspx."
Design your web page as shown below (Fig 10):
Make modifications to the source to make it look like the following:
The "LINQ to SQL" framework automatically encloses the modifications into a transaction, if it is trying to handle more than one row at a time (say, submitting more rows to save to a database).
Let us try to understand each line from the previous section.
Dim db As New SampleDBDataContext
Dim depts As Table(Of dept) = db.depts
The above creates an instance to the database context. Through the context object, we interact with the database. In other words, it is the main interface. As "Department" is the parent table, I created a reference to the collection of "dept" models. This can be used to add "dept" objects directly to it. We can also do the same using "db.depts" directly.
Dim NewDept As New dept
NewDept.deptno = 50
NewDept.dname = "Legal"
Adding a new department involves creating a new "dept" object. The above does the same. It instantiates a "dept" object and provides the values for the properties.
'create new emp
Dim NewEmp1 As New emp
NewEmp1.empno = 2001
NewEmp1.ename = "aaa"
NewEmp1.sal = "2500"
NewEmp1.deptno = 50
'create another emp
Dim NewEmp2 As New emp
NewEmp2.empno = 2002
NewEmp2.ename = "bbb"
NewEmp2.sal = "5500"
NewEmp2.deptno = 50
Once the "Dept" object is ready, we need to add a few employees (say "emp" objects) to the newly created "dept" object. The above creates two new "emp" objects and provides the values for their properties.
NewDept.emps.Add(NewEmp1)
NewDept.emps.Add(NewEmp2)
As the two employees are related to the same department, we need to add those to the sub-collection of "Dept" object (which is nothing but the "Emps" collection). The above does the same.
db.depts.InsertOnSubmit(NewDept)
db.SubmitChanges()
The first line in the above sample adds the entire dept structure (along with employees) to the "depts" collection of the database context. Finally, we save it using the second line.
The "db.SubmitChanges()" opens a transaction. The transaction is implicitly handled as part of the "SubmitChanges" method.
Make sure that if the "SubmitChanges" method needs to work with the explicit transaction (say distributed), we can execute the statement in a separate "TransactionScope" enclosure as follows:
Using ts As New System.Transactions.TransactionScope
.
.
db.SubmitChanges()
.
.
End Using
I hope you enjoyed the article. Any suggestions, bugs, errors, enhancements, etc. are highly appreciated at http://jagchat.spaces.live.com
Beginning LINQ to SQL Using Visual Studio 2008
