[c#] Direct method from SQL command text to DataSet

What is the most direct route to get a DataSet if I have a sql command?

string sqlCommand = "SELECT * FROM TABLE";
string connectionString = "blahblah";

DataSet = GetDataSet(sqlCommand,connectionString);

GetDataSet()
{
   //...?
}

I started with SqlConnection and SqlCommand, but the closest thing I see in the API is SqlCommand.ExecuteReader(). With this method, I'll need to get a SqlDataReader and then convert this to a DataSet manually. I figure there is a more direct route to accomplish the task.

If easier, a DataTable will also fit my goal.

This question is related to c# sql dataset

The answer is


Just finish it up.

string sqlCommand = "SELECT * FROM TABLE";
string connectionString = "blahblah";

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

public static string textDataSource = "Data Source=localhost;Initial Catalog=TEST_C;User ID=sa;Password=P@ssw0rd";

public static DataSet LoaderDataSet(string StrSql)      
{
    SqlConnection cnn;            
    SqlDataAdapter dad;
    DataSet dts = new DataSet();
    cnn = new SqlConnection(textDataSource);
    dad = new SqlDataAdapter(StrSql, cnn);
    try
    {
        cnn.Open();
        dad.Fill(dts);
        cnn.Close();

        return dts;
    }
    catch (Exception)
    {

        return dts;
    }
    finally
    {
        dad.Dispose();
        dts = null;
        cnn = null;
    }
}