Solved

Data Reader vs Data Set vs Other

Posted on 2010-11-12
13
239 Views
Last Modified: 2012-05-10
I am new to C# and ASP.NET programming, and need some procedural clarification.

I have an application where I read some data from a SQL Server database via a stored procedure.   The structure would be as such:

Person 1
-- Name
-- Age
-- Gender

Person 2
-- Name
-- Age
-- Gender

Person 3
-- Name
-- Age
-- Gender

Assuming I need to:

1. Read the database
2. Populate "SOMETHING" with the result set (Data reader? Array? Data Set?)
3. Display the results on a web page

I have been reading extensively about this and what I am confused about is, what method should I use? I have read about DataGrids, List Tables, Repeaters.  I suspect I'm going to have to use a foreach loop, but can't seem to get my code to loop through more than once if there are multiple records.

I'm embarrassed to post my code, but in the example below, there are three records to the dataset (confirmed) but only one shows. Why? and am I using the right (most efficient) method for displaying this?
//Open connection and execute the reader

            conn.Open();

            SqlDataReader degreeReader = cmd.ExecuteReader();



            // create an instance for ArrayList 



            ArrayList degArrList = new ArrayList();



            while (degreeReader.Read())

            {



                // add the column value to the ArrayList 



                degArrList.Add(degreeReader["ID"].ToString());

                degArrList.Add(degreeReader["degreeName"].ToString());

                degArrList.Add(degreeReader["description"].ToString());

                degArrList.Add(degreeReader["admission"].ToString());

                degArrList.Add(degreeReader["standards"].ToString());

                degArrList.Add(degreeReader["advising"].ToString());

                degArrList.Add(degreeReader["prerequisites"].ToString());

                degArrList.Add(degreeReader["requirements"].ToString());

                degArrList.Add(degreeReader["otherInfo"].ToString());

                degArrList.Add(degreeReader["certification"].ToString());

            }



            foreach (Object objReader in degArrList)

            {



                if (!string.IsNullOrEmpty((string)degArrList[1]))

                {

                    lblDegreeName.Visible = true;

                    lblDegreeName.Text = string.Format("{0}", degArrList[1]);

                }

                if (!string.IsNullOrEmpty((string)degArrList[2]))

                {

                    litDescription.Visible = true;

                    litDescription.Text = string.Format("{0}", degArrList[2]);

                }

                if (!string.IsNullOrEmpty((string)degArrList[3]))

                {

                    lblAdmissionHdr.Visible = true;

                    litAdmission.Visible = true;

                    litAdmission.Text = string.Format("{0}", degArrList[3]);

                }

                if (!string.IsNullOrEmpty((string)degArrList[4]))

                {

                    lblStandardsHdr.Visible = true;

                    litStandards.Visible = true;

                    litStandards.Text = string.Format("{0}", degArrList[4]);

                }

                if (!string.IsNullOrEmpty((string)degArrList[5]))

                {

                    lblAdvisingHdr.Visible = true;

                    litAdvising.Visible = true;

                    litAdvising.Text = string.Format("{0}", degArrList[5]);

                }

                if (!string.IsNullOrEmpty((string)degArrList[6]))

                {

                    lblPrerequisitesHdr.Visible = true;

                    litPrerequisites.Visible = true;

                    litPrerequisites.Text = string.Format("{0}", degArrList[6]);

                }

                if (!string.IsNullOrEmpty((string)degArrList[7]))

                {

                    lblRequirementsHdr.Visible = true;

                    litRequirements.Visible = true;

                    litRequirements.Text = string.Format("{0}", degArrList[7]);

                }

                if (!string.IsNullOrEmpty((string)degArrList[8]))

                {

                    litOther.Visible = true;

                    litOther.Text = string.Format("{0}", degArrList[8]);

                }

                if (!string.IsNullOrEmpty((string)degArrList[9]))

                {

                    lblCertificationHdr.Visible = true;

                    litCertification.Visible = true;

                    litCertification.Text = string.Format("{0}", degArrList[9]);

                }

            }

            

            degreeReader.Close();

            conn.Close();

        }

Open in new window

0
Comment
Question by:vcbertini
  • 5
  • 4
  • 2
  • +2
