I have something like that on DB side:
CREATE TYPE dbo.idstable AS TABLE
(
Idx bigint NOT NULL PRIMARY KEY
);
GO
CREATE PROCEDURE [usp_GetIds]
@input idstable READONLY
AS
SELECT * from @input
go
Id like to call usp_GetIds from C# code using odbc.
using (var command = new OdbcCommand("usp_GetIds", conn))
{
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddRange(parameters);
using (var reader = command.ExecuteReader())
{
....
}
}
Right now I stuck on something like that:
var parameters = new[] { new OdbcParameter("@input", CreateDataTable(ids)) };
private static DataTable CreateDataTable(IEnumerable<int> Ids)
{
DataTable table = new DataTable();
table.Columns.Add("Idx", typeof(Int23));
foreach (int id in Ids)
{
table.Rows.Add(id);
}
return table;
}
and message: "No mapping exists from object type System.Data.DataTable to a known managed provider native type."
Is there any way to pass table as input param to stored procedure using odbc? or I have to use SqlConnection/SqlCommand/SqlParameter?
