Create a simple class based one tier architecture in ASP.Net

Here i have created a class in which i have written 4 function Data modification and retrieval using Query and SP.

HTML:

<form id="form1" runat="server">
    <div>
        <table border="0" cellpadding="0" cellspacing="0">
            <tr>
                <td>
                    Name
                </td>
                <td>
                    <asp:TextBox ID="txtName" runat="server" />
                </td>
            </tr>
            <tr>
                <td>
                    City
                </td>
                <td>
                    <asp:TextBox ID="txtCity" runat="server" />
                </td>
            </tr>
            <tr>
                <td colspan="2" align="center">
                    <asp:Button ID="Button1" Text="Save" OnClick="Save" runat="server" />
                </td>
            </tr>
        </table>
        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false">
            <Columns>
                <asp:BoundField DataField="Name" HeaderText="Name" />
                <asp:BoundField DataField="City" HeaderText="City" />
                <asp:TemplateField HeaderText="Delete Data">
                    <ItemTemplate>
                        <asp:Button ID="Button2" Text="Delete" runat="server" OnClick="Delete" />
                    </ItemTemplate>
                </asp:TemplateField>
            </Columns>
        </asp:GridView>
        <asp:GridView ID="GridView2" runat="server" AutoGenerateColumns="false">
            <Columns>
                <asp:BoundField DataField="Name" HeaderText="Name" />
                <asp:BoundField DataField="City" HeaderText="City" />
            </Columns>
        </asp:GridView>
    </div>
    </form>

Here is the class which i have added in App_Code folder.

