Beginning LINQ to SQL Using Visual Studio 2008

This is an introductory article which focuses on fetching and updating databases using the DataContext object in "LINQ to SQL." By reading this article, you will learn how to work with "LINQ to SQL" manually, without using a designer and with great simplicity. It should serve to give a solid foundation to both VB and C# programmers.

Contributed by
Rating: 5 stars5 stars5 stars5 stars5 stars / 17
March 31, 2008
Rate this Article:
MEH MEH++


SEARCH ASP FREE
TOOLS YOU CAN USE

advertisement

I will not be including great theories of LINQ or related technologies in this article; Microsoft already features great content on those topics on its MSDN. If you are absolutely new to LINQ, I request that you go through the information you'll find at this link first:


http://msdn2.microsoft.com/en-us/netframework/aa904594.aspx


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.

Developing a simple "LINQ to SQL" application (without LINQ designer): creating the project

It is always easier to work with wizards and designers rather than coding manually. But when it comes to customizing the auto-generated code, we should have a fair understanding of generated code and its related pieces.

We can generate lots of code automatically using Visual Studio designers (like DataSet Designer, LINQ to SQL Designer, etc.). As this is the most fundamental article on "LINQ to SQL," I would like to introduce the manual way of coding first rather than using "LINQ to SQL Designer" and complicating things. This is only to give you a better understanding of "LINQ to SQL" from the point of view of grasping the basics. My upcoming articles gradually focus on working with LINQ to SQL Designer.

To make this article simple, I created two tables, "emp" and "dept" as follows:


The following are the steps for creating an ASP.NET 3.5 application with "LINQ to SQL" support:

  • 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 the OK button.

 

Developing a simple LINQ to SQL application (without LINQ designer): UI Design and source in VB.NET

The following steps are continued from the previous section.

  • Add a reference to "System.Data.Linq" (Go to Project || Add Reference)

  • Add a new class (Project || Add class) to the project and name it "Emp.vb"

  • Copy the following code into it:


Imports System.Data.Linq.Mapping


<Table(Name:="emp")> _

Public Class Emp


Private _empno As Integer

<Column(IsPrimaryKey:=True)> _

Public Property empno() As Integer

Get

Return _empno

End Get

Set(ByVal value As Integer)

_empno = value

End Set

End Property


Private _ename As String

<Column()> _

Public Property ename() As String

Get

Return _ename

End Get

Set(ByVal value As String)

_ename = value

End Set

End Property


Private _sal As Nullable(Of Double)

<Column()> _

Public Property Sal() As Nullable(Of Double)

Get

Return _sal

End Get

Set(ByVal value As Nullable(Of Double))

_sal = value

End Set

End Property


Private _deptno As Nullable(Of Integer)

<Column()> _

Public Property deptno() As Nullable(Of Integer)

Get

Return _deptno

End Get

Set(ByVal value As Nullable(Of Integer))

_deptno = value

End Set

End Property


End Class


  • Open "default.aspx", add a GridView and Button controls as follows:


<body>

<form id="form1" runat="server">

<div>

<asp:Button ID="btnSelectStar" runat="server" Text="Select *" />

<asp:GridView ID="GridView1" runat="server">

</asp:GridView>

</div>

</form>

</body>


  • Open "default.aspx.vb" (code behind) and modify as follows:


Imports System.Data.Linq

Partial Public Class _Default

Inherits System.Web.UI.Page


Protected Sub btnSelectStar_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnSelectStar.Click

Dim db As New DataContext(New System.Data.SqlClient.SqlConnection("data source=.sql2k5;initial catalog=Sample;user id=sa;Password=eXpress2005"))

Dim tblEmps As Table(Of Emp) = db.GetTable(Of Emp)()

Me.GridView1.DataSource = tblEmps

Me.GridView1.DataBind()

End Sub

End Class


Once executed (pressing F5), you should be able to see the output as follows (Fig 4):


Developing a simple LINQ to SQL application (without LINQ designer): source in C#


To code in C#, follow the steps in the previous section with the following code (UI Design would be the same).

The following is the "emp" class written in C#:


using System.Data.Linq.Mapping;


namespace SampleCS

{

[Table(Name="emp")]

public class Emp

{


[Column(Name = "empno", IsPrimaryKey = true)]

public Int32 empno { get; set; }


[Column(Name = "ename")]

public string ename { get; set; }


[Column(Name = "sal")]

public System.Nullable<double> sal { get; set; }

 

[Column(Name = "deptno")]

public System.Nullable<Int32> deptno { get; set; }


}

}


