What to do to avoid rounding off decimal in SqlBulkCopy from C# to SQL Server?

Question

I am importing .csv file data into a SQL Server table using SqlBulkCopy. My .csv file can be of any format as I can't determine the columns in it and the datatypes of it as same column can have decimal and text values, so I need to pass the value as such in the .csv file. But for some reason, some .csv files decimal points getting rounding off.

DataTable csvData = new DataTable();
StreamReader streamreader = new StreamReader(apsrcfile);
string Headline = File.ReadLines(Csvfilepath).ElementAt(0);|
string[] Headerline = Headline.Split(',');
for (int i = 0; i < Headerline.Length; i++)
 {
    csvData.Columns.Add();
 }
string[] lines = File.ReadAllLines(csvfilepath);
foreach (string line in lines)
{
string[] col = line.Split(',');
}
csvData.Rows.Add(col);
streamreader.Dispose();

SqlConnection objConn = new SqlConnection(ConfigurationManager.ConnectionStrings["Live"].ConnectionString);
objConn.Open();

SqlBulkCopy objbulkinsert = new SqlBulkCopy(objConn);
objbulkinsert.DestinationTableName = "Mappingtable";

foreach (DataColumn column in csvData.Columns)
{
    objbulkinsert.ColumnMappings.Add(column.ColumnName.ToString(), column.ColumnName.ToString());
}

objbulkinsert.BulkCopyTimeout = 0;
objbulkinsert.WriteToServer(csvData);
objConn.Close();
                    

It works for other files. I don't know whether the .csv file is of problem, but opening it in notepad it shows the decimal point. I will attach the image of .csv file and SQL output I got.

Expected output: as such as .CSV file data in the SQL Server table.

Answer 1

Table Design Matters

The table design of the target table and the view of the CSV is unknown. It makes difficult to answer. Column2 in destination table can be DECIMAL or MONEY and it must have 2 decimal digits that are stored to the right of the decimal point. If the data type is DECIMAL without any decimal digits that upload data without decimal digits. This may have happened to you. I assumed the text file(MappingTable.CSV) is something like it

/94HD012,194.37,10
/94HD013,483.37,
/94HD014,100,1
11-0001,112.57,100

The structure of the destination will be

CREATE TABLE [dbo].[MappingTable](
    [Column1] [varchar](50) NULL,
    [Column2] [decimal](12, 2) NULL,
    [Column3] [varchar](50) NULL,
    [Column4] [varchar](50) NULL
)

Finally, it properly works with the following code.

        private void btnSQLBulkCopyCSV_Click(object sender, EventArgs e)
        {
            DataTable dataTable = new DataTable();
            string ConnStr = "Data Source= MyDB; Database=Learning; Integrated Security=SSPI;";
            SqlConnection objConn = new SqlConnection(ConnStr);
            objConn.Open();

            using (StreamReader reader = new StreamReader(@"E:\\Learning\\CSharp\\SampleData\MappingTable.csv"))
            {
                string[] headers = reader.ReadLine().Split(',');
                int columnCount = headers.Count();
                DataColumn[] columns; //
                columns = Enumerable.Range(1, columnCount).Select(x => new DataColumn($"Column{x}")).ToArray();
                dataTable.Columns.AddRange(columns);

                DataRow dataRow1 = dataTable.NewRow();
                for (int i = 0; i < columnCount; i++)
                {
                    dataRow1[i] = headers[i];
                }
                dataTable.Rows.Add(dataRow1);

                while (!reader.EndOfStream)
                {
                    string[] rows = reader.ReadLine().Split(',');
                    DataRow dataRow = dataTable.NewRow();
                    for (int i = 0; i < headers.Length; i++)
                    {
                        dataRow[i] = rows[i];
                    }
                    dataTable.Rows.Add(dataRow);
                }
                SqlBulkCopy objbulkinsert = new SqlBulkCopy(objConn);
                objbulkinsert.DestinationTableName = "Mappingtable";

                foreach (DataColumn column in dataTable.Columns)
                {
                    objbulkinsert.ColumnMappings.Add(column.ColumnName.ToString(), column.ColumnName.ToString());
                }

                objbulkinsert.BulkCopyTimeout = 0;
                objbulkinsert.WriteToServer(dataTable);
                objConn.Close();
            }
        }

This was implemented on .Net Framework 4.7.2