CODEWITHSUNDEEP
c#asp.netsql-serversql-server-2005datatable
thevan
thevan

Reputation: 10354

How to Insert a DataTable containing Null values in to Sql Server Table?

I have a DataTable which also contains some Null values such as

   ID             CustomerID           CompanyID
 -------        --------------       --------------
   1                  2                  Null
   2                 Null                1
   3                  5                  4

I want to insert this DataTable into a Table. In SQL Server 2005, I have one Table called "JobDetails" which has three columns "JobOrderID", "CustomerID" and "CompanyID". So the above DataTable to be inserted into the "JobDetails" table like follows:

   JobOrderID      CustomerID          CompanyID
  ------------    ------------        ------------
       1               2                Null
       2              Null                1
       3               5                  4

How to achieve this? I tried to pass the DataTable as XML. But due to the presence of NUll values it throws error not inserting.

Error means, the XML tags not considering the NULL values, so it takes as 

  <NewDataSet>
      <JobDetails> 
          <JobOrderID>1</JobOrderID>
          <CustomerID>2</CustomerID>
      </JobDetails>
      <JobDetails> 
          <JobOrderID>2</JobOrderID>
          <CompanyID>1</CompanyID>
      </JobDetails>
      <JobDetails> 
          <JobOrderID>3</JobOrderID>
          <CustomerID>5</CustomerID>
          <CompanyID>4</CompanyID>
      </JobDetails>
  </NewDataSet>

It neglects the tags which contains NULL. So the insertion not takes place.

Upvotes: 1

Views: 3143

Answers (4)

Pankaj Agarwal
Pankaj Agarwal

Reputation: 11311

I think you can replace NULL with their default value for ex. in companyId field NULL replace with -1 or 0 and in SQL Server you can again replace -1 or o to NULL.

Through replacing ID will be part of XML. I know it is not good solution.

Upvotes: 1

Akram Shahda
Akram Shahda

Reputation: 14781

I think it would be easier if you follow the method described in the following article:

Use DataTable to update table in Database

Upvotes: 1

Marc Gravell
Marc Gravell

Reputation: 1062865

Normally you would insert from a DataTable via an adapter (such as System.Data.SqlClient.SqlDataAdapter). This will handle the nulls etc. You could also use SqlBulkCopy if it is very large (although you need to be careful about transaction logs etc in that case, as a bulk insert might not be fully logged, IIRC).

caveat: personally, I would be using a DataTable here anyway - I'd be using regular typed classes.

Upvotes: 2

Adam Tuliper
Adam Tuliper

Reputation: 30152

In a case to get a DataTable into another table, use the SqlBulkCopy class. Ensure your values that are null are set to DBNull.Value as opposed to 'null' and use the SqlBulkCopy to set the destination table name


using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
{
   bulkCopy.DestinationTableName = destinationTableName;
   bulkCopy.WriteToServer(yourDataTable);
}

Upvotes: 1

Related Questions