The ASPX code would be the same as given in the previous section. The following is the code for code-behind (default.aspx.cs):


using System.Data.Linq;


namespace SampleCS

{

public partial class _Default : System.Web.UI.Page

{


protected void btnSelectStar_Click(object sender, EventArgs e)

{

DataContext db = new DataContext(new System.Data.SqlClient.SqlConnection("Data Source=.sql2k5;initial catalog=sample;user id=sa; password=eXpress2005"));

Table<Emp> tblEmps = db.GetTable<Emp>();

this.GridView1.DataSource = tblEmps;

this.GridView1.DataBind();

}

}

}


CRUD operations using the LINQ to SQL application (without LINQ designer): UI Design


Add a new web form (aspx) to the project named "CRUDSample.aspx" and modify the user interface to look like the following (Fig 05):



The following is the code to design the above user interface:


<%@ Page Language="vb" AutoEventWireup="false" CodeBehind="CRUDSample.aspx.vb" Inherits="SampleVB.CRUDSample" %>


<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">


<html xmlns="http://www.w3.org/1999/xhtml" >

<head id="Head1" runat="server">

<title>Untitled Page</title>

<style type="text/css">

.style1

{

width: 100%;

}

</style>

</head>

<body>

<form id="form1" runat="server">

<div>

 

<table class="style1">

<tr>

<td valign="top">

<table class="style1">

<tr>

<td>

<asp:Label ID="Label1" runat="server" Text="Empno:"></asp:Label>

</td>

<td>

<asp:TextBox ID="txtEmpno" runat="server"></asp:TextBox>

<asp:Button ID="btnSearch" runat="server" Text="-&gt;" />

</td>

</tr>

<tr>

<td>

<asp:Label ID="Label2" runat="server" Text="Ename:"></asp:Label>

</td>

<td>

<asp:TextBox ID="txtEname" runat="server"></asp:TextBox>

</td>

</tr>

<tr>

<td>

<asp:Label ID="Label3" runat="server" Text="Sal:"></asp:Label>

</td>

<td>

<asp:TextBox ID="txtSal" runat="server"></asp:TextBox>

</td>

</tr>

<tr>

<td>

<asp:Label ID="Label4" runat="server" Text="Deptno:"></asp:Label>

</td>

<td>

<asp:TextBox ID="txtDeptno" runat="server"></asp:TextBox>

</td>

</tr>

</table>

<asp:Button ID="btnInsert" runat="server" Text="Add New" />

<asp:Button ID="btnUpdate" runat="server" Text="Update" />

<asp:Button ID="btnDelete" runat="server" Text="Delete" /><br />

<asp:Label ID="lblMsg" runat="server" ForeColor="Maroon"></asp:Label>

</td>

<td valign="top">

 

<asp:Button ID="btnSelectStar" runat="server" Text="SELECT *" />

<asp:GridView ID="GridView1" runat="server">

</asp:GridView>

 

</td>

</tr>

</table>

 

</div>

</form>

</body>

</html>


CRUD operations using the LINQ to SQL application (without LINQ designer): Source in VB.NET

Once the UI is designed as discussed in the previous section, we need to code for the button clicks (in code-behind) for all add, update, delete, search and refresh buttons.

The following is the source code in VB.NET 2008:


Imports System.Data.Linq


Partial Public Class CRUDSample

Inherits System.Web.UI.Page


Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

Me.lblMsg.Text = ""

End Sub


Protected Sub btnSelectStar_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnSelectStar.Click

Dim db As New DataContext(New System.Data.SqlClient.SqlConnection("data source=.sql2k5;initial catalog=Sample;user id=sa;Password=eXpress2005"))

Dim tblEmps As Table(Of Emp) = db.GetTable(Of Emp)()

Me.GridView1.DataSource = tblEmps

Me.GridView1.DataBind()

End Sub


Protected Sub btnSearch_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnSearch.Click

Try

'getting table

Dim db As New DataContext(New System.Data.SqlClient.SqlConnection("data source=.sql2k5;initial catalog=Sample;user id=sa;Password=eXpress2005"))

Dim tblEmps As Table(Of Emp) = db.GetTable(Of Emp)()

'getting an exiting row

Dim objEmp As Emp = tblEmps.SingleOrDefault(Function(p) p.empno = Me.txtEmpno.Text)

If objEmp IsNot Nothing Then

