C# filling a dataset from the database

Question

I have some troubles with dataset in c#. I want to fill it with content from the database, but can't get it to work.

Here is my code:

from the main database layer containing the connection string

public static SqlCommand GetDbCommand(string sql)
        {
            if (dbconn.State.ToString().CompareTo("Open") != 0)
                Open();
            if (dbCmd == null)
            {
                dbCmd = new SqlCommand(sql, dbconn);
            }
            dbCmd.CommandText = sql;
            return dbCmd;
        }

This is the method that should fill the dataset from my DBMovie class

public static DataSet GetMovieSet()
    {
        DataSet movieSet = new DataSet();

        string sql = "select * from Movie";
        dbCmd = DBConnection.GetDbCommand(sql);
        SqlDataAdapter da = new SqlDataAdapter();

        da.Fill(movieSet);

        DBConnection.Close();
        return movieSet;

    }

How do I connect the dataAdapter together with the database connection?

Answer 1

Connect the adapter to the command like so:

    dbCmd = DBConnection.GetDbCommand(sql);
    SqlDataAdapter da = new SqlDataAdapter();
    da.SelectCommand = dbCmd; //Add this
    da.Fill(movieSet);