Loading an SqlDataReader into a DataGridView

This is a simple example of how to load the results from an arbitrary SQL Query, contained in a SqlDataReader, into a DataGridView on a Windows Form (C# Client application). This is an example of a customised view and not an automatically generated one using the AutoGenerateColumns feature and it also demonstrates calling a SQL Server Stored Procedure from C#.

1. Create the DataGridView object e.g. dgvTrials
2. Add the Column names to the DataGridView using Visual Studio. For each of the columns set the DataPropertyName to the column name returned in the SQL query result set.
   
3. Create the SQL Server stored procedure.

   IF OBJECT_ID('sys.sp_getTrialsByUser') IS NOT NULL
    DROP PROCEDURE [sys].sp_getTrialsByUser
   GO
   CREATE PROCEDURE [sys].sp_getTrialsByUser (@username varchar(50)) AS
   select distinct TRIAL_ID, START_DATE, END_DATE, BOOKACRONYM
       from [sys].TRIALS t
   	where t.USERNAME = @username ;

4. Code up the SQL Query using a SqlConnection, SqlCommand and SqlDataReader and load the SqlDataReader results into a DataTable. Then bind the DataTable to the DataGridView.

   using (SqlConnection connection = new SqlConnection(sConnectString))
   {
     connection.Open();
     using (SqlCommand command = connection.CreateCommand())
     {
       command.CommandText = "[sys].sp_getTrialsByUser";
       command.CommandType = CommandType.StoredProcedure;
       command.Parameters.Add("@USERNAME", SqlDbType.VarChar, 50);
       command.Prepare();
       command.Parameters["@USERNAME"].Value = tbUserName.Text;
       SqlDataReader reader = command.ExecuteReader();
       using (reader)
       {
         DataTable table = new DataTable();
         table.Load(reader);
         dgvTrials.DataSource = table;
       }
     }
   }