C# DataTable with a ushort field as parameter to a SQL Server stored procedure

Question

I have a store procedure in SQL Server that has as a parameter a table value type.

Table valued type is the following:

CREATE TYPE [Serveis].[tblType_DispensadorBebidas_Abonados] AS TABLE 
(
    [ID] [varchar](9) NOT NULL,
    [Field1] [smallint] NOT NULL,
    [Field2] [smallint] NOT NULL,
    [Date] [datetime] NOT NULL,
    PRIMARY KEY ([ID])
)

Then the stored procedure takes this table valued type as parameter:

PROCEDURE [dbo].[AddData] 
    @DataTable MytableValuedType READONLY

From C# I create a DataTable as below:

DataTable dt = new DataTable();

dt.Columns.Add("ID", typeof(string)).SetOrdinal(0);           
dt.Columns.Add("Field1", typeof(ushort)).SetOrdinal(1);
dt.Columns.Add("Field2", typeof(ushort)).SetOrdinal(2);
dt.Columns.Add("Date", typeof(DateTime)).SetOrdinal(3);

dt.Rows.Add(this.ID,this.Field1,this.Field2,this.Date);

In C# data types are:

• ID string
• Field1 ushort
• Field2 ushort
• Date DateTime

and then I create a SQL Parameter:

SqlParameter param = new SqlParameter();
param.ParameterName = "@DataTable";
param.Value = dt;
param.SqlDbType = SqlDbType.Structured;

Add it using command.Parameters.Add(param)

and finally I call ExecuteNonQuery.

It throws the error (translated from spanish):

Column type 'Field1' not allowed. The type is "'UInt16'".

How could I solve this?

Answer 1

smallint in SQL Server is signed. You will need to use a matching signed data type (i.e. short) in your C# code. This should be as simple as:

    dt.Columns.Add("ID", typeof(string)).SetOrdinal(0);           
    dt.Columns.Add("Field1", typeof(short)).SetOrdinal(1);
    dt.Columns.Add("Field2", typeof(short)).SetOrdinal(2);
    dt.Columns.Add("Date", typeof(DateTime)).SetOrdinal(3);