SQL Server programming : Best practice

In the previous article we discussed the Database Tier of the application that lays a foundation for a strong Enterprise Application.

In this tutorial we will take a look at the Business Logic Tier which is also known as the Service Layer.

The Business Logic Tier is where all the logic is encapsulated. This SQL Server programming tutorial will look into some of the good and bad practices for making the Business Logic Tier.

We will also take a look at the Model View Controller Pattern (MVC).

SQL Server programming : Business Layer

The main purpose of the business layer is to encapsulate all the business logic. The business layer must be separated from the Database layer and the presentation layer.

A strong business layer hides all the confidential information from the user and the user has no power to communicate with the business layer directly.

The Business layer should be made in such a way that it is portable; meaning that the methods and the functionality of that layer can be used in other applications.

The Business layer should also be extendible; meaning that in the future we should be able to add more methods without changing the whole architecture of the layer.

SQL Server programming : Bad Practice Let's look at an example of weak business layers which allows the user to directly communicate with the service layer and jeopardize security. Let's make a simple web form which will go to the database and fetch some results depending upon the selection of the user. We will be using our database "MyDatabase" which we created in the last tutorial.

Below is the image of the web form:

As you can see we have a simple DropDownList and a Button control. Now, when I select any name from the DropDownlist and click on the Display Result button, it shows me the phone numbers for that particular person:
Seems pretty simple, and it is very simple. But the thing I like to show you is the code behind meaning the code that has achieved this feature:

private void Button1_Click(object sender, System.EventArgs e)

{string query = "SELECT PhoneNumber FROM Phone
WHERE PersonID="+ddlNames.SelectedItem.Value;

SqlCommand myCommand = new SqlCommand(query,myConnection);

myCommand.CommandType = CommandType.Text;

myConnection.Open();
SqlDataReader dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
myDataGrid.DataSource = dr;
myDataGrid.DataBind();
myConnection.Close();
}

You might not be able to understand all the code that is written. The code above is written in C# and the interface is drawn in Asp.net. Now, in this SQL Server programming tutorial, let's study the bad things about the code above.

First of all this code is implemented inside the button click event, which means that when the button is clicked this code is fired.

That means, it opens the database connection, searches for the name, and returns the phone numbers associated with the name. As you can see the user has the direct interaction with the business logic and the database tier, which is like putting our whole confidential information into the hands of the user.

The other bad thing we are doing, is we are using Ad-Hoc queries. In the last tutorial I emphasized on using the stored procedures instead of the Ad-Hoc queries because stored procedures are fast and reliable. Now let’s see that how we can make this code better.

SQL Server programming : Good Practice The interface of our application will remain the same. The business logic will be changed, and will be handled in a more secure way. We will also use a stored procedure for faster database access. First of all we will make a class that will access the data from the database. We will name the class "DBPerson".

The use of the class will hide the business logic from the user. Below is the class; it contains a method "GetPersonPhoneNumber" which will retrieve the phone numbers from the database.

public SqlDataReader GetPersonPhoneNumber(int personID)
{
string connectionString = (string)
ConfigurationSettings.AppSettings["ConnectionString"];

SqlDataReader dr = null;

SqlConnection myConnection = new SqlConnection(connectionString);

SqlCommand myCommand = new
SqlCommand("sp_GetPhoneNumbers",myConnection);
myCommand.CommandType = CommandType.StoredProcedure;

try
{

myConnection.Open();

dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection);

return dr;

}
catch(Exception ex)
{
// Logg the exception here
return null;
}

finally
{
myConnection.Close();
dr.Close();
}
}

As you can see, we have separated the business logic from the presentation layer and now users cannot access the phone numbers unless they call the GetPersonPhoneNumber method.

Now, let's see how we are going to call the "GetPersonPhoneNumber" from the button click event.

private void Button1_Click(object sender, System.EventArgs e)

{
int personID = 0;

if(ddlNames.SelectedItem.Value != null &&
ddlNames.SelectedItem.Value != "")
}
personID = Convert.ToInt32(ddlNames.SelectedItem.Value); {

DBPerson person = new DBPerson();
myDataGrid.DataSource = person.GetPersonPhoneNumber(personID);

}

Observe how good it looks now. Now, if you want to change the way the data is retrieved from the database, you just need to change the GetPersonPhoneNumber method and that's it.

This technique of separating the presentation layer, business logic layer and the database layer is also known as the MVC pattern.

In Model View Controller pattern the model is the business logic which is hidden from the user.

The view is the presentation layer and the controller is the events that are generated from the user interface.

It’s always a good idea to put all the class files in a separate folder to organize them properly.

SQL Server programming : Using Static Methods

Sometimes you'll find you are repeating code over and over again which is serving a common or general purpose.

If so is the case, you can use the static methods in a class. Let's see how we can perform this:

We make a messages class which will display messages if the data has been inserted or not. Since the messages will be same, we will use static methods. Our new class will be called Messages.

public class Messages
{
public Messages()
{
}

public static string Inserted(bool isInserted)
{
if(isInserted) return "Data has been inserted";
else
return "Data not inserted";
}
}

As you can see, the method Inserted takes a Boolean value and returns the message if the data is inserted or not.

Using this method is pretty straight forward.

string messaage = Messages.Inserted(true);

Later, if you want to change the displayed message, you can always change it in the Messages class instead of changing it all over the presentation layer. You can always deploy your class for other people to use.

Using business logic layer is very important, as it hides the data that is not meant to be seen by ordinary users.

Apart from hiding the data it also serves as a layer between the presentation layer and the database layer.

I hope you enjoyed this SQL Server programming tutorial.

Return from SQL Server programming to Application Architecture