/// <summary>
/// Writes all the values to the .bcp file.
/// </summary>
/// <remarks>
/// You must call AddColumn() or AddColumns() before.<br/>
/// The rows given to this method should match the SQL types (the columns).<br/>
/// Don't forget to close your BinaryWriter after using this method.
/// </remarks>
/// <param name="writer">BinaryWriter to use to write the .bcp file.</param>
/// <param name="rows">The values to write to the .bcp file.</param>
public void WriteRows(BinaryWriter writer, IEnumerable <object> rows)
{
if (_columns.Count() == 0)
{
throw new ArgumentException("No columns");
}
int nbRows = rows.Count();
int nbColumns = _columns.Count();
int modulo = nbRows % nbColumns;
if (modulo != 0)
{
throw new ArgumentException(
string.Format("The number of rows ({0}) should match the number of columns ({1})",
nbRows, nbColumns));
}
for (int i = 0; i < nbRows; i++)
{
IBCPSerialization column = _columns[i % nbColumns];
object row = rows.ElementAt(i);
column.Write(writer, row);
}
if (Mode == BackendMode.Debug)
{
new BCPWriterSQLServer(writer, _columns, rows);
}
}
/// <summary>
/// Gets the string to insert values into the SQL table.
/// </summary>
///
/// <remarks>
/// <example>
/// <code>
/// INSERT INTO BCPTest VALUES
/// (
/// 'string',
/// 'string',
/// NULL,
/// NULL
/// ),
/// (
/// 'string',
/// 'string',
/// NULL,
/// NULL
/// )
/// </code>
/// </example>
/// </remarks>
///
/// <param name="columns">List of SQL types.</param>
/// <param name="rows">Values.</param>
/// <returns>SQL insert into table string.</returns>
static private string GetInsertIntoString(List <IBCPSerialization> columns, IEnumerable <object> rows)
{
StringBuilder insertIntoString = new StringBuilder();
if (rows.Count() == 0)
{
return(string.Empty);
}
insertIntoString.AppendLine("INSERT INTO BCPTest VALUES");
for (int i = 0; i < rows.Count(); i++)
{
int modulo = i % columns.Count();
if (modulo == 0 && i > 0)
{
insertIntoString.AppendLine("),");
}
if (modulo > 0)
{
insertIntoString.AppendLine(",");
}
if (modulo == 0)
{
insertIntoString.Append("(");
}
IBCPSerialization column = columns[modulo];
object row = rows.ElementAt(i);
// FIXME Is there a better way than casting every type?
// Don't forget to add new SQL types here and to modify the unit tests accordingly
if (column is SQLBinary)
{
SQLBinary sql = (SQLBinary)column;
byte[] value = (byte[])row;
if (value == null)
{
insertIntoString.Append("NULL");
}
else
{
insertIntoString.AppendFormat(
"CAST('{0}' AS binary({1}))",
Encoding.Default.GetString(value), sql.Length);
}
}
else if (column is SQLChar)
{
string value = (string)row;
if (value == null)
{
insertIntoString.Append("NULL");
}
else
{
insertIntoString.AppendFormat("'{0}'", value);
}
}
else if (column is SQLInt)
{
int?value = (int?)row;
if (!value.HasValue)
{
insertIntoString.Append("NULL");
}
else
{
insertIntoString.AppendFormat("{0}", value.Value);
}
}
else if (column is SQLNChar)
{
string value = (string)row;
if (value == null)
{
insertIntoString.Append("NULL");
}
else
{
insertIntoString.AppendFormat("'{0}'", value);
}
}
else if (column is SQLNVarChar)
{
string value = (string)row;
if (value == null)
{
insertIntoString.Append("NULL");
}
else
{
insertIntoString.AppendFormat("'{0}'", value);
}
}
else if (column is SQLVarBinary)
{
SQLVarBinary sql = (SQLVarBinary)column;
byte[] value = (byte[])row;
if (value == null)
{
insertIntoString.Append("NULL");
}
else
{
if (sql.Length == SQLVarBinary.MAX)
{
insertIntoString.AppendFormat(
"CAST('{0}' AS varbinary(max))",
Encoding.Default.GetString(value));
}
else
{
insertIntoString.AppendFormat(
"CAST('{0}' AS varbinary({1}))",
Encoding.Default.GetString(value), sql.Length);
}
}
}
else if (column is SQLVarChar)
{
string value = (string)row;
if (value == null)
{
insertIntoString.Append("NULL");
}
else
{
insertIntoString.AppendFormat("'{0}'", value);
}
}
else if (column is SQLNText)
{
string value = (string)row;
if (value == null)
{
insertIntoString.Append("NULL");
}
else
{
insertIntoString.AppendFormat("'{0}'", value);
}
}
else if (column is SQLText)
{
string value = (string)row;
if (value == null)
{
insertIntoString.Append("NULL");
}
else
{
insertIntoString.AppendFormat("'{0}'", value);
}
}
else if (column is SQLXml)
{
XmlDocument value = (XmlDocument)row;
if (value == null)
{
insertIntoString.Append("NULL");
}
else
{
insertIntoString.AppendFormat("'{0}'", value.DocumentElement.OuterXml);
}
}
else if (column is SQLReal)
{
float?value = (float?)row;
if (!value.HasValue)
{
insertIntoString.Append("NULL");
}
else
{
insertIntoString.AppendFormat("{0}", value.Value);
}
}
else if (column is SQLFloat)
{
if (row is float)
{
// Don't treat null case here
float?value = (float?)row;
insertIntoString.AppendFormat("{0}", value.Value);
}
else
{
// If we don't know, let's cast it to double
// if value is null then double? will work, not float?
// More explanations inside SQLFloat
double?value = (double?)row;
if (!value.HasValue)
{
insertIntoString.Append("NULL");
}
else
{
insertIntoString.AppendFormat("{0}", value.Value);
}
}
}
else if (column is SQLUniqueIdentifier)
{
Guid?value = (Guid?)row;
if (!value.HasValue)
{
insertIntoString.Append("NULL");
}
else
{
insertIntoString.AppendFormat("'{0}'", value.Value);
}
}
else if (column is SQLBigInt)
{
long?value = (long?)row;
if (!value.HasValue)
{
insertIntoString.Append("NULL");
}
else
{
insertIntoString.AppendFormat("{0}", value.Value);
}
}
else if (column is SQLDateTime)
{
DateTime?value = (DateTime?)row;
if (!value.HasValue)
{
insertIntoString.Append("NULL");
}
else
{
insertIntoString.AppendFormat("'{0}'", value.Value);
}
}
else if (column is SQLDateTime2)
{
DateTime?value = (DateTime?)row;
if (!value.HasValue)
{
insertIntoString.Append("NULL");
}
else
{
insertIntoString.AppendFormat("'{0}'", value.Value);
}
}
else if (column is SQLDate)
{
DateTime?value = (DateTime?)row;
if (!value.HasValue)
{
insertIntoString.Append("NULL");
}
else
{
insertIntoString.AppendFormat("'{0}'", value.Value);
}
}
else if (column is SQLTime)
{
DateTime?value = (DateTime?)row;
if (!value.HasValue)
{
insertIntoString.Append("NULL");
}
else
{
insertIntoString.AppendFormat("'{0}'", value.Value);
}
}
else
{
System.Diagnostics.Trace.Assert(false);
}
}
insertIntoString.Append(")");
return(insertIntoString.ToString());
}
/// <summary>
/// Add a column (SQL type).
/// </summary>
/// <param name="column">SQL type of the column.</param>
public void AddColumn(IBCPSerialization column)
{
_columns.Add(column);
}
/// <summary>
/// Add a column (SQL type).
/// </summary>
/// <param name="column">SQL type of the column.</param>
public void AddColumn(IBCPSerialization column)
{
_columns.Add(column);
}
