ADO.net Programming Style

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 ADO.net Programming Style?

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)
as
Insert into employees
(FirstName,LastName)
values
(@FirstName,@LastName)
set @EmployeeId = @@Identity

2) Updating Emplyee Record

create proc procUpdateEmployee
@EmployeeId int,
@FirstName varchar(20),
@LastName varchar(20)
as
Update Employees
set
FirstName = @FirstName,
LastName = @LastName
where EmployeeId = @EmployeeId

3) Delete Employee Record

create proc procDeleteEmployee
@EmployeeId int
as
Delete from Employees
where EmployeeId = @EmployeeId

4) Get Specific Employee

create proc procGetEmployeeRec
@EmployeeId int
as
select EmployeeId,FirstName,LastName
from employees
where EmployeeId = @EmployeeId

5) Get All Employees

Create proc procGetAllEmployee
as
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 <appSettings> 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;
    try
    {
        conn.Open();

// Executing Command and retrieving result.

        int RecordsAffected = cmd.ExecuteNonQuery();
        int EmpId;
        if(RecordsAffected != 0)
            EmpId = (int)cmd.Parameters["@EmployeeId"].Value;
        else
            EmpId = -1;
        return EmpId;
    }
    catch(SqlException ex)
    {
        throw new ApplicationException("Data Error");
   }
    finally
    {

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

conn.Close();
}
}

// 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.

Conclusion

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.

- CodeGuru-Dot-Net "Lets make programming easy"