0

I need to insert bulk data into a SQL Server database.

The data is fetched in SqlParameter.

foreach (DataColumn c in data.Columns)
{
    SqlDbType dbType = GetSqlDbTypeFromDotnetType(c.DataType);

    var columnDataObject = data.AsEnumerable().Select(r => r.Field<object>(c.ColumnName));

    object[] paramDataArray = (UploadEverythingInSingleBatch)
             ? columnDataObject.ToArray()
             : columnDataObject.Skip(skipOffset).Take(batchSize).ToArray();

    SqlParameter param = new SqlParameter();
    param.SqlDbType = dbType;
    param.Value = paramDataArray;
    parameters.Add(param);
}

SqlCommand cmd = _connection.CreateCommand();
cmd.CommandText = commandText;

//parameters data replaced
parameters.ForEach(p => cmd.Parameters.Add(p));

int result = cmd.ExecuteNonQuery();

// on execution nonquery 
// {"Failed to convert parameter value from a Object[] to a Decimal."}

enter image description here

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Sakul Bhatia
  • 1
  • 1
  • You cannot pass an array as a parameter. You would need a Table Valued Parameter for that, and therefore a table type, which would be difficult to do generically. I suggest you create a custom table type and script for the table you want to upload into. Alternatively just use `SqlBulkCopy`, which is very fast and easy to use (but only works with simple inserts) – Charlieface Sep 22 '22 at 11:22
  • Thank you. Can you please share SqlBulk copy – Sakul Bhatia Sep 22 '22 at 11:33
  • Second link above should help you – Charlieface Sep 22 '22 at 11:47

0 Answers0