Add parameters to SqlCommand using C# in ASP.Net

Hi surbhik82,

Refer the below sample code.

First you need to change the stored procedure to assign where clause to it like below.

SQL

CREATE PROCEDURE smms.GetPPOM
    @FileID INT = NULL
AS
BEGIN
    SELECT * -- Assign column name instead of *
    FROM PPOM
    WHERE ID = @FileID OR @FileID IS NULL
END

Then in code you need to add a parameter to command object to pass the value for filter.

C#

string constr = ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
    using (SqlCommand cmd = new SqlCommand())
    {
        cmd.CommandText = "smms.GetPPOM";
        cmd.CommandType = CommandType.StoredProcedure;
        if (!string.IsNullOrEmpty(id))
        {
            cmd.Parameters.AddWithValue("@FileID", Convert.ToInt32(id));
        }
        cmd.Connection = con;
        con.Open();
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataTable dt = new DataTable();
        da.Fill(dt);
        con.Close();
        // Bind dt to datacontrol to display the result. 
    }
}