Link to home
Create AccountLog in
Avatar of vcbertini
vcbertiniFlag for United States of America

asked on

Data Reader vs Data Set vs Other

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

Avatar of Carl Tawn
Carl Tawn
Flag of United Kingdom of Great Britain and Northern Ireland image

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 

ASKER CERTIFIED SOLUTION
Avatar of jasonduan
jasonduan
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of vcbertini

ASKER

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
}
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.
Avatar of ajb2222
ajb2222

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();


@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.
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.
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?
Thanks, that worked once I adjusted my ID parameter.
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.
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 :)
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.
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?