Me.txtEname.Text = objEmp.ename

Me.txtSal.Text = objEmp.Sal

Me.txtDeptno.Text = objEmp.deptno

Else

Me.lblMsg.Text = "Employee not found"

End If

Catch ex As Exception

Me.lblMsg.Text = ex.Message

End Try

End Sub


Protected Sub btnInsert_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnInsert.Click

Try

'getting table

Dim db As New DataContext(New System.Data.SqlClient.SqlConnection("data source=.sql2k5;initial catalog=Sample;user id=sa;Password=eXpress2005"))

Dim tblEmps As Table(Of Emp) = db.GetTable(Of Emp)()

'adding a new row

Dim rEmp As Emp = New Emp With {.empno = Me.txtEmpno.Text, .ename = Me.txtEname.Text, .Sal = Me.txtSal.Text, .deptno = Me.txtDeptno.Text}

tblEmps.InsertOnSubmit(rEmp)

'saving rows added

db.SubmitChanges()

Me.lblMsg.Text = "Added Successfully"

Catch ex As Exception

Me.lblMsg.Text = ex.Message

End Try

End Sub


Private Sub btnUpdate_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnUpdate.Click

Try

'getting table

Dim db As New DataContext(New System.Data.SqlClient.SqlConnection("data source=.sql2k5;initial catalog=Sample;user id=sa;Password=eXpress2005"))

Dim tblEmps As Table(Of Emp) = db.GetTable(Of Emp)()

'getting an exiting row

Dim objEmp As Emp = tblEmps.SingleOrDefault(Function(p) p.empno = Me.txtEmpno.Text)

If objEmp IsNot Nothing Then

'modifying the row

objEmp.ename = Me.txtEname.Text

objEmp.Sal = Me.txtSal.Text

objEmp.deptno = Me.txtDeptno.Text

'saving rows added

db.SubmitChanges()

Me.lblMsg.Text = "Updated Successfully"

Else

Me.lblMsg.Text = "Employee not found"

End If

Catch ex As Exception

Me.lblMsg.Text = ex.Message

End Try

End Sub


Private Sub btnDelete_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnDelete.Click

Try

'getting table

Dim db As New DataContext(New System.Data.SqlClient.SqlConnection("data source=.sql2k5;initial catalog=Sample;user id=sa;Password=eXpress2005"))

Dim tblEmps As Table(Of Emp) = db.GetTable(Of Emp)()

'getting an exiting row

Dim objEmp As Emp = tblEmps.SingleOrDefault(Function(p) p.empno = Me.txtEmpno.Text)

If objEmp IsNot Nothing Then

'delete the row

tblEmps.DeleteOnSubmit(objEmp)

'saving rows deleted

db.SubmitChanges()

Me.lblMsg.Text = "Deleted Successfully"

Else

Me.lblMsg.Text = "Employee not found"

End If

Catch ex As Exception

Me.lblMsg.Text = ex.Message

End Try

End Sub

End Class


Once executed, it should give the output as follows (Fig 06):


CRUD operations using the LINQ to SQL application (without LINQ designer): Source in C#


The following is the source code in C# 2008 for the UI designed in the previous sections:


using System.Data.Linq;


namespace SampleCS

{

public partial class CRUDSample : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

this.lblMsg.Text = string.Empty;

}


protected void btnSearch_Click(object sender, EventArgs e)

{

try

{

DataContext db = new DataContext(new System.Data.SqlClient.SqlConnection("data source=.sql2k5;initial catalog=Sample;user id=sa;Password=eXpress2005"));

//getting table

Table<Emp> tblEmps = db.GetTable<Emp>();

//getting an existing row

Emp objEmp = tblEmps.SingleOrDefault(p => p.empno == Int32.Parse( this.txtEmpno.Text ));

if (objEmp!=null )

{

this.txtEname.Text = objEmp.ename;

this.txtSal.Text = Convert.ToString( objEmp.sal) ;

this.txtDeptno.Text = Convert.ToString(objEmp.deptno) ;

}

else

this.lblMsg.Text = "Employee not found";

}

catch (Exception ex)

{

this.lblMsg.Text = ex.Message;

}

}


protected void btnSelectStar_Click(object sender, EventArgs e)

{

DataContext db = new DataContext(new System.Data.SqlClient.SqlConnection("data source=.sql2k5;initial catalog=Sample;user id=sa;Password=eXpress2005"));

Table<Emp> tblEmps = db.GetTable<Emp>();

this.GridView1.DataSource = tblEmps;

this.GridView1.DataBind();

}


