/// <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>
/// Gets the string to create the SQL table.
/// </summary>
///
/// <remarks>
/// <example>
/// <code>
/// CREATE TABLE BCPTest (
/// col0 xml,
/// col1 xml,
/// col2 nvarchar(max),
/// col3 varbinary(max)
/// )
/// </code>
/// </example>
/// </remarks>
///
/// <param name="columns">List of SQL types.</param>
/// <returns>SQL create table string.</returns>
static private string GetCreateTableString(IEnumerable <IBCPSerialization> columns)
{
StringBuilder createTableString = new StringBuilder();
createTableString.AppendLine("CREATE TABLE BCPTest (");
int columnNumber = 0;
foreach (IBCPSerialization column in columns)
{
createTableString.AppendFormat("col{0} ", columnNumber++);
// 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;
createTableString.AppendFormat("binary({0})", sql.Length);
}
else if (column is SQLChar)
{
SQLChar sql = (SQLChar)column;
createTableString.AppendFormat("char({0})", sql.Length);
}
else if (column is SQLInt)
{
SQLInt sql = (SQLInt)column;
createTableString.Append("int");
}
else if (column is SQLNChar)
{
SQLNChar sql = (SQLNChar)column;
createTableString.AppendFormat("nchar({0})", sql.Length);
}
else if (column is SQLNVarChar)
{
SQLNVarChar sql = (SQLNVarChar)column;
if (sql.Length == SQLNVarChar.MAX)
{
createTableString.Append("nvarchar(max)");
}
else
{
createTableString.AppendFormat("nvarchar({0})", sql.Length);
}
}
else if (column is SQLVarBinary)
{
SQLVarBinary sql = (SQLVarBinary)column;
if (sql.Length == SQLVarBinary.MAX)
{
createTableString.Append("varbinary(max)");
}
else
{
createTableString.AppendFormat("varbinary({0})", sql.Length);
}
}
else if (column is SQLVarChar)
{
SQLVarChar sql = (SQLVarChar)column;
if (sql.Length == SQLVarChar.MAX)
{
createTableString.Append("varchar(max)");
}
else
{
createTableString.AppendFormat("varchar({0})", sql.Length);
}
}
else if (column is SQLNText)
{
SQLNText sql = (SQLNText)column;
createTableString.Append("ntext");
}
else if (column is SQLText)
{
SQLText sql = (SQLText)column;
createTableString.Append("text");
}
else if (column is SQLXml)
{
SQLXml sql = (SQLXml)column;
createTableString.Append("xml");
}
else if (column is SQLReal)
{
SQLReal sql = (SQLReal)column;
createTableString.Append("real");
}
else if (column is SQLFloat)
{
SQLFloat sql = (SQLFloat)column;
createTableString.Append("float");
}
else if (column is SQLUniqueIdentifier)
{
SQLUniqueIdentifier sql = (SQLUniqueIdentifier)column;
createTableString.Append("uniqueidentifier");
}
else if (column is SQLBigInt)
{
SQLBigInt sql = (SQLBigInt)column;
createTableString.Append("bigint");
}
else if (column is SQLDateTime)
{
SQLDateTime sql = (SQLDateTime)column;
createTableString.Append("datetime");
}
else if (column is SQLDateTime2)
{
SQLDateTime2 sql = (SQLDateTime2)column;
createTableString.Append("datetime2");
}
else if (column is SQLDate)
{
SQLDate sql = (SQLDate)column;
createTableString.Append("date");
}
else if (column is SQLTime)
{
SQLTime sql = (SQLTime)column;
createTableString.Append("time");
}
else
{
System.Diagnostics.Trace.Assert(false);
}
if (columnNumber < columns.Count())
{
createTableString.AppendLine(",");
}
}
createTableString.Append(")");
return(createTableString.ToString());
}
/// <summary>
/// Writes a SQL varbinary.
/// </summary>
public void WriteVarBinary(byte[] value, uint length)
{
SQLVarBinary.Write(_writer, value, length);
}