CODEWITHSUNDEEP
c#.netsql-server
knowdotnet
knowdotnet

Reputation: 925

SQL Server UDT and stored procedure trimming decimal digit values from .Net code

We are using .net framework 4.7.2. we call a sp with User Defined type variable as its only parameter.

CREATE TYPE [dbo].[ABC] AS TABLE(
[A] [int] NOT NULL,
[B] [datetime] NOT NULL,
[C] [datetime] NOT NULL,
[Value] [decimal](19, 6) NULL)

corresponding stored procedure is

CREATE PROCEDURE [dbo].[myUSP]
@data dbo.ABC readonly AS
BEGIN
SET NOCOUNT ON;
IF EXISTS (SELECT 1 FROM @data)
BEGIN
      INSERT INTO dbo.MyTable
      SELECT A, B, C, [Value] FROM @data;
END END

My .Net code is 

            using (SqlConnection con = new SqlConnection(connectionString))
            {
                    using (SqlCommand insertCmd = new SqlCommand("dbo.myUSP", con))
                    {
                        con.Open();
                        using (transaction = con.BeginTransaction(IsolationLevel.RepeatableRead))
                        {
                            insertCmd.Transaction = transaction;
                            insertCmd.CommandType = CommandType.StoredProcedure;
                            try
                            {
                                SqlParameter parameter1 = insertCmd.Parameters.AddWithValue("@data", CreateSqlRecord(insert));
                                parameter1.SqlDbType = SqlDbType.Structured;
                                parameter1.TypeName = "dbo.ABC";
                                insertCmd.ExecuteNonQuery();
                                transaction.Commit();
                            }
                            catch (Exception ex)
                            {
                                transaction.Rollback();
                            }
                        }
                    }
            }

    private IEnumerable<SqlDataRecord> CreateSqlRecord(IEnumerable<DataElementInput> entities)
    {
        SqlMetaData[] metaData = new SqlMetaData[4];
        metaData[0] = new SqlMetaData("A", SqlDbType.Int);
        metaData[1] = new SqlMetaData("B", SqlDbType.DateTime);
        metaData[2] = new SqlMetaData("C", SqlDbType.DateTime);
        metaData[3] = new SqlMetaData("Value", SqlDbType.Decimal);
        SqlDataRecord record = new SqlDataRecord(metaData);
        foreach (Model myModel in entities)
        {
            record.SetInt32(0, myModel .A);
            record.SetDateTime(1,myModel.B);
            record.SetDateTime(2, myModel.C);
            record.SetDecimal(3, (Decimal)myModel.Value);
            yield return record;
        }
    }

I checked the value of parameter1 bvefore it is passed to SQLConnection and executed using ExecuteNonQuery, and it contains decimal values correctly. On other hand i also checked run my sp directly from SQL server management studio and it is inserting right decimal values in table dbo.MyTable.

BEGIN DECLARE @data dbo.ElementFactData;INSERT @data (ElementId,StartDateTime, EndDateTime, Value) VALUES(  1002, '1/1/1800' , '1/1/1900' , 0.786); exec dbo.myUSP @data;END

However when i try to insert records from .net code decimal value less than 0.5 become 0 and values above 0.5 become 1. Like 4.2 become 4 and 5.87 become 6

Anything wrong in my .net code?

Upvotes: 1

Views: 305

Answers (1)

Matthias Ausw&#246;ger
Matthias Ausw&#246;ger

Reputation: 632

I guess that

  metaData[3] = new SqlMetaData("Value", SqlDbType.Decimal);

needs to be specified with a precisionand scale. So that it looks like that:

  metaData[3] = new SqlMetaData("Value", SqlDbType.Decimal, 19, 6);

Your CreateSqlRecord method should look then like this:

private IEnumerable<SqlDataRecord> CreateSqlRecord(IEnumerable<DataElementInput> entities)
{
    SqlMetaData[] metaData = new SqlMetaData[4];
    metaData[0] = new SqlMetaData("A", SqlDbType.Int);
    metaData[1] = new SqlMetaData("B", SqlDbType.DateTime);
    metaData[2] = new SqlMetaData("C", SqlDbType.DateTime);
    metaData[3] = new SqlMetaData("Value", SqlDbType.Decimal, 19, 6);
    SqlDataRecord record = new SqlDataRecord(metaData);
    foreach (Model myModel in entities)
    {
        record.SetInt32(0, myModel .A);
        record.SetDateTime(1,myModel.B);
        record.SetDateTime(2, myModel.C);
        record.SetDecimal(3, (Decimal)myModel.Value);
        yield return record;
    }
}

Upvotes: 3

Related Questions