none
.NET QueryBuilder and IN(Transact-SQL) function RRS feed

  • Question

  •  

    Hello!

     

    I am using Visual Studio 2005 and developing in VB.NET. I have created a standard windows application and am using the standard Data Access tools (MSDataSetGenerator) to create my datatables with tableadapters which represent my database in SQL Server 2005.

     

    If I want to select table data using a parameter that represents a single value, everything works fine using:

    Select * from Employees where EmployeeID = @EmployeeID            [value of @EmployeeID = 1]

     

    However, if I want to select a subset of the records in the Employees table using:

    Select * from Employees where EmployeeID IN(@EmployeeIDs)        [value of @EmployeeIDs = 1, 3]

     

    Some background, the EmployeeID is an Int field. I have tried declaring the @EmployeeIDs variable as String and it still doesn't work. I get an error that it cannot convert the string value of @EmployeeID to an Integer for the field EmployeeID.

     

    So, I guess my question is... does the .NET Queries TableAdapter not support Transact-SQL functions like "IN(" when passing in multiple values of EmployeeID?

    Monday, September 24, 2007 5:45 PM

All replies

  • Can you post your source so we can see what is wrong with it. Also check the Profiler to see what is the command that is executing on the server. When using IN clause you must dinamically create that part of the query instead of using command parameters.

    Monday, September 24, 2007 9:19 PM
  • High,

     

    In principle, each parameter used in a query must have a type that can match a valid type in the database.

    What you try to do is to create a type whose value is something like 1,3,5,....

    At my knowledge there is no type within SQL whose value can be a string made of ints separated by commas.

    So the IN() query must be built at run time. The best way to do this is to create a Class that inherits from System.Collections.Generic(List Of System.Int32) and to override the ToString Method. Then you just have to add to the collection all the necessary IDs and then to call the ToString method.

     

     

     

    Monday, September 24, 2007 9:58 PM
  •  

    Thank you both for your response.

     

    Yes, we have a custom built interface that we can pass a SQL statement as text into, so I pass in "Select * from Employees where EmployeeID IN(1, 3)" as a string and it runs the SQL against the database and I get my resultset back.

     

    However I prefer, whenever possible, to use the builtin functionality of the .NET framework, so I am trying to find out if the TableAdapter FillBy Query (or even the Stored Procedure) functionality will allow you to pass in a value that would be used in an "IN(" clause.  That is a pretty common SQL function and it seems odd to me that the .NET framework tools wouldn't address this functionality in the QueryBuilder....

     

    It wouldn't matter to me if I had to pass it in as a integer array or a string or whatever, my problem is simply that it doesn't appear to be possible in the given toolset....

    Tuesday, September 25, 2007 2:36 AM
  • I wrote my first post on a hurry. You can do that using command parameters but you must do it on the right way:

    private void button1_Click(object sender, System.EventArgs e)

    {

        SqlConnection conn = new SqlConnection("Data Source=(local);Initial Catalog=AdventureWorks;Integrated Security=True");

        SqlCommand cmd = new SqlCommand("SELECT * From Person.Contact WHERE ContactID IN (@FirstID, @SecondID)", conn);

        cmd.Parameters.Add("@FirstID", SqlDbType.Int, 4).Value = 1;

        cmd.Parameters.Add("@SecondID", SqlDbType.Int, 4).Value = 2;

        DataTable dt = new DataTable("Contacts");

        SqlDataAdapter da = new SqlDataAdapter(cmd);

        da.Fill(dt);
        //this is valid and you will have two rows in the table

    }

     

    private void button2_Click(object sender, System.EventArgs e)

    {

        SqlConnection conn = new SqlConnection("Data Source=(local);Initial Catalog=AdventureWorks;Integrated Security=True");

        SqlCommand cmd = new SqlCommand("SELECT * From Person.Contact WHERE ContactID IN (@IDs)", conn);

        cmd.Parameters.Add("@IDs", SqlDbType.VarChar, 20).Value = "1, 2";  //this is invalid because you will get "IN ('1, 2')" instead of "IN (1, 2)"

        DataTable dt = new DataTable("Contacts");

        SqlDataAdapter da = new SqlDataAdapter(cmd);

        da.Fill(dt);

        //this fails here with conversion error
    }


    So if you need to create dinamic query where you don't know the namber of id's in IN clause then you will have a List<int> as previous post described and you will dinamically create the command text on IN clause part and you will again with generic procedure add all params. It will be something like this:

    private void button3_Click(object sender, System.EventArgs e)

    {

        List<int> ids = new List<int>();

        ids.Add(1);

        ids.Add(2);

        ids.Add(5);

     

        SqlConnection conn = new SqlConnection("Data Source=(local);Initial Catalog=AdventureWorks;Integrated Security=True");

        SqlCommand cmd = new SqlCommand("", conn);

        string commandText = "SELECT * From Person.Contact";

        if (ids.Count != 0)

        {

            commandText += " WHERE ContactID IN (";

            foreach (int id in ids)

            {

                commandText += "@ID" + id + ", ";

            }

            commandText = commandText.Substring(0, commandText.Length - 2) + ")";

            foreach (int id in ids)

            {

                cmd.Parameters.Add("@ID" + id, SqlDbType.Int, 4).Value = id;          

            }

        }

        cmd.CommandText = commandText;

        DataTable dt = new DataTable("Contacts");

        SqlDataAdapter da = new SqlDataAdapter(cmd);

        da.Fill(dt);

    }

     
    You can change the source to use StringBuilder for creating the commandText and also 'for' loop will be better if you want to handle the last ',' comma better.

    Tuesday, September 25, 2007 8:18 AM
  • No, you cannot do this passing multiple values in single parameters. Passing array of values is not supported. But in a case of SQL Server there is a solution how to do this. Check my article about how you could do this, but most likely you would need to do it outside of the TableAdapter

     

    http://support.microsoft.com/kb/555266/en-us
    Tuesday, September 25, 2007 10:33 AM
    Moderator
  • Hi again!

     

    My goal was to use the TableAdapter to elminate the need for me to have connection strings and SQL query text in more than one place in the code.  I was trying to keep it all in the .xsd using the DataTables and TableAdapters but it sounds like it isn't possible...

     

    Thanks for all of the responses.

    Tuesday, September 25, 2007 4:57 PM
  • Almost everything is posible. And surrelly this is posible. I would prefer the solution with dinamic query creation as i post in my previous post. If you still want table adapter, then no problem. Create procedure that will accept string parameter with all added keys. Then in stored procedure you will there create dinamic query, and the id's from string parameter will be splited and all ids will be used when creating that query. You will use sp_executesql to execute dinamic sql query string.

    Tuesday, September 25, 2007 5:53 PM
  • You could always extend TableAdapter, since it is partial class. Regarding dynamic concatenation - it may lead to SQL injection vulnerability in your application and if you go this way you should pay attention to the way how you do this and provide good validation.
    Wednesday, September 26, 2007 10:48 AM
    Moderator