Returning a Dataset as an Object in a Method

Question

I am writing a method that will query a table and return a Dataset object containing the specified column. Moreover, I have a problem with my Username & Password, so I am using Windows authentication for the same but I am not too sure about that in my snippet I have written till now.

 protected void GetProgramList()
    {
        SqlConnection cn = new SqlConnection("server=Daffodils-PC/sqlexpress;Database=Assignment1;Trusted_Connection=Yes;");
        SqlCommand cmd = new SqlCommand("SELECT ProgramName FROM Program", cn);
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataSet1 ds1 = new DataSet1();

    }

I have been trying to follow official MS documentation but I am not sure where I am going? Can someone help me with some links or snippets?

What you are looking for is explained here. http://stackoverflow.com/questions/6073382/read-sql-table-into-c-sharp-datatable — test, Jul 22 '12 at 06:07

score 7 · Accepted Answer · edited Jul 01 '16 at 20:52

I would say you have 2 options here: 1. to make a DataSet class variable, so its reference can be accessed from all over the class (set its access modifier to public so it can be accessed from other classes) 2. or create a method with its return type of DataSet. But in this case on the other side must be set to receive the DataSet as well:

//2. solution:

    private void GetData()
    {
        //from inside some method:
        DataSet ds = GetProgramList();
    }

    protected DataSet GetProgramList()
    {
        DataSet ds1 = new DataSet();
        using (SqlConnection cn = new SqlConnection("server=Daffodils-PC/sqlexpress;Database=Assignment1;Trusted_Connection=Yes;"))
        {
            using (SqlDataAdapter da = new SqlDataAdapter(@"SELECT ProgramName FROM Program", cn))
                da.Fill(ds1, "TableName1");
        }
        return ds1;
    }
    //


//1. solution:
class YourClass
{
    DataSet ds1;
    protected void GetProgramList()
    {
        SqlConnection cn = new SqlConnection("server=Daffodils-PC/sqlexpress;Database=Assignment1;Trusted_Connection=Yes;");
        SqlCommand cmd = new SqlCommand("SELECT ProgramName FROM Program", cn);
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        ds1 = new DataSet();
    }
}

The mistake I am making here is, I am not returning anything, I am using a void method. What return type should I use for a Dataset? — unknownsatan, Jul 22 '12 at 06:23
DataSet return type of course - if you use my 2nd solution! ... If you use my 1st solution you use void return type!! — Mitja Bonca, Jul 22 '12 at 06:31
One more question, what kind of variable type should I use to store the returned Dataset? — unknownsatan, Jul 22 '12 at 06:36

score 2 · Answer 2 · answered Feb 22 '14 at 00:33

Place your connection string in AppSettings section in app.config or web.config

   public string GetSqlConnection()
    {
        return  System.Configuration.ConfigurationManager.AppSettings["SqlConnectionString"];
    }



  public DataSet getDataSet(string sql)
    {
        DataSet ds = new DataSet();
        SqlConnection conn = new SqlConnection(GetSqlConnection());
        SqlDataAdapter da = new SqlDataAdapter(sql, conn);
        da.Fill(ds);
        conn.Close();
        conn.Dispose();
        da.Dispose();
        return ds;
    }

score 0 · Answer 3 · answered Jul 22 '12 at 06:09

Suggestion: "use" System.Data and System.Data.SqlClient, and use a "SqlDataReader":

http://www.csharp-station.com/Tutorial/AdoDotNet/lesson04

Either read everything in your routine (generally preferred), or pass the SqlDataReader back to the caller (as a function return).

And be sure to .Close() the reader when you're done :)

score 0 · Answer 4 · answered Jul 22 '12 at 06:22

0

SQLDataAdapter basic will get you started with the basics of creating a connection and consuming it in your code.

answered Jul 22 '12 at 06:22

Rohit Vats

79,502
12
161
185

Returning a Dataset as an Object in a Method

4 Answers4