.Net Multi-Tier Application

This blog takes up the issue of creating multi tier application with VB.Net (Windows Forms) and C# (Asp .Net).

Start up with creating two stored procedures.

create procedure spAllCategories
as
select * from Categories
go


create procedure spInsertCategories (@pCategoryName varchar(40), @pDescription varchar(60))
as
insert into Categories values (@pCategoryName, @pDescription)
go

Create an new class Library project called ShalvinLib and add two methods that will call the stored procedures. This is going to act as the business tier.

Imports System.Data.SqlClient
Public Class BusinessLayer
Public Shared Function GetCategories() As DataTable
Dim cnn As New SqlConnection("Integrated Security=sspi;Initial Catalog=Northwind;Data Source=.\sqlexpress")
cnn.Open()

Dim ds As New DataSet
Dim da As New SqlDataAdapter("spAllCategories", cnn)
da.Fill(ds, "Cat")
Return ds.Tables("Cat")
End Function

Public Shared Sub InsertCategories(ByVal mCategoryName As String, ByVal mDescription As String)
Dim cnn As New SqlConnection("Integrated Security=sspi;Initial Catalog=Northwind;Data Source=.\sqlexpress")
cnn.Open()
Dim cmd As SqlCommand

cmd = New SqlCommand("spInsertCategories " + mCategoryName + ", " + mDescription, cnn)

cmd.ExecuteNonQuery()

End Sub

End Class

C# and Asp.Net
Here instead of using Windows Forms I am opening a Asp.Net Application and Instead of starting a new class Library I am adding a Class to the existing project.

web.config




using System.Data.SqlClient;
public static DataTable GetCategories()
{
SqlConnection cnn = new SqlConnection(ConfigurationManager.AppSettings.Get("Cnn"));
SqlDataAdapter da = new SqlDataAdapter("spAllCategories", cnn);
DataSet ds = new DataSet();
da.Fill(ds, "Cat");
return ds.Tables["Cat"];
}

public static void InsertCategories(string mCategoryName, string mDescription)
{
SqlConnection cnn = new SqlConnection(ConfigurationManager.AppSettings.Get("Cnn"));
cnn.Open();
string strSql = "spInsertCategory '" + mCategoryName + "', '" + mDescription + "'";
SqlCommand cmd = new SqlCommand(strSql , cnn);
cmd.ExecuteNonQuery();
}


Having created the class library dll, we can proceed to create a windows forms application.
Set a reference to the class library. Create the visual interface and invoke the methods of class library.

Dim bl As New ShalvinLib.BusinessLayer
Private Sub frmShowCategories_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
DataGridView1.DataSource = bl.GetCategories
End Sub

C# and Asp .Net
protected void Page_Load(object sender, EventArgs e)
{
GridView1.DataSource = BusinessLayer.GetCategories();
DataBind();
}
protected void Button1_Click(object sender, EventArgs e)
{
BusinessLayer.InsertCategories("Books", ".Net Books");
Response.Write("Record Saved");
}

Happy programming