This tutorial will show you how to create LINQ to SQL Entities by hand, instead of using the Object Relational Designer in Visual Studio. C# version.
Looking for the VB.NET 2005 Version? Click Here!
Looking for more .NET Database Tutorials? Click Here!
We chose Server Intellect for its dedicated servers, for our web hosting. They have managed to handle virtually everything for us, from start to finish. And their customer service is stellar.
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:
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" /> |
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.
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(); } |
Yes, it is possible to find a good web host. Sometimes it takes a while. After trying several, we went with Server Intellect and have been very happy. They are the most professional, customer service friendly and technically knowledgeable host we've found so far.
Looking for the VB.NET 2005 Version? Click Here!
Looking for more .NET Database Tutorials? Click Here!
|