C# BulkCopy.WriteToServer Changing Values

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

Answer

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!

Advertisement

Answer