Getting the number of rows in an SQL database in a C# app?

  • (2 Pages)
  • +
  • 1
  • 2

22 Replies - 8567 Views - Last Post: 09 March 2011 - 09:20 AM Rate Topic: -----

#1 Atomika3000  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 88
  • Joined: 15-October 10

Getting the number of rows in an SQL database in a C# app?

Posted 08 March 2011 - 06:16 AM

I want to know, how i can get the number of rows in an SQL database, using a C# app. My database doesn't have an auto-number PK, as i couldn't find it when making the DB, so instead the users manually enter a number for the id.

What i want to do, is add validation when the add a new record so that greater than the last number in the table.

How can i go about doing this?
Is This A Good Question/Topic? 0
  • +

Replies To: Getting the number of rows in an SQL database in a C# app?

#2 Sergio Tapia  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 1251
  • View blog
  • Posts: 4,168
  • Joined: 27-January 10

Re: Getting the number of rows in an SQL database in a C# app?

Posted 08 March 2011 - 06:27 AM

The only solution would be iterating through each and every row and replacing the highest ID value was you find it to a local variable in your software. Then save the new user with that highest value + 1. Which sucks. It utterly sucks. :(

What chances are there to rebuild that table with a proper, autoincremented ID. It's as simple as:

PersonId int primary key identity(1,1)


Where the first number is the starting value, and the second the amount to increment at each hop.

This post has been edited by Sergio Tapia: 08 March 2011 - 06:28 AM

Was This Post Helpful? 0
  • +
  • -

#3 Atomika3000  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 88
  • Joined: 15-October 10

Re: Getting the number of rows in an SQL database in a C# app?

Posted 08 March 2011 - 06:35 AM

I could do it, but, would mean reconnecting the database, and re-pointing the databounds labels/boxes/datagrids!

Alot of hassle, due to the amount of things i would have to reconnect or re-point
Was This Post Helpful? 0
  • +
  • -

#4 Sergio Tapia  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 1251
  • View blog
  • Posts: 4,168
  • Joined: 27-January 10

Re: Getting the number of rows in an SQL database in a C# app?

Posted 08 March 2011 - 06:39 AM

Do yourself a favor my friend, and invest that time here and now. You will eventually have to face this poor decision.

What do you do to prevent duplicate ID's when saving? Do you iterate through each and every row? See where I'm getting at?

Go back and fix that problem. If your UI is that tightly bound then you have to refactor. Your UI shouldn't have direct connection to the database.

This post has been edited by Sergio Tapia: 08 March 2011 - 06:40 AM

Was This Post Helpful? 0
  • +
  • -

#5 Atomika3000  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 88
  • Joined: 15-October 10

Re: Getting the number of rows in an SQL database in a C# app?

Posted 08 March 2011 - 06:47 AM

hmmm, was hoping to avoid doing it, oh well, if it must be done then it must.

As for entering data, i entered it all manually, making sure i didn't duplicate the numbers.

The UI doesn't just some of the items on the UI get info from the database using the connection string while its running
Was This Post Helpful? 0
  • +
  • -

#6 Sergio Tapia  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 1251
  • View blog
  • Posts: 4,168
  • Joined: 27-January 10

Re: Getting the number of rows in an SQL database in a C# app?

Posted 08 March 2011 - 06:54 AM

Do you honestly expect your users to give a single regard to your software usage? They'll hammer whatever number they feel like.

It's like a throwing a monkey with a razor blade inside of a chuck e cheese ballpen. Bad news.

You're making the correct choice by going back to square one.
Was This Post Helpful? 1
  • +
  • -

#7 Atomika3000  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 88
  • Joined: 15-October 10

Re: Getting the number of rows in an SQL database in a C# app?

Posted 08 March 2011 - 06:58 AM

True, should have thought of that, oh well, guess thats what comes from being new to it!
Was This Post Helpful? 0
  • +
  • -

#8 Atomika3000  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 88
  • Joined: 15-October 10

Re: Getting the number of rows in an SQL database in a C# app?

Posted 08 March 2011 - 07:08 AM

haha! i have done it with re-doing the database! forgot to mention i was making the program in VS 2008!

Basically there was a function for my "dataGridView1" call "RowCount" :P

simply made it so that it counted how many rows, stored it in an Int called "rows", and then created a new int called "CurrentID" then did "CurrentID = rows +1" to get the new ID, then put "CurrentID" in the sql query!

also left the ID text box as read only!
Was This Post Helpful? 0
  • +
  • -

#9 Sergio Tapia  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 1251
  • View blog
  • Posts: 4,168
  • Joined: 27-January 10

Re: Getting the number of rows in an SQL database in a C# app?

Posted 08 March 2011 - 07:14 AM

Delete a row, then try saving a new row with that process you described above. ;)
Was This Post Helpful? 0
  • +
  • -

#10 eclipsed4utoo  Icon User is offline

  • Not Your Ordinary Programmer
  • member icon

Reputation: 1524
  • View blog
  • Posts: 5,957
  • Joined: 21-March 08

Re: Getting the number of rows in an SQL database in a C# app?

Posted 08 March 2011 - 08:05 AM

You can get the number of rows in a table by doing this...

SELECT COUNT(*) 
FROM Table1



If you have a number in that table and you want to get the highest one, then you would do this..

SELECT MAX(NumberField)
FROM Table1



While this is a horrible way of going about doing an auto-numbering, it will work.

View PostSergio Tapia, on 08 March 2011 - 09:27 AM, said:

