This tutorial was created with Visual Studio .NET 2008, but can be recreated in 2005, after downloading and installing Microsoft's LINQ Community Technology Preview release, which can be downloaded from here.
Need help with Windows Dedicated Hosting? Try Server Intellect. I'm a happy customer!
Visual Studio.NET 2008 makes it very easy for us to create LINQ to SQL Entities using the Object Relational Designer. What it does is creates classes and methods that relate to the database columns and tables. This makes it possible for us to communicate with the data using LINQ (Language Integrated Query).
This tutorial will show how we can bypass the Designer and write the class ourselves, so that we get a better understanding of what's going on. For this example, we will be using a SQL database with one table and three columns - id, name, and city.
Once we have our database set up, we will create a new class to represent the database table structure. It should look something like this:
Imports System
Imports System.Data.Linq.Mapping
<Table(name:="tblPeople")> _
Public Class people
Private _Id As Integer
Private _name As String
Private _city As String
<Column(IsPrimaryKey:=True, IsDbGenerated:=True)> _
Public Property Id() As Integer
Get
Return _Id
End Get
Set(ByVal value As Integer)
_Id = value
End Set
End Property
<Column(CanBeNull:=True)> _
Public Property name() As String
Get
Return _name
End Get
Set(ByVal value As String)
_name = value
End Set
End Property
<Column(CanBeNull:=True)> _
Public Property city() As String
Get
Return _city
End Get
Set(ByVal value As String)
_city = value
End Set
End Property |
It is advised to always include the table name in the class, although it is not really required if the class is named the same as the table in the SQL database. You should always declare the Primary Key, especially if you are planning on making changes to the database. IsDbGenerated is also used where the database will auto-generate the values upon insert.
In the class, we need to define a [Column] for each in the database table, and then the name of the column should be represented by the public string (or int, etc.)
I just signed up at Server Intellect and couldn't be more pleased with my Windows Server! Check it out and see for yourself.
Next, we are going to display the data with a GridView, and we will also add a textbox and button to the page to allow searching of the database.
Our ASPX page will look something like this:
<form id="form1" runat="server">
Name: <asp:TextBox ID="txtName" runat="server" /><br />
<asp:Button ID="butSearch" runat="server" Text="Search"
onclick="butSearch_Click" /><br />
<br />
<asp:GridView ID="GridView1" runat="server" /> |
We are also going to need a connection string. We have the following in our Web.config file:
| <connectionStrings>
<add name="ConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"/>
</connectionStrings> |
Server Intellect offers Windows Hosting Dedicated Servers at affordable prices. I'm very pleased!
Now we have our database set up, and our class representing the database, we can add to the code-behind to interact with our database. We will use the textbox to allow the user to search for matches in the name column of the database, and the code will go under the button click event:
Dim con As String = WebConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString
Dim db As New DataContext(con)
Dim tPerson As Table(Of people)
tPerson = db.GetTable(Of people)()
GridView1.DataSource = tPerson.Where(Function(p) p.name.Contains(txtName.Text))
GridView1.DataBind() |
The above code is using a Lambda Expression, which can be a lot shorter than using a regular LINQ Query. We are simply selecting the records that match our request, and then binding the gridview with the data returned from this query. Note that GetTable<> references the class name; not the table name (if different).
When we run this web application, we are greeted with just the textbox and the button. If we leave the textbox blank and hit the button, we should be shown all the records in the database. If we enter some text, we will be shown matches from the database.
The entire code-behind looks something like this:
Imports System.Web.Configuration
Imports System.Linq
Imports System.Data.Linq
Partial Class _Default
Inherits System.Web.UI.Page
Protected Sub butSearch_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles butSearch.Click
Dim con As String = WebConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString
Dim db As New DataContext(con)
Dim tPerson As Table(Of people)
tPerson = db.GetTable(Of people)()
GridView1.DataSource = tPerson.Where(Function(p) p.name.Contains(txtName.Text))
GridView1.DataBind()
End Sub |
Try Server Intellect for Windows Server Hosting. Quality and Quantity!
