Calling a Stored Procedure to list Data

I need to call a stored procedure in order to list products by either a text box based search or by clicking on a category.

I am very new to this and would really appreciate any knowledge anyone could share.

A quick example:

protected void Button1_Click(object sender, EventArgs e)
{
string connectionString = @."Data Source=Confute;Initial Catalog=AdventureWorks;Integrated Security=SSPI";

using (SqlConnection connection = new SqlConnection(connectionString))
{

SqlCommand cmd = new SqlCommand("sp_SearchByCategory", connection);
cmd.Parameters.Add("@.category", txtBox_Name.Text);

cmd.CommandType = CommandType.StoredProcedure;
connection.Open();
cmd.ExecuteNonQuery();

SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
sda.Fill(dt);
DataGrid dg = new DataGrid();
dg.DataSource = dt;
txtBox_Name.Text = dt.Rows[0][0].ToString();
}

}