none
How do I send a multi value parameter into a typed dataset? RRS feed

  • Question

  • Hi.
    I have a typed dataset made by the VS2010 Designer. I have then created an additional query like:
    Select *
    From Table
    Where Table.GUID IN (@param)

    The VS2010 Designer then creates a function for so after initiating the dataset I can call ds.GetData(@param).

    This works well if I only want to pass one value to the GetData(@param)-function. But if I want to pass more than one value, I don't know what to do. With an untyped dataset I could send the values in a normal string separated by commas, like @param = "value1, value2, value3". But a typed dataset seems to recognize that as a string only, giving me an error message "Value of type 'String' cannot be converted to 'System.GUID'.

    Is there any other techniques for sending multiple values as an array to a typed dataset?
    Monday, April 19, 2010 6:58 PM

Answers

  • Anders,

    You can definitely accomplish this using SQL Server 2008 and table valued parameters.  I've included some sample code to demonstrate this approach.

    I hope this information proves helpful.

     

    //Create table, populate table, create type
    PrepDb(connection);
    
    //Create the DataTable of just IDs
    DataTable idsTable = new DataTable();
    idsTable.Columns.Add("ID", typeof(int));
    
    //Add the IDs to the DataTable
    idsTable.Rows.Add(2);
    idsTable.Rows.Add(3);
    idsTable.Rows.Add(5);
    
    //Create a parameterized query passing in the DataTable
    SqlCommand command = connection.CreateCommand();
    command.CommandText = 
        "SELECT ID, OtherField FROM ParameterizedIn_Table " +
        "  WHERE ID IN (SELECT ID FROM @ids)";
    command.Parameters.AddWithValue("@ids", idsTable);
    
    //Make sure you set the TypeName property on the parameter
    command.Parameters["@ids"].TypeName = "ParameterizedIn_Type";
    
    //Process the results
    SqlDataReader reader = command.ExecuteReader();
    while (reader.Read())
        Console.WriteLine("{0} - {1}", reader[0], reader[1]);
    

     

    Here's the code I used to create the test table and type:

     

     

    static void PrepDb(SqlConnection connection)
    {
        SqlCommand command = connection.CreateCommand();
    
        command.CommandText = "DROP TYPE ParameterizedIn_Type";
        try { command.ExecuteNonQuery(); }
        catch { }
    
        command.CommandText = "DROP TABLE ParameterizedIn_Table";
        try { command.ExecuteNonQuery(); }
        catch { }
    
        command.CommandText = 
            "CREATE TABLE ParameterizedIn_Table " +
            "  (ID int PRIMARY KEY, OtherField nvarchar(255))";
        command.ExecuteNonQuery();
    
        command.CommandText = 
            "INSERT INTO ParameterizedIn_Table (ID, OtherField) " + 
            "  VALUES (1, 'First Row');" +
            "INSERT INTO ParameterizedIn_Table (ID, OtherField) " +
            "  VALUES (2, 'Second Row');" +
            "INSERT INTO ParameterizedIn_Table (ID, OtherField) " +
            "  VALUES (3, 'Third Row');" +
            "INSERT INTO ParameterizedIn_Table (ID, OtherField) " +
            "  VALUES (4, 'Fourth Row');" +
            "INSERT INTO ParameterizedIn_Table (ID, OtherField) " +
            "  VALUES (5, 'Fifth Row');" +
        command.ExecuteNonQuery();
    
        command.CommandText = 
            "CREATE TYPE ParameterizedIn_Type AS TABLE (ID int)";
        command.ExecuteNonQuery();
    }
    

     


    David Sceppa
    Monday, April 26, 2010 10:43 PM
    Moderator

