CODEWITHSUNDEEP
c#sqldataset
Gobbledigook
Gobbledigook

Reputation: 472

Select value IN DataSet

Is there any way to use a DataSet as an IN 'value'?

For instance, I have a query that loads a DataSet with options that the user can choose between when doing their work. The problem is, the query that loads the DataSet is very long and arduous (selects and table joins inside of other selects and other table joins, etc. etc. etc.), so I want to be able to fill the DataSet exactly one time, and just reference the DataSet when running other queries.

Now, I've gotten it to work by calling each row, getting the value from the row, and appending it to the query. 

//GetAvailableOptions is just the long and complicated query
//Code simplified just for example sake
DataSet ds = GetAvailableOptions();
string str_qry = "select from TestFile where (";
foreach(DataRow dr in ds.Tables[0].Rows)
{
    string str_test = dr[0].ToString();
    str_qry += dr[0].ToString() + " or ";
}

So the resulting query looks like this:

select from TestFile where (TestField = 'A' or TestField = 'B' or ... TestField = 'Z')

But I was wondering if I couldn't make it faster by doing the following:

DataSet ds = GetAvailableOptions(); 
string str_qry = "select from TestFile where TestField in " + ds.Tables[0].Columns[0];

But I can't get it to work. Is this possible? Is my logic sound? The name of the Column I'm trying to reference from the DataSet is the same as the field I'm trying to load (in this example, TestField).

Another, I'm thinking more subjective question is, how much faster (if at all?) would it be if this would work? Or even with the joins and multiple queries, would it be faster to pull from there instead of looping through and appending? Or perhaps if there's another way I haven't thought of? Can someone share their experience?

Upvotes: 0

Views: 1207

Answers (2)

SGB
SGB

Reputation: 636

One thing though you mention the data that returns the columns you are after is a complicated join, and if so can this actually all be done on the server? So put together a new stored proc that retrives the inital complicated user data, and then in turn run the select you are constructing on the client on the server?

Upvotes: 1

Maess
Maess

Reputation: 4146

This sounds like something better done as a stored procedure. I assume that the DataSet you are creating is populated from a stored procedure?

Also, the type of dynamic SQL you are generating is not a best practice for a number of reasons, chief among them security, you open yourself up to SQL injection.

Upvotes: 1

Related Questions