Serializing Object - Replacing Values

Question

I've got a collection of around 20,000 objects that need to get persisted to my database. Now, instead of doing 20,000 insert statements, I want to pass all the records in using an XML parameter.

As far as serializing the object and passing it into the procedure goes, I'm all set. However, I'm wondering if anyone has an elegant way to do the following:

In our C# code base; we have some static values that represent a NULL when saved to the database. For example, if an integer equals -1, or a DateTime equals DateTime.MinValue; save NULL. We have our own little custom implementation that handles this for us when saving objects.

Is there any way I can do something similar to this when performing the XML serialization? Right now it's outputting -1 and DateTime.MinValue in the XML. I do have an extension method (IsNull()) that will return true/false if the value being saved is the null default value.

Any suggestions? Tips/Tricks?

If you're using 3.5, why are you using reserved values instead of `Nullable` for null values? — Adam Robinson, Jul 21 '10 at 18:22
*Now, instead of doing 20,000 insert statements, I want to pass all the records in using an XML parameter.* Whoa there, cowboy! Let's step back and address this before before committing yourself to XML, have a look at the BULK INSERT statement which is designed for these sort of queries: http://msdn.microsoft.com/en-us/library/ms188365.aspx — Juliet, Jul 21 '10 at 18:38
@STW: My comment was intended to emphasize that (he's using 3.5, which is two releases newer than the version where they were introduced). — Adam Robinson, Jul 21 '10 at 19:42
@Juliet: Good point. I read through that documentation, but is using bulk insert possible using a parameter? I'm not loading from a datafile; rather these are all records that are being "assembled" in a C# class library. — Jim B, Jul 21 '10 at 20:06
@Jim: there's nothing in principle which prevents you from writing our your objects to a flat file. So long as your SQL Server can get to your file location, then yes, you can pass a filename to your stored procedure as a parameter in order to perform your bulk insert. — Juliet, Jul 21 '10 at 20:34

score 0 · Answer 1 · edited Jul 21 '10 at 19:22

0

You can implement IXmlSerializable to control an object's XML serialization. In particular, implement WriteXml to substitute blank or xsi:null values (however you want to handle this) for those properties/fields that contain your null signifier values.

edited Jul 21 '10 at 19:22

STW

44,917
17
105
161

answered Jul 21 '10 at 18:25

Ben M

22,262
3
67
71

STW · Accepted Answer · 2010-07-21T19:22:29.243

The XmlSerializer understands a number of different attributes; one of them is DefaultValueAttribute.

When included, the XmlSerializer will only serialize the value check if the actual value differs from the default, so all you should need is:

[DefaultValue(-1)]
public int SomeProperty
{get;set;}

Also, if you haven't considered it, take a look at the SqlBulkCopy class, which is a highly-performant approach to sending a large number of records to SQL Server.

Serializing Object - Replacing Values

2 Answers2