Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Data Reader vs Data Set vs Other

Posted on 2010-11-12
13
Medium Priority
?
249 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 

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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

In my previous two articles we discussed Binary Serialization (http://www.experts-exchange.com/A_4362.html) and XML Serialization (http://www.experts-exchange.com/A_4425.html). In this article we will try to know more about SOAP (Simple Object Acces…
This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Screencast - Getting to Know the Pipeline

610 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