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

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.

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.

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

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

AS
SET NOCOUNT ON
SELECT
UserId,
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.

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

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

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.

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.

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

Technorati Tags: Linq-To-Sql,Stored Procedure,SQLServer

Continue Reading

Implementing repository pattern with the ado.net entity model

There are lots of ways to implement repository pattern but with ado.net entity model you can create repository pattern within 10 to 15 minutes.

If you are creating asp.net mvc application then just right click->Model folder->Add->Net Item->select ado.net entity data model.



From database explorer right click and drag your tables to your database explorer. It will create a entity class. For example i have created a notes table for my sample and it will create a notes entity class in my ado.net entity data model.



After creating the entity class not its time to create a interface for repository pattern which will contain my all the operations related to notes entity.

Following is the interface which we will implement use for repository.

namespace DotNetJapsMVC.Models.Respository.Interface
{
interface INotesRepository
{
void CreateNotes(Notes notesToCreate);
void EditNotes(Notes notesToEdit);
void DeleteNotes(Notes notesToDelete);
Notes GetNotes(Guid noteId);
IEnumerable ListNotes();
}
}

Now we will create a class that will implement this repository interface. Here is the coding for that.


using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace DotNetJapsMVC.Models.Respository.Class
{
public class NotesRepository:Interface.INotesRepository
{
private DotNetJapsEntities _myEntities = new DotNetJapsEntities();

public void CreateNotes(Notes notesToCreate)
{
_myEntities.AddToNotes(notesToCreate);
_myEntities.SaveChanges();
}

public void EditNotes(Notes notesToEdit)
{
var originalNotes = (from n in _myEntities.Notes
where n.NoteId == notesToEdit.NoteId
select n).FirstOrDefault();

_myEntities.ApplyPropertyChanges(originalNotes.EntityKey.EntitySetName, notesToEdit);
_myEntities.SaveChanges();

}

public void DeleteNotes(Notes notesToDelete)
{
var originalNotes = GetNotes(notesToDelete.NoteId);
_myEntities.DeleteObject(originalNotes );
_myEntities.SaveChanges();
}

public Notes GetNotes(Guid noteId)
{
return (from n in _myEntities.Notes
where n.NoteId == noteId
select n ).FirstOrDefault();
}

public IEnumerable ListNotes()
{
return _myEntities.Notes.ToList();
}

}
}

That's it we have created the repository classes and repository interface. You can use as following in your class or pages.

private  INotesRepository _repository;

public List LoadNotes()
{
 return _repository.ListNotes();
}

Advantage Of Repository Pattern:

1. Make application loosely coupled so if you want to change the something then you don't need to change everything from scratch.
2. With the repository pattern we can have nice abstraction which will separate our business logic as well as database logic.
3. You can prevent dependency injection through the repository pattern.

Happy Programming

Continue Reading

Which .NET Object Relational Mapper is fastest? In .NET Nhibernate,Linq 2 SQL,Entity Framework Or SubSonic, NHibernate

There are lots of ORM(Object Relational Mapper) tools are available for the Microsoft.NET like linq 2 sql, ado.net entity framework, nhibernate, subsonic and so many others. Developer often confuses which technlogy he should choose but i think a developer should deternmine his requiremnt first then he has check the pros and cons of every tool there .

I have used ado.net entity framework for my application as i like it most and its easy to use.
Following are some good article which will help you to choose right orm tool for your applications.

http://ayende.com/Blog/archive/2007/06/03/On-SubSonic-amp-NHibernate.aspx

http://blog.wekeroad.com/blog/aspnet-mvc-choosing-your-data-access-method/

http://dotnet.netindonesia.net/?0::41743

http://geekswithblogs.net/diadiora/archive/2009/01/12/best-.net-orm-tool.aspx

http://madgeek.com/Articles/ORMapping/EN/mapping.htm

Happy Programming...

Continue Reading

Microsoft Enterprise library 4.0 CTP Release

For some days ago microsoft has release Microsoft enterprise library 4.0 CTP.

This release has been adapted to work with WMI version 2.0 and version 3.5 of the .NET Framework.

For more Details visit following links..

http://www.codeplex.com/Release/ProjectReleases.aspx?ProjectName=entlib&ReleaseId=12142

http://blogs.msdn.com/agile/archive/2008/03/31/enterprise-library-4-0-march-2008-ctp-released.aspx

Here are the system requiement :

• Microsoft Windows XP Professional, Windows Server 2003, Windows Server 2008, or Windows Vista operating system
• Microsoft .NET Framework 3.5 or higher.
• Microsoft Visual Studio 2008 development system (any of the following editions): Standard Edition, Professional Edition, Team Edition for Software Developers, Team Edition for Software Testers, Team Edition for Software Architects, or Team Suite

Continue Reading