Solved

Binding a datareader to a gridview

Posted on 2010-08-25
9
479 Views
Last Modified: 2012-05-10
Hi Experts,

Does anyone know why the gridview isn't populated.

please see code.

thanks
OracleConnection con = new OracleConnection();
        con = new OracleConnection("Data Source=***; User ID=***;Password=****");
        OracleCommand cmd = new OracleCommand();
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Connection = con;

        cmd.CommandText = "HE_GETGRADES";
        cmd.Parameters.Add("grades_cursor", OracleType.Cursor);
        cmd.Parameters["grades_cursor"].Direction = ParameterDirection.ReturnValue;
        cmd.Parameters.Add("vp_id", OracleType.Number).Value = "15000028435389";

        con.Open();

        OracleDataReader reader;
        bool haveRows = false;

        reader = cmd.ExecuteReader();

        while (reader.Read())
        {
            Console.WriteLine("=========================");
            Console.WriteLine("Column1 has: " + (Convert.IsDBNull(reader.GetValue(0)) ? "null" : reader.GetString(0).ToString()));
            Console.WriteLine("Column2 has: " + (Convert.IsDBNull(reader.GetValue(1)) ? "null" : reader.GetString(1).ToString()));
            Console.WriteLine("Column3 has: " + (Convert.IsDBNull(reader.GetValue(2)) ? "null" : reader.GetDecimal(2).ToString()));
            Console.WriteLine("Column4 has: " + (Convert.IsDBNull(reader.GetValue(3)) ? "null" : reader.GetDecimal(3).ToString()));
            //Console.WriteLine("Column5 has: " + (Convert.IsDBNull(reader.GetValue(4)) ? "null" : reader.GetOracleDecimal(4).ToString()));
            // Console.WriteLine("Column6 has: " + (Convert.IsDBNull(reader.GetValue(5)) ? "null" : reader.GetOracleDecimal(5).ToString()));
            Console.WriteLine("Column7 has: " + (Convert.IsDBNull(reader.GetValue(6)) ? "null" : reader.GetString(6).ToString()));
            Console.WriteLine("Column8 has: " + (Convert.IsDBNull(reader.GetValue(7)) ? "null" : reader.GetString(7).ToString()));
            Console.WriteLine("Column9 has: " + (Convert.IsDBNull(reader.GetValue(8)) ? "null" : reader.GetString(8).ToString()));
            Console.WriteLine("Column10 has: " + (Convert.IsDBNull(reader.GetValue(9)) ? "null" : reader.GetDecimal(9).ToString()));
            Console.WriteLine("Column11 has: " + (Convert.IsDBNull(reader.GetValue(10)) ? "null" : reader.GetDateTime(10).ToString()));
            Console.WriteLine("Column12 has: " + (Convert.IsDBNull(reader.GetValue(11)) ? "null" : reader.GetDecimal(11).ToString()));
            Console.WriteLine("Column13 has: " + (Convert.IsDBNull(reader.GetValue(12)) ? "null" : reader.GetDecimal(12).ToString()));
            Console.WriteLine("Column14 has: " + (Convert.IsDBNull(reader.GetValue(13)) ? "null" : reader.GetDecimal(13).ToString()));
            Console.WriteLine("Column15 has: " + (Convert.IsDBNull(reader.GetValue(14)) ? "null" : reader.GetString(14).ToString()));
            Console.WriteLine("Column16 has: " + (Convert.IsDBNull(reader.GetValue(15)) ? "null" : reader.GetString(15).ToString()));
            haveRows = true;
        }
        {
            reader.NextResult();
            //Console.ReadLine();

              GridView1.DataSource = reader;
              GridView1.DataBind();
        }


        if (!haveRows)
        {
            Console.WriteLine("No Rows Found.");
        }

        con.Close();
        con.Dispose();
    }

Open in new window

0
Comment
Question by:SirReadAlot
  • 5
  • 3
9 Comments
 
LVL 52

Accepted Solution

by:
Carl Tawn earned 500 total points
ID: 33519796
Well, you use reader.NextResult() which would suggest you are retrieving multiple result sets from your SP and you are trying to bind to the second set. Is this the case or are you trying to bind to the first set?

If you are trying to bind to the first set then you can't read through all the records manually first. The DataReader is forward-only, so once you have read through it you can't read through it again in order to bind.
0
 

Author Comment

by:SirReadAlot
ID: 33519821
>>Is this the case or are you trying to bind to the first set?

yes


>>The DataReader is forward-only, so once you have read through it you can't read through it again in order to bind.

so use a dataset??
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 33519838
That depends. Are you writing everything out to a console window purely for debug information? If so then you can simply take that code out and bind to the reader as normal.

But, if you do need to access the data multiple times, then a DataSet/DataTable is the way to go.
0
Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:SirReadAlot
ID: 33519839
sorry...

trying to bind all retrieved data to a gridview
0
 

Author Comment

by:SirReadAlot
ID: 33519856
I first tried to see if i could retrieve data so i used a console app.

forgot to get rid of the evidence.

>>But, if you do need to access the data multiple times, then a DataSet/DataTable is the way to go.

    do you have code examples?


0
 
LVL 1

Expert Comment

by:zaping
ID: 33519872
You must first fill the reader variable with the values and only after finishing the retrieve of values, you do the binding. Neither way you should be getting at least the first line appearing in the gridview.
0
 

Author Comment

by:SirReadAlot
ID: 33519894
Hi I have managed to fill the grid.

would this be the best way??
OracleConnection con = new OracleConnection();
        con = new OracleConnection("Data Source=**; User ID=***;Password=****");
        OracleCommand cmd = new OracleCommand();
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Connection = con;

        cmd.CommandText = "HE_GETGRADES";
        cmd.Parameters.Add("grades_cursor", OracleType.Cursor);
        cmd.Parameters["grades_cursor"].Direction = ParameterDirection.ReturnValue;
        cmd.Parameters.Add("vp_id", OracleType.Number).Value = "15000028435389";

        con.Open();

        OracleDataReader reader;
        bool haveRows = false;

        reader = cmd.ExecuteReader();


        DataTable dt = new DataTable();
        dt.Load(reader);
        GridView1.DataSource = dt;
        GridView1.DataBind();
        haveRows = true;


        if (!haveRows)
        {
            Console.WriteLine("No Rows Found.");
        }

Open in new window

0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 33519961
Yep, that's fine. Pluse populating a DataTable from a reader is slightly quicker than using a DataAdapter :)
0
 

Author Comment

by:SirReadAlot
ID: 33520008
cool
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

This article describes a simple method to resize a control at runtime.  It includes ready-to-use source code and a complete sample demonstration application.  We'll also talk about C# Extension Methods. Introduction In one of my applications…
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…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

856 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