Developing the Components for the Middle Tier in visual basic .Net

What is Middle Tier ?

Middle tier equates to the Business tier or Application tier or Logical tier and contains the logic of the application. Middle tier simplifies the client´s access to the database (SQL Server) by isolating the business services from the Database and Presentation tiers. The components of the Application or Business tier are frequently called business services. Middle tier runs on a server and is often called the application server.

create a middletier component using visual

Middletier components are an important part of any large scale database application. Knowledge of developing a components based 3 tier application is critical for any databse developer.

The need for a Middle Tier

  • Centralizing the business logic in an application tier has several advantages including maintainability, extensibility, and interoperability.
  • Business rules often change, and we need to shield the rest of the system from the mutable logic. This decreases the effect on the rest of the application.
  • Flexibility in reusing existing middle tier code can also assist organizations developing applications for various PC client operating system platforms.
  • Coding application logic in middle tier decreases the overall development time.

Why we need to create a middle tier as a component

The component can be centralized on an application server and can be used the same way from the traditional Windows Form-based application as it would from a web application. You get a greater degree of reuse when you create a middle tier. If we centralize logic into a componenet, multiple client applications can use that logic without rewriting any of the code. The components isolate the front-end application from the SQL Server database.

How to create a Component in visual basic.Net for accessing SQL Server Data

This article will explain the concept of creating a middle tier component to perform data access.

I have used the NorthWind Database and Customers Table in this article.

Creating a Component is done by creating a Class Library in visual basic .Net.

Launch VS .NET and select File- New- Project from the menu. Select the Class Library project type from the Visual Basic Projects folder, within the New Project dialog box. Choose a file path for your project and name the class library CustomersManager. Click OK to create the project.

When we are creating class libraries, its important to name the class library as we create the new project. This way, we can take advantage of VS .NET´ s auto-generation feature, which automatically sets the root namespace and assembly name based on the name of the project. We can examine these attributes by right-clicking on the solution in the Solution Explorer and selecting Properties from the shortcut menu. You will see that the assembly name and root namespace are automatically chosen for you. This is shown in Fig.1.1.

Project Properties Window

Fig:- 1.1. Project Properties Window

By default, a single empty class, Class1, will be created for you. In visual basic .NET, you can store more than one class inside the .visual basic file.

In the code designer, rename the Class1 to Customers. If you look in your Solution Explorer, you will see that this does not automatically rename the file for you. For consistency, rename the Class1.vb file to Customers.vb via the Solution Explorer by right-clicking the file and selecting Rename. This changes the name of the file.

You will notice that an AssemblyInfo.vb file was automatically created for you. This is the manifest for the assembly. If you like, you can double-click the file to view the source code for the manifest.

Now, let ´s add a public function to the class (Customers). Double-click the Customers.vb file to launch the code designer window. First, import the namespaces that you will use in this class and write the following function GetCustomers().

Imports System.Data 
Imports System.Data.SqlClient 
Next, declare the public function, GetCustomers() as shown below: 
Public Class Customers 
Public Function GetCustomers(ByVal CompanyName As String) As DataSet 
  Dim myConnection As SqlConnection = New SqlConnection 
	 "Data Source=SYS1;Integrated Security=SSPI;Initial Catalog=NorthWind") 
  Dim cmdselect As New SqlCommand() 
  Dim daCustomers As New SqlDataAdapter() 
  Dim dsCustomers As New DataSet() 
   With cmdselect 
   .CommandText = "SearchCustomers" 
   .CommandType = CommandType.StoredProcedure 
   .Connection = myConnection 
   .Parameters.Add(New SqlParameter("@CompanyName", 
		SqlDbType.VarChar, 40, ParameterDirection.Input, True, 
		CType(10, Byte), CType(0, Byte), "", 
		DataRowVersion.Current, Nothing)) 
    .Parameters("@CompanyName").Value = CompanyName 
    End With 
    With daCustomers 
    .SelectCommand = cmdselect 
    .Fill(dsCustomers, "Customers") 
   End With 
   GetCustomers = dsCustomers 
    Catch excSQL As SqlException 
    End Try 
   End Function 

End Class

How to use a component in visual basic .Net application

To test the component, right-click the solution file in the Solution Explorer and select Add - New Project. Select the Windows Application project type. Name this project as CustomerForm. After the project has been created, right-click on the project and chose the Set As StartUp Project option so that this will be the first project to load after you execute your solution.

We need to set a reference to the CustomersManager project so that the CustomersForm project can use it. Right-Click the CustomerForm project and select the Add reference option. This launches the Add Reference dialog box. Choose the Projects tab. You will see the CustomersManager project name listed at the top of the form. Click the Select button to reference the project, as shown in Fig:-1.2. This will add your project to the Selected Components list.