13 Comments
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 34124165
As a simple starter approach you are on the right lines. DataReaders allow fast reading/loading of data. DataReaders, along with DataSets/DataTables, allow you to retrieve data. Grids, Repeaters, etc are server controls that allow you to display that data.

In your scenario a Repeater would be a sensible option, because you are showing a set of records, although a Grid would suffice too. The Repeater loops over the records in its DataSource and displays the data as defined in its Template.

Microsoft provide a lot of tutorials to get you started working with, and displaying data:

    http://www.asp.net

0
 
LVL 11

Accepted Solution

by:
jasonduan earned 500 total points
ID: 34124184
Change your code to:
           
            // create an instance for ArrayList
            ArrayList allArrList = new ArrayList();

            while (degreeReader.Read())
            {

                // add the column value to the ArrayList
                ArrayList degArrList = new ArrayList();
                allArrList.Add(degArrList);

                degArrList.Add(degreeReader["ID"].ToString());
                degArrList.Add(degreeReader["degreeName"].ToString());
                degArrList.Add(degreeReader["description"].ToString());
                degArrList.Add(degreeReader["admission"].ToString());
                degArrList.Add(degreeReader["standards"].ToString());
                degArrList.Add(degreeReader["advising"].ToString());
                degArrList.Add(degreeReader["prerequisites"].ToString());
                degArrList.Add(degreeReader["requirements"].ToString());
                degArrList.Add(degreeReader["otherInfo"].ToString());
                degArrList.Add(degreeReader["certification"].ToString());
            }

            foreach (ArrayList degArrList in allArrList)
            {

                if (!string.IsNullOrEmpty((string)degArrList[1]))
                {
                    lblDegreeName.Visible = true;
                    lblDegreeName.Text = string.Format("{0}", degArrList[1]);
                }
                if (!string.IsNullOrEmpty((string)degArrList[2]))
                {
                    litDescription.Visible = true;
                    litDescription.Text = string.Format("{0}", degArrList[2]);
                }
                if (!string.IsNullOrEmpty((string)degArrList[3]))
                {
                    lblAdmissionHdr.Visible = true;
                    litAdmission.Visible = true;
                    litAdmission.Text = string.Format("{0}", degArrList[3]);
                }
                if (!string.IsNullOrEmpty((string)degArrList[4]))
                {
                    lblStandardsHdr.Visible = true;
                    litStandards.Visible = true;
                    litStandards.Text = string.Format("{0}", degArrList[4]);
                }
                if (!string.IsNullOrEmpty((string)degArrList[5]))
                {
                    lblAdvisingHdr.Visible = true;
                    litAdvising.Visible = true;
                    litAdvising.Text = string.Format("{0}", degArrList[5]);
                }
                if (!string.IsNullOrEmpty((string)degArrList[6]))
                {
                    lblPrerequisitesHdr.Visible = true;
                    litPrerequisites.Visible = true;
                    litPrerequisites.Text = string.Format("{0}", degArrList[6]);
                }
                if (!string.IsNullOrEmpty((string)degArrList[7]))
                {
                    lblRequirementsHdr.Visible = true;
                    litRequirements.Visible = true;
                    litRequirements.Text = string.Format("{0}", degArrList[7]);
                }
                if (!string.IsNullOrEmpty((string)degArrList[8]))
                {
                    litOther.Visible = true;
                    litOther.Text = string.Format("{0}", degArrList[8]);
                }
                if (!string.IsNullOrEmpty((string)degArrList[9]))
                {
                    lblCertificationHdr.Visible = true;
                    litCertification.Visible = true;
                    litCertification.Text = string.Format("{0}", degArrList[9]);
                }
            }
           
            degreeReader.Close();
            conn.Close();
0
 

Author Comment

