Query based on user input

  • Thread starter Thread starter nemo
  • Start date Start date
N

nemo

Try to explain this as simple as I can -

User provides a list (names, ID's, or ....) into a text box on a GUI
application...

Based on which options they select I have to look up the corresponding
values from our sql databases.

For example:

user provides:
adamsj
nimmow
dandekp

I need to return
nimmow 1234
dandekp 2341
adamsj <>

where nimmow and dandekp are actual employees and adamsj does not have
an ID

I need to do this for thousands of rows at a time, so I want to avoid
doing a for each with a select statement.

Whats the best way to get this data from SQL? Ive thought about doing
a for each through the list the user provides and appending on a "where
username in ('x','y','z')" to the end of a sql query and then just
getting the results back in a datatable, but then I would not get the
users who were not found in the list..

ideas?

thanks

will
 
There are a few different ways to do this. The method you suggested works
well. You would need to keep track of those results that weren't returned
by the query. You could do that using a Generic.List or one of the other
Generic collections. Add all of the search strings to the list.

Dim allSearch As New Generic.List(Of String)
allSearch.Add("adamsj")
allSearch.Add("nimmow")

Then as you parse the results from your SQL query, delete the ones you find.

allSearch.Remove(CStr(returnedTable!ShortName))

Anything left in the list will be your "<>" elements.

For Each oneEntry As String IN allSearch
myStringBuild.AppendLine(oneentry & " <>")
Next oneEntry

You could also create a temporary SQL Server table with your search strings,
and join this table to the real table.

SELECT TT.SearchText, RT.ID
FROM ##MyTempTable AS TT LEFT JOIN RealTable AS RT
ON TT.SearchText = RT.ShortName

Any record that comes back with a NULL ID field is a "<>" entry. Don't forget
to remove the temporary table when you're finished with it.
 
I agree using a temp table would be preferrable.

Unfortunately that would require inserting all 10K rows into the
database, which would require 10K sql insert statements. Kinda defeats
the intention of reducing the number of SQL calls..

Your idea of removing systems as they are found is not bad.. I may look
into that.

Im just kinda surprised .net doesnt have some kind of method where you
could get all of the user's input into a single datatable and get the
other information into another datatable from SQl and then do some sort
of merge on the two datatables.

Im looking into the joinview class right now, but frankly it confuses
me.

What makes this even more complicated is that I have simplified the
problem.. The data I need to relate the input to actually resides on
two seperate servers that cannot be linked and have no common ID with
permissions to both..

so whatever I end up doing, I actually have to do twice..

fun stuff..

any more suggestions?
 
The classes in the System.Collections namespace provide similar functionality
to the Generic collections, although they are not strongly typed.
 
You might want to ask a SQL Server newsgroup if there is a way to build a
table on the fly from a chunk of single-column data. I kind of doubt it--Oracle
doesn't have a feature like that in its standard DDL commands. But SQL Server
Data Transformation Services (DTS) is able to import tab-delimited data into
tables quick quickly, so there may be some advanced or back-door way to do
it.
 
Unfortunately that would require inserting all 10K rows into the
database, which would require 10K sql insert statements. Kinda defeats
the intention of reducing the number of SQL calls..
Click to expand...

You could bulk load the data using DTS or BCP.

Or just do the SELET * FROM USERS WHERE UserName in ('user1', 'user2', etc)
method - perhaps chunk the select into ~100 - 1000 users? Once you get the
datatable back - merge the data into a large datatable containing the
complete results?
Im just kinda surprised .net doesnt have some kind of method where you
could get all of the user's input into a single datatable and get the
other information into another datatable from SQl and then do some sort
of merge on the two datatables.
Click to expand...

MS Linq will allow you to dynamically query data tables and join tables
together...but it's in alpha or beta.
 
nemo said:
Whats the best way to get this data from SQL? Ive thought about doing
a for each through the list the user provides and appending on a
"where username in ('x','y','z')" to the end of a sql query and then
Click to expand...

Don't forget to escape apostrophes somehow (or use a stored procedure)
because otherwise that's going to fall over as soon as you get an employee
with an apostrophe in their name.

Andrew
 
Back
Top