Digicorp

• Download Source Code – 143.73 KB
• Download SQL Script – 2.29 KB

Table of contents

1. Introduction
2. Define Tiers for the Application
3. Role of Tiers
4. Implementing CLR Stored Procedure in LINQ based n-tier Architecture Step by Step
5. Steps to Create Architecture
6. Using the code
7. Conclusion
8. Points of Interest
9. History

Introduction

This article is a sequence of my previous article about “CLR Stored Procedure & creating it step by step”. In my previous article I have explained CLR Stored Procedure, benefits & drawbacks of it and also how to implement it. CLR Stored Procedure is very powerful and gives better results while executing complex logic, intense string operation or string manipulations, cryptography, accessing system resources and file management, etc. Development of any project in n-tier architecture is different taste and having its own benefits and drawbacks. Microsoft has launched LINQ i.e. Language Integrated Query with .net framework 3.0. LINQ can be implemented in variety of ways for e.g. LINQ with SQL, LINQ with objects, LINQ with dataset and LINQ with xml etc. I have implemented simple n-tier architecture few years back in my company. It is suitable for both web as well as desktop applications. It is a centralized n-tier architecture i.e. all the objects are resides at the same place in the target system. I don’t claim this as best but I have implemented this architecture as I want at least following benefits out of it.

1. It must be secure, easy to implement and easy to deploy
2. It must be tiered based and at least 3 tiered so that if we change code of any tier, we need to deploy dll of that tier only.
3. Single architecture for web and desktop application with standard application performance
4. Parallel development should be possible i.e. one developer can work with database, another can work with business logic and one more can work with user interface and so on
5. No in-line SQL queries
6. It must not be complex so that new developer can easily set on development without undergoing long training and existing developers can be easily migrated to and from any of the projects as basic development architecture of all the projects will remain same

Define Tiers for the Application

Now, let me explain various tiers of the architecture. Any good architecture will consist at least 3 important tiers viz. 1) User Interface or Presentation Layer 2) Business Logic Layer 3) Data Layer. The architecture which I am going to explain have 5 tiers. Any architecture having more than 3 tiers is called n-tier architecture. Following image will clarify it.

Following is the image showing architecture in our application. It contains following 4 tiers (see image) and 1 database so total 5 tiers.

Role of Tiers

Let me explain each of above tier in brief.

1. Tier – 1: Presentation Layer: This layer will be responsible for various activities between Users and application. All the user interface related logic will reside at this layer. That means web forms or Win forms will reside here. This layer does not have direct access to the database or Data Access Layer. So, all the data goes in or comes out to Presentation Layer will be through the Business Logic Layer only. A reference of Business Logic Layer will be added to this layer. A separate project will be there for this layer.
2. Tier – 2: Business Logic Layer: Business Logic Layer will perform all the Business Logics of the application. Business logic consists of two things viz., Core business logic and Data access logic. Data access logic is been segregated to different layer to provide more security and data encapsulation to the application. There will be a separate class for each table of the database to provide better management, in this layer. This class will have various methods. Business logic will be applied to data coming from or going to Presentation Layer and Data Access Layer. A reference of Data Access Layer will be added to this layer. A separate project will be there for this layer.
3. Tier – 3: Data Access Layer: This layer is a part of Business logic but is separated from core business logic. All the data related operations between application and database will be performed over here. This layer will be created using “LINQ”. One LINQ to SQL class will be created over here and all the needed database tables or Stored Procedures will be dragged and dropped over here. This layer will have minimum manual coding. A separate project will be there for this layer.
4. Tier – 4: CLR Stored Procedure Layer: This layer will be responsible for defining various CLR Stored Procedures. There will be a separate class for each table of the database to provide better management, in this layer. This layer is not directly associated with Presentation Layer, Business Logic Layer or Data Access Layer. CLR Stored Procedure will be deployed to SQL server. A separate project will be there for this layer. To know, what is CLR Stored Procedure and how to create it refer my article “CLR Stored Procedure and Creating It Step by Step”.
5. Tier – 5: Database: This is core data and other objects to maintain and access it. For e.g. SQL server database. Core data is data in various tables and objects to maintain & access such data are various tables, Stored Procedures, CLR Stored Procedures, Views and Functions etc.

Physically there are 5 layers but logically they are 4 layers as assembly of CLR Stored Procedure will be the part of Database at the time of deployment.

To add reference of one project to another project is very simple. Just right click on the project >> Click on Add Reference. A dialog box will be displayed. Select “Projects” tab and select appropriate project in the list of projects. Click “OK” button. We can not add circular reference within project for e.g. In our case, Presentation Layer refers to Bussiness Layer and Business Layer refers to Data Access Layer. Now, we can not add reference of Presentation Layer to Data Access Layer as it will create circular reference.

Implementing CLR Stored Procedure in LINQ based n-tier Architecture Step by Step

Let us implement CLR Stored Procedure in LINQ based n-tier architecture. I have listed all SQL statements used for creating database, creating table, inserting dummy records in the table, etc. in the script attached with this article.

