CODEWITHSUNDEEP
c#asp.netsql-server
unknownsatan
unknownsatan

Reputation: 731

Returning a Dataset as an Object in a Method

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?

Upvotes: 3

Views: 37146

Answers (4)

Mitja Bonca
Mitja Bonca

Reputation: 4546

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

Upvotes: 7

A Ghazal
A Ghazal

Reputation: 2813

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

Upvotes: 2

Rohit Vats
Rohit Vats

Reputation: 81243

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

Upvotes: 0

paulsm4
paulsm4

Reputation: 121619

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

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 :)

Upvotes: 0

Related Questions