Passing dataTable to SP with result

-1

im trying to send dataTable as parameter to SP . i have in the DB user-defined table with 6 columns.

[OrderId] [int] NULL,
[IsOr] [bit] NULL,
[CId] [int] NULL,
[Op] [varchar](20) NULL,
[string1] [nvarchar](max) NULL,
[datetime] [datetime2](0) NULL

Im using Nhibernate to send the objects but i get the following error:

2020-01-16 16:36:13,240 [58] [0] ERROR NHibernate.AdoNet.AbstractBatcher [(null)] <(null)> - Could not execute query: EXEC Test @Parameters=@p0 System.Data.SqlClient.SqlException (0x80131904): Trying to pass a table-valued parameter with 6 column(s) where the corresponding user-defined table type requires 1 column(s). at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at NHibernate.AdoNet.AbstractBatcher.ExecuteReader(IDbCommand cmd)

 public class a{

            IList<Product> Products


            DataTable dataTable = new DataTable();
            dataTable.Columns.Add("OrderId", typeof(int));
            dataTable.Columns.Add("IsOr", typeof(bool));
            dataTable.Columns.Add("CId", typeof(int));
            dataTable.Columns.Add("Op", typeof(string));
            dataTable.Columns.Add("string1", typeof(string));
            dataTable.Columns.Add("datetime", typeof(DateTime));

            foreach (var product in Products)
            {
                dataTable.Rows.Add(product.OrderId,product.IsOr,
                product.CId, product.Op,product.string1,product.datetime);

            }


           var query = Session.CreateSQLQuery($"EXEC Test @Parameters=:Parameters");
           query.SetStructured("Parameters", dataTable).List();
    }

Can someone help me please? Thank you.

c#
sql
nhibernate
datatable
asked on Stack Overflow Jan 16, 2020 by daniel • edited Jan 16, 2020 by Mahdi Anjam

0 Answers

Nobody has answered this question yet.


User contributions licensed under CC BY-SA 3.0