Application Development Specification

• IDE: Visual Studio 2008
• Framework: 3.5 with SP 1
• Language: C# 3.0
• Database: Microsoft SQL Server 2005 Express edition
• Operating system: Windows XP with SP 2

Steps to Create Architecture

1. Open Microsoft Visual Studio 2008 >> Create New Project. A dialog box will be opened. Select, Visual C# >> Windows on left side “Project Types” panel. Select, “Windows Forms Application” on right side “Templates” panel. Give proper name to project, solution and select location to save this solution. This project will be our presentation layer. Following image will make it clear.

A default form with the name “Form1” will be created with the project. Rename it to “Demo”. Design it as shown in following image. There is a Label control for heading, one Data Grid View control, a Group Box control to hold various buttons viz., Get Data, Insert Random Record, Delete Selected Record, Clear Grid and Exit. Following image will make it clear.

2.    Right click on Solution, click on Add >> New Project. A dialog box will be opened. Select, Database Projects >> Microsoft SQL Server >> SQL CLR on left side “Project Types” panel. Select, “SQL Server Project” on right side “Templates” panel. Give proper name to project and select location to save this solution. This project will be our CLR Stored Procedure Layer. Follow the steps I have explained in my article “CLR Stored Procedure and Creating It Step by Step” to add CLR Stored Procedure in this project. Following image will make it clear.

Right click on the project >> Select Add >> Add New Items. A dialog box will be displayed as shown in following image. Select Visual C# Items from left “Categories” panel. Select Stored Procedure from right side “Templates” panel. Give proper name to it.

3.    Right click on Solution, click on Add >> New Project. A dialog box will be opened. Select, Visual C# >> Windows on left side “Project Types” panel. Select, “Class Library” on right side “Templates” panel. Give proper name to project and select location to save this solution. This project will be our Business Logic Layer. I have added two classes to this project i.e. 1) “ConvertToDataTable” and 2) “CustomerSalesInformation”. “ConvertToDataTable” class has various methods to convert array of objects to DataTable or DataSet. CustomerSalesInformation class consist various methods to Insert a record, Delete a record and Get records from database. Following image will make it clear.

4.    Once again, Right click on Solution, click on Add >> New Project. A dialog box will be opened. Select, Visual C# >> Windows on left side “Project Types” panel. Select, “Class Library” on right side “Templates” panel (same as step 3). Give proper name to project and select location to save this solution. This project will be our Data Access Layer.

5.    Right click on “DataAccessLayer” project, Select Add >> New Item option from the menu. A dialog box shown in following image will be displayed. Select on Visual C# Items in left side “Categories” panel and “Linq to SQL Classes” in right panel “Templates”.

6.    Open Server explorer and click on “Connect to database” button given at
top-middle of the server explorer window. It will start a new wizard to
connect to the database. Now drag and drop Stored Procedures
“CustomerSalesInformationDelete” and
“CustomerSalesInformationInsertRandomRecord”. Do NOT drag and drop
“CustomerSalesInformationGet” right now as we need to change it. I have
added “AccessData” class to add various useful methods and variables to
be used on Business Logic Layer. Write now I have declared one private
object of “LinqToSqlDataContext” and exposed it using property “Call”.
Following images will make it clear. Change the connection string I
have statically wrote while declaring object of “LinqToSqlDataContext”.

7.    I have suggested not to drag and drop “CustomerSalesInformationGet” because it is a Stored Procedure which does not contain any SELECT query. That is why if you drag and drop it in .dbml file, the return type of the method of the said Stored Procedure will be “int” instead of ISingleResult and so it will not produce any result which we are expecting from this Stored Procedure. You can see this in .cs file of dbml. The work around I am going to explain is not the standard way but it works fine. Let me know if you find any better option. Following are the steps of the work around.

Step 1: Go to query analyzer and drop the Stored Procedure “CustomerSalesInformationGet” which was automatically created at the time of deploying CLR stored procedure, using following SQL statement.

DROP PROCEDURE [dbo].[CustomerSalesInformationGet]

Step 2: Create new stored procedure with the same name as stored procedure created at the time of deploying CLR stored procedure, using following SQL statements.

CREATE PROCEDURE [dbo].[CustomerSalesInformationGet]
AS
BEGIN
	SELECT * from CustomerSalesInformation
END

Step 3: Now come to the Visual Studio and refresh the “Stored Procedure” element of the tree view. You can simply right click on the element and click on “Refresh” option of the menu or cliking on refresh icon at the top-left of Server Explorer. Now drag and drop “CustomerSalesInformationGet” stored procedure to the .dbml file. This will create a method for the said stored procedure with the return type “ISingleResult”. You can verify this in .cs file of dbml. So this will return the result per our expectation.

Step 4: Once again go to query analyzer and drop the manually created stored procedure “CustomerSalesInformationGet” using following SQL statement.

DROP PROCEDURE [dbo].[CustomerSalesInformationGet]

