Solved

Data Reader vs Data Set vs Other

Posted on 2010-11-12
13
245 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 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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

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…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

691 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