In this article I will explain with an example, how to delete data from database using Stored Procedure in Three Tier Architecture in ASP.Net using C# and VB.Net.
ADO.Net will be used to perform insert operation in ASP.Net.
Note: The complete implementation of Three Tier Architecture using Stored Procedure is explained in my article Implement Three Tier Architecture using Stored Procedure in ASP.Net and it is required to be read first in order to understand this article.
 
 
Database
I have made use of the following table Customers with the schema as follows.
Delete data from database using Stored Procedure in Three Tier Architecture in ASP.Net
 
I have already inserted few records in the table.
Delete data from 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.
         Download SQL file
 
 
Stored Procedure
The following Stored Procedure will be used to Delete data from the SQL Server database table.
This Stored Procedure accepts CustomerId parameter, which is used to DELETE the records in Customers Table.
CREATE PROCEDURE [Customers_DeleteCustomer]
      @CustomerId INT
AS
BEGIN
      DELETE FROM [Customers]
      WHERE CustomerId = @CustomerId
END
 
 
Implementing Delete Stored Procedure in Three Tier Architecture
The complete implementation of Three Tier Architecture using Stored Procedure is explained in my article Implement Three Tier Architecture using Stored Procedure in ASP.Net and it is required to be read first in order to understand this article.
 
Data Transfer Object (DTO)
The Customer 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.
DeleteCustomer
This method accepts the Customer class object as parameter and uses Customers_DeleteCustomer Stored Procedure.
Inside this method, the parameter is passed as Dictionary object to the Execute method.
Finally, the Execute method returns the affected rows as scalar value.
public class CustomerDL
{
    ///<summary>
    /// Delete Customer data.
    ///</summary>
    ///<param name="Customer">Customer object.</param>
    ///<returns>RowsAffected</returns>
    public static int DeleteCustomer(Customer customer)
    {
        string sql = "Customers_DeleteCustomer";
 
        Dictionary<string, object> parameters = new Dictionary<string, object>();
        parameters.Add("@CustomerId", customer.CustomerId);
        return Convert.ToInt32(sql.Execute(parameters, true));
    }
}
 
 
Business Layer (CustomerBL class)
The CustomerBL class belongs the Business Layer and it implements the DeleteCustomer method which makes call to the DeleteCustomer method of the Data Layer class.
public class CustomerBL : ICustomerBL
{
    ///<summary>
    /// Delete Customer data.
    ///</summary>
    ///<param name="Customer">Customer object.</param>
    ///<returns>RowsAffected</returns>
    public int DeleteCustomer(Customer customer)
    {
        return CustomerDL.DeleteCustomer(customer);
    }
}
 
 
Business Layer (ICustomerBL interface)
The interface implements the DeleteCustomer method of the CustomerBL class.
public interface ICustomerBL
{
    ///<summary>
    /// Delete Customer data.
    ///</summary>
    ///<param name="Customer">Customer object.</param>
    ///<returns>RowsAffected object.</returns>
    int DeleteCustomer(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.
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: 200px">
            <br />
            <asp:Button Text="Delete" 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 Delete button is clicked, an object of Customer class is created and the CustomerId is fetched from the respective TextBox and set.
Then, the DeleteCustomer method is called using the ICustomerBL interface and the data of Customer is deleted from the database using Three Tier Architecture
Finally, based on whether record is deleted 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)
    };
    ICustomerBL customerBL = new CustomerBL();
 
    //Delete Customer data.
    int rowsAffected = customerBL.DeleteCustomer(customer);
 
    if (rowsAffected > 0)
    {
        ClientScript.RegisterStartupScript(this.GetType(), "alert", "alert('Customer record deleted.');", 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
    }
    Dim customerBL As ICustomerBL = New CustomerBL()
 
    'Delete customer data.
    Dim rowsAffected As Integer = customerBL.DeleteCustomer(customer)
 
    If rowsAffected > 0 Then
        ClientScript.RegisterStartupScript(Me.GetType(), "alert", "alert('Customer record deleted.');", True)
    Else
        ClientScript.RegisterStartupScript(Me.GetType(), "alert", "alert('Customer not found.');", True)
    End If
End Sub
 
 
Screenshot
Delete data from database using Stored Procedure in Three Tier Architecture in ASP.Net
 
 
Downloads
Download Code