CODEWITHSUNDEEP
c#sql-serverado.netdecimalsqlbulkcopy
Alsin
Alsin

Reputation: 1628

DataTable and big SqlDecimal

I just found out that when you use DataTable and SqlBulkCopy in an attempt to load a SQL Server table, you can't use big decimal values.

DataTable only supports the .NET decimal type. .NET decimal is smaller than SQL Server decimal, so when you exceed the .NET range, you get an exception.

I prepared a DataTable that contains data rows with SqlDecimal columns and tried to bulk-load it.

bulkCopy.WriteToServer(dt);

But I get this error:

InvalidCastException: Failed to convert parameter value from a SqlDecimal to a Decimal.

Is there a workaround for this problem?

Update: After David's answer I double-checked the error. It turns out that it is not decimal but smallmoney column that caused this issue. An attempt to put SqlDecimal into smallmoney raises an error. It is very easy to avoid, for smallmoney and money columns .NET decimal is big enough.

Upvotes: 1

Views: 265

Answers (1)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89361

I can't repro that.

using System.Data.SqlClient;
using System.Collections;
using System.Data;
using System.Data.Common;
using System.Data.SqlTypes;

using var con = new SqlConnection("server=localhost;database=tempdb;integrated security=true;TrustServerCertificate=true");
con.Open();

var cmd = con.CreateCommand();
cmd.CommandText = "drop table if exists foo; create table foo(id int, d decimal(38,2))";
cmd.ExecuteNonQuery();

var dt = new DataTable();
dt.Columns.Add("id", typeof(int));
dt.Columns.Add("d", typeof(SqlDecimal));

var d = SqlDecimal.Parse("123456789012345678901234567890123456.78");
dt.Rows.Add(1, d);


var bc = new SqlBulkCopy(con);
bc.DestinationTableName = "foo";
bc.WriteToServer(dt);

Upvotes: 0

Related Questions