C# BulkCopy.WriteToServer Changing Values

Question

I have a datatable that is created from and excel sheet. I am trying to SqlBulkCopy.WriteToServer. When I do so it converts my "Percent" value to 0 when my tables schema has type Decimal(38,0) for the "Percent" column. However, it inserts correctly when I have the Data Type as Float. I am not sure what is going on. I would like to use Decimal(38, 0) if possible because the spread sheet I am given may go more than 15 decimal places for that value.

The datatable is type string for all columns because I may have to deal with null values.

Here is my c# code:

private bool ImportToDataBase(DataTable emeTable)
{
    string serverName = PARR_DBDataObject.ServerName;
    string dbName = PARR_DBDataObject.DBName;
    bool dataUploaded = false;

    using ( SqlConnection conn = SQL.ConnectToDB(dbName, serverName, false))
    {
        conn.Open();
        string createTempTable = $@"CREATE TABLE #Holdings(
                                        [Fund][varchar](25) NULL,
                                        [Percent] [varchar](38) NULL,
                                        [Committed] [varchar](25) NULL,
                                        [DryPowder] [varchar](25) NULL,
                                        [CashBalance] [varchar](25) NULL,
                                        [Trades] [varchar](25) NULL,
                                        [Capital] [varchar](25) NULL,
                                        [MgmtFee] [varchar](25) NULL,
                                        [Cash] [varchar](25) NULL,
                                        [ReportDate] [datetime] NULL);";
        SqlCommand cmd = new SqlCommand(createTempTable, conn);
        cmd.ExecuteNonQuery();

        using(SqlBulkCopy blkCopy = new SqlBulkCopy(conn))
        {
            decimal test = Convert.ToDecimal(emeTable.Rows[0][1]);
            foreach(DataColumn col in emeTable.Columns)
            {
                blkCopy.ColumnMappings.Add(col.Ordinal, col.Ordinal);
            }

            blkCopy.DestinationTableName = "#Holdings";
            blkCopy.WriteToServer(emeTable);

            string sqlSelect = "SELECT * FROM #Holdings";
            SqlCommand emeCmd = new SqlCommand(sqlSelect, conn);
            SqlDataAdapter da = new SqlDataAdapter(emeCmd);
            DataTable testTable = new DataTable();
            da.Fill(testTable);
            da.Dispose();
        }
        string spName = DBDataObject.sp_Insert_Into_CashHoldings;
        SqlCommand cmdSP = new SqlCommand(spName, conn);
        cmdSP.ExecuteNonQuery();

        //SQL.NonQueryStoredProcedure(spName, dbName, serverName, false, 600);
        dataUploaded = true;

    }
    return dataUploaded; 
}

Here is my SQL for getting the data from the Temp Table and into the main Table:

ALTER PROCEDURE [dbo].[sp_Insert_Into_CashHoldings] 

AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    MERGE t_CashHoldings AS TARGET 
        USING #Holdings AS SOURCE
    ON (TARGET.Fund = SOURCE.Fund AND TARGET.ReportDate= SOURCE.ReportDate)
    WHEN MATCHED THEN
        UPDATE SET TARGET.Percent = SOURCE.Percent, 
                   TARGET.Committed = SOURCE.Committed,
                   TARGET.DryPowder = SOURCE.DryPowder,
                   TARGET.Trades = SOURCE.Trades,
                   TARGET.Capital = SOURCE.Capital,
                   TARGET.MgmtFee = SOURCE.MgmtFee,
                   TARGET.Cash = SOURCE.Cash
    WHEN NOT MATCHED THEN
    INSERT (Fund, Percent, Committed, DryPowder, CashBalance, Trades, Capital, MgmtFee, Cash, ReportDate)
    VALUES (SOURCE.Fund, SOURCE.Percent, SOURCE.Committed, SOURCE.DryPowder, SOURCE.CashBalance, SOURCE.Trades, SOURCE.Capital,
            SOURCE.MgmtFee, SOURCE.Cash, SOURCE.ReportDate);

    DROP TABLE #Holdings;

END

Daniel Lorenz · Accepted Answer · 2020-02-04 03:30:20Z

1

If this is the wrong answer, I can delete it - but if you do decimal(38,0) that is saying you want a # that has 38 places to the left of the decimal and allow 0 to the right. You'd want something like 38,10 which would mean 38 total numbers but 28 to the left and 10 to the right. If your values are stored like 0.56 then this is probably why its getting cut off.

That being said, I would recommend doing something like 28,10 because .NET only supports up to 28 precision vs sql server's 38. If you try to bring in a big # like that to .NET later, it'll crash.

Hope this helps!

answered Feb 4, 2020 at 3:30

Daniel Lorenz

4,3461 gold badge35 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dano · Accepted Answer · 2023-05-25 22:32:44Z

0

I was running into this as well. The data type in Sql was decimal(x,y), don't think the precision mattered, some posts i found mention trying (38,5) or other wierd variants, still no work...

I changed the column type to be money in SQL, seems that works perfect now, while i didn't try with a high level of precision, but would assume money in SQL will best ensure accuracy vs normal floating point would by the standard never ever guarantee any level of accuracy...

answered May 25, 2023 at 22:32

Dano

1127 bronze badges

Collectives™ on Stack Overflow

C# BulkCopy.WriteToServer Changing Values

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related