DataSet DS;
SqlConnection MyConnection;
SqlDataAdapter MyDataAdapter;
// Create a connection to the SQL Server.
MyConnection = new SqlConnection("server=(local);database=pubs;Trusted_Connection=yes");
// Create a DataAdapter, and then provide the name of the stored procedure.
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 Parameters collection.
MyDataAdapter.SelectCommand.Parameters.Add(new SqlParameter("@RowCount", SqlDbType.Int, 4));
// Set the direction for the parameter. This parameter returns the Rows returned.
MyDataAdapter.SelectCommand.Parameters["@RowCount"].Direction = ParameterDirection.Output;
DS = new DataSet();
// Create a new DataSet to hold the records.
MyDataAdapter.Fill(DS, "AuthorsByLastName");
// Fill the DataSet with the rows returned.
// Get the number of rows returned, and then 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!");
Grdauthors.DataSource = DS.Tables["AuthorsByLastName"].DefaultView;
// Bind the DataSet to the DataGrid.
// NOTE: If you do not call this method, the DataGrid is not displayed!
Grdauthors.DataBind();
MyDataAdapter.Dispose();
// Dispose of the DataAdapter.
MyConnection.Close();
// Close the connection