• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 8032
  • Last Modified:

How to get a recordcount from a datareader C#

I have a stored procedure call listed below I need to know how to get a recordcount
SqlConnection connstored = null;
            SqlDataReader rdr  = null;

            // typically obtained from user
            // input, but we take a short cut
            int FBID = 5543917;
   
   
                  // create and open a connection object
                  connstored = new SqlConnection(ConfigurationSettings.AppSettings["myconn"]);
           
                  connstored.Open();
                  // 1.  create a command object identifying
                  //     the stored procedure
                  SqlCommand cmd  = new SqlCommand("sp_LoadTempIOD", connstored);

                  // 2. set the command object so it knows
                  //    to execute a stored procedure
                  cmd.CommandType = CommandType.StoredProcedure;

                  // 3. add parameter to command, which
                  //    will be passed to the stored procedure
                  cmd.Parameters.Add(
                        new SqlParameter("@FBID", FBID));
            cmd.Parameters.Add(
                new SqlParameter("@BOL", ""));
                  // execute the command
                  rdr = cmd.ExecuteReader();
0
soccerman777
Asked:
soccerman777
  • 10
  • 6
  • 5
  • +1
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
> I need to know how to get a recordcount
do you REALLY need that?
if you really need it, use a tableadaptor and fill the results into a DataSet. the resulting DataTable's Rows.Count() will give the information.

you probably don't need the information, but instead just loop through the records, and at the end have the count...
0
 
Bob LearnedCommented:
Guy,

The related question shows how to take an SqlDataReader, and load a DataTable, which you can use to get DataTable.Rows.Count.

Bob
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>The related question shows how to take an SqlDataReader, and load a DataTable,

can you give me 1 reason why one should want to execute a procedure into a datareader, and then transform that into a datatable, when you could do that in 1 step?
0
Independent Software Vendors: 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!

 
Bob LearnedCommented:
It was quick and easy, and showed the realm of possibilities.
0
 
soccerman777Author Commented:
Hey Learned One.  The last timed I tried to convert it to a datatable it was still throwing a error(you could see that in my last post before I accepted your answer). It never worked for me. I did it another way. I accepted your soultion because you had put effort into it so It was only fair.  It is probably my fault it is not working since this is my first C# application and I have only been using asp.net for about 1 month.

0
 
soccerman777Author Commented:
How do I just tell if there is any records returned?
0
 
soccerman777Author Commented:
Please give me sample code
0
 
Bob LearnedCommented:
If you need that amount of information, then as AngelIII has said, use a DataTable, and an SqlDataAdapter to fill the DataTable, since you can do that in one step.  There might be a slight speed difference in getting an SqlDataReader, which is light-weight, and filling a DataTable from it.

            SqlDataReader reader;
            DataTable dt = new DataTable();
            dt.Load(reader);
0
 
Bob LearnedCommented:
int recordCount = dt.Rows.Count;
0
 
soccerman777Author Commented:
I am still getting this error

'System.Data.DataTable' does not contain a definition for 'Load' using  this code

SqlConnection connstored = null;
            SqlDataReader rdr  = null;

            // typically obtained from user
            // input, but we take a short cut
        int FBID = Convert.ToInt32(dispatch);
   
   
                  // create and open a connection object
                  connstored = new SqlConnection(ConfigurationSettings.AppSettings["connString"]);
           
                  connstored.Open();
                  // 1.  create a command object identifying
                  //     the stored procedure
                  SqlCommand cmd  = new SqlCommand("sp_LoadTempIOD", connstored);

                  // 2. set the command object so it knows
                  //    to execute a stored procedure
                  cmd.CommandType = CommandType.StoredProcedure;

                  // 3. add parameter to command, which
                  //    will be passed to the stored procedure
                  cmd.Parameters.Add(
                        new SqlParameter("@FBID", FBID));
            cmd.Parameters.Add(
                new SqlParameter("@BOL", ""));
                  // execute the command
                  
            SqlDataReader reader;
            DataTable dt = new DataTable();
            dt.Load(reader);    
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
first of all, in your code the reader is still null
qlDataReader reader = cmd.ExecuteReader

now, what version of the .net framework are you using?
AFAIK, the Load() method referred to is only as from 2.0 ...
0
 
