Table of contents
- Introduction
- Define tiers for the application
- Role of tiers
- Implementing CLR Stored Procedure in LINQ based n-tier
architecture step by step
- Steps to create architecture
- Using the code
- Conclusion
- Suggestion
- Points of interest
- History
Introduction
This article is a sequence of my previous article about “CLR Stored Procedure and
creating it step by step”. In my previous article, I explained CLR Stored Procedures, their benefits and drawbacks, and also how to
implement them. CLR Stored Procedures are very powerful and give better results while executing complex logic, intense string operations or string
manipulations, cryptography, accessing system resources, and file management, etc. Development of any project in an n-tier architecture is a different taste
and has its own benefits and drawbacks. Microsoft has launched LINQ, i.e., Language Integrated Query, with .NET Framework 3.0. LINQ can be implemented in a
variety of ways, for example, LINQ with SQL, LINQ with Objects, LINQ with DataSet, and LINQ with XML, etc. I implemented a simple n-tier architecture a
few years back in my company. It was suitable for web as well as desktop applications. It is a centralized n-tier architecture, i.e., all the objects
reside at the same place in the target system. I don’t claim this as the best but I have implemented this architecture as I want at least the following benefits out of it:
- It must be secure, easy to implement, and easy to deploy.
- It must be tier based and at least 3 tiered so that if we change the code of any tier, we need to deploy the DLL of that tier only.
- Single architecture for web and desktop applications with standard application performance
- Parallel development should be possible, i.e., one developer can work with the database, another can work with the business logic,
and yet another can work with the user interface, and so on.
- No in-line SQL queries.
- It must not be complex so that a new developer can easily set on development without undergoing long training, and existing developers can be easily migrated
to and from any project as the basic development architecture of all the projects will remain the same.
Define tiers for the application
Now let me explain the various tiers of the architecture. Any good architecture will consist of at least three important tiers:
- User Interface or Presentation Layer
- Business Logic Layer
- Data Layer
The architecture which I am going to explain has five tiers. Any architecture that has more than three tiers is called n-tier architecture. The following image will clarify this:
Following is the image showing the architecture in our application. It contains the following four tiers (see image) and a database, so total five tiers.
Role of tiers
Let me explain each of the above tier in brief:
- Tier – 1: Presentation Layer: This layer is responsible for the various activities between the users and the application. All the user
interface related logic will reside at this layer. That means web forms or WinForms will reside here. This layer does not have direct access to the
database or Data Access Layer. So, all the data that goes in or comes out of the 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.
- Tier – 2: Business Logic Layer: Business Logic Layer will perform all the business logic of the application. The business logic consists
of two things viz., the core business logic and the data access logic. The data access logic is segregated to a 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. The business logic will be applied to data coming from or going to the Presentation Layer and Data Access Layer. A reference
of the Data Access Layer will be added to this layer. A separate project will be there for this layer.
- Tier – 3: Data Access Layer: This layer is a part of the business logic but is separated from the core business logic. All the data
related operations between the application and the database will be performed over here. This layer will be created using LINQ. A 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.
- 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 a CLR
Stored Procedure is and how to create one, refer my article “CLR
Stored Procedure and Creating it Step by Step”.
- Tier – 5: Database: This is core data and objects to maintain and access it. For example, a SQL Server database. Core data is data in
various tables, and objects to maintain and access such data are various tables, Stored Procedures, CLR Stored Procedures, Views and Functions, etc.
Physically there are five layers but logically they are only four layers as the assembly of the CLR Stored Procedure will be part of the database at the time of deployment.
To add a 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 the "Projects" tab and select the appropriate project in the list of projects. Click the "OK" button. We can not add a circular reference
within the project, for example, in our case, the Presentation Layer refers to the Business Layer and the Business Layer refers to the Data Access Layer. Now,
we can not add a reference of the Presentation Layer to the Data Access Layer as it will create a circular reference.
Implementing a CLR Stored Procedure in a LINQ based n-tier architecture step by step
Let us implement a CLR Stored Procedure in a LINQ based n-tier architecture. I have listed all the SQL statements used for creating a database, creating a
table, inserting dummy records in the table, etc., in the script attached with this article.
Application development specification
- IDE: Visual Studio 2008
- .NET 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 the architecture
1. Creating the Presentation Layer: Open Microsoft Visual Studio 2008 >> Create New Project. A dialog box will be opened. Select, Visual
C# >> Windows on the left side “Project Types” panel. Select “Windows Forms Application” on the right side “Templates” panel. Give a proper name to the
project, solution, and select a location to save this solution. This project will be our presentation layer. The 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 the following image. There is a Label
control for heading, a DataGridView control, and a GroupBox control to hold various buttons viz., Get Data, Insert Random Record, Delete
Selected Record, Clear Grid, and Exit. The following image will make it clear.
Following is the code for the demo form:
using System;
using System.Windows.Forms;
using BusinessLayer;
namespace ClrInNTierPresentationLayer
{
public partial class frmDemo : Form
{
#region Constructor
public frmDemo()
{
InitializeComponent();
}
#endregion
#region Event Handler
#region Buttons
private void btnGetData_Click(object sender, EventArgs e)
{
try
{
FillDataGridView();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error",
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void btnInsertRandomRecord_Click(object sender, EventArgs e)
{
try
{
CustomerSalesInformation.CustomerSalesInformationInsertRandomRecord();
FillDataGridView();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error",
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
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);
}
}
private void btnClearGrid_Click(object sender, EventArgs e)
{
try
{
dgvData.DataSource = null;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error",
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
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
private void FillDataGridView()
{
try
{
dgvData.DataSource =
CustomerSalesInformation.CustomerSalesInformationGet();
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
}
}
2. Creating the CLR Stored Procedure Layer: Right click on Solution, click on Add >> New Project. A dialog box will be opened. Select
Database Projects >> Microsoft SQL Server >> SQL CLR on the left side “Project Types” panel. Select “SQL Server Project” on the right side “Templates” panel.
Give a proper name to the project and select the 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 a CLR Stored Procedure in this project. The 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 the following image. Select Visual C# Items from the
left side "Categories" panel. Select Stored Procedure from the right side "Templates" panel. Give a proper name to it.
Following is the code for the CustomerInformationClrSP class:
using System;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
public partial class CustomerInformationClrSP
{
#region Pubic Methods
[Microsoft.SqlServer.Server.SqlProcedure]
public static void CustomerSalesInformationGet()
{
#region Variable Declaration
SqlConnection sqlConnection = new SqlConnection();
SqlCommand sqlCommand = new SqlCommand();
#endregion
try
{
sqlConnection = new SqlConnection("context connection=true");
sqlConnection.Open();
sqlCommand = new SqlCommand("SELECT * FROM CustomerSalesInformation",
sqlConnection);
SqlContext.Pipe.ExecuteAndSend(sqlCommand);
}
catch (Exception)
{
throw;
}
finally
{
sqlConnection.Close();
sqlConnection.Dispose();
sqlCommand.Dispose();
}
}
[Microsoft.SqlServer.Server.SqlProcedure]
public static void CustomerSalesInformationInsertRandomRecord()
{
#region Variable Declaration
SqlConnection sqlConnection = new SqlConnection();
SqlCommand sqlCommand = new SqlCommand();
#endregion
try
{
sqlConnection = new SqlConnection("context connection=true");
sqlConnection.Open();
sqlCommand = new SqlCommand("INSERT INTO [dbo].[CustomerSalesInformation]" +
"([Name], [Sales]) VALUES ('" + Guid.NewGuid().ToString() +
"', 50000)", sqlConnection);
SqlContext.Pipe.ExecuteAndSend(sqlCommand);
}
catch (Exception)
{
throw;
}
finally
{
sqlConnection.Close();
sqlConnection.Dispose();
sqlCommand.Dispose();
}
}
[Microsoft.SqlServer.Server.SqlProcedure]
public static void CustomerSalesInformationDelete(double CustomerId)
{
#region Variable Declaration
SqlConnection sqlConnection = new SqlConnection();
SqlCommand sqlCommand = new SqlCommand();
#endregion
try
{
sqlConnection = new SqlConnection("context connection=true");
sqlConnection.Open();
sqlCommand = new SqlCommand("DELETE FROM [DbForClrDemo]." +
"[dbo].[CustomerSalesInformation] WHERE ID = " +
CustomerId, sqlConnection);
SqlContext.Pipe.ExecuteAndSend(sqlCommand);
}
catch (Exception)
{
throw;
}
finally
{
sqlConnection.Close();
sqlConnection.Dispose();
sqlCommand.Dispose();
}
}
#endregion
};
3. Creating the Business Layer: Right click on the Solution, click on Add >> New Project. A dialog box will be opened. Select Visual C# >>
Windows on the left side “Project Types” panel. Select “Class Library” on the right side “Templates” panel. Give a proper name to the project and select a
location to save this solution. This project will be our Business Logic Layer. I have added two classes to this project:
ConvertToDataTableCustomerSalesInformation
The ConvertToDataTable class has various methods to convert an array of objects to a DataTable or DataSet.
The CustomerSalesInformation class consists of various methods to insert a record, delete a record, and get records from the database. The following image will make it clear:
Following is the code for the CustomerSalesInformation class of the Business Layer.
using System;
using System.Data;
using System.Linq;
using DataAccessLayer;
namespace BusinessLayer
{
public class CustomerSalesInformation
{
#region Public Methods
public static DataTable CustomerSalesInformationGet()
{
try
{
object[] objData =
AccessData.Call.CustomerSalesInformationGet().
ToArray<CustomerSalesInformationGetResult>();
return ConvertToDataTable.ConvertToDatatable(objData);
}
catch (Exception)
{
throw;
}
}
public static void CustomerSalesInformationInsertRandomRecord()
{
try
{
AccessData.Call.CustomerSalesInformationInsertRandomRecord();
}
catch (Exception)
{
throw;
}
}
public static void CustomerSalesInformationDelete(double CustomerID)
{
try
{
AccessData.Call.CustomerSalesInformationDelete(CustomerID);
}
catch (Exception)
{
throw;
}
}
#endregion
}
}
4. Creating the Data Access Layer: Once again, right click on Solution, click on Add >> New Project. A dialog box will be opened. Select
Visual C# >> Windows on the left side “Project Types” panel. Select “Class Library” on the right side “Templates” panel (same as step 3). Give a proper
name to the project and select a location to save this solution. This project will be our Data Access Layer.
Right click on the “DataAccessLayer” project, select the Add >> New Item option from the menu. The dialog box shown in the following image will be
displayed. Select Visual C# Items in the left side “Categories” panel and “LINQ to SQL Classes” in the right side panel “Templates”.
5. Adding a Stored Procedure to the DBML file: Open Server Explorer and click on the “Connect to database” button given at the top-middle
of the Server Explorer window. It will start a new wizard to connect to the database. Now drag and drop the Stored Procedures CustomerSalesInformationDelete
and CustomerSalesInformationInsertRandomRecord. Do not drag and drop “CustomerSalesInformationGet” right now as we need to change it. I
have added the AccessData class to add various useful methods and variables to be used on the Business Logic Layer. Right now, I have declared a
private object of LinqToSqlDataContext and exposed it using the property Call. The following image will make it clear. Change the
connection string I have statically written while declaring the object of LinqToSqlDataContext.
6. Adding the Stored Procedure "CustomerSalesInformationGet": I suggested not to drag and drop CustomerSalesInformationGet in the
previous step because it is a Stored Procedure which does not contain any SELECT query. That is why if you drag and drop it in a .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 the .cs file of the DBML. The workaround I am going to explain is not the standard way
but it works fine. Let me know if you find any better options. Following are the steps of the workaround.
- Step 1: Go to query analyzer and drop the Stored Procedure
CustomerSalesInformationGet which was automatically created at the time
of deploying the CLR stored procedure, using the following SQL statement:
DROP PROCEDURE [dbo].[CustomerSalesInformationGet]
Step 2: Create a new Stored Procedure with the same name as the Stored Procedure created at the time of deploying the CLR stored procedure,
using the following SQL statements:
CREATE PROCEDURE [dbo].[CustomerSalesInformationGet]
AS
BEGIN
SELECT * from CustomerSalesInformation
END
Step 3: Now come to Visual Studio and refresh the “Stored Procedure” element of the tree view. You can simply right click on the
element and click on the “Refresh” option of the menu or click on the Refresh icon at the top-left of Server Explorer. Now drag and drop
the 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 the .cs file of the DBML. This will return the result as per our expectations.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.
7. Changing connection: You have to change the connection at two places while using sample source code.
- In the “AccessData.cs” file of the data access layer where we have declared an object of
LinqToSqlDataContext. The following will clarify this:
private static LinqToSqlDALDataContext objLinqToSqlDALDataContext =
new LinqToSqlDALDataContext("Data Source=OM\\SQLEXPRESS;" +
"Initial Catalog=DbForClrDemo;User ID=sa;Password=airborne");
- In the “CLRStoredProcedureLayer” project. Right click on the said project in Solution Explorer and click on “Properties”. A screen like the following image will
be displayed. Select the “Database” tab and change the connection string at the “Connection String” option where a text box and Browse button is given.
8. Save and Run the application: Save the whole solution. Now you can run the application by hitting F5. A dialog box will be opened as shown
in the following image. Click on the “Get Data” button, it will retrieves records from the database and load in to the grid. “Insert Random Record” will insert
a random record in the table and once again retrieve records from the database and load in to the grid. “Delete Selected Record” deletes the selected record.
The “Clear Grid” button clears the records from the grid. The “Exit” button will close and exit the application. Following is the demo of the same.
Using the code
- Download the Zip files for the source code and the SQL script and extract them.
- Create the database and tables using "Script to create Database and Table.sql". SQL statements to insert dummy records are also in the same file.
- We have to configure the connection of the application with your database. Make changes in connection at two places in the application as explained in Step 8.
- Run the demo. It should run without problems now.
- When you hit the "Get Data" button for the first time, it will take a little time to fetch data from the database. Now hit the "Clear Grid" button and hit
the "Get Data" button once again, and you will realize the speed. Also, try to hit the "Insert Random Record" button continuously for many times. It inserts
a record to the database and fills the grid at every hit, but watch the speed of it.
- This will give a little slower performance in the web application than the Windows application.
Conclusion
CLR Stored Procedures can be efficiently implemented in n-tier architectures.
Suggestion
Refer my another article "CLR
Stored Procedure and Creating It Step by Step".
Points of interest
- Explore
IMultipleResult in LINQ.
History
- 6th September, 2009: Initial release.
