Store in a variable the number of rows from a table where a column has a certain value in SQL Server Compact

Question

I have a Windows Forms application and a SQL Server Compact 3.5 database. I want to store in a variable the number of rows from a table where a column has a certain value. I have something like this to count all the rows from a table:

CarsDataSet carsData = new CarsDataSet();
int nrCars = carsData.CarName.Rows.Count;

How can I get the information I need?

Answer 1

First you'll want to write your SQL command which returns the number of rows in the query (using the count(*) operator). The where clause of the SQL statement is where you can filter what specific Cars you want (e.g. Model = 'Ford')

string sql = "select count(*) from Cars where SomeColumn='{put_your_filter_here}'";

Now create a SqlCommand object which we will execute on your SqlCeConnection. You'll need to open a SqlCeConnection to your local Sql Compact Edition database.

SqlCeCommand cmd = new SqlCeCommand(sql, DbConnection.ceConnection);

Execute the command which returns the count(*) number of rows and stores in cars variable

int cars = (int)cmd.ExecuteScalar();

Use/print the result of the query

Console.WriteLine("Number of cars: " + cars);