Solved

How to count number of records/rows in a returned dataset?

Posted on 2009-07-15
10
1,138 Views
Last Modified: 2013-12-17

hello group,

Is the following piece of code a right way to count number of records which are returned after a SELECT has run?

ak
//code goes here
 

                    string strSQLStmt = "select * from MyTABLE where id='" + t.app_id + "'";

                    command.CommandText = strSQLStmt;

                    connection.Open();

                    Reader = command.ExecuteReader();
 

                    Reader.RecordsAffected();  //Is this a proper method?

Open in new window

0
Comment
Question by:akohan
10 Comments
 
LVL 8

Expert Comment

by:RyanAndres
ID: 24864699
Use parameterized queries for security and to prevent sql injections.

Below is a snippet of something I'm currently working on.

once you fill 'result', you can get its result.Rows.Count value.
private readonly SqlConnection _connection;
 

public DataTable GetEmployees(int? employeeId, int? locationId)

 {

     SqlCommand cmd = _connection.CreateCommand();

     cmd.CommandText = "asp_get_scheduler_employee";

     cmd.CommandTimeout = 600;

     cmd.CommandType = CommandType.StoredProcedure;
 

     cmd.Parameters.Add("@Employee_UID", SqlDbType.Int).Value =

         (object) employeeId ?? DBNull.Value;
 

     DataTable result = new DataTable();

     SqlDataAdapter adapter = new SqlDataAdapter(cmd);

     adapter.Fill(result);
 

     return result;

 }

Open in new window

0
 
LVL 2

Expert Comment

by:d34thd34l34r
ID: 24864768
It is a property that will show you how many rows were updated, deleted or inserted however, to my knowledge, it is not meant for select queries as you are describing in you post.  I usually just use the example i posted below however, simply because its simple and you can read it like a book...
dr = cmd.ExecuteReader();

                int count = 0;

                while (dr.Read())

                {

                    this.userID = Convert.ToInt32(dr["UserID"]);

                    this.roleID = Convert.ToInt32(dr["RoleID"]);

                    this.name = dr["PersonName"].ToString();

                    this.franchiseID = Convert.ToInt32(dr["FranchiseID"]);

                    if (dr["FranchiseParentID"] != DBNull.Value) { this.parentFranchiseID = Convert.ToInt32(dr["FranchiseParentID"]); }
 
 
 
 
 
 
 

                    count++;

                }
 

                if (count > 0) { _isValid = true; } else { _isValid = false; }

Open in new window

0
 

Author Comment

by:akohan
ID: 24864993

Thanks for both your code but what should I do for cases like:

                    this.userID = Convert.ToInt32(dr["UserID"]);

I just want to run a SELECT command and get the number of records returned.






Regards

0
 

Author Comment

by:akohan
ID: 24864997

As a matter of fact, I'm reading data from a text file and then trying to see if a record has the ID I have fetched from the text file. If true then update it if not then log it.

0
 
LVL 2

Expert Comment

by:d34thd34l34r
ID: 24865067
Well that changes things a bit if your reading from a text file.  Your example above showed you hitting a sql table.  If you want to get the record count from a query without running you could simply fill a dataset and the do something like this.dataset.tables[0].rows.count... If your having problems with a text file place your code here so i can see it and help you out a little easier.
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

Author Comment

by:akohan
ID: 24865465

OK, I'm using

while(Reader.Read()) iExists++;

to see if there is any record existing (it should be 1 always) so I'm fine as far as find a record but the problem is that I cannot update the record after finding it.

Any idea?

Thanks,



//
 

strSQLStmt1 = "";

strSQLStmt1 = "UPDATE table1 set myfield= '" + t.score.Trim().ToString() + "' WHERE Id = '" + t.applicant_id.Trim() + "' ";

Reader.Close();

Open in new window

0
 
LVL 6

Expert Comment

by:HarryNS
ID: 24867285
You can write a Stored Procedure and return the no. of records affected back to your application. It will be a return parameter of your SP.
0
 
LVL 2

Expert Comment

by:d34thd34l34r
ID: 24869011
I agree with HarryNS, you need to put everything in an stored procedure and run the following code i have provided in sql, return that to your .net code, then based on that create another SPROC to update the records.  See below for what your SPROCS should look like:



SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO
 

CREATE PROCEDURE SelectCount_ByID

	@Id			int

AS

BEGIN
 

    Select

			count(1)

	From

			myTable

	Where

			tableID = @Id

END

GO
 
 
 

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO
 

CREATE PROCEDURE UpdateTable_ByID

	@Id			int,

	@param1		char(10),

	@param2		char(2),

	@param3		bit

AS

BEGIN
 

    Update

			myTable

	Set

			param1 = @param1,

			param2 = @param2,

			param3 = @param3

	Where

			tableID = @Id

END

GO

Open in new window

0
 

Author Comment

by:akohan
ID: 24899446

Thanks but no it shouldn't be that complicate. I found that I was not supposed to use the code I used to.

In order to count the number of rows I'm using a variable  also instead of using   ExecuteReader()  I'm using ExecuteNonQuery() method which made it work.

Thank again for your help and time you took to explain it.


Regards,
ak
0
 

Accepted Solution

by:
akohan earned 0 total points
ID: 25028055

Thank you all for your help. since I didn't get the answer so I will be closing the thread so feel free to let me know if you don't agree with it.

Regards,
ak


p.s. I have posted the solution I'm using




                    int iRows = 0;

                    if (Reader.HasRows)

                    {

                        while (Reader.Read())

                        {

                            some code gone here

                            iRows++;

                        }

                    }

                    

                    //Here I will have total # of records.

Open in new window

0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Introduction Hi all and welcome to my first article on Experts Exchange. A while ago, someone asked me if i could do some tutorials on object oriented programming. I decided to do them on C#. Now you may ask me, why's that? Well, one of the re…
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…
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
The viewer will learn how to synchronize PHP projects with a remote server in NetBeans IDE 8.0 for Windows.

920 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now