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.
User contributions licensed under CC BY-SA 3.0