Public Class Class2
    ''' Gets Data Using StoredProcedure    
    Public Function GetDataUsingSp(ByVal strproc As String, ByVal sql1 As SqlParameter()) As DataSet
        Dim strcon As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
        Dim ds As New DataSet()
        Using objcon As New SqlConnection(strcon)
            Using objcmd As New SqlCommand(strproc, objcon)
                Using da As New SqlDataAdapter(objcmd)
                    objcmd.CommandType = CommandType.StoredProcedure

                    For Each sqtemp As SqlParameter In sql1
                        If sqtemp IsNot Nothing Then
                            objcmd.Parameters.Add(sqtemp)
                        End If
                        da.SelectCommand = objcmd
                    Next
                    da.Fill(ds)
                    Return ds
                End Using
            End Using
        End Using
    End Function

    ''' Gets Data Using Query  
    Public Function GetDataUsingQuery(ByVal strqry As String, ByVal sql1 As SqlParameter()) As DataSet
        Dim strcon As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString

        Using objcon As New SqlConnection(strcon)
            Using objcmd As New SqlCommand(strqry, objcon)
                Using da As New SqlDataAdapter(objcmd)
                    For Each sqtemp As SqlParameter In sql1
                        If sqtemp IsNot Nothing Then
                            objcmd.Parameters.Add(sqtemp)
                        End If
                    Next
                    Dim ds As New DataSet()
                    da.Fill(ds)
                    Return ds
                End Using
            End Using
        End Using
    End Function

    ''' Insert Upate Delete Data Using Sp
    Public Function InsertUpateDeleteUsingSP(ByVal strproc As String, ByVal sql1 As SqlParameter()) As Integer
        Dim strcon As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
        Using objcon As New SqlConnection(strcon)
            Using objcmd As New SqlCommand(strproc, objcon)
                objcmd.CommandType = CommandType.StoredProcedure
                objcon.Open()
                For Each sqtemp As SqlParameter In sql1
                    If sqtemp IsNot Nothing Then
                        objcmd.Parameters.Add(sqtemp)
                    End If
                Next

                Dim k As Integer = objcmd.ExecuteNonQuery()
                objcon.Close()
                Return k
            End Using
        End Using
    End Function

    Public Function insertupadate(ByVal str As String, ByVal sql1 As SqlParameter()) As Integer
        Dim strcon As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
        Using objcon As New SqlConnection(strcon)
            Using objcmd As New SqlCommand(str, objcon)
                For Each sqtemp As SqlParameter In sql1
                    If sqtemp IsNot Nothing Then
                        objcmd.Parameters.Add(sqtemp)
                    End If
                Next
                objcon.Open()
                Dim k As Integer = objcmd.ExecuteNonQuery()
                objcon.Close()
                Return k
            End Using
        End Using
    End Function
End Class

HTML.aspx.vb

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    If Not Me.IsPostBack Then
        Me.PopulateUsingQuery()
        Me.PopulateUsingSp()
    End If
End Sub

Protected Sub Delete(ByVal sender As Object, ByVal e As EventArgs)
    Dim row As GridViewRow = TryCast(TryCast(sender, Button).NamingContainer, GridViewRow)
    Dim name As String = row.Cells(0).Text
    Dim obj As New Class2()
    Dim sp1 As SqlParameter() = New SqlParameter(0) {}
    sp1(0) = New SqlParameter("@Name", name)
    Dim k As Integer = obj.InsertUpateDeleteUsingSP("DeleteName", sp1)
    Response.Redirect(Request.Url.AbsoluteUri)
End Sub

Protected Sub Save(ByVal sender As Object, ByVal e As EventArgs)
    Dim query As String = "INSERT INTO Temp(name,city) VALUES(@Name,@City)"
    Dim sp1 As SqlParameter() = New SqlParameter(1) {}
    sp1(0) = New SqlParameter("@Name", Me.txtName.Text.Trim())
    sp1(1) = New SqlParameter("@City", Me.txtCity.Text.Trim())
    Dim obj As New Class2()
    Dim k As Integer = obj.insertupadate(query, sp1)
    If k > 0 Then
        ClientScript.RegisterClientScriptBlock(Me.[GetType](), "alert", "alert('Added Successfully')", True)
    End If

End Sub

Private Sub PopulateUsingSp()
    Dim obj As New Class2()
    Dim ds As New DataSet()
    Dim sp1 As SqlParameter() = New SqlParameter(0) {}
    sp1(0) = New SqlParameter("@Name", "Azim")
    ds = obj.GetDataUsingSp("GetNames", sp1)
    Me.GridView2.DataSource = ds
    Me.GridView2.DataBind()
End Sub

Private Sub PopulateUsingQuery()
    'Pass this if you want to Pass parameter to the query
    'string query = "SELECT * FROM Temp WHERE name = @Name ";
    Dim query As String = "SELECT * FROM Temp"
    Dim ds As New DataSet()
    Dim obj As New Class2()
    Dim sp1 As SqlParameter() = New SqlParameter(0) {}
    ' sp1[0] = new SqlParameter("@Name", "Azim");
    ds = obj.GetDataUsingQuery(query, sp1)
    If ds.Tables(0).Rows.Count > 0 Then
        GridView1.DataSource = ds
        GridView1.DataBind()
    End If
End Sub

In C# language Class1:

/// Gets Data Using StoredProcedure    
public DataSet GetDataUsingSp(string strproc, SqlParameter[] sql1)
{
    string strcon = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    DataSet ds = new DataSet();
    using (SqlConnection objcon = new SqlConnection(strcon))
    {
        using (SqlCommand objcmd = new SqlCommand(strproc, objcon))
        {
            using (SqlDataAdapter da = new SqlDataAdapter(objcmd))
            {
                objcmd.CommandType = CommandType.StoredProcedure;

                foreach (SqlParameter sqtemp in sql1)
                {
                    if (sqtemp != null)
                    {
                        objcmd.Parameters.Add(sqtemp);
                    }
                    da.SelectCommand = objcmd;
                }
                da.Fill(ds);
                return ds;
            }
        }
    }
}

/// Gets Data Using Query  
public DataSet GetDataUsingQuery(string strqry, SqlParameter[] sql1)
{
    string strcon = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;

    using (SqlConnection objcon = new SqlConnection(strcon))
    {
        using (SqlCommand objcmd = new SqlCommand(strqry, objcon))
        {
            using (SqlDataAdapter da = new SqlDataAdapter(objcmd))
            {
                foreach (SqlParameter sqtemp in sql1)
                {
                    if (sqtemp != null)
                    {
                        objcmd.Parameters.Add(sqtemp);
                    }
                }
                DataSet ds = new DataSet();
                da.Fill(ds);
                return ds;
            }
        }
    }
}

/// Insert Upate Delete Data Using Sp
public int InsertUpateDeleteUsingSP(string strproc, SqlParameter[] sql1)
{
    string strcon = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection objcon = new SqlConnection(strcon))
    {
        using (SqlCommand objcmd = new SqlCommand(strproc, objcon))
        {
            objcmd.CommandType = CommandType.StoredProcedure;
            objcon.Open();
            foreach (SqlParameter sqtemp in sql1)
            {
                if (sqtemp != null)
                {
                    objcmd.Parameters.Add(sqtemp);
                }
            }

            int k = objcmd.ExecuteNonQuery();
            objcon.Close();
            return k;
        }
    }
}

public int insertupadate(string str, SqlParameter[] sql1)
{
    string strcon = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection objcon = new SqlConnection(strcon))
    {
        using (SqlCommand objcmd = new SqlCommand(str, objcon))
        {
            foreach (SqlParameter sqtemp in sql1)
            {
                if (sqtemp != null)
                {
                    objcmd.Parameters.Add(sqtemp);
                }
            }
            objcon.Open();
            int k = objcmd.ExecuteNonQuery();
            objcon.Close();
            return k;
        }
    }
}

HTML.aspx.C#

protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        this.PopulateUsingQuery();
        this.PopulateUsingSp();
    }
}

protected void Delete(object sender, EventArgs e)
{
    GridViewRow row = (sender as Button).NamingContainer as GridViewRow;
    string name = row.Cells[0].Text;
    Class1 obj = new Class1();
    SqlParameter[] sp1 = new SqlParameter[1];
    sp1[0] = new SqlParameter("@Name", name);
    int k = obj.InsertUpateDeleteUsingSP("DeleteName", sp1);
    Response.Redirect(Request.Url.AbsoluteUri);
}

protected void Save(object sender, EventArgs e)
{
    string query = "INSERT INTO Temp(name,city) VALUES(@Name,@City)";
    SqlParameter[] sp1 = new SqlParameter[2];
    sp1[0] = new SqlParameter("@Name", this.txtName.Text.Trim());
    sp1[1] = new SqlParameter("@City", this.txtCity.Text.Trim());
    Class1 obj = new Class1();
    int k = obj.insertupadate(query, sp1);
    if (k > 0)
    {
        ClientScript.RegisterClientScriptBlock(this.GetType(), "alert", "alert('Added Successfully')", true);
    }

}

private void PopulateUsingSp()
{
    Class1 obj = new Class1();
    DataSet ds = new DataSet();
    SqlParameter[] sp1 = new SqlParameter[1];
    sp1[0] = new SqlParameter("@Name", "Azim");
    ds = obj.GetDataUsingSp("GetNames", sp1);
    this.GridView2.DataSource = ds;
    this.GridView2.DataBind();
}

private void PopulateUsingQuery()
{
    //Pass this if you want to Pass parameter to the query
    //string query = "SELECT * FROM Temp WHERE name = @Name ";
    string query = "SELECT * FROM Temp";
    DataSet ds = new DataSet();
    Class1 obj = new Class1();
    SqlParameter[] sp1 = new SqlParameter[1];
    // sp1[0] = new SqlParameter("@Name", "Azim");
    ds = obj.GetDataUsingQuery(query, sp1);
    if (ds.Tables[0].Rows.Count > 0)
    {
        GridView1.DataSource = ds;
        GridView1.DataBind();
    }
}

SQL:

CREATE TABLE [dbo].[Temp](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](50) NOT NULL,
	[City] [varchar](50) NOT NULL,
 CONSTRAINT [PK_Temp] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
-- GetNames 'azim'
ALTER PROCEDURE GetNames
	@Name VARCHAR(50)
AS
BEGIN	
	SET NOCOUNT ON;
	SELECT * FROM Temp WHERE [Name] =  @Name
END
GO

-- DeleteName 'azim'
Create PROCEDURE DeleteName
	@Name VARCHAR(50)
AS
BEGIN	
	SET NOCOUNT ON;	
	DELETE from Temp WHERE Name = @Name
END
GO