How do you use a binarywriter to write the correct MS SQL native format for the Money data type?

Question

How do you use a binarywriter to write the correct MS SQL native format for the Money data type?

I'd like to take a value in .net, read from a file as string representation of a decimal amount (actually an exported "Money" data type from SQL, but that is unimportant).

How can I use a binary writer to write the value so that you can use BCP or BULK INSERT in native format mode to read the value in successfully?

Answer 1

(where w is a binary writer previously instantiated)

                    Dim dec = CDec(aString)
                    Dim lng = CLng(dec * 10000)
                    Dim bytes = BitConverter.GetBytes(lng)
                    w.Write(bytes(4))
                    w.Write(bytes(5))
                    w.Write(bytes(6))
                    w.Write(bytes(7))
                    w.Write(bytes(0))
                    w.Write(bytes(1))
                    w.Write(bytes(2))
                    w.Write(bytes(3))

There may be cleaner or better ways, but this seems to be ok

as a heads up, this format is only for NON NULL money columns, I think you have to write a length byte first or something to that effect for nullable money columns

Answer 2

I don't recall how to do null values on numeric types in delimited files with BCP but I believe if you use a length-prefixed file, a length value of -1 denotes a null value.