Direct method from SQL command text to DataSet

Question

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.

score 96 · Accepted Answer · edited Nov 23 '19 at 23:17

96

public DataSet GetDataSet(string ConnectionString, string SQL)
{
    SqlConnection conn = new SqlConnection(ConnectionString);
    SqlDataAdapter da = new SqlDataAdapter();
    SqlCommand cmd = conn.CreateCommand();
    cmd.CommandText = SQL;
    da.SelectCommand = cmd;
    DataSet ds = new DataSet();

    ///conn.Open();
    da.Fill(ds);
    ///conn.Close();

    return ds;
}

edited Nov 23 '19 at 23:17

Al-Hanash Moataz

385
4
14

answered Jul 05 '11 at 15:06

Adriano Carneiro

57,693
12
90
123

score 30 · Answer 2 · edited Oct 06 '14 at 21:00

30

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;
}

edited Oct 06 '14 at 21:00

gunr2171

16,104
25
61
88

answered Jul 05 '11 at 15:07

Odd. I've been having a SQL problem recently and DataSet.Fill has been returning no records. table.Load however does return records. Thanks for sharing this method - wish I knew why I was having problems. – Andrew Vogel Oct 14 '13 at 20:25
If you were referring to Adrian's code, it uses the **DataAdapter** to fill (i.e. `da` and not `ds` - the two variable names are similar). – Oct 14 '13 at 20:31
Yes, that's what I mean. Any idea why using the DataAdapter to fill returns null, but using table.Load(cmd.ExecuteReader()) returns a row? – Andrew Vogel Oct 14 '13 at 21:56
I was thinking you may have accidentally typed in `ds.Fill` instead of `da.Fill`. – Oct 14 '13 at 21:59
Ah no, I wish that was it :). – Andrew Vogel Oct 14 '13 at 22:24
Thank you for this answer. I'm wondering, why you dont close the connection ? – Jeson Martajaya Nov 15 '13 at 13:36
1

The `using` statement calls `try...finalize` under the hood, and the `finalize` statement is supposed to close all connections and dispose of all memory references (note that Microsoft Access does NOT do this!). – Nov 15 '13 at 17:45

score 1 · Answer 3 · edited Jul 10 '18 at 16:54

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;
    }
}

Direct method from SQL command text to DataSet

3 Answers3

Linked