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.
Try Server Intellect for Windows Server Hosting. Quality and Quantity!
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:
using System;
using System.Data.Linq.Mapping;
[Table(Name="tblPeople")]
public class people
{
[Column(IsPrimaryKey=true, IsDbGenerated=true)]
public int Id { get; set; }
[Column(CanBeNull=true)]
public string name { get; set; }
[Column(CanBeNull=true)]
public string city { get; set; }
public people()
{
} |
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" /> |
Server Intellect assists companies of all sizes with their hosting needs by offering fully configured server solutions coupled with proactive server management services. Server Intellect specializes in providing complete internet-ready server solutions backed by their expert 24/365 proactive support team.
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:
string con = WebConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
DataContext db = new DataContext(con);
var tPerson = db.GetTable<people>();
GridView1.DataSource = tPerson.Where( 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.
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!
The entire code-behind looks something like this:
using System;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Data.Linq;
using System.Web.Configuration;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void butSearch_Click(object sender, EventArgs e)
{
string con = WebConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
DataContext db = new DataContext(con);
var tPerson = db.GetTable<people>();
GridView1.DataSource = tPerson.Where( p => p.name.Contains(txtName.Text) );
GridView1.DataBind();
} |
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.
