Return single row from Database using Business class in ASP.Net

Hi nauna,

Refer below sample.

SQL

CREATE PROCEDURE GetCustomerById
	@CustomerId INT
AS
BEGIN
	SELECT CustomerId
		  ,Name
		  ,Country
	FROM Customers 	WHERE CustomerId = @CustomerId
END

HTML

Id :<asp:TextBox ID="txtCustomerId" runat="server"></asp:TextBox><br />
<asp:Button Text="Display" runat="server" OnClick="GetData" />
<asp:GridView runat="server" AutoGenerateColumns="false" ID="gvCustomers">
    <Columns>
        <asp:BoundField DataField="CustomerId" HeaderText="CustomerId" />
        <asp:BoundField DataField="Name" HeaderText="Name" />
        <asp:BoundField DataField="Country" HeaderText="Country" />
    </Columns>
</asp:GridView>

Namespaces

C#

using System.Configuration;
using System.Data.SqlClient;
using System.Data;

VB.Net

Imports System.Configuration
Imports System.Data.SqlClient
Imports System.Data

Code

CustomerProp.cs

public class CustomerProp
{
    public int CustomerId { get; set; }
}

CS.aspx.cs

protected void GetData(object sender, EventArgs e)
{
    DataTable dt = GetDataById(Convert.ToInt16(txtCustomerId.Text));
    gvCustomers.DataSource = dt;
    gvCustomers.DataBind();
}

private DataTable GetDataById(int id)
{
    CustomerProp obj = new CustomerProp();
    obj.CustomerId = id;
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand("GetCustomerById", con))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@CustomerId", obj.CustomerId);
            using (SqlDataAdapter da = new SqlDataAdapter(cmd))
            {
                DataTable dt = new DataTable();
                da.Fill(dt);
                return dt;
            }
        }
    }
}

CustomerPropVB.vb

Public Class CustomerPropVB
    Public Property CustomerId As Integer
End Class

VB.asp.vb

Protected Sub GetData(ByVal sender As Object, ByVal e As EventArgs)
    Dim dt As DataTable = GetDataById(Convert.ToInt16(txtCustomerId.Text))
    gvCustomers.DataSource = dt
    gvCustomers.DataBind()
End Sub
Private Function GetDataById(ByVal id As Integer) As DataTable
    Dim obj As CustomerPropVB = New CustomerPropVB()
    obj.CustomerId = id
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As SqlConnection = New SqlConnection(constr)
        Using cmd As SqlCommand = New SqlCommand("GetCustomerById", con)
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.AddWithValue("@CustomerId", obj.CustomerId)
            Using da As SqlDataAdapter = New SqlDataAdapter(cmd)
                Dim dt As DataTable = New DataTable()
                da.Fill(dt)
                Return dt
            End Using
        End Using
    End Using
End Function

Screenshot