All replies

  • Hi Anders,

    I think you could add the query to the table adapter like this:

    Select * From Table Where Table.GUID In (@para1, @para2, @para3)

    and get data by GetData(value1, value2, value3)

    Best regards,
    Alex Liang


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Thursday, April 22, 2010 1:48 PM
    Moderator
  • The IN expression cannot accept parameters. I've talked about this a number of times and so have a lot of other people. Basically, you have to use another approach or figure out how to pass the set of parameter values to a SQL function that converts the values to a table and use that table in a SELECT as in

    SELECT....
    FROM...
    WHERE x IN (SELECT y FROM myTVF(@MyDelimitedListParms))


    I have an example of a CLR function that does this in my 7th edition.


    __________________________________________________________________
    William Vaughn
    Mentor, Consultant, Trainer, MVP
    http://betav.com
    http://betav.com/blog/billva
    http://www.hitchhikerguides.net

    “Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)”

    Please click the Mark as Answer button if a post solves your problem!

    Thursday, April 22, 2010 8:40 PM
    Moderator
  • Hi, thanks for your tip.

    As the number of parameters are dynamically created (by the user checking checkboxes) I will never know how parameters I have to set up. Maybe I could just list up a lot of them like In (@para1, @para2, @para3, @para4, @para5, ..... , ..... , @para100) and it would work, but I'm looking for a more elegant solution.

    Sunday, April 25, 2010 10:17 PM
  • Hi

    Does your solution involve the use of Table-Valued Parameters? I've tried to implement such an feature, but the Dataset Designer in Visual Studio 2010 just kicks back with the message "The CREATE TYPE SQL construct or statement is not supported.".

    Do you have to alter the SQL server in order to implement this solution?

    Is it possible to implement this solution if you only have read-access to the SQL Server with the database?

    Sunday, April 25, 2010 10:24 PM
  • Anders,

    You can definitely accomplish this using SQL Server 2008 and table valued parameters.  I've included some sample code to demonstrate this approach.

    I hope this information proves helpful.

     

    //Create table, populate table, create type
    PrepDb(connection);
    
    //Create the DataTable of just IDs
    DataTable idsTable = new DataTable();
    idsTable.Columns.Add("ID", typeof(int));
    
    //Add the IDs to the DataTable
    idsTable.Rows.Add(2);
    idsTable.Rows.Add(3);
    idsTable.Rows.Add(5);
    
    //Create a parameterized query passing in the DataTable
    SqlCommand command = connection.CreateCommand();
    command.CommandText = 
        "SELECT ID, OtherField FROM ParameterizedIn_Table " +
        "  WHERE ID IN (SELECT ID FROM @ids)";
    command.Parameters.AddWithValue("@ids", idsTable);
    
    //Make sure you set the TypeName property on the parameter
    command.Parameters["@ids"].TypeName = "ParameterizedIn_Type";
    
    //Process the results
    SqlDataReader reader = command.ExecuteReader();
    while (reader.Read())
        Console.WriteLine("{0} - {1}", reader[0], reader[1]);
    

     

    Here's the code I used to create the test table and type:

     

     

    static void PrepDb(SqlConnection connection)
    {
        SqlCommand command = connection.CreateCommand();
    
        command.CommandText = "DROP TYPE ParameterizedIn_Type";
        try { command.ExecuteNonQuery(); }
        catch { }
    
        command.CommandText = "DROP TABLE ParameterizedIn_Table";
        try { command.ExecuteNonQuery(); }
        catch { }
    
        command.CommandText = 
            "CREATE TABLE ParameterizedIn_Table " +
            "  (ID int PRIMARY KEY, OtherField nvarchar(255))";
        command.ExecuteNonQuery();
    
        command.CommandText = 
            "INSERT INTO ParameterizedIn_Table (ID, OtherField) " + 
            "  VALUES (1, 'First Row');" +
            "INSERT INTO ParameterizedIn_Table (ID, OtherField) " +
            "  VALUES (2, 'Second Row');" +
            "INSERT INTO ParameterizedIn_Table (ID, OtherField) " +
            "  VALUES (3, 'Third Row');" +
            "INSERT INTO ParameterizedIn_Table (ID, OtherField) " +
            "  VALUES (4, 'Fourth Row');" +
            "INSERT INTO ParameterizedIn_Table (ID, OtherField) " +
            "  VALUES (5, 'Fifth Row');" +
        command.ExecuteNonQuery();
    
        command.CommandText = 
            "CREATE TYPE ParameterizedIn_Type AS TABLE (ID int)";
        command.ExecuteNonQuery();
    }
    

     


    David Sceppa
    Monday, April 26, 2010 10:43 PM
    Moderator
  • Is there still no elegant solution for this problem? Has this been solved in VS 2010? I mean come on, Microsoft, how hard is it to get the parsing of a passed parameter right when the parameter consists of an array or is an array?

    The work arounds are just that and might be OK or even good in some cases, but try to imagine the performance issues of for example the solution given by Mr. Sceppa when you have hundreds or even thousands of IDs involved. This solution won't be an option anymore.

    Friday, August 20, 2010 9:19 AM
  • I walk through a number of alternatives in the CLR Executables chapter of my 7th edition. There are choices that range from mechanically building SQL (dangerous) to building a CLR routine that takes an array and returns a Table to pass to an IN expression. Microsoft has given us a number of ways to address the problem. Yes, it would be nice if TSQL just supported parameter-driven IN expressions but I expect there is something holding them back. Perhaps they're too distracted creating new stuff to worry about existing problems with several work-arounds.

     


    __________________________________________________________________
    William Vaughn
    Mentor, Consultant, Trainer, MVP
    http://betav.com
    http://betav.com/blog/billva
    http://www.hitchhikerguides.net

    “Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)”

    Please click the Mark as Answer button if a post solves your problem!

    Monday, August 23, 2010 8:18 PM
    Moderator