by:vcbertini
ID: 34124222
I had initially set up repeaters, but because of the parameter passing (there is an embedded call to a separate database in my code that I haven't programmed yet... I am being forced to write the C# code rather than sticking to the design side controls.

Here is how it will look:

Information Section 1    (from stored procedure #1)
- details
- details
- details

      -subdetails (from stored procedure #2)
      -subdetails (from stored procedure #2)
      -subdetails (from stored procedure #2)

- more details (from stored procedure #1)    

Information Section 2    (from stored procedure #1)
- details
- details
- details

      -subdetails (from stored procedure #2)
      -subdetails (from stored procedure #2)
      -subdetails (from stored procedure #2)

- more details (from stored procedure #1)    

Information Section 3    (from stored procedure #1)
- details
- details
- details

      -subdetails (from stored procedure #2)
      -subdetails (from stored procedure #2)
      -subdetails (from stored procedure #2)

- more details (from stored procedure #1)    

Will this be sufficient?

while (degreeReader.Read())
            {
display something
}
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 34124257
The looping construct is certainly correct. Calling Read() on the DataReader will return "true" until it runs out of records. So your only problem is how you want to display the output. I'm guessing you're planning to dump raw HTML, because you're limited on options if you don't want to use server controls.
0
 
LVL 6

Expert Comment

by:ajb2222
ID: 34124261
fill a datatable  with the data and databind it to a datagrid

after opening your connection this is your code.

        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataTabe dt;
        da.Fill(dt);
        Grid1.DataSource = dt;
        Grid1.DataBind();


0
 

Author Comment

by:vcbertini
ID: 34124325
@carl, it's not that I don't want to use server controls, I just had trouble passing the parameters to my datasources unless I did it in the code behind.  I am just controlling what shows up by manipulating the "visible" elements of my label and literal tags.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:vcbertini
ID: 34124350
Ugh. I feel so stupid... it's only displaying one record because my Stored Procedure is sending in the wrong ID number... so sorry. Thanks for all the great advice, though, it's helping me to understand more about how to read data. There's almost too many choices.
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 34124351
Oh, ok. You don't have to use markup based controls for databinding. You can add a Repeater to you markup but then populate and bind it to datasource using codebehind.

Simplisticly:

     // code to create connection/command, etc

    SqlDataReader reader = YourCommand.ExecuteReader();

    YourRepeater.DataSource = reader;
    YourRepeater.DataBind();

Open in new window


I'm guessing since you have tried it that you are aware of how to bind values in the repeater template?
0
 

Author Closing Comment

by:vcbertini
ID: 34124353
Thanks, that worked once I adjusted my ID parameter.
0
 
LVL 10

Expert Comment

by:P1ST0LPETE
ID: 34124358
What you probably want to focus on more is DataTable vs DataReader - Instead of DataSet vs. DataReader.  The reason being is a DataSet is basically an array of DataTables, and in most casses all you need is the DataTable.  So unless you need to hold multiple result sets, you only typically need the DataTable.

DataReader in almost all cases is going to be faster.  The reason being is the DataReader does not wait for all rows of the the sql query to be returned before it starts to populate whatever container (DataList, Repeater, GridView, etc) you're using.  This is why you are only seeing one row in your code above, because the DataReader deals with one row at a time, until is can gone through all the rows that are returned from the query result.

DataTable on the other hand gets the entire result set BEFORE you are allow to access the data with your code.  This is why the DataTable can be slower than the DataReader.  However the DataTable offers far more functionality than a DataReader. You typically want to use a DataTable when you need to do to multiple processes with the same set of data within the same code cycle.

All that being said, you will notice absolutely no difference in speed when you're talking about 3 rows.  The speed difference of a DataReader over DataTable is noticed when you are returning like 100 or more rows of data.
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 34124363
No problem, we all make mistakes (even when we've been doing it for years). You'll find as you gain experience the way you do things will evolve. I can't remember the last time I had an ASPX page that ever came anywhere near knowing anything about ow to talk to the database :)
0
 
LVL 6

Expert Comment

by:ajb2222
ID: 34124383
Ok, after reading what your output should look like I would create a user control that would display all the information for one employee.  the user control would have labels with the employee information and a datatable for the sub information.

The user control would be passed the employee information in it's constructor and would look up the detail information.

then as you read in the employee information, for each employee you create a new user control and add it onto the form.
0
 

Author Comment

by:vcbertini
ID: 34124396
One last problem... all the records are coming through, but only the third one is showing up on the page - is this because I am turning visible on and off? I should probably be building the HTML in the back-end, right?
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

706 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

21 Experts available now in Live!

Get 1:1 Help Now