Add a Project Reference Fig:-1.2. Add a Project Reference.

Now we will design the form.

From the Toolbox, add a new TextBox, DataGrid and Button control. The TextBox will be used to accept the search criteria- Company Name as shown in Fig:-1.3.

Windows Form - customers form

Fig:-1.3. Customers Form.

Next, add the code as shown below to the click event of the button.

Program Code :

Imports System.Data 
Imports System.Data.SqlClient 
Private Sub Form1_Load(ByVal sender As Object, ByVal e 
	 As System.EventArgs) Handles MyBase.Load 
End Sub 
Private Sub Button1_Click(ByVal sender As System.Object, 
	 ByVal e As System.EventArgs) Handles Button1.Click 
  Dim objCusManager As New CustomersManager.Customers() 
  Dim dsCustomers As DataSet 
  dsCustomers = objCusManager.GetCustomers(TextBox1.Text) 
  DataGrid1.DataSource = dsCustomers 
End Sub 
As you can see from the code, you first have to create a reference to your class library. We do this by using objCustomersManager variable and creating a new reference to the CustomersManager.GetCustomers class. Next, we need to create a DataGrid to store the results of the GetCustomers() method. You then use this DataSet to populate the DataGrid by using the DataSource property.

When you compile your code, the source code is generated into the Microsoft IL. As your code is executed, the CLR appropriately processes the IL and translates it to binary code at runtime. In the .NET Framework, the IL and metadata together compose the assembly, which is the unit of deployment.

If the application is production ready, you can choose to build by using the Release build option.

Select the CustomersManager project within your Solution Explorer. Choose the Release option from the drop-down menu. You can compile your project by right-clicking the CustomersManager project in the Solution Explorer and choosing Build.


When the button is pressed, DataGrid is displayed with customers as shown in Fig:-1.4.

 DataGrid with Customers data

Fig:-1.4. DataGrid with Customers data.

This article demonstrates how to use the middle tier to access SQL Server data. Middle tier can also be used to perform calculations. In this example of using a middle tier component we have seen how to separate UI, Logic, and Storage.

cover image of azure cloud book

Develop Azure Applications

Azure Cloud is gaining popularity and is competing with Amazon Elastic Cloud and Google Cloud for a spot in the billion dollar Cloud computing space.

With this book learn how to use - Table (an Azure Table is different from a SQL Server Table), Queue, Blob storage services, use your local machine to create an Application and a Service, how Azure splits a Table to achieve Load Balancing, Scalability based on the Partition Key, AppFabric Service Bus, Access Control Service, role of Fabric Controller, features of Azure SQL Database and Content Delivery Network.

Many have mastered Cloud Programming and are enjoying the benefits. Why not you?

View Highlights » View Contents »
cover image of foundation database programming book

Create your Own Accounting Software

A foundation book to Master Accounting Software Development, VB.Net, C# and OOPs programming. Detailed explanation of the Accounting domain for programmers is included. SQL and ADO.Net is also covered in detail.

This book gives you a strong foundation in Application Development.

A thorough understanding of the contents will enable you to grasp the topics covered in the accompanying books quite easily. This is because, the accounting domain is dealt with great detail in this book.

The ideal book to get started with application development in the .Net world.

View Highlights » View Contents »

cover image of book

Create your Own WEB ERP Software using ASP.Net

This book opens you to the Web. Using ASP.Net, C# and SQL Server as the tools, you will learn to build a Multi Tier Web Application as recommended by Microsoft. You will learn implementing inline GridView control, Transaction Management for a layered Web Application, build Data Access component and a Business Layer component.

Opens you to a world of opportunities. You will be able to develop a Web ERP application including all the five modules and learn how to use the TreeView control and GridView control to implement transactions such as Purchase Invoice, Sales Order and Sales Invoice.

View Highlights » View Contents »
cover image of entity framework book

ASP.Net MVC 5 Application Development Using Entity Framework 6 and VS 2015

Entity frameworks is the latest buzz in .Net programming and is the data access technology for Microsoft OS based hand held devices and web applications. Learn to integrate EF into an ASP.Net Application and MVC application. Create, explore and query the Entity Data Model. Learn how to use ObjectDataSource control and GridView control in an ASP.Net Application.

Also, learn to build Three-Tier ASP.Net application, create a business-class, implement methods in the class to perform CRUD operations and bind a GridView to the ObjectDataSource control. Learn how to handle concurrency and managing Transactions.

View Contents »