Retrieve COUNT through a command struc

[Windsailor]

I can retrieve a COUNT through a dataset and test it like this:

Dim myvalue As Integer
myvalue = Me.Dataset1.Tables("MyTableName").Rows(0)("MyColumnName")

If myvalue = 0 Then
    Exit Sub
Else
    DoSomethingElse
End If

How do I do this using the command structured syntax?

Dim command As SqlServerCe.SqlCeCommand = New SqlServerCe.SqlCeCommand

Which is better, using the dataset approach or using the command dialog?

Thanks

 

[Tom]

I'm not sure I understand your question, your first example does not return a COUNT, it returns the actual value in the record field.

For example the following would return the count of records in the table.
myvalue = Me.Dataset1.Tables("MyTableName").Rows.Count

Please clarify your question in more detail.

 

[Windsailor]

That was a new one... I didn't know about that one. Hmmm... that just simplified any SQL statement a whole bunch... I wonder which one is faster?

I currently have a working SQL COUNT statement for the dataset for what I want to do; so that part works just fine with the previous code. I should have probably named the alias column something like "MyTable1Count", sorry for the confusion.

What had me curious is how to do that same thing using a command syntax structure something like this:

Dim conn As New SqlServerCe.SqlCeConnection
conn.ConnectionString = Global.MyApp.My.MySettings.Default.MyConnectionString

Using conn

conn.Open()
Dim command As SqlServerCe.SqlCeCommand = New SqlServerCe.SqlCeCommand
command.Connection = conn
command.CommandText = "SELECT COUNT(*) FROM Table1"
command.ExecuteScalar()

Using datasets I would have to be aware of when to clear and refresh the dataset to be sure the data was accurate.
Using the command. structure I was hoping to by-pass having to clear the dataset etc. and was curious if it was a better method or at least quicker. I have never done it that way and was looking for opinions or options like the one you mentioned.

 

[Tom]

Yes, if all your statement is returning is an single value (in this case a count) then a dataset is not needed. The executescalar method will be faster and use less resources then creating an un-needed dataset/datatable/datacolumn/datarow and dataadapter.

If you are holding data records in memory to work with and need to show the count of those records in the datasets table, my first example could then be used without the need of a seperate call to the database.

Probably wouldnt make much of a difference but you also might want to consider putting your query (Select Count(FieldName) From Table1) into a stored procedure in your database.

conn.Open
command.Connection = conn
command..CommandType = CommandType.StoredProcedure
command.CommandText = "spGetTable1Count"
intCount = command.ExecuteScalar()
conn.Close

 

[Windsailor]

The silly thing that I am having a problem with when using the command syntax is assigning that value to a variable for running conditional checks etc.

This is where things ground to a halt.

 

[Tom]

The silly thing that I am having a problem with when using the command syntax is assigning that value to a variable for running conditional checks etc.

This is where things ground to a halt.

That was my fault, my example didnt show converting the value to the datatype needed. Add the below in red to your code.

intCount = [COLOR="Red"][B]CInt([/B][/COLOR]command.ExecuteScalar()[COLOR="red"][B])[/B][/COLOR]

 

[cjard]

Yes, if all your statement is returning is an single value (in this case a count) then a dataset is not needed.

To outline Tom's point, just ponder on which of these would be faster:


Use an SQL to ask the database to count all rows in a table of 150,000 rows - it can probably get the value from a cached set of statstics about the table.

or

Create a dataset, tableadapter, perform a select * from the table, download 150,000 rows of information over a network into the client machine, allocating all necessary memory for holding possibly millions of strings and then ask the datatable row collection for the count of how many rows it downloaded..


Learning point: leave data in the database where it belongs, get out only the bits you want to work with. Have the database do as much donkey work as possible; it's what they are designed for

 

[Windsailor]

All very good points.

That was in a sense why I wanted to try and eliminate using datasets whenever possible. Querying data wasn't too expensive, but clearing and re-filling datasets got real expensive.

Hope you guys are up on the Entity Framework... that's next

And of course learning about caching sqlcommand queries for doing parameter inserts... when I don't need items for editing just for inserting...

 

[cjard]

That was in a sense why I wanted to try and eliminate using datasets whenever possible. Querying data wasn't too expensive, but clearing and re-filling datasets got real expensive.

Well, it's quite cheap if you want to edit 3 out of 1 million rows, to just download the 3, edit them and send them back.. Naturally you wouldnt download 1,000,000 and then hunt for your 3, edit them, and then let the adapter hunt the datatable for the 3 changed ones..