Binding A Custom Entity Class to stored procedure using Linq-To-SQL

No.of Views5325
Bookmarked0 times
Downloads 
Votes0
By  jalpesh   On  05 Aug 2010 11:08:59
Tag : LINQ , T-SQL
I have already written several article about Linq its a great ORM that we can use in various way. The purpose of this post to demonstrate How we can bind custom entity to stored procedure result with use of Linq-To-SQL.
emailbookmarkadd commentsprint

Images in this article missing? We recently lost them in a site migration. We're working to restore these as you read this. Should you need an image in an emergency, please contact us at info@codegain.com

 

Introduction

I have already written several post about Linq its a great ORM that we can use in various way. The purpose of this post to demonstrate How we can bind custom entity to stored procedure result with use of Linq-To-SQL. Let’s go through it and you will realize that how easy it will be to bind a Custom Entity to Stored Procedure result.

You can find my articles here

Let’s first create a simple table to which will hold the user data. It will contain four field like UserId,UserName,FirstName and LastName like following. 

Image Loading

Now let’s insert some data into the table like following.

Image Loading

Now let’s create a stored procedure which will return the table data and a new field called Full Name like following. Here full name is a combination of first name and last name

CREATE PROCEDURE dbo.GetAllUsers
 
ASSET NOCOUNT ONSELECTUserId,UserName,FirstName,LastName,FirstName + ' ' + LastName AS [FullName]FROM dbo.Users

 After creating a stored procedure it time to create a Linq-To-SQL Right Click Project->Add New Item and Go To->Data and Add LINQ to SQL Classes called MyBlogDataContext.dbml.After creating datacontext class for Linq just drag above store procedure to Linq-To-SQL classes and it will create a function like following. 

Image Loading

Now let’s add a New Entity Class called UserInfo into Linq-To-SQL DataContext via Right Click Add New Class Just like following. 

Image Loading

After adding class I have added same property as its having in user table and Hence our UserInfo Class will look like following. 

Image Loading

Now everything is ready Custom Entity Class called UserInfo and we have Our Function ready which will return Stored Procedure output. Here Linq-To-SQL Provides a property called ReturnType If you select function which we have created via dragging a stored procedure in data context class. We just need to select our UserInfo class there just like following and it will bind the stored procedure with that particular UserInfo class. here only condition should be satisfied that Our Custom Entity class should contain all the field with compatible .NET Data types which will return the data. Below is the property which we are talking about. 

Image Loading

Now let’s add grid view to default.aspx page like following and Let’s bind output of stored procedure to that grid view.

<asp:GridView ID="grdUserList" runat="server"></asp:GridView> 

 After placing the Grid View in page here is the code for biding grid view in default.aspx page_load event.

protected void Page_Load(object sender, EventArgs e)
{if (!Page.IsPostBack)
  {using (MyBlogDataContextDataContext myContext =new MyBlogDataContextDataContext())
    {
         List<UserInfo> MyUserList =
    myContext.GetAllUsers().ToList<UserInfo>();
         grdUserList.DataSource = MyUserList;
         grdUserList.DataBind(); 
     }
  }
}

And here is the output which we get in browser after running our web application. 

Image Loading

That’s it its very easy.. Hope this will help you.

 
Sign Up to vote for this article
 
About Author
 
jalpesh
Occupation-Software Engineer
Company-DotNetJaps
Member Type-Gold
Location-India
Joined date-08 May 2010
Home Page-http://www.dotnetjalps.com
Blog Page-http://www.dotnetjalps.com
I am jalpesh vadgamaa an Microsoft MVP for Visual C# and BrainBench Certified ASP.NET Developer having experience of five year in Microsoft .NET Technology.I am working as Project Leader in Mid Size company.My work area comprises of Enterprise Level projects using ASP.NET and other Microsoft .NET Technologies.Please feel free to contact me for any queries via posting comments on my blog I will try to reply as early as possible.
 
 
Other popularSectionarticles
    In this article, I am going to show how you can filter your data by RowNumbers that you assigned to your records.
    Published Date : 03/Feb/2011
    SelectMany is Projects each element of a sequence to an IEnumerable and flattens the resulting sequences into one sequence.In this post I am going to show how you can use SelectMany Extension method to achieve the join between related tables easily without writing long queries.
    Published Date : 18/Jan/2011
    Now a days most of the developers are moving towards new LINQ to SQL they find difficult to write down SQL query in C# to query data using LINQ. LINQ is a query language which get integrated in C# to query data form ObjectCollects, SQL, XML etc.
    Published Date : 08/Jan/2011
    Today one of my friend asked me about simple insert,update and delete example with LINQ-To-SQL but at that time i was not having any simple example which will show the power of LINQ-To-SQL Example
    Published Date : 17/May/2010
    After two decades, the industry has reached a stable point in the evolution of object-oriented (OO) programming technologies
    Published Date : 12/May/2010
Comments
By:raniDate Of Posted:5/16/2011 6:05:42 AM
got many problems cant explain in dis small blog
regarding asp.net and sql server.
By:raniDate Of Posted:5/16/2011 6:03:48 AM
hello Jalpesh sir
sir, if possible can i have ur mail id so that i can mail u explain my problem......reply on my mail
By:Jalpesh VadgamaDate Of Posted:12/28/2010 4:47:09 AM
Jalpesh
You can create partial class then use that property. For example create partial class userInfo with same name and use property there. In that way you will not modify the code generated by entity framework and also at compile time it will be a same class
By:munDate Of Posted:12/27/2010 11:14:42 PM
query to the above
can we add new columns(which won't have any value as per now but will be manually selected or entered when the asp.net page is run) to the already created grid as shown above. If yes please let me know how. Thanks in advance!!
Leave a Reply
Title:
Display Name:
Email:
(not display in page for the security purphase)
Website:
Message:
Please refresh your screen using Ctrl+F5
If you can't read this number refresh your screen
Please input the anti-spam code that you can read in the image.
^ Scroll to Top