protected void btnInsert_Click(object sender, EventArgs e)

{

try

{

DataContext db = new DataContext(new System.Data.SqlClient.SqlConnection("data source=.sql2k5;initial catalog=Sample;user id=sa;Password=eXpress2005"));

//getting table

Table<Emp> tblEmps = db.GetTable<Emp>();

//adding a new row

Emp rEmp = new Emp {empno = Int32.Parse(this.txtEmpno.Text), ename = this.txtEname.Text, sal = Double.Parse(this.txtSal.Text), deptno = Int32.Parse(this.txtDeptno.Text)};

tblEmps.InsertOnSubmit(rEmp);

//saving rows added

db.SubmitChanges();

this.lblMsg.Text = "Added Successfully";

}

catch (Exception ex)

{

this.lblMsg.Text = ex.Message;

}

}


protected void btnUpdate_Click(object sender, EventArgs e)

{

try

{

DataContext db = new DataContext(new System.Data.SqlClient.SqlConnection("data source=.sql2k5;initial catalog=Sample;user id=sa;Password=eXpress2005"));

//getting table

Table<Emp> tblEmps = db.GetTable<Emp>();

//getting an existing row

Emp objEmp = tblEmps.SingleOrDefault(p => p.empno == Int32.Parse(this.txtEmpno.Text));

if (objEmp != null)

{

//modifying the row

objEmp.ename=this.txtEname.Text ;

objEmp.sal = Double.Parse(this.txtSal.Text);

objEmp.deptno = Int32.Parse(this.txtDeptno.Text);

//saving rows modified

db.SubmitChanges();

this.lblMsg.Text = "Updated Successfully";

}

else

this.lblMsg.Text = "Employee not found";

}

catch (Exception ex)

{

this.lblMsg.Text = ex.Message;

}

}


protected void btnDelete_Click(object sender, EventArgs e)

{

try

{

DataContext db = new DataContext(new System.Data.SqlClient.SqlConnection("data source=.sql2k5;initial catalog=Sample;user id=sa;Password=eXpress2005"));

//getting table

Table<Emp> tblEmps = db.GetTable<Emp>();

//getting an existing row

Emp objEmp = tblEmps.SingleOrDefault(p => p.empno == Int32.Parse(this.txtEmpno.Text));

if (objEmp != null)

{

//delete the row

tblEmps.DeleteOnSubmit(objEmp);

//saving rows modified

db.SubmitChanges();

this.lblMsg.Text = "Deleted Successfully";

}

else

this.lblMsg.Text = "Employee not found";

}

catch (Exception ex)

{

this.lblMsg.Text = ex.Message;

}

}

}

}

My upcoming articles will focus further in-depth on "LINQ to SQL" (including designers). I hope you enjoyed this article and any suggestions, bugs, errors, enhancements etc. are highly appreciated at http://jagchat.spaces.live.com

blog comments powered by Disqus
.NET ARTICLES

- .Net 4.5 Brings Changes
- Understanding Events in VB.NET
- Objects, Properties, Events and Methods in V...
- Install Visual Web Developer Express 2010
- Microsoft Gadgeteer an Open Source Alternati...
- Best DotNetNuke Modules
- Facebook Image Viewer in Visual Basic
- Murach`s ADO.NET 4 Database Programming with...
- 5 Must Have Visual Studio 2010 Extensions
- Dynamic Web Applications with ASP.NET Mono u...
- PDFSharp: HTML to PDF in ASP.NET 3.5 using V...
- Using the PDFSharp Library in ASP.NET 3.5 wi...
- Sending Email in ASP.NET 3.5 using VB.NET wi...
- ASP.NET 3.5 Role Based Security and User Aut...
- Creating ASP.NET Login Web Pages and Basic C...

ASP Web Hosting ASP.Net Web Hosting Windows Web Hosting
 
 
 
ASP Free Forums 
 RSS  Tutorials RSS
 RSS  Forums RSS
 RSS  All Feeds
.Net RAD
Contact Us
Free Virtualization Tools
Site Map 
Request Media Kit
Write For Us Get Paid 
Weekly Newsletter
 
Developer Updates  
Free Website Content 
Privacy Policy 
Support 


© 2003-2012 by Developer Shed. All rights reserved. DS Cluster 3 - Follow our Sitemap
Most Popular Topics
All ASP.Net Tutorials