How to populate a gridview with table data from sql

Question

I want to populate a gridview with table data from SQL. I tried just returning my SqlDataReader object and using it as my datasource, but I am getting errors that the reader has been closed. I wanted to convert this to a DataSet and just return a dataset, but I couldn't find an easy way to convert the row data to a dataset. I've also read that DataSets are dead in .NET 3.5/4.0, is this true?

Here's my Data Layer method. It would be awesome if I could return something useable as a datasource:

public SqlDataReader GetSites()
{
    SqlConnection sqlCon = null;
    SqlDataReader rdr = null;
    try
    {
        sqlCon = new SqlConnection(StoredProcedures.conString);
        sqlCon.Open();
        SqlCommand cmd = new SqlCommand("GetSites", sqlCon);
        cmd.CommandType = CommandType.StoredProcedure;
        rdr = cmd.ExecuteReader();
        return rdr;
    }
    finally
    {
        if (sqlCon != null)
        {
            sqlCon.Close();
        }
        //if (rdr != null)
        //{
        //    rdr.Close();
        //}
    }
}

Answer 1

I suggest using a Typed-Dataset with typed table adapters. Add a new Typed-DataSet into your project and drop the tables from the server explorer in visual studio directly into your typed-dataset and configure it. You can then use that type dataset as the datasource of your datagrid

Answer 2

As Carlos Munoz has stated, you closed your sql connection. You need the SQL connection opened in order for the reader to read. Simply comment out

if (sqlCon != null)
{
        sqlCon.Close();
}

and you should be fine.

Another alternative is to use SqlDataAdapter which I prefer.

Here is an example...

public static DataSet GetDataSet(string sql, DatabaseType database)
{
    using ( var connection = new SqlConnection( GetConnectionString(database) ) )
    {
        using (var adapter = new SqlDataAdapter(sql, connection))
        {
            var temp = new DataSet();
            adapter.Fill(temp);
            return temp;
        }
    }
}

Just set the data source of your data grid to the Data set's table returned.

DGV.DataSource = DatabaseFunction.GetDataSet(sql, DatabaseType.Outage).Tables[0].DefaultView;

Answer 3

dataAdapter = new SqlDataAdapter(sqlQuery, DatabaseConnectionString);
SqlCommandBuilder builder = new SqlCommandBuilder(dataAdapter);
builder.GetUpdateCommand();
dataSet = new DataSet();
DataAdapter.Fill(dataSet);
dataGridView.DataSource = dataSet.Tables[0];

SqlCommandBuilder should be used if you want to update changes made in dataGridView back to SqlDatabase! Than you can write:

dataAdapter.Update(dataSet);

If you want to use new stuff, you can generate object model that corresponds to you SqlDatabase with SqlMetal tool. This will generate classes that have members as tables is your dataabse. After that you use LINQ query that is used as datasource of you dataGeidView.