The only solution would be iterating through each and every row and replacing the highest ID value was you find it to a local variable in your software. Then save the new user with that highest value + 1. Which sucks. It utterly sucks. :(


Yes, that would suck if he had to do it that way...but he doesn't. A simple SQL query can do that for him.
Was This Post Helpful? 1
  • +
  • -

#11 Atomika3000  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 88
  • Joined: 15-October 10

Re: Getting the number of rows in an SQL database in a C# app?

Posted 08 March 2011 - 11:28 AM

Yeah, that didn't work how i planned, it tryed to duplicate the the number :(

will try adding the SQL statement, will let you know
Was This Post Helpful? 0
  • +
  • -

#12 Atomika3000  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 88
  • Joined: 15-October 10

Re: Getting the number of rows in an SQL database in a C# app?

Posted 08 March 2011 - 11:34 AM

Ok, i got the SQL query, who would i go about putting the result into a variable?

my current code for it is...

//Executing the query
            SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\CoffeeDatabase.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True");
            SqlCommand rows = new SqlCommand("SELECT MAX(CoffeeID) FROM CoffeeInfo", conn);
            
            conn.Open();
            rows.ExecuteNonQuery();
            conn.Close();
            //Getting the answer from the SQL query "rows"
            CurrentID = rows;
            // SQL to add new product
            SqlCommand add = new SqlCommand("INSERT INTO CoffeeInfo (CoffeeID, CoffeeName, CoffeeStrength, Origin, Price, Quantity) VALUES (" + CurrentID +", '" + NameBox.Text + "','" + StrengthBox.Text + "','" + OriginBox.Text + "'," + PriceBox.Text + "," + QuantityBox.Text + ")", conn);

Was This Post Helpful? 0
  • +
  • -

#13 eclipsed4utoo  Icon User is offline

  • Not Your Ordinary Programmer
  • member icon

Reputation: 1524
  • View blog
  • Posts: 5,957
  • Joined: 21-March 08

Re: Getting the number of rows in an SQL database in a C# app?

Posted 08 March 2011 - 11:47 AM

When executing a SELECT statement, you should use the ExecuteReader or ExecuteScalar methods to get the data back.

string connectionString = @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\CoffeeDatabase.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True";

int coffeeID = 0;

using (SqlConnection cn = new SqlConnection(connectionString))
using (SqlCommand cmd = cn.CreateCommand())
{
	cmd.CommandText = "SELECT MAX(CoffeeID) FROM CoffeeInfo";
	cmd.CommandType = CommandType.Text;
	
	cn.Open();
	
	coffeeID = (int)cmd.ExecuteScalar();
	
	int newCoffeeID = coffeeID + 1;
	
	cmd.CommandText = "INSERT INTO CoffeeInfo (CoffeeID, CoffeeName, CoffeeStrength, Origin, Price, Quantity) VALUES (@CoffeeID, @Name, @Strength, @Origin, @Price, @Quantity)";
	
	cmd.Parameters.AddWithValue("@CoffeeID", newCoffeeID);
	cmd.Parameters.AddWithValue("@Name", NameBox.Text);
	cmd.Parameters.AddWithValue("@Strength", StrengthBox.Text);
	cmd.Parameters.AddWithValue("@Origin", OriginBox.Text);
	cmd.Parameters.AddWithValue("@Price", PriceBox.Text);
	cmd.Parameters.AddWithValue("@Quantity", QuantityBox.Text);
	
	cmd.ExecuteNonQuery();
}



This also uses parameters in the query. This is called a parameterized query. You NEVER want to take text inputted by the user and concatenate it directly into a SQL query.

I also want to note that this is a HORRIBLE way of doing it. You can easily change your table to use an auto-incrementing identity(like what Sergio Tapia mentioned). You can write a SQL script that will put all of the current data in the table into a temporary table, add the new column to the current table, then insert all of the data from the temporary table back into the current table. This will give you the auto-incrementing identity. It will also save you from having to do the code above.
Was This Post Helpful? 1
  • +
  • -

#14 eclipsed4utoo  Icon User is offline

  • Not Your Ordinary Programmer
  • member icon

Reputation: 1524
  • View blog
  • Posts: 5,957
  • Joined: 21-March 08

Re: Getting the number of rows in an SQL database in a C# app?

Posted 08 March 2011 - 12:05 PM

Just for the hell of it, here is the script you would need to update your table with the new column...

-- this matches the schema of the current table
CREATE TABLE #Person(
	[FirstName] [varchar](50) NOT NULL,
	[LastName] [varchar](50) NOT NULL
) 

-- selects everything from our main table
--    and inserts it into the temp table
INSERT INTO #Person
SELECT FirstName, LastName
FROM Person

GO

-- deletes all data from main table
TRUNCATE TABLE Person

-- adds new identity column to main table
ALTER TABLE Person
ADD PersonID int primary key identity(1,1)

GO

-- inserts data from temp table into main table
-- this will generate the auto-incrementing 
--    ids when each row is inserted
INSERT INTO Person
SELECT FirstName, LastName
FROM #Person



Of course, this is a theoretical table. You would need to change it to match your table.
Was This Post Helpful? 1
  • +
  • -

#15 Atomika3000  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 88
  • Joined: 15-October 10

Re: Getting the number of rows in an SQL database in a C# app?

Posted 08 March 2011 - 12:08 PM

ok, i understand how that works, just one last question. why should you not get input from user and concatanate it in an SQL query?

is it just coding eticate, or is there a general reason?
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2