What is the correct way to use DataSets in ADO.NET?

Posted on 2005-04-14
Last Modified: 2008-02-01
I am learning ADO.NET (I am familar with ADO) and had worked out the basic way to load data using a DataReader:

                  string sql = "select categories.NAME from categories";
                  string retVal = "";

                  OdbcCommand odbcCommand = new OdbcCommand (sql,odbcConnection);
                  OdbcDataReader odbcDataReader = odbcCommand.ExecuteReader ();
                  if (odbcDataReader.HasRows )
                        while (odbcDataReader.Read ())
                              if (retVal != "") retVal += ",";
                              retVal = retVal + odbcDataReader.GetString(0);

However I then came across a problem where I needed to get another resultset while the first was open. It seems you can't do this with DataReader and need to use a DataSet, so I have "re-written" the above as:

                  string retVal = "";
                  string sql = "select NAME from categories";
                  OdbcCommand odbcCommand = new OdbcCommand (sql,odbcConnection);
                  OdbcDataAdapter accountsDA = new OdbcDataAdapter ();
                  catDA.SelectCommand = odbcCommand;
                  DataSet catDS = new DataSet();
                  catDA.Fill (catDS,"categories");

                  if (catDS.Tables["categories"].Rows.Count > 0)  
                        foreach (DataRow pRow in catDS.Tables["categories"].Rows )
                              if (retVal != "") retVal += ",";
                              retVal = retVal + pRow["NAME"].ToString();

I have three questions:

1. Is this the correct way to use a DataSet and DataAdapter - am I doing anything wrong? For example you don't appear to need to close the DataSet or DataAdapter. Is there anything I could be doing better?

2. One obvious difference between the DataReader and the DataSet is lack of strongly typed functions to return values - there is no GetInt32, GetDateTime, GetString etc. How do you get non-string values back?

3. What is the best C# ADO.NET book available? I want a book I can work through examples, not a reference book.
Question by:purplesoup
    LVL 8

    Expert Comment

    1. I dont see anything wrong
    2. You should use Covert.ToDouble, Covert.ToDecimal(pRow["NAME"]) etc or Double.Parse(pRow["NAME"].ToString()) etc
    3.  ??

    LVL 22

    Expert Comment

    1. Yes

    2. int x = MyDataRow[0];

    3. I'm always go to read comments in amazon before buying the book, you could get a good reviews there, here are a list of some of Books

    in your example, you could try to use Sql statment with command object to make the update in one statmenet
    update mytable, set myfield = retvalue + myfield

    Author Comment

    Three questions on the responses so far:

    I notice subtimes rajaloysious uses

    pRow["NAME"].ToString() and other times just pRow["NAME"] - what does the latter do? Which is better?

    Regarding books, all the Amazon books I was interested in had a one star review saying it was rubbish followed by a five star review saying it was brilliant - I wondered if anyone here could recommend a book they had read?

    Also no one has said if there is a DataSet equivalent to odbcDataReader.Read () - how can I get the result if I know there will only be one row returned?
    LVL 8

    Accepted Solution

    ToAoM passed on this site for comparison on Parse and Convert Performance comparisons

    If there is one records and one field (only one value)
    then you can use ExecuteScalar method


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Introduction                                                 Was the var keyword really only brought out to shorten your syntax? Or have the VB language guys got their way in C#? What type of variable is it? All will be revealed.   Also called…
    We all know that functional code is the leg that any good program stands on when it comes right down to it, however, if your program lacks a good user interface your product may not have the appeal needed to keep your customers happy. This issue can…
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now