Passing an array to query in a table adapter

Question

User1629518816 posted

I'm looking for a way to pass an array of values as a parameter to a query in a table adapter.  For example I want to run a query something like:

SELECT * FROM menu WHERE menu_role IN (@roles)

And I could pass something like 'RegisteredUser, SuperUser, OtherUser' to the @roles parameter.

For some reason I can't figure out a way to do this.  Any help would be greatly appericated.

Thanks,

Ryan.



 

Answer 1

User-913230384 posted

This is possible from only code behind. Dynamically create the string and assrign it to the select command of tableadapter.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=319884&SiteID=1

check this link

Answer 2

User1629518816 posted
Thanks,  but I don't think that that helps my situation.  I guess I will just have to create a query string on the fly even though I hate doing that.

Answer 3

User-1786411686 posted

Check if this helps: http://weblogs.sqlteam.com/dinakar/archive/2007/03/28/60150.aspx

Answer 4

User-943423874 posted

There's more than one way to skin a cat...

I was trying to figure out the same thing when I realized I could filter the data AFTER it was returned.  I put it in a dataview and used the rowfilter property.  Hope that helps!

Answer 5

User1629518816 posted
My solutions was to create a stored procedure that dynamically setup the query on the fly before sending the results back. All in all it turned out to be a fairly elegant solution, although I do wish I could just pass an array in through a parameter.

Answer 6

User-253324762 posted

You're a genius. Thank you so much! 

I did something along the lines of turning off all rows in the dataGridView and then turning them back on if the information I wanted to match was equal to any of the values in the array.

foreach (DataGridViewRow row in dataGridView1.Rows)
{
    currencyManager1.SuspendBinding();
    row.Visible = false;

    for (int i = 0; i < sRelationArray.Length; i++)
    {
         if (sRelationArray[i] == row.Cells[0].Value.ToString())
        {
            row.Visible = true;
        }
    }
}
currencyManager1.ResumeBinding();