Populate a DataGridView with SqlDataReader

In order to display the data in a SqlDataReader object in a DataGridView control, you first need to load the data into a DataTable object. The code below is in C# and the Connection String is for a SQL Server database connection.

[csharp]
using System.Data.SqlClient;

SqlConnection con = new SqlConnection();
SqlCommand cmd = new SqlCommand();
SqlDataReader dr;

// set the connection string
con.ConnectionString = “Data Source=SqlServerName;Initial Catalog=DbName;Integrated Security=True”
con.Open();

string SQL = “SELECT * FROM tbl_Users”;

// create the SQL command
cmd = new SqlCommand(SQL, con);

// execute the SQL
dr = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);

DataTable table = new DataTable();
table.Load(dr);

reader.Close();
reader.Dispose();
con.Close();
con.Dispose();
cmd.Dispose();
cmd = null;

// Display the data in the DataGridView control…
DataGridView1.DataSource = table;
[/csharp]