Step 5: Now come to the Visual Studio and Right click on “ClrStoredProcedureLayer” project and click on “Deploy” option of the menu. This will deploy CLR stored procedure to the database once again. Now do NOT drag and drop this stored procedure to the dbml file otherwise we have to repeat this whole work around.

8.    You have to change connection at two places while using my sample source code.

1.    In the “AccessData.cs” file where we have declared an object of “LinqToSqlDataContext”. Following will clarify this.

private static LinqToSqlDALDataContext objLinqToSqlDALDataContext = _
new LinqToSqlDALDataContext("Data Source=OM\\SQLEXPRESS;Initial Catalog=DbForClrDemo;User ID=sa;Password=airborne");

2.    In the “CLRStoredProcedureLayer” project. Right click on the said project in solution explorer and click on “Properties”. A screen like following image will be displayed. Select “Database” tab and change the connection string at “Connection String” option where a textbox and Browse button is given.

9.    Save the whole solution. Right click on the solution in solution explorer and click on “Build” option. Now, you can run the application by hitting F5. A dialog box will be opened as shown in following image. Click on “Get Data” button, it will retrieves records from the database and loads in to the grid. “Insert Random Record” will insert random record in the table and once again retrieves records from the database and loads in to the grid. “Delete Selected Record” deletes the selected record. “Clear Grid” button clears the records from the grid. “Exit” button will close and exit the application. Following is the demo of the same.

Using the Code

1. Download the zip files for source code and sql script  and extract them
2. Create Database and tables using “Script to create Database and Table.sql”. Also, insert records in the table using same file
3. Change connections at 2 places in application as explained in Step 8
4. Run the demo.
5. When you hit “Get Data” button for the first time, it will take little time to fetch data from database. Now, hit “Clear Grid” button and hit “Get Data” button once again, you will realize the speed. Also, try to hit “Insert Random Record” button continuously for many time. It inserts record to the database and fills the grid at every hit, but still see the speed of it.
6. This will give little slow performance in web application than windows application.

Conclusion

Clr Stored Procedures can be efficiently implemented in n-tier architecture

Suggestion

Refer my another article “CLR Stored Procedure and Creating It Step by Step”

Points of Interest

1. Exploring IMultipleResult in LINQ.

History

1. 08^th September, 2009: Initial release

using System;
using System.Windows.Forms;
using BusinessLayer;

namespace ClrInNTierPresentationLayer
{
/// <summary>
/// Represents demo form for the application
/// </summary>
public partial class frmDemo : Form
{
#region Constructor

/// <summary>
/// Constructor of the application
/// </summary>
public frmDemo()
{
InitializeComponent();
}

#endregion

#region Event Handler

#region Buttons

/// <summary>
/// Handles Click event of the button
/// </summary>
/// <param name=”sender”></param>
/// <param name=”e”></param>
private void btnGetData_Click(object sender, EventArgs e)
{
try
{
FillDataGridView();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, “Error”, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}

/// <summary>
/// Handles Click event of the button
/// </summary>
/// <param name=”sender”></param>
/// <param name=”e”></param>
private void btnInsertRandomRecord_Click(object sender, EventArgs e)
{
try
{
CustomerSalesInformation.CustomerSalesInformationInsertRandomRecord();
FillDataGridView();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, “Error”, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}

/// <summary>
/// Deletes selected record from the database
/// </summary>
/// <param name=”sender”></param>
/// <param name=”e”></param>
private void btnDeleteSelectedRecord_Click(object sender, EventArgs e)
{
try
{
if (dgvData.Rows.Count > 0)
{
if (MessageBox.Show(”Are you sure to delete selected record?”, “Confirmation”, MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
{
CustomerSalesInformation.CustomerSalesInformationDelete(Convert.ToDouble(dgvData.CurrentRow.Cells["ID"].Value.ToString()));
FillDataGridView();
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, “Error”, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}

/// <summary>
/// Handles Click event of the button
/// </summary>
/// <param name=”sender”></param>
/// <param name=”e”></param>
private void btnClearGrid_Click(object sender, EventArgs e)
{
try
{
dgvData.DataSource = null;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, “Error”, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}

/// <summary>
/// Handles Click event of the button
/// </summary>
/// <param name=”sender”></param>
/// <param name=”e”></param>
private void btnExit_Click(object sender, EventArgs e)
{
try
{
Application.Exit();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, “Error”, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}

#endregion

#endregion

#region Private Methods

/// <summary>
/// Fills the data in the DataGridView
/// </summary>
private void FillDataGridView()
{
try
{
//Filling the grid with data
dgvData.DataSource = CustomerSalesInformation.CustomerSalesInformationGet();

//Formating the columns of the grid
dgvData.Columns[0].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
dgvData.Columns[1].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
dgvData.Columns[2].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;

dgvData.Columns[2].DefaultCellStyle.Alignment = DataGridViewContentAlignment.TopRight;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, “Error”, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}

#endregion
}
}