Refer Below sample code for your reference.
SQL
CREATE PROCEDURE GetCustomerDetails
@City VARCHAR(20) = NULL
AS
BEGIN
SELECT Top 10 CustomerID,CompanyName,City,ContactName FROM Customers
Where City =@City OR @City IS NULL
END
GO
HTML
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
City <asp:TextBox ID="txtCity" runat="server"></asp:TextBox> <asp:Button
ID="btnSearch" runat="server" OnClick="Search" Text="Search" />
<asp:GridView ID="GridView1" runat="server">
</asp:GridView>
</div>
</form>
</body>
</html>
C#
protected void Page_Load(object sender, EventArgs e)
{
if (!this.IsPostBack)
{
PopualteGrid();
}
}
protected void Search(object sender, EventArgs e)
{
PopualteGrid();
}
private void PopualteGrid()
{
String strConnString = ConfigurationManager.ConnectionStrings["ConStr"].ConnectionString;
SqlConnection con = new SqlConnection(strConnString);
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "GetCustomerDetails";
if (!string.IsNullOrEmpty(txtCity.Text.Trim()))
{
cmd.Parameters.Add("@City", SqlDbType.VarChar).Value = txtCity.Text.Trim();
}
cmd.Connection = con;
con.Open();
GridView1.EmptyDataText = "No Records Found";
GridView1.DataSource = cmd.ExecuteReader();
GridView1.DataBind();
con.Close();
}
VB.Net
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
If Not Me.IsPostBack Then
PopualteGrid()
End If
End Sub
Protected Sub Search(ByVal sender As Object, ByVal e As EventArgs)
PopualteGrid()
End Sub
Private Sub PopualteGrid()
Dim strConnString As String = ConfigurationManager.ConnectionStrings("ConStr").ConnectionString
Dim con As SqlConnection = New SqlConnection(strConnString)
Dim cmd As SqlCommand = New SqlCommand()
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "GetCustomerDetails"
If Not String.IsNullOrEmpty(txtCity.Text.Trim()) Then
cmd.Parameters.Add("@City", SqlDbType.VarChar).Value = txtCity.Text.Trim()
End If
cmd.Connection = con
con.Open()
GridView1.EmptyDataText = "No Records Found"
GridView1.DataSource = cmd.ExecuteReader()
GridView1.DataBind()
con.Close()
End Sub
Screenshot
