SqlBulkCopy Implementation

This are the code changes to get the SqlBulkCopy procedure working on
mono.

3 files changed, 266 insertions, 23 deletions

diff --git a/mcs/class/Mono.Data.Tds/Mono.Data.Tds.Protocol/TdsBulkCopy.cs b/mcs/class/Mono.Data.Tds/Mono.Data.Tds.Protocol/TdsBulkCopy.cs
index 2374eb71a79..e7360d0a0d0 100644
--- a/mcs/class/Mono.Data.Tds/Mono.Data.Tds.Protocol/TdsBulkCopy.cs
+++ b/mcs/class/Mono.Data.Tds/Mono.Data.Tds.Protocol/TdsBulkCopy.cs
@@ -71,7 +71,16 @@ namespace Mono.Data.Tds.Protocol {
 					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);
@@ -80,37 +89,82 @@ namespace Mono.Data.Tds.Protocol {
 			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 ();
@@ -118,16 +172,33 @@ namespace Mono.Data.Tds.Protocol {
 				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
@@ -135,6 +206,16 @@ namespace Mono.Data.Tds.Protocol {
 				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
 	}
diff --git a/mcs/class/Mono.Data.Tds/Mono.Data.Tds.Protocol/TdsComm.cs b/mcs/class/Mono.Data.Tds/Mono.Data.Tds.Protocol/TdsComm.cs
index 2f5eb561e5c..c44f8d51d73 100644
--- a/mcs/class/Mono.Data.Tds/Mono.Data.Tds.Protocol/TdsComm.cs
+++ b/mcs/class/Mono.Data.Tds/Mono.Data.Tds.Protocol/TdsComm.cs
@@ -398,6 +398,18 @@ namespace Mono.Data.Tds.Protocol {
 			}
 		}	
 
+		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)
 		{
@@ -440,9 +452,47 @@ namespace Mono.Data.Tds.Protocol {
 		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]);
@@ -746,6 +796,7 @@ namespace Mono.Data.Tds.Protocol {
 		{
 			if (nextOutBufferIndex > headerLength || packetType == TdsPacketType.Cancel) {
 				byte status =  (byte) ((isLastSegment ? 0x01 : 0x00) | (connReset ? 0x08 : 0x00)); 
+
 				// packet type
 				Store (0, (byte) packetType);
 				Store (1, status);
@@ -760,6 +811,12 @@ namespace Mono.Data.Tds.Protocol {
 
 				stream.Write (outBuffer, 0, nextOutBufferIndex);
 				stream.Flush ();
+
+				if (!isLastSegment && packetType == TdsPacketType.Bulk)
+				{
+					System.Threading.Thread.Sleep (100);    
+				}
+
 				packetsSent++;
 			}
 		}
diff --git a/mcs/class/Mono.Data.Tds/Mono.Data.Tds/TdsMetaParameter.cs b/mcs/class/Mono.Data.Tds/Mono.Data.Tds/TdsMetaParameter.cs
index aa5422df5e3..8b704455cb3 100644
--- a/mcs/class/Mono.Data.Tds/Mono.Data.Tds/TdsMetaParameter.cs
+++ b/mcs/class/Mono.Data.Tds/Mono.Data.Tds/TdsMetaParameter.cs
@@ -37,6 +37,11 @@ namespace Mono.Data.Tds {
 
 	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;
@@ -175,6 +180,78 @@ namespace Mono.Data.Tds {
 			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
@@ -346,7 +423,7 @@ namespace Mono.Data.Tds {
 					return TdsColumnType.IntN ;
 				return TdsColumnType.BigInt;
 			case "char":
-				return TdsColumnType.Char;
+				return TdsColumnType.BigChar;
 			case "money":
 				if (IsNullable)
 					return TdsColumnType.MoneyN;
@@ -354,7 +431,7 @@ namespace Mono.Data.Tds {
 			case "smallmoney":
 				if (IsNullable)
 					return TdsColumnType.MoneyN ;
-				return TdsColumnType.Money4;
+				return TdsColumnType.SmallMoney;
 			case "decimal":
 				return TdsColumnType.Decimal;
 			case "datetime":
@@ -409,6 +486,34 @@ namespace Mono.Data.Tds {
 			}
 		}
 
+		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) &&