This tutorial will show you how to create LINQ to SQL Entities by hand, instead of using the Object Relational Designer in Visual Studio. VB version.
If you're looking for a really good web host, try Server Intellect - we found the setup procedure and control panel, very easy to adapt to and their IT team is awesome!
Looking for the C# 2005 Version? Click Here!
Looking for more .NET Database Tutorials? Click Here!
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 ourself, 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. The first thing we want to do is right-click our project in the Solution Explorer, and then Add Reference. We want to add the System.Data.Linq DLL under the .NET tab.
Once we have our database set up and added the reference to our project, 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.)
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 used over 10 web hosting companies before we found Server Intellect. Their dedicated servers and add-ons were setup swiftly, in less than 24 hours. We were able to confirm our order over the phone. They respond to our inquiries within an hour. Server Intellect's customer support and assistance are the best we've ever experienced.
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> |
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 |
Looking for the C# 2005 Version? Click Here!
Looking for more .NET Database Tutorials? Click Here!
If you're ever in the market for some great Windows web hosting, try Server Intellect. We have been very pleased with their services and most importantly, technical support.
|