.Net Layered design (Manually) using Stored Procdure

.Net Layered design
ADO.net Programming article which focus on both how to write efficient code and how to represent the information which take advantage of layered design of .net framework. Articles describes how you can write code efficiently and improve performance of application and avoids errors due to poor coding style.

Target Audience

Programmers having basic ADO.net Knowledge and who are interested to write efficient industry based ADO.net programming.

Advantage / Why use of Layered Design???
This article focus on how to efficiently write database programming so that we can take following advantage and prepared a layered code which is followed by industry standards.

i) Opening of connection for a short time and Closing it as soon as database task is completed, it thus help in improving performance of application.

ii) Better usage of Error-Handling, to make sure that connection is closed even if the exception occurs.

iii) Follow Stateless design, taking all needed information as parameter and return all the data through return value. It helps in load balancing and it would be easy while implementing web-service.

iv) Storing connection string in app.config file allow us to made a single change, to change a connection string. It helps in connection pooling.

Preparing Code

Now, that you ready to learn how to write ADO.net Programming efficiently lets execute the preliminary steps to make code ready for your demo.

Open DemoCode Application
Change Server Name in “app.Config” File if your database is located on Remote Server. Also modifies connection string if it requires user id and password.

Open and Login to MS SQL SERVER, open query analyzer and execute following stored procedure

Select “NORTHWIND” Database

1) Adding Employee Record
create proc procInsertEmployee
@EmployeeId int OUTPUT,
@FirstName varchar(20),
@LastName varchar(20)
Insert into employees
set @EmployeeId = @@Identity

2) Updating Emplyee Record
create proc procUpdateEmployee
@EmployeeId int,
@FirstName varchar(20),
@LastName varchar(20)
Update Employees
FirstName = @FirstName,
LastName = @LastName
where EmployeeId = @EmployeeId

3) Delete Employee Record
create proc procDeleteEmployee
@EmployeeId int
Delete from Employees
where EmployeeId = @EmployeeId

4) Get Specific Employee
create proc procGetEmployeeRec
@EmployeeId int
select EmployeeId,FirstName,LastName
from employees
where EmployeeId = @EmployeeId

5) Get All Employees
Create proc procGetAllEmployee
select EmployeeId,FirstName,LastName
from employees

How it works!!!
ADO.net Programming Style consist of Layered design which describes as bellow:

1. User Interface Layer

User Interface consist of Forms which are been designed to interact with User.

Screen Shot

2. Stub for Accessing Database Layer

Stub for accessing database layer is been prepared by creating classes which contains a specific details related to access data and task such as adding, editing and deleting functionality..

Note: Connection string is retrieved from section of the app.config file, rather than hard-coded. This is beneficial for connection pooling, which ultimately improves performance of application.

Stub consist of two class files:

1) A data details class and

2) A database utility class.

1) Data Details Class: It consist of details of record which is manipulated.

public class EmpDetails
/* Declaring Property for each field. */
private int employeeId;
public int EmployeeId
get{return employeeId;}
set{employeeId = value;}

private string firstName;
public string FirstName
get{return firstName;}
set{firstName = value;}

private string lastName;
public string LastName
get{return lastName;}
set{lastName = value;}

/* Constructor definition */
public EmpDetails(int employeeId,
string firstName,string lastName)
this.employeeId = employeeId;
this.firstName = firstName;
this.lastName = lastName;

2) Database Utility Class: It consist of different data manipulation operation (such as add, delete, update, select) which is performed to the actual database.

public class EmpDB


string connStr;

/* Retrieving Connection String in Constructor*/
public EmpDB()

connStr = ConfigurationSettings.AppSettings[“connStr”];


/* Insertion of employee details*/
public int InsertEmployee(EmpDetails emp)

// Opening Connection
SqlConnection conn = new SqlConnection(connStr);

// Creating Command

SqlCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;

cmd.CommandText = “procInsertEmployee”;

// Adding required parameter
cmd.Parameters.Add(new SqlParameter(“@EmployeeId”,SqlDbType.Int));
cmd.Parameters[“@EmployeeId”].Direction = ParameterDirection.Output;

cmd.Parameters.Add(new SqlParameter(“@FirstName”,SqlDbType.NVarChar,20));

cmd.Parameters[“@FirstName”].Value = emp.FirstName;

cmd.Parameters.Add(new SqlParameter(“@LastName”,SqlDbType.NVarChar,20));

cmd.Parameters[“@LastName”].Value = emp.LastName;




// Executing Command and retrieving result.

int RecordsAffected = cmd.ExecuteNonQuery();
int EmpId;

if(RecordsAffected != 0)

EmpId = (int)cmd.Parameters[“@EmployeeId”].Value;


EmpId = -1;

return EmpId;


catch(SqlException ex)


throw new ApplicationException(“Data Error”);




// Ensuring that Connection is closed even if exception occurs.


// Similarly Perform different database opperation
// Check out “EmpDB.cs” for demo code.
public int UpdateEmployee(EmpDetails emp)

public int DeleteEmployee(int EmployeeId)


public EmpDetails GetEmployee(int EmployeeId)


public EmpDetails[] GetAllEmployee()



3. Database Layer
Database layer is server where MS SQL Server is installed, or where the database is actually stored. This code design is independent of database layer.
Thus by end of this article, you have understand how to write efficient code and how to represent the information which take advantage of layered design of .net framework. You must also now aware about how you can write code efficiently and improve performance of application and avoids errors due to poor coding style.

Hope to hear your useful comments so that together we can make programming easy and efficient.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: