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


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

akohanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RyanAndresCommented:
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
d34thd34l34rCommented:
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
akohanAuthor Commented:

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
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

akohanAuthor Commented:

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
d34thd34l34rCommented:
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
akohanAuthor Commented:

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
HarryNSCommented:
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
d34thd34l34rCommented:
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
akohanAuthor Commented:

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
akohanAuthor Commented:

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.