Direct Method to Retrieve DataSet from SQL Command in C#

In C# application development, it is common to query data from databases and store it in a DataSet for processing or binding. The questioner seeks a direct method to obtain a DataSet from SQL command text, avoiding the cumbersome manual conversion from SqlDataReader.

Core Method: Filling DataSet Using SqlDataAdapter

In the .NET Framework, SqlDataAdapter offers an efficient way to execute SQL queries and fill a DataSet. This method simplifies implementation by automatically managing connections through the Fill method.

public DataSet GetDataSet(string ConnectionString, string SQL)
{
    using (SqlConnection conn = new SqlConnection(ConnectionString))
    {
        SqlDataAdapter da = new SqlDataAdapter();
        SqlCommand cmd = new SqlCommand(SQL, conn);
        da.SelectCommand = cmd;
        DataSet ds = new DataSet();
        da.Fill(ds);
        return ds;
    }
}

In this implementation, the Fill method encapsulates the opening and closing of connections, reducing error risks and improving code readability.

Alternative: Loading Data with DataTable

If the application only needs to handle a single data table, DataTable can serve as a simplified target. This is achieved by using SqlCommand.ExecuteReader to obtain a data stream and DataTable.Load for filling.

DataSet GetDataSet(string sqlCommand, string connectionString)
{
    DataSet ds = new DataSet();
    using (SqlConnection conn = new SqlConnection(connectionString))
    {
        conn.Open();
        using (SqlCommand cmd = new SqlCommand(sqlCommand, conn))
        {
            DataTable table = new DataTable();
            table.Load(cmd.ExecuteReader());
            ds.Tables.Add(table);
        }
    }
    return ds;
}

This method provides more direct data manipulation but requires manual handling of connection states and resource disposal.

Analysis and Performance Considerations

The SqlDataAdapter method is suitable for batch data operations and complete DataSet filling, while the DataTable method may be more efficient in single-table scenarios. The use of using statements in the code ensures proper disposal of database connections and command objects.

Overall, the choice depends on specific requirements: SqlDataAdapter simplifies data filling and is recommended for complex DataSet operations, whereas the DataTable method offers flexibility for simple queries.