In this article I will explain with an example, how to update data into database using Stored Procedure in Three Tier Architecture in ASP.Net using C# and VB.Net.
ADO.Net will be used to perform update operation in ASP.Net.
Database
I have made use of the following table Customers with the schema as follows.
![Update data into database using Stored Procedure in Three Tier Architecture in ASP.Net]()
I have already inserted few records in the table.
![Update data into database using Stored Procedure in Three Tier Architecture in ASP.Net]()
Note: You can download the database table SQL by clicking the download link below.
Stored Procedure
This Stored Procedure accepts CustomerId, Name and Country parameters, which are used to UPDATE the records in Customers Table.
CREATE PROCEDURE .[Customers_UpdateCustomer]
@CustomerId INT
,@Name VARCHAR(100)
,@Country VARCHAR(50)
AS
BEGIN
UPDATE Customers
SET [Name] = @Name,
[Country] = @Country
WHERE [CustomerId] = @CustomerId
END
Implementing Update Stored Procedure in Three Tier Architecture
Data Transfer Object (DTO)
The DTO class consists of the following properties.
///<summary>
/// Customer Data Transfer Object.
///</summary>
public class Customer
{
public int CustomerId { get; set; }
public string Name { get; set; }
public string Country { get; set; }
}
Data Layer (CustomerDL class)
The CustomerDL class belongs to the Data Layer project and it consists of following method.
UpdateCustomer
This method accepts the Customer class object as parameter and uses Customers_UpdateCustomer Stored Procedure.
Inside this method, the parameters are passed as Dictionary object to the Execute method.
Finally, the Execute method returns the affected rows as scalar value.
public class CustomerDL
{
///<summary>
/// Update Customer data.
///</summary>
///<param name="customer">Customer object.</param>
///<returns>RowsAffected object.</returns>
public static int UpdateCustomer(Customer customer)
{
string sql = "Customers_UpdateCustomer";
Dictionary<string, object> parameters = new Dictionary<string, object>();
parameters.Add("@CustomerId", customer.CustomerId);
parameters.Add("@Name", customer.Name);
parameters.Add("@Country", customer.Country);
return sql.Execute(parameters, true);
}
}
Business Layer (CustomerBL class)
The CustomerBL class belongs the Business Layer and it implements the UpdateCustomer method which makes call to the UpdateCustomer method of the Data Layer class.
public class CustomerBL : ICustomerBL
{
///<summary>
/// Update Customer data.
///</summary>
///<param name="customer">Customer object.</param>
///<returns>RowsAffected object.</returns>
public int UpdateCustomer(Customer customer)
{
return CustomerDL.UpdateCustomer(customer);
}
}
Business Layer (ICustomerBL interface)
The interface implements the UpdateCustomer method of the CustomerBL class.
public interface ICustomerBL
{
///<summary>
/// Update Customer data.
///</summary>
///<param name="customer">Customer object.</param>
///<returns>RowsAffected object.</returns>
int UpdateCustomer(Customer customer);
}
Front End Layer
The Business Layer method will be called in the Front End layer.
HTML Markup
The HTML Markup consists of:
TextBox – For entering Id, Name and Country.
Button – Submitting the Form.
The Button has been assigned OnClick event handler.
<table border="0" cellpadding="0" cellspacing="0">
<tr>
<td style="width: 60px">Id<br />
<asp:TextBox ID="txtId" runat="server" Width="50px" />
</td>
<td style="width: 150px">Name<br />
<asp:TextBox ID="txtName" runat="server" Width="140px" />
</td>
<td style="width: 150px">Country:<br />
<asp:TextBox ID="txtCountry" runat="server" Width="140px" />
</td>
<td style="width: 200px">
<br />
<asp:Button Text="Update" runat="server" OnClick="OnSubmit" />
</td>
</tr>
</table>
Namespaces
You will need to import the following namespaces.
C#
using DTO;
using Data_Layer;
VB.Net
Imports DTO
Imports Data_Layer
Button Click event
When Update button is clicked, an object of Customer class is created and the CustomerId, Name and Country values are fetched from their respective TextBoxes are set.
Then, the UpdateCustomer method is called using the ICustomerBL interface and the data of Customer is updated into the database using Three Tier Architecture.
Finally, based on whether record is updated or not, appropriate message is displayed in JavaScript Alert Message Box using RegisterStartupScript method.
C#
protected void OnSubmit(object sender, EventArgs e)
{
Customer customer = new Customer
{
CustomerId = Convert.ToInt32(txtId.Text),
Name = txtName.Text,
Country = txtCountry.Text
};
ICustomerBL customerBL = new CustomerBL();
//Update Customer data.
int rowsAffected = customerBL.UpdateCustomer(customer);
if (rowsAffected > 0)
{
ClientScript.RegisterStartupScript(this.GetType(), "alert", "alert('Customer record updated.');", true);
}
else
{
ClientScript.RegisterStartupScript(this.GetType(), "alert", "alert('Customer not found.');", true);
}
}
VB.Net
Protected Sub OnSubmit(ByVal sender As Object, ByVal e As EventArgs)
Dim customer As Customer = New Customer With {
.CustomerId = txtId.Text,
.Name = txtName.Text,
.Country = txtCountry.Text
}
Dim customerBL As ICustomerBL = New CustomerBL()
'Update customer data.
Dim rowsAffected As Integer = customerBL.UpdateCustomer(customer)
If rowsAffected > 0 Then
ClientScript.RegisterStartupScript(Me.GetType(), "alert", "alert('Customer record updated.');", True)
Else
ClientScript.RegisterStartupScript(Me.GetType(), "alert", "alert('Customer not found.');", True)
End If
End Sub
Screenshot
![Update data into database using Stored Procedure in Three Tier Architecture in ASP.Net]()
Downloads
