Linq to SQL using Stored Procedures in nThier Design

After doing a lot of research, I realised that when using Linq to Sql to communicate with the database, it is quite impossible or too complicated to use datatables or datarows. In this example I will demonstrate an easy and quick way to retrieve a whole table from a database using stored procedures assuming we are using a three thier design.

Before we start, make sure you have a solution containing 3 Projects, The Presentation Layer, The Business Logic and the Data Access Layer. In our case they will be referred to as PresLayer, BLL and DAL. Each must be referenced; the PresLayer must reference the BLL, and the BLL must reference the DAL.

1.First we need a sample table in a database, which I named Users who will of course contain details regarding users such as Username, UserFirstName, etc.

Create a Connection Class in the DAL

Add a new Class, name it ConnectionClass.cs and make it public. Once created add the following namespace:

using System.Data.SqlClient;

In the main class method add the following code:

//setting up the connection string

SqlConnection _myConnection;

public SqlConnection MyConnection

{

get { return _myConnection; }

set { _myConnection = value; }

}
public static string ConnectionString

{

get { return @”Data Source=SERVER-NAME\SQLEXPRESS;Initial Catalog=DataBaseName; MultipleActiveResultSets=True; Integrated Security=True“; }

//The above connection string (In blue) must be replaced with your actual connection string.

}

public ConnectionManagement()        { _myConnection = new SqlConnection(ConnectionString);      }

 

***For Steps in: Creating a Database and Connection String, Click Here

2.Secondly we need to create a LINQ to SLQ class to Map Tables from the database, for steps Click Here

3. Now it is time to start creating our Classes in the DAL to access data directly from the database using stored procedures. Create a new Class and name ManageUser.cs, make it public and inherit from the interface DataContext as shown below. The Base will set a common connection for all methods in that class.

public class ManageUser: DataContext

{

public ManageUser()

: base(ConnectionClass.ConnectionString)

{                    }

}

4. Create new methods. For this sample, we will create a simple select from table which will return all users having a

given roleID.

Create New Stored Procedure:

CREATE PROCEDURE dbo.sp_GetUsersByRole

(  @RoleID int  )

AS

SELECT * FROM Users WHERE RoleID = @RoleID

RETURN

Simple Select From Table in the DAL (Data Access Layer)

public IEnumerable<sp_GetUsersByRole> GetUsersByRole(int RoleID)

{

try

{

ConnectionManagement cm = new ConnectionManagement();
//Checking is connection is open, if not, we need to open it.

//Checking if connection is open is optional but highly suggested to avoid multiple open connections.

bool connectionOpenedHere = false;

if (cm.MyConnection.State != ConnectionState.Closed)

{

connectionOpenedHere = false;

}

else

{

cm.MyConnection.Open();

connectionOpenedHere = true;

}

LearningSpaceDataContext db = new LearningSpaceDataContext();

//This is the actual method which is calling the stored procedure and passing the parameters.
var result = from u in db.sp_GetUsersByRole(RoleID) //Stored Procedure Name

select u;

//The below is Checking if connection was open here, than we close the connection.

if (connectionOpenedHere == true)

cm.MyConnection.Close();

//Returns the Table / List / Object
return result;

}

catch (Exception e)

{                throw e;            }

}

Calling Methods in DAL from the BLL (Business Layer)

5. In the BLL we need to create a new Class to contain all entities required. Lets create a new class and name it UserEntities.cs and make it public. The content should look like the below:

namespace BLL

{

public class UserEntity

{

public string Username { get; set; }

public string UserFirstName { get; set; }

public string UserLastName { get; set; }

public int RoleID { get; set; }

}

}

6. In order to Call classes and methods in the DAL from the BLL, we must create another class in the BLL and name it UserBLL.cs or as desired. One namespace must be added in order to access methods in DAL:

using DAL;

7. To keep in track with our scenario we will add the method to call the Method created in step 4, which will look like:

public object GetUsersByRole(int RoleID)

{

try

{

var courses = new ManageUser().GetUsersByRole(RoleID);
return courses;

}

catch (Exception e)            {                throw e;            }

}

The above code will return an object which can be places anywhere inside an ADO.NET component such as grid views and list views.

That’s all for coding… but if you’d like to view a sample of how you use these methods in the presentation layer read more here…

Using Methods in Presentation Layer

1. Create a new Content Page and insert the code below in Source Code:

<asp:GridView ID=”gridView_BindUsers” runat=”server”> </asp:GridView>

2. In the Code Behind add reference to the following namespaces:

using System.Data;

using BLL; //The Business Layer according to our scenario


3. In the Code Behind insert the code below in the PageLoad :

if (!IsPostBack)

{

gridView_BindUsers.DataSource = new ManageUsers().GetUsersByRole(2);

gridView_BindUsers.DataBind();

}

 

3. Build and Run

2 thoughts on “Linq to SQL using Stored Procedures in nThier Design

  1. I have Store SQL :
    alter proc [dbo].[BKGetInvoiceNew]
    (
    @Control int,
    @checkno nvarchar(20),
    @RVC nvarchar(10),
    @FormDay nvarchar(15),
    @ToDay nvarchar(15)
    )
    AS
    BEGIN
    declare @sql nvarchar(2000)

    set @sql =’SELECT CheckNo,TableID,TotalBase,SubTotal,TaxTotal,Discount,STaxTotal,CloseBy,NGuest,CloseTime,PONumber,PaymentMode,PaymentInfo,NChild,TableNo
    from SMILE_POS.dbo.Invoice where 1=1′

    if @checkno ”
    set @sql = @sql + ‘and Checkno =’+@checkno+”

    if @RVC ”
    set @sql =@sql + ‘ and RVC =’+@RVC+”

    if @FormDay ” and @ToDay”
    set @sql = @sql+’ Convert(DateTime,Cast(IMonth as varchar) + ”/” + Cast(IDay as varchar) + ”/” + Cast(IYear as varchar),101) between ”’ + cast(@FormDay as varchar) +”’ And ”’ + cast(@ToDay as varchar)+ ”
    –PRINT @sql

    EXECUTE (@sql)

    end

    I can not call this store SQL in winform, can you help me call this store? Thanks

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s