Question about SqlCe Parameters usage

  • Thread starter Thread starter tiger79
  • Start date Start date
T

tiger79

Hello,
I've been using paramters to fill tables in a SqlCe database. This was not
that difficult because I found a couple of examples in Rob Tiffany's book
(Sql CE Database Development with the CF) and on the net.
But now I was wondering if its possible to use paramters to retrieve info
fron the database ? I havent found any exapmles on the net so far :(
Do u have a little code-snappet to show me or maybe a link or just an
explanation ?
 
here i got an example of code i'd like to use parameters in it :

for(int index=1; index<=51; index++)

{

string tSQL = "SELECT AttributeId, EntityId, Units, DataType, Size,
Description FROM Attributes WHERE AttributesIndex = "+index+"";

..

..

..

as u can see the problem is that the sql query is instantiated all 51 times,
which really slows down everything, now i'd like to be able to pass the sql
string only once (outside te loop I guess) and use paramater(s) for the
index variable.

How can I do that ???
 
tiger79 said:
here i got an example of code i'd like to use parameters in it :

for(int index=1; index<=51; index++)

{

string tSQL = "SELECT AttributeId, EntityId, Units, DataType, Size,
Description FROM Attributes WHERE AttributesIndex = "+index+"";

.

.

.

as u can see the problem is that the sql query is instantiated all 51 times,
which really slows down everything, now i'd like to be able to pass the sql
string only once (outside te loop I guess) and use paramater(s) for the
index variable.

How can I do that ???
Click to expand...

Create your SQL command outside the loop, as

SELECT AttributeId, EntityId, Units, DataType, Size,
Description FROM Attributes WHERE AttributesIndex = ?

and then add a parameter to it with the appropriate type. Within the
loop, set the parameter's value, and then execute the query.
 
hhhmmm... do u have an example of that ???
cause I do understand that I need the query to stand out of the loop, but
how do I add the paramter to it ? And how do I change the running-time value
of it ???
 
tiger79 said:
hhhmmm... do u have an example of that ???
Click to expand...

There are loads of examples of parameter use in MSDN.
cause I do understand that I need the query to stand out of the loop, but
how do I add the paramter to it ?
SqlCeCommand.Parameters.Add

And how do I change the running-time value
of it ???
Click to expand...

SqlCeCommand.Parameters[...].Value = ...
 
Cause now I've implemented this :
string tSQL = "SELECT * FROM Attributes WHERE AttributesIndex = ?";

SqlCeCommand cmdSelect = new SqlCeCommand(tSQL, con);

cmdSelect.Parameters.Add("index",SqlDbType.Int,4);

cmdSelect.CommandType = CommandType.Text;



for(int index=1; index<=51; index++)

{



SqlCeDataReader dtr = cmdSelect.ExecuteReader(CommandBehavior.Default);

cmdSelect.Parameters["index"].Value = index;

cmdSelect.Prepare();

cmdSelect.ExecuteNonQuery();

while (dtr.Read())

{

myAttributeId = dtr.GetInt32(0);

myEntityId = dtr.GetInt32(1);

..

..

but I get this error : An unhandled exception of type
'System.NullReferenceException' occurred in System.Data.SqlServerCe.dll

Additional information: index


What is the problem here ? Do I have to give additional information of index
Attribute ? So more information tham just the type of data it would contain
?



Jon Skeet said:
tiger79 said:
hhhmmm... do u have an example of that ???
Click to expand...

There are loads of examples of parameter use in MSDN.
cause I do understand that I need the query to stand out of the loop, but
how do I add the paramter to it ?
SqlCeCommand.Parameters.Add

And how do I change the running-time value
of it ???
Click to expand...

SqlCeCommand.Parameters[...].Value = ...
Click to expand...
 
tiger79 said:
Cause now I've implemented this :
string tSQL = "SELECT * FROM Attributes WHERE AttributesIndex = ?";

SqlCeCommand cmdSelect = new SqlCeCommand(tSQL, con);
cmdSelect.Parameters.Add("index",SqlDbType.Int,4);
cmdSelect.CommandType = CommandType.Text;

for(int index=1; index<=51; index++)

{

SqlCeDataReader dtr = cmdSelect.ExecuteReader(CommandBehavior.Default);
Click to expand...

You're executing the reader *before* setting the parameter here. I
believe that's your problem. Also, you haven't set (as far as I can
see) the connection anywhere.
 
ok , I managed now, but now I see there has be no improvement at all :(
Any idea how to implement my coe in a faster way ?
 
yes, i discovered about the reader :) that was stupid...
about the connection, don't worry it has been set before (i guess u mean the
connection to the DB)...
 
btw, should the prepare method be called before the loop or inside the loop
just before the executenonquery ? I mean from a performance point of view
??? Cause I tried both ways now but seems there is no difference... still
takes about 7 seconds to place those 51 records in varikables and display
their description variable as text in treeview nodes :( its way too slow for
me...
 
tiger79 said:
btw, should the prepare method be called before the loop or inside the loop
just before the executenonquery ? I mean from a performance point of view
??? Cause I tried both ways now but seems there is no difference... still
takes about 7 seconds to place those 51 records in varikables and display
their description variable as text in treeview nodes :( its way too slow for
me...
Click to expand...

You don't need to call Prepare at all.

Try taking the UI side of things out of the equation - don't update the
treeview at all, and see how long that takes. Also, why not just use a
single query and load the result into a DataTable?
 
Ok, so I will leave prepare out...
about the treeview, i allready use beginupdate and endupdate methods, so
there is no constant refreshing.
about the datatable : i personally think its double work. first get the data
from the database, then get the data from the datatable... another thing is
that ik got 5 tables, this one is only 51 records but another one is 41000
so that would be too much strain in my opinion
 
btw, my code is runned when i press a button. Now I noticed that the first
time I press it it takes about 6-7 seconds but the second time it takes
about half that time : 3-4 seconds, while doing the exact same query. Is
there some kind of caching involved here ???
 
tiger79 said:
Ok, so I will leave prepare out...
about the treeview, i allready use beginupdate and endupdate methods, so
there is no constant refreshing.
about the datatable : i personally think its double work. first get the data
from the database, then get the data from the datatable...
Click to expand...

Getting the data from the datatable is quick. Getting the data in one
block from the database may very well be quicker than doing 51 separate
queries.
another thing is
that ik got 5 tables, this one is only 51 records but another one is 41000
so that would be too much strain in my opinion
Click to expand...

You don't have to load the whole table into the DataTable - just the
appropriate records.
 
tiger79 said:
btw, my code is runned when i press a button. Now I noticed that the first
time I press it it takes about 6-7 seconds but the second time it takes
about half that time : 3-4 seconds, while doing the exact same query. Is
there some kind of caching involved here ???
Click to expand...

There may be caching involved, and there may also be JIT compilation
going on the first time you run the code - especially if it's creating
UI elements which haven't been used before.
 
about that datatable usage : "Getting the data in one
block from the database may very well be quicker than doing 51 separate
queries."
Click to expand...
do I still have to make use of paramaters then ? I mean what do u mean
exactly ? First I make a datatable containing all records from DB, then I
perform like a search on this datatable ???
 
tiger79 said:
about that datatable usage : "Getting the data in one
do I still have to make use of paramaters then ? I mean what do u mean
exactly ? First I make a datatable containing all records from DB, then I
perform like a search on this datatable ???
Click to expand...

No, you create an empty table and fill it with a SQL query which only
fetches the relevant data. In your original post, you could just have
done a query which specified the range of index to fetch, for instance.
You might want to order the results by index, too.
 
tiger79 said:
Sorry Jon, but how can I make a Query in qhich I specify the range of index
to fetch ?
Click to expand...

SELECT [...] WHERE AttributesIndex >= 1 AND AttributesIndex <= 51

You can specify the two values (1 and 51 here) as parameters, of
course.
 
ok, I used * instead, cause for this time (as a ts) i'm gonna fill the
datatable with all the data.
It works, only I cant seem to manage to retrieve data from the datatable.
cant semee to find any method like fetch() or retrieve() or something in the
datatable instance I made :(

Jon Skeet said:
tiger79 said:
Sorry Jon, but how can I make a Query in qhich I specify the range of index
to fetch ?
Click to expand...

SELECT [...] WHERE AttributesIndex >= 1 AND AttributesIndex <= 51

You can specify the two values (1 and 51 here) as parameters, of
course.
Click to expand...
 
Back
Top