Mental Jetsam

By Peter Finch

Loading an SqlDataReader into a DataGridView

Posted by pcfinch on November 20, 2008

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.
    Add Column to DataGridView
  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;
        }
      }
    }
  5. Advertisements

4 Responses to “Loading an SqlDataReader into a DataGridView”

  1. roshini said

    can u tell me how to insert data through datagridview using stored procedure

  2. Vikas said

    Thank you. you have solved my problem. Thanks again.

  3. MediMan said

    Nice. And VERY FAST
    Here is sample Vb code
    Using cn As New sqlConnection
    Using cmd As New sqlCommand
    Call OpenConnectionSQL(cn, DataFolder) ‘Make your connection string and open it in the call or function
    cmd.CommandText = “SELECT * FROM Customers WHERE [Order ID]=:OrderID”
    cmd.Connection = cn
    cmd.Parameters.Add(New AdsParameter(“@OrderID”, OrderID))
    Using reader As AdsDataReader = cmd.ExecuteReader(CommandBehavior.KeyInfo)
    If reader.HasRows Then
    Using reader
    Dim table As DataTable = New DataTable()
    table.Load(reader)
    dgv_DepositsList.DataSource = table
    End Using
    End If
    End Using
    End Using
    End Using

  4. inspired said

    inspired…

    […]Loading an SqlDataReader into a DataGridView « Mental Jetsam[…]…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: