Question Passing parameter to oracle tableadapter

[dsk96m]

I have a dataset in my project that is connected to oracle. In the dataset i created a tableadapter fillbyEmplLIst. In short the query looks like this:

Select * from table where name in (:Value)

Where :Value is a parameter. In my code i have this:

oraddt = oradta.GetDataByEmplList(empllist.ToString)

where for example empllist.tostring = '9874454','8732874','2386428'

when i run it, i get nothing returned. If i put in just a single value, it works. If in the Dataset Designer I click on the query and do preview data, if i put in one value, it works, if i put in two it doesnt. Then if in the query i replace :Value with the values, it works.

Please help. I can provide screenshots if necessary.

 

[jmcilhinney]

What you're trying to do is not possible. A single parameter can only be used to pass a single value. You can't pass multiple values in a single parameter like that. The whole point of parameters is that they are NOT simply inserted into the String containing the SQL code. They are substituted at the binary level by the database itself. If you want to be able to pass an arbitrary number of values in a single parameter then your SQL code would have to be written such that it took that text in parsed it as SQL code. That can be done but it's complex and it removes the protection against SQL injection that parameters usually provide. The other options are to either add multiple queries to the table adapter that provided ever-increasing numbers of parameters, which would require you to know the maximum number of parameters you would send, or to build the SQL dynamically at run time.

 

[dsk96m]

So what is the best way. Here is what I am trying to do. I have data from oracle and data from mssql. Both are employee data. I need to compare every employee from the mssql data to their record in the oracle data to see if a value is the same. The data from the mssql is less than 2000 rows. The oracle data is a LOT more. I was hoping i could create a tableadapter that queried the oracle table for only the employees that show up in the mssql data. Does that make sense? If i try to pull then entire oracle data it takes way to long to run. I know i could just pull the mssql data, and then loop through the rows and then perform an query for each individual row, but that is running ~1200 queries. What do you suggest?

 

[jmcilhinney]

Have you tried the loop? Is there an issue with performance? There might well be an issue executing a single query with an IN clause containing 1200 values that is as bad or worse. I've had issues with long IN clauses myself in the past. If you're going to use an IN clause then you're going to have to build the SQL on demand. I'd do a test with a suitable number of static values and see what performance is like in each case rather than assuming that one way is good and one isn't.

 

[dsk96m]

no need, im just going to use the loop. it works. You know what they say: KISS(Keep It Simple Stupid). Got to remember that, lol.

Thanks for the help

 

[dsk96m]

What i am doing is looping through the oracle where the employee id = the value from the mssql and then adding those rows to a datatable. So now i have two datatables, emsdt and dt. I have to compare like 20 different values. What do you think is the best way to do that, a bunch of ifs?

 

[jmcilhinney]

Probably put the values into an array and then call Contains on it.

 

[cjard]

Consider uploading all your nasal data into a temporary table in the oracle db, then use SQL queries to do the compare

Example to find data on one table but not both, use a full outer join where table1 pk is null or table2 pk is null

To compare rows data for inequality, inner join on pk where table1.column1 not equal table2.column1