Pass List as Sql Table Type Parameter

Question

I'm trying to pass the list of my class as DbParameter. Probably the table type is defined in my stored procedure.

Now, I'm not getting how to pass the List<> into the stored procedure as there is table type defined which accepts Tables only.

Here, I'm putting my method.

public static AddCustomer(List<Customer> customer)
{
      List<DbParameter> lstDbParameters = null;

      try
      {
        #region Set the Parameters
        lstDbParameters = new List<DbParameter>();
        SqlParameter dbAcceptedBillDetails = new SqlParameter("@Customers",
                                                             customer);

        dbAcceptedBillDetails.SqlDbType = SqlDbType.Structured;
        lstDbParameters.Add(dbAcceptedBillDetails as DbParameter);
        lstDbParameters.Add(CDDAC.MakeDbParameter(dbProvider,
                                                  "@ErrorMessage",
                                                  DbType.String,
                                                  null,
                                                  500,
                                                  ParameterDirection.Output));
        #endregion

        //Call the static ExecuteNonQuery method.
        CDDAC.ExecuteNonQuery(dbProvider,
                              connectionString,
                              "AddCustomer",
                              CommandType.StoredProcedure,
                              lstDbParameters.ToArray());
      }
      catch (Exception ex)
      {
        throw;
      }
    }

And I'm getting error like this:

Failed to convert parameter value from a List1 to a IEnumerable1.

I know i can convert this list into DataTable and then pass it in the stored procedure but it seems time consuming. :(

Post the code for CDDAC.MakeDbParameter and the stack trace of the error. — Keith
– Keith, Commented Jun 5, 2014 at 14:06
If you look at the TechNet documentation on how to call a stored procedure with a table-valued parameter, you see that you have three choices: DataTable, IDataReader or a IEnumerable<SqlDataRecord> - you need to provide one of those three options - take your pick — marc_s
– marc_s, Commented Jun 5, 2014 at 15:05
@Keith, CDDAC.MakeDBParameter is common utility for our projects. its just simlply make the parameter for SP. — Mayur P.
– Mayur P., Commented Jun 6, 2014 at 4:07

Mayur P. · Accepted Answer · 2014-06-09 07:22:06Z

12

Finally, i got my answer byself. But during finding, i got that there is no way exist to convert List<> to IEnumerable<> directly.

But this article is very useful to transact data through object or List<Obj>

http://www.c-sharpcorner.com/UploadFile/pchandraker/passing-table-valued-parameter-to-stored-procedu-part-2/

very useful. :)

answered Jun 9, 2014 at 7:22

Mayur P.

2741 gold badge2 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

dya Over a year ago

Came here because I'm having the same problem. Yeah, .asenumerable() doesn't do it, that was one of my early guesses.

Abdurrahman Al Faruqi · Accepted Answer · 2019-02-19 03:17:45Z

-2

I know this question a bit old, but I found another way to pass a list to table type parameter by converting list into datatable. So your code can be like this :

public static AddCustomer(DataTable customer)
{
      try
      {
        DbCommand dbcomm = CDDAC.GetStoredProcCommand("name your stored procedure");
        CDDAC.AdInParameter(dbcomm, "@Param", SqlDbType.Structured, customer); //@param is an input parameter from your storedprocedure
        CDDAC.ExecuteNonQuery(dbcomm);
      }
      catch (Exception ex)
      {
        throw;
      }
    }

Just make sure you have created type for data customer in your database.

answered Feb 19, 2019 at 3:17

Abdurrahman Al Faruqi

11 bronze badge

1 Comment

Mayur P. Over a year ago

Please read the problem carefully first then provide your suggestion/solution

Collectives™ on Stack Overflow

Pass List as Sql Table Type Parameter

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related