Call SQL Server Stored Procedures in ASP.NET by Using Visual C# .NET

private void btnGetAuthors_Click(object sender, System.EventArgs e)
 {
  //Create a connection to the SQL Server; modify the connection string for your environment.
  //SqlConnection MyConnection = new SqlConnection("server=(local);database=pubs;Trusted_Connection=yes");
  SqlConnection MyConnection = new SqlConnection("server=(local);database=pubs;UID=myUser;PWD=myPassword;");

  //Create a DataAdapter, and then provide the name of the stored procedure.
  SqlDataAdapter MyDataAdapter = new SqlDataAdapter("GetAuthorsByLastName", MyConnection);

  //Set the command type as StoredProcedure.
  MyDataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;

  //Create and add a parameter to Parameters collection for the stored procedure.
  MyDataAdapter.SelectCommand.Parameters.Add(new SqlParameter("@au_lname", SqlDbType.VarChar, 40));

  //Assign the search value to the parameter.
  MyDataAdapter.SelectCommand.Parameters["@au_lname"].Value = (txtLastName.Text).Trim();

  //Create and add an output parameter to the Parameters collection. 
  MyDataAdapter.SelectCommand.Parameters.Add(new SqlParameter("@RowCount", SqlDbType.Int, 4));

  //Set the direction for the parameter. This parameter returns the Rows that are returned.
  MyDataAdapter.SelectCommand.Parameters["@RowCount"].Direction = ParameterDirection.Output;

  //Create a new DataSet to hold the records.
  DataSet DS = new DataSet();
  
  //Fill the DataSet with the rows that are returned.
  MyDataAdapter.Fill(DS, "AuthorsByLastName");

  //Get the number of rows returned, and assign it to the Label control.
  //lblRowCount.Text = DS.Tables(0).Rows.Count().ToString() & " Rows Found!"
  lblRowCount.Text = MyDataAdapter.SelectCommand.Parameters[1].Value + " Rows Found!";

  //Set the data source for the DataGrid as the DataSet that holds the rows.
  GrdAuthors.DataSource = DS.Tables["AuthorsByLastName"].DefaultView;

  //NOTE: If you do not call this method, the DataGrid is not displayed!
  GrdAuthors.DataBind();

  MyDataAdapter.Dispose(); //Dispose the DataAdapter.
  MyConnection.Close(); //Close the connection.
 }