locked
ADO.NET setup for passing collection to be used as a TABLE VALUED PARAMETER in stored procedure RRS feed

  • Question

  • Hi All,

    I am getting "Failed to convert parameter value from List'1 to IEnumerable'1" on an Execute Non Query when using the following code:

    private void button16_Click(object sender, EventArgs e)
            {
                SqlOperations sql = new SqlOperations();

                // no parameters to simple get radios proc
                ArrayList radios2 = sql.GetObjects(typeof(Radio2), "dbo.GetTestRadios", null);                                                

                List<Radio2> listOfRadios = new List<Radio2>();

                foreach (object obj in radios2)
                {
                    Radio2 radio2 = (Radio2)obj;
                    listOfRadios.Add(radio2);

                }           

                List<SqlParameter> prms = new List<SqlParameter>();

                prms.Add(sql.MakeParameter("@tvpRadio2", SqlDbType.Structured, 0, ParameterDirection.Input, listOfRadios, true, "dbo.Radio2TableType"));           
               
                sql.ExecuteNonQuery(CommandType.StoredProcedure, "Test.dbo.UseTableParameter", prms, false);
               
            }

     The Type and Procedure:

    CREATE TYPE [dbo].[Radio2TableType] AS TABLE(
     [RadioID] [nvarchar](50) NULL,
     [RadioSerialNumber] [nvarchar](50) NULL
    )
    GO

    CREATE PROCEDURE [dbo].[UseTableParameter]
    (  @tvpRadio2 dbo.Radio2TableType READONLY
     )
    AS
    BEGIN
     
     INSERT INTO dbo.RadioTestTwo (RadioID,RadioSerialNumber)
      SELECT RadioID,RadioSerialNumber FROM @tvpRadio2 
     
     
    END

    The class in the collection:

     [Serializable]
        public class Radio2
        {
            private string radioID;
            public string RadioID
            {
                get { return radioID; }
                set { radioID = value; }
            }

            private string radioSerialNumber;
            public string RadioSerialNumber
            {
                get { return radioSerialNumber; }
                set { radioSerialNumber = value; }
            }
        }

    TIA Doyle


    Doyle
    Saturday, May 28, 2011 6:45 PM

Answers

  • Hello Doyle,

    According to your description, we can convert List to IEnumerable manually. Please try to use the following code.

    Just an example:

    List<string> myList = new List<string>(); 
    IEnumerable<string> myEnumerable = myList; 
    List<string> listAgain = myEnumerable.ToList(); 

    The code above can convert a List to an IEnumerable and back again.

    I hope this can help you.

     

    Nice weekend,


    Jackie Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Proposed as answer by Jackie-Sun Tuesday, June 7, 2011 9:21 AM
    • Marked as answer by DoyleJofTexas Wednesday, June 8, 2011 1:34 PM
    Friday, June 3, 2011 8:35 AM

All replies