if (param.Value != null)
continue;
tds.Comm.Append ((short) 0x00);
- tds.Comm.Append ((short) 0x0a);
+
+ if (param.IsNullable) {
+ // fNullable = true
+ // usUpdateable = Unused/Unkown
+ tds.Comm.Append ((short) 0x09);
+ } else {
+ // usUpdateable = Unused/Unkown
+ tds.Comm.Append ((short) 0x08);
+ }
+
WriteParameterInfo (param);
tds.Comm.Append ((byte) param.ParameterName.Length);
tds.Comm.Append (param.ParameterName);
return true;
}
- public bool BulkCopyData (object o, int size, bool isNewRow)
+ public bool BulkCopyData (object o, bool isNewRow, int size, TdsMetaParameter parameter)
{
- if (isNewRow) {
+ // First append a new row byte if needed
+ if (isNewRow)
tds.Comm.Append ((byte) TdsPacketSubType.Row);
+
+ // Push the null value if that is what was supplied
+ if (o == null || o == DBNull.Value) {
+ if (parameter.IsAnyVarCharMax) {
+ // So max varchar and nvarchar needs to contain all F's as a long value. Seems crazy
+ // but oh well
+ tds.Comm.Append(System.Convert.ToInt64("0xFFFFFFFFFFFFFFFF", 16));
+ } else if (parameter.IsTextType) {
+ tds.Comm.Append((byte)0XFF);
+ tds.Comm.Append((byte)0XFF);
+ }
+ else
+ tds.Comm.Append ((byte)0);
+ return true;
}
- if (size > 0) {
- tds.Comm.Append ((short) size);
+
+ // Now we must put the size in if it is a VariableType
+ // The length of the size field varies based on what type it is
+ parameter.CalculateIsVariableType();
+ if (parameter.IsVariableSizeType) {
+ //int size = parameter.GetActualSize();
+ if (parameter.IsAnyVarCharMax) {
+ // So max varchar and nvarchar needs to contain the long value as well as size is specified as int
+ tds.Comm.Append(System.Convert.ToInt64("0xFFFFFFFFFFFFFFFE", 16));
+ tds.Comm.Append ((int) size);
+ }
+ else if (o.GetType() == typeof(string))
+ tds.Comm.Append ((short) size);
+ else
+ tds.Comm.Append ((byte) size);
}
- tds.Comm.Append (o);
+
+ // There are a few special cases for bulk insert that we will handle ourself
+ // Otherwise we can just pass the value down to the generic Append Object function
+ if (parameter.IsNonUnicodeText)
+ tds.Comm.AppendNonUnicode ((string)o);
+ else if (parameter.IsMoneyType)
+ tds.Comm.AppendMoney ((decimal)o, size);
+ else if (parameter.IsDateTimeType)
+ tds.Comm.Append((DateTime)o, size);
+ else if (parameter.IsDecimalType)
+ tds.Comm.AppendDecimal((decimal)o, size, parameter.Scale);
+ else
+ tds.Comm.Append (o);
+
+ // For some reason max varchar and nvarchar values need to have 4 bytes of 0 appended
+ if (parameter.IsAnyVarCharMax)
+ tds.Comm.Append ((int)0);
return true;
}
public bool BulkCopyEnd ()
{
- tds.Comm.Append ((short) TdsPacketSubType.Done);
+ tds.Comm.Append ((byte) TdsPacketSubType.Done);
+
+ // So the TDS spec calls for a Status (ushort), CurCmd (ushort) and DoneRowCount (long)
+ // all of which are 0.
+ // However it looks like MS .net is only sending 8 bytes not sure which parts they are leaving
+ // out but we are going with the TDS spec size
+ tds.Comm.Append ((short) 0x00);
+ tds.Comm.Append ((short) 0x00);
+ tds.Comm.Append ((long) 0x00);
+
tds.ExecBulkCopy (30, false);
return true;
}
private void WriteParameterInfo (TdsMetaParameter param)
{
- /*
- Ms.net send non-nullable datatypes as nullable and allows setting null values
- to int/float etc.. So, using Nullable form of type for all data
- */
- param.IsNullable = true;
TdsColumnType colType = param.GetMetaType ();
- param.IsNullable = false;
- tds.Comm.Append ((byte)colType); // type
-
int size = 0;
if (param.Size == 0)
size = param.GetActualSize ();
size = param.Size;
/*
- If column type is SqlDbType.NVarChar the size of parameter is multiplied by 2
- FIXME: Need to check for other types
+ * If column type is SqlDbType.NVarChar the size of parameter is multiplied by 2
+ * FIXME: Need to check for other types
*/
if (colType == TdsColumnType.BigNVarChar)
size <<= 1;
- if (tds.IsLargeType (colType))
+
+ // Total hack for varchar(max) and nvarchar(max)
+ // They are coming back as Text and not the correct values
+ // based on the size we can determine what the correct type is
+ // We need the size to come out to 0xFFFF on the wire.
+ if (param.IsVarNVarCharMax)
+ colType = TdsColumnType.BigNVarChar;
+ else if (param.IsVarCharMax)
+ colType = TdsColumnType.BigVarChar;
+
+ tds.Comm.Append ((byte)colType); // type
+
+ param.CalculateIsVariableType();
+
+ if (param.IsAnyVarCharMax) {
+ tds.Comm.Append ((byte)0xFF);
+ tds.Comm.Append ((byte)0xFF);
+ } else if (tds.IsLargeType (colType))
tds.Comm.Append ((short)size); // Parameter size passed in SqlParameter
else if (tds.IsBlobType (colType))
tds.Comm.Append (size); // Parameter size passed in SqlParameter
- else
+ else if (param.IsVariableSizeType)
tds.Comm.Append ((byte)size);
// Precision and Scale are non-zero for only decimal/numeric
tds.Comm.Append ((param.Precision!=0)?param.Precision:(byte)29);
tds.Comm.Append (param.Scale);
}
+
+ // Documentation is basically 0 on these 5 bytes. But in a nutshell it seems during a bulk insert
+ // these are required for text types.
+ if (param.IsTextType) {
+ tds.Comm.Append ((byte)0x09);
+ tds.Comm.Append ((byte)0x04);
+ tds.Comm.Append ((byte)0xd0);
+ tds.Comm.Append ((byte)0x00);
+ tds.Comm.Append ((byte)0x34);
+ }
}
#endregion
}
}
}
+ public void AppendNonUnicode (string s)
+ {
+ if (tdsVersion < TdsVersion.tds70) {
+ Append (encoder.GetBytes (s));
+ } else {
+ for (int i = 0; i < s.Length; i++) {
+ SendIfFull (sizeof(byte));
+ Append ((byte)s[i]);
+ }
+ }
+ }
+
// Appends with padding
public byte[] Append (string s, int len, byte pad)
{
public void Append (decimal d, int bytes)
{
int[] arr = Decimal.GetBits (d);
- byte sign = (d > 0 ? (byte)1 : (byte)0);
+ byte sign = (d > 0 ? (byte)1 : (byte)0);
+ SendIfFull (bytes);
+ Append (sign);
+ AppendInternal (arr[0]);
+ AppendInternal (arr[1]);
+ AppendInternal (arr[2]);
+ AppendInternal ((int)0);
+ }
+
+ public void AppendMoney (decimal d, int size)
+ {
+ // The method for this is to simply multiply by 10^4 and then stuff
+ // the value into either a int or long value depending on the size
+
+ SendIfFull (size);
+
+ decimal tmpD = Decimal.Multiply(d, 10000m);
+ if (size > 4) {
+ long longValue = Decimal.ToInt64(tmpD);
+
+ int significantHalf = (int) ((longValue >> 32) & 0xffffffff);
+ int lessSignificantHalf = (int)(longValue & 0xffffffff);
+
+ AppendInternal (significantHalf);
+ AppendInternal (lessSignificantHalf);
+ } else {
+ int intValue = Decimal.ToInt32(tmpD);
+ AppendInternal (intValue);
+ }
+ }
+
+ // A method for decimals that properly scales the decimal out before putting on the TDS steam
+ public void AppendDecimal (decimal d, int bytes, int scale)
+ {
+ decimal tmpD1 = Decimal.Multiply (d, (decimal)System.Math.Pow (10.0, scale));
+ decimal tmpD2 = System.Math.Abs(Decimal.Truncate (tmpD1));
+
+ int[] arr = Decimal.GetBits (tmpD2);
+ byte sign = (d > 0 ? (byte)1 : (byte)0);
SendIfFull (bytes);
- Append (sign) ;
+ Append (sign);
AppendInternal (arr[0]);
AppendInternal (arr[1]);
AppendInternal (arr[2]);
{
if (nextOutBufferIndex > headerLength || packetType == TdsPacketType.Cancel) {
byte status = (byte) ((isLastSegment ? 0x01 : 0x00) | (connReset ? 0x08 : 0x00));
+
// packet type
Store (0, (byte) packetType);
Store (1, status);
stream.Write (outBuffer, 0, nextOutBufferIndex);
stream.Flush ();
+
+ if (!isLastSegment && packetType == TdsPacketType.Bulk)
+ {
+ System.Threading.Thread.Sleep (100);
+ }
+
packetsSent++;
}
}
public class TdsMetaParameter
{
+ #region Static
+ public const int maxVarCharCharacters = 2147483647; // According to MS, max size is 2GB, 1 Byte Characters
+ public const int maxNVarCharCharacters = 1073741823; // According to MS, max size is 2GB, 2 Byte Characters
+ #endregion
+
#region Fields
TdsParameterDirection direction = TdsParameterDirection.Input;
set { isVariableSizeType = value; }
}
+ public bool IsVarNVarCharMax
+ {
+ get { return (TypeName == "ntext" && size >= maxNVarCharCharacters); }
+ }
+
+ public bool IsVarCharMax
+ {
+ get { return (TypeName == "text" && size >= maxVarCharCharacters); }
+ }
+
+ public bool IsAnyVarCharMax
+ {
+ get { return IsVarNVarCharMax || IsVarCharMax; }
+ }
+
+ public bool IsNonUnicodeText
+ {
+ get {
+ TdsColumnType colType = GetMetaType();
+ return (colType == TdsColumnType.VarChar ||
+ colType == TdsColumnType.BigVarChar ||
+ colType == TdsColumnType.Text ||
+ colType == TdsColumnType.Char ||
+ colType == TdsColumnType.BigChar);
+ }
+ }
+
+ public bool IsMoneyType
+ {
+ get {
+ TdsColumnType colType = GetMetaType();
+ return (colType == TdsColumnType.Money ||
+ colType == TdsColumnType.MoneyN ||
+ colType == TdsColumnType.Money4 ||
+ colType == TdsColumnType.SmallMoney);
+ }
+ }
+
+ public bool IsDateTimeType
+ {
+ get {
+ TdsColumnType colType = GetMetaType();
+ return (colType == TdsColumnType.DateTime ||
+ colType == TdsColumnType.DateTime4 ||
+ colType == TdsColumnType.DateTimeN);
+ }
+ }
+
+ public bool IsTextType
+ {
+ get {
+ TdsColumnType colType = GetMetaType();
+ return (colType == TdsColumnType.VarChar ||
+ colType == TdsColumnType.BigVarChar ||
+ colType == TdsColumnType.BigChar ||
+ colType == TdsColumnType.Char ||
+ colType == TdsColumnType.BigNVarChar ||
+ colType == TdsColumnType.NChar ||
+ colType == TdsColumnType.Text ||
+ colType == TdsColumnType.NText);
+ }
+ }
+
+ public bool IsDecimalType
+ {
+ get {
+ TdsColumnType colType = GetMetaType();
+ return (colType == TdsColumnType.Decimal ||
+ colType == TdsColumnType.Numeric);
+ }
+ }
+
#endregion // Properties
#region Methods
return TdsColumnType.IntN ;
return TdsColumnType.BigInt;
case "char":
- return TdsColumnType.Char;
+ return TdsColumnType.BigChar;
case "money":
if (IsNullable)
return TdsColumnType.MoneyN;
case "smallmoney":
if (IsNullable)
return TdsColumnType.MoneyN ;
- return TdsColumnType.Money4;
+ return TdsColumnType.SmallMoney;
case "decimal":
return TdsColumnType.Decimal;
case "datetime":
}
}
+ public void CalculateIsVariableType()
+ {
+ switch (GetMetaType ()) {
+ case TdsColumnType.UniqueIdentifier:
+ case TdsColumnType.BigVarChar:
+ case TdsColumnType.BigVarBinary:
+ case TdsColumnType.IntN:
+ case TdsColumnType.Text:
+ case TdsColumnType.FloatN:
+ case TdsColumnType.BigNVarChar:
+ case TdsColumnType.NText:
+ case TdsColumnType.Image:
+ case TdsColumnType.Decimal:
+ case TdsColumnType.BigBinary:
+ case TdsColumnType.DateTimeN:
+ case TdsColumnType.MoneyN:
+ case TdsColumnType.BitN:
+ case TdsColumnType.Char:
+ case TdsColumnType.BigChar:
+ case TdsColumnType.NChar:
+ IsVariableSizeType = true;
+ break;
+ default:
+ IsVariableSizeType = false;
+ break;
+ }
+ }
+
public void Validate (int index)
{
if ((this.direction == TdsParameterDirection.InputOutput || this.direction == TdsParameterDirection.Output) &&
if ((int)row ["ColumnSize"] != -1) {
param.Size = (int) row ["ColumnSize"];
}
+
+ short numericPresision = (short)row ["NumericPrecision"];
+ if (numericPresision != 255) {
+ param.Precision = (byte) numericPresision;
+ }
+
+ short numericScale = (short)row ["NumericScale"];
+ if (numericScale != 255) {
+ param.Scale = (byte) numericScale;
+ }
+
+ param.IsNullable = (bool)row ["AllowDBNull"];
tmpCmd.Parameters.Add (param);
break;
}
flag = false;
bool insertSt = false;
foreach (DataRow row in colMetaData.Rows) {
+ SqlDbType sqlType = (SqlDbType) row ["ProviderType"];
if (_columnMappingCollection.Count > 0) {
i = 0;
insertSt = false;
if ((bool)row ["IsReadOnly"]) {
continue;
}
+
+ int columnSize = (int)row ["ColumnSize"];
string columnInfo = "";
- if ((int)row ["ColumnSize"] != -1) {
+
+ if (columnSize >= TdsMetaParameter.maxVarCharCharacters && sqlType == SqlDbType.Text)
+ columnInfo = "VarChar(max)";
+ else if (columnSize >= TdsMetaParameter.maxNVarCharCharacters && sqlType == SqlDbType.NText)
+ columnInfo = "NVarChar(max)";
+ else if (IsTextType(sqlType) && columnSize != -1) {
columnInfo = string.Format ("{0}({1})",
- (SqlDbType) row ["ProviderType"],
- row ["ColumnSize"]);
+ sqlType,
+ columnSize.ToString());
} else {
- columnInfo = string.Format ("{0}", (SqlDbType) row ["ProviderType"]);
+ columnInfo = string.Format ("{0}", sqlType);
}
+
+ if ( sqlType == SqlDbType.Decimal)
+ columnInfo += String.Format("({0},{1})", row ["NumericPrecision"], row ["NumericScale"]);
+
if (flag)
statement += ", ";
string columnName = (string) row ["ColumnName"];
statement += string.Format ("[{0}] {1}", columnName, columnInfo);
if (flag == false)
flag = true;
- if (tableCollations != null) {
+ if (IsTextType(sqlType) && tableCollations != null) {
foreach (DataRow collationRow in tableCollations.Rows) {
if ((string)collationRow ["name"] == columnName) {
statement += string.Format (" COLLATE {0}", collationRow ["collation"]);
private void ValidateColumnMapping (DataTable table, DataTable tableCollations)
{
- foreach (SqlBulkCopyColumnMapping _columnMapping in _columnMappingCollection) {
- if (ordinalMapping == false &&
- (_columnMapping.DestinationColumn == String.Empty ||
- _columnMapping.SourceColumn == String.Empty))
- throw new InvalidOperationException ("Mappings must be either all null or ordinal");
- if (ordinalMapping &&
- (_columnMapping.DestinationOrdinal == -1 ||
- _columnMapping.SourceOrdinal == -1))
- throw new InvalidOperationException ("Mappings must be either all null or ordinal");
- bool flag = false;
- if (ordinalMapping == false) {
- foreach (DataRow row in tableCollations.Rows) {
- if ((string)row ["name"] == _columnMapping.DestinationColumn) {
- flag = true;
- break;
- }
- }
- if (flag == false)
- throw new InvalidOperationException ("ColumnMapping does not match");
- flag = false;
- foreach (DataColumn col in table.Columns) {
- if (col.ColumnName == _columnMapping.SourceColumn) {
- flag = true;
- break;
- }
- }
- if (flag == false)
- throw new InvalidOperationException ("ColumnName " +
- _columnMapping.SourceColumn +
- " does not match");
- } else {
- if (_columnMapping.DestinationOrdinal >= tableCollations.Rows.Count)
- throw new InvalidOperationException ("ColumnMapping does not match");
- }
- }
+ // So the problem here is that temp tables will not have any table collations. This prevents
+ // us from bulk inserting into temp tables. So for now we will skip the validation and
+ // let SqlServer tell us there is an issue rather than trying to do it here.
+ // So for now we will simply return and do nothing.
+ // TODO: At some point we should remove this function if we all agree its the right thing to do
+ return;
+
+// foreach (SqlBulkCopyColumnMapping _columnMapping in _columnMappingCollection) {
+// if (ordinalMapping == false &&
+// (_columnMapping.DestinationColumn == String.Empty ||
+// _columnMapping.SourceColumn == String.Empty))
+// throw new InvalidOperationException ("Mappings must be either all null or ordinal");
+// if (ordinalMapping &&
+// (_columnMapping.DestinationOrdinal == -1 ||
+// _columnMapping.SourceOrdinal == -1))
+// throw new InvalidOperationException ("Mappings must be either all null or ordinal");
+// bool flag = false;
+// if (ordinalMapping == false) {
+// foreach (DataRow row in tableCollations.Rows) {
+// if ((string)row ["name"] == _columnMapping.DestinationColumn) {
+// flag = true;
+// break;
+// }
+// }
+// if (flag == false)
+// throw new InvalidOperationException ("ColumnMapping does not match");
+// flag = false;
+// foreach (DataColumn col in table.Columns) {
+// if (col.ColumnName == _columnMapping.SourceColumn) {
+// flag = true;
+// break;
+// }
+// }
+// if (flag == false)
+// throw new InvalidOperationException ("ColumnName " +
+// _columnMapping.SourceColumn +
+// " does not match");
+// } else {
+// if (_columnMapping.DestinationOrdinal >= tableCollations.Rows.Count)
+// throw new InvalidOperationException ("ColumnMapping does not match");
+// }
+// }
}
private void BulkCopyToServer (DataTable table, DataRowState state)
statement += ")";
}
#endregion Check requested options and add corresponding modifiers to the statement
-
+
blkCopy.SendColumnMetaData (statement);
}
blkCopy.BulkCopyStart (tmpCmd.Parameters.MetaParameters);
rowToCopy = parameter.Value = parameter.ConvertToFrameworkType (rowToCopy);
}
string colType = string.Format ("{0}", parameter.MetaParameter.TypeName);
- if (colType == "nvarchar") {
- if (row [i] != null) {
+ if (colType == "nvarchar" || colType == "ntext" || colType == "nchar") {
+ if (row [i] != null && row [i] != DBNull.Value) {
size = ((string) parameter.Value).Length;
size <<= 1;
}
+ } else if (colType == "varchar" || colType == "text" || colType == "char") {
+ if (row [i] != null && row [i] != DBNull.Value)
+ size = ((string) parameter.Value).Length;
} else {
size = parameter.Size;
}
rowToCopy = parameter.Value = parameter.ConvertToFrameworkType (rowToCopy);
}
string colType = string.Format ("{0}", parameter.MetaParameter.TypeName);
- if (colType == "nvarchar") {
- if (row [mapping.SourceColumn] != null) {
+ if (colType == "nvarchar" || colType == "ntext" || colType == "nchar") {
+ if (row [mapping.SourceColumn] != null && row [mapping.SourceColumn] != DBNull.Value) {
size = ((string) rowToCopy).Length;
size <<= 1;
}
+ } else if (colType == "varchar" || colType == "text" || colType == "char") {
+ if (row [mapping.SourceColumn] != null && row [mapping.SourceColumn] != DBNull.Value)
+ size = ((string) rowToCopy).Length;
} else {
size = parameter.Size;
}
If column type is SqlDbType.NVarChar the size of parameter is multiplied by 2
FIXME: Need to check for other types
*/
- if (colType == "nvarchar") {
+ if (colType == "nvarchar" || colType == "ntext" || colType == "nchar") {
size = ((string) row [param.ParameterName]).Length;
size <<= 1;
+ } else if (colType == "varchar" || colType == "text" || colType == "char") {
+ size = ((string) row [param.ParameterName]).Length;
} else {
size = param.Size;
}
}
if (rowToCopy == null)
continue;
- blkCopy.BulkCopyData (rowToCopy, size, isNewRow);
+
+ blkCopy.BulkCopyData (rowToCopy, isNewRow, size, param.MetaParameter);
+
if (isNewRow)
isNewRow = false;
} // foreach (SqlParameter)
blkCopy.BulkCopyEnd ();
}
+ private bool IsTextType(SqlDbType sqlType)
+ {
+ return (sqlType == SqlDbType.NText ||
+ sqlType == SqlDbType.NVarChar ||
+ sqlType == SqlDbType.Text ||
+ sqlType == SqlDbType.VarChar ||
+ sqlType == SqlDbType.Char ||
+ sqlType == SqlDbType.NChar);
+ }
+
public void WriteToServer (DataRow [] rows)
{
if (rows == null)
projects / mono.git / commitdiff