How to pass Business Object Array into SQL server database using Microsoft.Practices.EnterpriseLibrary

Question

My requirement is similer to following : What are the best ways to send arrays from js (jQuery) to sql sproc? Array transmitting between layers

BUT I am using C# MVC 4 and for database operations I am using Microsoft.Practices.EnterpriseLibrary.

I am passing DataTable object to Data access layer using

       SqlDatabase sqlServerDB = new      SqlDatabase(SqlHelper.GetConnectionString());
       string query = @"   EXEC Some_Proc ";

      DbCommand cmd = sqlServerDB.GetSqlStringCommand(query);
      cmd.CommandTimeout = 120;
      sqlServerDB.AddInParameter(cmd, "@DataArray", DbType.Object, timesheetList);
      result = Convert.ToInt64(sqlServerDB.ExecuteNonQuery(cmd));

I am getting following error :

Server Error in '/' Application.

The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 3 ("@DataArray"): Data type 0x62 (sql_variant) has an invalid type for type-specific metadata.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 3 ("@DataArray"): Data type 0x62 (sql_variant) has an invalid type for type-specific metadata.

Can somebody help me please ?

Ok then try this - `sqlServerDB.AddInParameter(cmd, "@DataArray", DbType.Structured, timesheetList);` — Krishnraj Rana, Apr 08 '16 at 13:13
DbType.Structured is not allowed in Microsoft.Practices.EnterpriseLibrary — Hardipsinh Jadeja, Apr 08 '16 at 13:39

score 0 · Accepted Answer · answered Apr 11 '16 at 12:10

Thank you Krishnaraj Rana for your support. Finally I got the solution as below :

SqlDatabase sqlServerDB = new SqlDatabase(SqlHelper.GetConnectionString());

string query = @"  EXEC Some_Proc";

DbCommand cmd = sqlServerDB.GetSqlStringCommand(query);
cmd.CommandTimeout = 120;

SqlParameter temp = new SqlParameter("@DataArray", timesheetList);
temp.SqlDbType = SqlDbType.Structured;
temp.TypeName = "dbo.My_Custom_Table_Type_Name";
cmd.Parameters.Add(temp);
result = Convert.ToInt64(sqlServerDB.ExecuteNonQuery(cmd));

How to pass Business Object Array into SQL server database using Microsoft.Practices.EnterpriseLibrary

1 Answers1