Query based on user input

6 Nov 2006 5:00 PM

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

6 Nov 2006 5:27 PM

Tim Patrick

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.

-----
Tim Patrick
Start-to-Finish Visual Basic 2005

Show quoteHide quote

> 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
>

6 Nov 2006 5:53 PM

nemo

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?

6 Nov 2006 6:00 PM

nemo

also - not using .net 2.0 yet.. is there a 1.1 equivalent of
generic.list?
nemo wrote:
Show quoteHide quote

> 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?

6 Nov 2006 8:28 PM

Tim Patrick

The classes in the System.Collections namespace provide similar functionality
to the Generic collections, although they are not strongly typed.

-----
Tim Patrick
Start-to-Finish Visual Basic 2005

Show quoteHide quote

> also - not using .net 2.0 yet.. is there a 1.1 equivalent of
> generic.list?
> nemo wrote:
>> 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?
>>

6 Nov 2006 8:30 PM

Tim Patrick

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.

-----
Tim Patrick
Start-to-Finish Visual Basic 2005

Show quoteHide quote

> 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?
>

6 Nov 2006 10:04 PM

Spam Catcher

"nemo" <miamin***@gmail.com> wrote in news:1162835614.928309.289380

@f16g2000cwb.googlegroups.com:

> 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..

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.

MS Linq will allow you to dynamically query data tables and join tables
together...but it's in alpha or beta.

7 Nov 2006 1:08 PM

Andrew Morton

nemo wrote:

> 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

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