Bob LearnedCommented:
What .NET version are you using (and don't say 1.1)?
0
 
soccerman777Author Commented:
1.14 yes it is true(I am so ashamed). I am not cumfortable with a upgrade until I am a little more experienced in .net. Most of the web site I am working on is coded  C# written in notepad so it is not even compatable in  Visual studio .

Can you just tell me how to reference a variable from rdr = cmd.ExecuteReader();  If I FBID is a variable name how would I reference it.
0
 
AmarIs26Commented:
Hi soccerman,
If you have control over the query then you can return multiple result sets in single datareader. This means that the first result would be the count and second would be actual query. Not the best solution but the only other i can think of without actually putting the results of the data reader into another datastructure and then counting it.

so you return two results from db call. First one the count and second one the result.
then when you read from datareader you can do this

if(rdr.read())
{
  //Get count

if(rdr.movenext()) // Makes the reader move onto next resultset.
{
 while(rdr.reader())
{
  // extract other results
}

}
}
0
 
soccerman777Author Commented:
AmarIs26: Can you just tell me how to reference a variable from rdr = cmd.ExecuteReader();  If I FBID is a variable name how would I reference it.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
can you please clarify what you mean by " how to reference a variable from rdr = cmd.ExecuteReader(); "
what is the "result" of what you are trying to achieve?
0
 
soccerman777Author Commented:
I just have a variable called FBID that contains. I am using it for a recordcount. So I just want to test.


from rdr = cmd.ExecuteReader();
numberofresults= rdr.FBID

I just want to know the syntax.
0
 
AmarIs26Commented:
if your query returns
select COUNT(*) AS FBID from .....
or
Select FBID from .....
 
then you can jst do
rdr["FBID"]  
the above returns an object which you have to cast to your type i myself avoid this and use
int fbid_index= rdr.GetOrdinal("FBID");
rdr.GetInt32(fbid_index);
Before you are looping and reading values from datareader you should get the indexes of each column and then you can use then inside your loop.
so
while(rdr.reader())
{
int fbid_value =     rdr.GetInt32(fbid_index);
}
http://www.akadia.com/services/dotnet_data_reader.html
 
0
 
AmarIs26Commented:
Just so you know, datareader provides a method for retrieving each type of value so if your FBID is int32 use
rdr.GetInt32(...)
If it is string then use
rdr.GetString(...)
but each of these require an index of your column/field which you can get by calling the GetOrdinal method. If you dont want to do all this then the easiest way of getting values from datareader is
rdr["fbid"]
0
 
soccerman777Author Commented:
Whanever I try to use rdr.reader()  I get this error

'System.Data.SqlClient.SqlDataReader' does not contain a definition for 'reader'  

thelearnedone above implied that using 1.14 might me the problem.


If I try to use   numberOfResults = rdr["FBID"];   I get

CS0029: Cannot implicitly convert type 'object' to 'long'


If I try   If I try to use   numberOfResults = rdr["FBID"].ToString();    I get the same error
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
it's not reader(), but Read()

and then:

numberOfResults = (long)rdr["FBID"];
0
 
AmarIs26Commented:
What IDE are you using for development? Dont be using notepad. you can use some of the free express editions that microsoft has made available.
as i said before you can access your data using the following method but it returns as object so you have to cast it to the type of your variable.

rdr["FBID"]  
the above returns an object which you have to cast to your type i myself avoid this and use

 
0
 
soccerman777Author Commented:
I am using visiual web developer express. The guy who coded this application originally used notpad. I am redoing it.    This is the first C# page I have looked at in my whole life.  There is some info I should have already told you. my select querry looks like this
select FBID  from tbltempbol where FBID=@FBID

This returns 4 records using the current call.

I am casting numberof results like this

 protected int numberOfResults;

then I am setting numberofResults

numberofresults=(int)rdr["FBID"];

This is returning 0. But in my output page I use a reapeater and I am reciving a value above 0 for FBID.
0
 
AmarIs26Commented:
as you were told before that DataReader will not give you a count, unless you convert it to a datatable or something else. DataReader lets you go through each record at one time. It is the C# equivelant of a SQL Cursor.
Now, from your query you will get 4 records returned, but you cannjt get the number of records by doing what you are doing. You need to modify your query
select Count(FBID) as FBID  from tbltempbol where FBID=@FBID ; select FBID  from tbltempbol where FBID=@FBID ;
if you are using microsoft sql server then it will happily let you run two queries in one go. This means that you will be getting two results. First query will give you the number of records while the second query gives you the actual records.
then you would do this (Look at code)
 
also have a look at this site : note the code is in vb but its .net
http://www.dotnetjohn.com/articles.aspx?articleid=23 
 

if(rdr.reader())
{
 // Get result from query 1, number of records
  numberofresults=(int)rdr["FBID"];
 
  //Get result of query 2 by moving to NextResult
  rdr.NextResult();
 
  //Reader is now pointing to next result set which is the actual 
  // 4  rows
}

Open in new window

0
 
soccerman777Author Commented:
I have already tried the method above (except I did not use the nextResult method). My problem is everybody keeps giving me soultions using rdr.reader. This is throwing a error telling me  this error

'System.Data.SqlClient.SqlDataReader' does not contain a definition for 'reader'  

I have just used hasROws to see if I have records and then in my reapeater I am iterating a count so at the end I have my record count to display. I am still curious though if my version for asp.net(1.14) does not support rdr.reader or if I am doing something wrong.
0
 
AmarIs26Commented:
There is no such thing as rdr.reader
however what i have been saying is use rdr.Read()
Here is some code from one my my projects.
notice that GetLogEntries method is retrieving two resultsets from the datareader.
first one is read using while(reader.Reader)
the other
            if (reader.NextResult() && reader.Read())
         
Also you can find various ways of accessing information from datareader from the code i just attached.

       public override List<LogEntry> GetLogEntries(ref ILogFilterArgs filter)
        {
            List<LogEntry> logEntries = new List<LogEntry>();
 
            IDataReader reader = GetDataReaderLogEntries(ref filter);
            while (reader.Read())
            {
                LogEntry logEntry = GetFromReader(reader);
                logEntries.Add(logEntry);
            }
 
            if (reader.NextResult() && reader.Read())
            {
                filter.TotalRecords = Convert.ToInt32(reader.GetInt32(0));
            }
 
            reader.Close();
 
 
            return logEntries;
        }
 
        private LogEntry GetFromReader(IDataReader reader)
        {
            LogEntry logEntry = null;
 
            if (reader != null && !reader.IsClosed)
            {
                logEntry = new LogEntry();
                logEntry.LogID = reader.GetInt64(reader.GetOrdinal("LogID"));
                logEntry.LogTypeID = (int)reader.GetInt16(reader.GetOrdinal("LogTypeID"));
                logEntry.Priority = reader.GetInt32(reader.GetOrdinal("LogPriority"));
                logEntry.LogTypeString = reader.GetString(reader.GetOrdinal("LogType"));
 
                if (!reader.IsDBNull(reader.GetOrdinal("MethodID")))
                {
                    logEntry.MethodID = reader.GetString(reader.GetOrdinal("MethodID"));
                }
            }
 
            return logEntry;
        }

Open in new window

0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

  • 10
  • 6
  • 5
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now