This article assumes the developer knows how to create a web site and reference the projects using Visual Studio 2008. This article focuses on the simple display of data in a GridView control in an ASP.NET page using LinqToSql class and by populating the data running a stored procedure.
Open Visual Studio 2008 and create a web application project. Now go to the Server Explorer and add a new database or open an existing database. I am using a new database for testing purposes.
I name my new database "MyTestDB". Note that you need to provide the credentials for the SQL Server you are connecting to.
Now add a new table called Students by expanding the tree in the Server Explorer and right-click on the tables and select "Add New Table".
Now define the columns to be added to the table. For simplicity I have named the table "Students" and added the two columns StudentID and StudentName. Also I have specified StudentID is a primary key column and set the Identity property to seed by 1.
Now save the table with the name "Students". Right-click on the table Student and select "Show Table Data". Add data to the table.
Note that the StudentID is a primary key with Identity set with seed 1, you do not need to enter the value for this column. Just enter the data for the StudentName and then click the "!" icon in the right on the top tool bar.
This saves your data. The next step is to write a stored procedure to get the data. Expand the tree in the Server Explorer and right-click on the "Stored Procedures" and select the option "Add New Stored Procedure" and you are shown a stored procedure with basic syntax as below:
CREATE PROCEDURE dbo.StoredProcedure2
/*
(
@parameter1 int = 5,
@parameter2 datatype OUTPUT
)
*/
AS
/* SET NOCOUNT ON */
RETURN
Now you need to modify the stored procedure to populate the Students data as below:
ALTER PROCEDURE dbo.GetStudents
AS
select * from dbo.student
Now click the "Save" button on the top to save the stored procedure.
We are done with the database stuff needed for this example. Let us create a LinqToSQL class by adding a new item to the web application project.
I named my DBML file as "MyTestDB.DBML" and now drag and drop the Table and Stored procedure created in the Server Explorer.
Now we are done with adding table and stored procedure. This makes the Visual Studio generate code for you to access the table and stored procedures in your program. You can check the code generated by opening the MyTestDB.designer.cs file which is available under MyTestDB.dbml file.
Code for stored procedure:
[Function(Name="dbo.GetStudents")]
public ISingleResult<GetStudentsResult> GetStudents()
{
IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())));
return ((ISingleResult<GetStudentsResult>)(result.ReturnValue));
}
Now we need to add a GridView in the default.aspx page and add the following code in the code behind file:
ASPX file
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="LinqGridView._Default" %>
<!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 runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="gvStudents" runat="server"></asp:GridView>
</div>
</form>
</body>
</html>
Code behind
using System;
using System.Collections;
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.Xml.Linq;
namespace LinqGridView
{
public partial class _Default : System.Web.UI.Page
{
MyTestDBDataContext ctxt = new MyTestDBDataContext();
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
gvStudents.DataSource = ctxt.GetStudents().ToList();
gvStudents.DataBind();
}
}
}
}
That's it; you are done with the coding and now run the application. You should see the data populated as below:
| StudentID | StudentName |
| 1 | John |
| 2 | Mary |
| 3 | Rahul |
| 4 | Sanjay |
| 5 | Priyanka |
| 6 | Modi |
If you want to query a particular student by the stored procedure then you can write another stored procedure and drag and drop the stored procedure onto the DBML file and you can use the stored procedure to get the data.
I have added a new stored procedure:
ALTER PROCEDURE dbo.GetStudentByID
@studentId int
AS
select * from dbo.student where StudentID = @studentId
Now, I have modified my aspx to include a label to display the student name and also the code behind file.
<form id="form1" runat="server">
<div>
<asp:GridView ID="gvStudents" runat="server"></asp:GridView>
<br />
<asp:Label ID="StudentName" runat="server"></asp:Label>
</div>
</form>
if (!IsPostBack)
{
gvStudents.DataSource = ctxt.GetStudents().ToList();
gvStudents.DataBind();
StudentName.Text = ctxt.GetStudentByID(2).ToList()[0].StudentName;
}
Now my output is as below:
| StudentID | StudentName |
| 1 | John |
| 2 | Mary |
| 3 | Rahul |
| 4 | Sanjay |
| 5 | Priyanka |
| 6 | Modi |