1

I have an array list which contains ids for some items. I would like to perform a multiple select at once from a SQL Server CE database and using my array list which contains what items id to be selected, something similar when doing for example multiple update in oracle (ODP.NET) as explained here: Oracle bulk updates using ODP.NET

where you can pass an array as a parameter.

I would like to do the same but for a multiple select instead in case of SQL Server CE. Is it possible?

DRAFT about what I would like to do:

SqlCeCommand = SqlCeConnection.CreateCommand()
SqlCeCommand.CommandText = "SELECT * FROM MyTable WHERE Id=:ids"
SqlCeCommand.CommandType = CommandType.Text

SqlCeCommand.Parameters.Add(":ids", DbType.Int32, ArrayListOfIds, ParameterDirection.Input)

Using reader As System.Data.SqlServerCe.SqlCeDataReader = SqlCeCommand.ExecuteReader()
  Using targetDb As Oracle.DataAccess.Client.OracleBulkCopy = New Oracle.DataAccess.Client.OracleBulkCopy(con.ConnectionString)
    targetDb.DestinationTableName = "MyTable"
    targetDb.BatchSize = 100
    targetDb.NotifyAfter = 100
    targetDb.BulkCopyOptions = Oracle.DataAccess.Client.OracleBulkCopyOptions.UseInternalTransaction
    AddHandler targetDb.OracleRowsCopied, AddressOf OnOracleRowsCopied targetDb.WriteToServer(reader)
    targetDb.Close()
  End Using
  reader.Close()
End Using
Community
  • 1
  • 1
Willy
  • 9,848
  • 22
  • 141
  • 284
  • Can we see a version of the SQL that you're using? This can help us get a better understanding of what you're trying to do. As far as I know, there is no such operation as multiple selects in SQL Server CE. – Cameron Tinker Aug 13 '13 at 12:46
  • Well, I have not done anything yet. I have an array list of ids and from that what I would like to do is to select the ids in the array list from a table in SqlCe database, but doing this at once, not one by one, and finally, once I have done this select, I would like to read this using a datareader in order to do a bulkcopy of those records into a table in Oracle. Obviously, the result obtained from the select against SqlCe has the same columns and types that the table in oracle. – Willy Aug 13 '13 at 12:58

2 Answers2

1

You should try this approach by constructing your "IN" clause and adding each parameter in a for each loop:

SqlCeCommand = SqlCeConnection.CreateCommand()

SqlCeCommand.CommandType = CommandType.Text
Dim sb As New StringBuilder()
Dim i As Integer = 1

For Each id As Integer In ArrayListOfIds
    ' IN clause
    sb.Append("@Id" & i.ToString() & ",")

    ' parameter
    SqlCeCommand.Parameters.Add("@Id" & i.ToString(), DbType.Int32, id, ParameterDirection.Input)

    i += 1
Next
Cameron Tinker
  • 9,634
  • 10
  • 46
  • 85
  • For some reason, I can't post this last line in the answer so I'm adding it here `SqlCeCommand.CommandText = "SELECT * FROM MyTable WHERE Id IN (" + sb.ToString() + ")"` – Cameron Tinker Aug 13 '13 at 14:46
  • 1
    The symbol @ is equivalent to : in Oracle, right? is for parametrize. One thing, last character in In clause should be removed when converting to string: i.ToString().TrimEnd(",") – Willy Aug 14 '13 at 10:03
0

If you're calling a Stored Procedure, you can do this:

  1. Serialize the array to a string of XML, like this: https://stackoverflow.com/a/6937351/734914
  2. Call the stored procedure, passing in the string parameter
  3. Parse the string of XML into a local table variable containing the ID's, like this: https://stackoverflow.com/a/8046830/734914
  4. Execute whatever queries you need to using the ID's

The links that I referenced might not be the best examples on the web, but the concept of "serialize to XML, pass string parameter, deserialize XML" should work here

Community
  • 1
  • 1
plukich
  • 635
  • 1
  • 6
  • 14
  • 1
    The author is not using the full SQL Server so this would not work. SQL Server CE is a very limited subset of the full SQL Server. – Cameron Tinker Aug 13 '13 at 13:58