C# SqlCommand Inserting Null Values

Question

I have a CSV file that has about 4 million rows of clean data in it, and I need to import it into a SQL Server 2008 R2 database.

I tried to do this via TSQL only, SSIS, and few other methods, but in the end I always encountered the same problem, each row has a 2,000+ columns. SQL server had a problem with reading that many columns in a CSV file.

So, I ended up writing a little C# console program to parse the csv file and do all the work to break out the 2,000+ columns into their relational tables in my database. Everything is working great, but with the CSV file having 4 million rows, efficiency is key here.

All that said, here is the problem I am having, and can't for the life me figure out. I am using the old SqlCommand class to do bulk inserts. I can't figure out how to get a null value into the db. For instance, take this line

var PreparedCommand = "insert into person_modeling_info (person_id, modeling_info,     response) values "; 

PreparedCommand += "( " + info[0] + ",'" + info[1] + "'," + (item2.Value == "" ? "'NULL'" : "'"+item2.Value+"'") + "),";

I am not worried about SQL injection, as I am 100% sure that the data is clean. I know I can insert null using parameters, but parameters are slower as I have to insert one record at a time vs. doing a bulk insert like insert into table (values), (values), (values)

When I perform the insert using concatenation, it gets inserted into the database as an empty string.

If any one has an ideas, any help would be greatly appreciated.

I am not worried about SQL injection You should my friend, you should.. — Soner Gönül
– Soner Gönül, Commented Jan 6, 2014 at 14:10
Why, it's moving data from one system in house to another in house. — mituw16
– mituw16, Commented Jan 6, 2014 at 14:11
NULL without quotes should do. Of course, injection issue stands as is. — Abhitalks
– Abhitalks, Commented Jan 6, 2014 at 14:12
"'NULL'" produces the string NULL, remove the single quotes as its a DB keyword — Alex K.
– Alex K., Commented Jan 6, 2014 at 14:13

chridam · Accepted Answer · 2014-01-06 14:22:57Z

4

Are you sure item2.Value isn't already == null? Have you tried:

PreparedCommand += "( " + info[0] + ",'" + info[1] + "'," + (item2.Value == null ? "'NULL'" : "'"+item2.Value+"'") + "),";

edited Jan 6, 2014 at 14:22

chridam

104k26 gold badges246 silver badges243 bronze badges

answered Jan 6, 2014 at 14:17

TylerD87

1,61813 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Chris Over a year ago

I'd suggest use of String.IsNullOrEmpty rather than a simple null check (given that what you are doing is changing the behaviour of empty strings) but yes, this seems like a plausible explanation.

Andomar Over a year ago

@Chris: in a database, like in C#, null and '' are different.

Chris Over a year ago

@Andomar: Yes, they are but the user is currently treating an empty string the same as null so I would be inclined to keep that behaviour while also checking for null rather than change the existing behaviour without explicitly flagging it up. If the user has problems with empty strings going in as null as well then I'm sure he would have saaid.

Bhavesh Kachhadiya · Accepted Answer · 2014-01-06 14:22:09Z

1

You should try in this way:

you have to remove single quote from the 'NULL', also add check for item2.Value is null or empty.

var PreparedCommand = "insert into person_modeling_info (person_id, modeling_info,     response) values "; 

    PreparedCommand += "( " + info[0] + ",'" + info[1] + "'," + ( string.IsNullOrEmpty(item2.Value)==true?  "NULL" : "'"+item2.Value+"'") + "),";

edited Jan 6, 2014 at 14:22

answered Jan 6, 2014 at 14:15

Bhavesh Kachhadiya

3,9723 gold badges18 silver badges20 bronze badges

Comments

Akeel · Accepted Answer · 2014-01-06 14:24:20Z

1

You could just make your insert columns conditional using the same idea:

var PreparedCommand = "insert into person_modeling_info (person_id, modeling_info" + (item2.Value == "" ? "" : ",response") + ") values "; 

PreparedCommand += "( " + info[0] + ",'" + info[1] + "'" + (item2.Value == "" ? "" : ",'"+item2.Value+"'") + "),";

Preferably, you'd want to use SQL Parameters and use DbNull.Value

answered Jan 6, 2014 at 14:24

Akeel

1214 bronze badges

1 Comment

mituw16 Over a year ago

Right, I tried that initially, but it was slower as doing that required me to insert each row individually. When I moved to shudder string concatenation with bulk inserting, it shaved about 40 seconds off run time.

Remus Rusanu · Accepted Answer · 2014-01-06 14:15:30Z

0

Use SqlParameters and assign DbNull.Value. Really, no excuses. On a public forum like this posting SQL injection code is an offense. Some innocent idiot will copy/paste it 2 years later...

Of course, moving efficiently 4M rows from CSV is really a question about bcp.exe, SSIS or, at worst, SqlBulkCopy. I recommend reading the The Data Loading Performance Guide and learn about Operations That Can Be Minimally Logged.

answered Jan 6, 2014 at 14:15

Remus Rusanu

296k42 gold badges459 silver badges583 bronze badges

1 Comment

mituw16 Over a year ago

It's not an offense, and you are wrong. I am not an idiot, and I am very aware that it is SQL injection vulnerable. However, I am not worried about that as this program is doing nothing more than moving CLEAN data from an in house flat file into a relational database.

Collectives™ on Stack Overflow

C# SqlCommand Inserting Null Values

4 Answers 4

3 Comments

Comments

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related