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

Posted on 2005-04-14
Medium Priority
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
  • 2

Expert Comment

ID: 13781284
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

by:Mohammed Nasman
ID: 13781385
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 ADO.net 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

ID: 13788567
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?

Accepted Solution

rajaloysious earned 2000 total points
ID: 13789055
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


Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article series is supposed to shed some light on the use of IDisposable and objects that inherit from it. In essence, a more apt title for this article would be: using (IDisposable) {}. I’m just not sure how many people would ge…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
Suggested Courses

621 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