I have a native C++ windows client application and a SQLServer 2008 server instance. I need to transfer a whole bunch of data both ways. I'm currently using SOCI (http://soci.sourceforge.net/) with an ODBC backend, but have already identified several performance problems with it and would like to try alternatives. SQL data types used are int, float, datetime, nvarchar and ntext.
Reading:
I'd like to use ODBC's SQLExtendedFetch to retrieve a whole bunch of rows at a time thus avoiding server roundtrips. However, with rows containing variable length strings (nvarchar) and fields requiring conversion (datetime) I don't know how to prepare a buffer to copy the data into. Is this at all possible?
Writing:
What's the fastest way to bulk insert lots of rows?
Is it advisable to use the ODBC interface in the first place or does a faster native interface exist?