Parameterized query

[Wentu]

Hi all

 

i have a function paramQuery that, given an integer i and an Object param, returns the result of a scalar query from a SqlCEConnection gConn. I store all my possible queries in an array SELECTsqlstrs and all my commands in an array cmds. Depending on the passed i, I know which command excecute. If the command does not exists, i build it, otherwise i just invoke it.

 

Well, the first time i call the function with a given i, it works, but at the second time, it throws an exception (SqlServerCE.SqlCEException)

 

I think i am not allowed to close the gConn but i suspect i need to .close() something else...

could u tell me where's the problem ?

 

I am working on a PocketPC appl, with WinCE. All the queries return a scalar and have only one parameter. che gConn is global.

 

thankx

 

Wentu

 

******************************************

 

If cmds(i) Is Nothing Then

cmds(i) = gConn.CreateCommand

cmds(i).CommandText = SELECTsqlstrs(i)

Dim p As SqlCeParameter = New SqlCeParameter

cmds(i).Parameters.Add("Par1", param)

cmds(i).Prepare()

Else

cmds(i).Parameters(0).Value = param

End If

paramQuery = cmds(i).ExecuteScalar

[Rodenberg]

I'm not really sure how this works the first time you invoke, the connection is never set to open(). I'm no vb.net guru, but you should try:

 

cmds(i).Open()

cmds(i).ExecuteScalar

cmds(i).Close()

 

I'm also curious as to why you are using arraylist of command object instances, why not just create new instances as they are needed? If performance is your primary concern, I would recommend using a pool of connection objects and allocating them as needed, instead of bothering with the collection of command objects.

 

ADO.Net was supposed to implement connection pooling, but it doesn't... so to obtain maximal performance you will need to implement it yourself. Having only once instance of a connection object is a really bad idea, especially if the single object will be accessed by multiple threads. You will almost certainly encounter a lot of problems with this type of implementation.

[Wentu]

1st of all thankx for the reply

 

you should try:

 

cmds(i).Open()

cmds(i).ExecuteScalar

cmds(i).Close()

 

 

as i said, the connection is opened outside the function and is global. then, i can't even see how to open a Command. if i write "cmds(i)." it does not shows the open method

 

I'm also curious as to why you are using arraylist of command object instances, why not just create new instances as they are needed?

 

i read an article that says i must do that: having a command object for every query i often use, so that i can prepare it and use it every time i want, in different places.

 

 

If performance is your primary concern, I would recommend using a pool of connection objects and allocating them as needed, instead of bothering with the collection of command objects.

 

SQLCE allows, afaik, only 1 connection at a time

 

 

so far, the only solution i found is to specify the SQLCEType of parameter and set it every time to a NVarChar. in this case, it works. I found out that the problem was a sort of invalid cast when i set the value of the parameter the second time.

 

Wentu