This article assumes that developer knows how create a web
site and reference the projects using Visual Studio 2008. This article focuses
on simple display of data in a GridView control in 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 server explorer and add a new database or open existing
database. I am using a new database for testing purpose.
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 name as “Students” and also added two columns
StudentID, 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 name as “Students”. Right click on
the table Student and select “Show Table Data”. Add the 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 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 on “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 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
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 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 data populated as below:
StudentID
|
StudentName
|
1
|
John
|
2
|
Mary
|
3
|
Rahul
|
4
|
Sanjay
|
5
|
Priyanka
|
6
|
Modi
|
In case if you want to query a particular student by stored
procedure you can write another stored procedure and drag and drop the stored
procedure on to 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
|
Mary
No comments:
Post a Comment