Merge pull request #1773 from ztzg/sql-server-datetime2

Allow transferring datetime2/datetimeoffset to/from SQL Server 2012

3 files changed, 97 insertions, 6 deletions

diff --git a/mcs/class/Mono.Data.Tds/Mono.Data.Tds.Protocol/Tds70.cs b/mcs/class/Mono.Data.Tds/Mono.Data.Tds.Protocol/Tds70.cs
index 54210049067..725248a8fc0 100644
--- a/mcs/class/Mono.Data.Tds/Mono.Data.Tds.Protocol/Tds70.cs
+++ b/mcs/class/Mono.Data.Tds/Mono.Data.Tds.Protocol/Tds70.cs
@@ -586,6 +586,13 @@ namespace Mono.Data.Tds.Protocol
 			} else if (colType == TdsColumnType.BigVarBinary) {
 				if (size > 8000)
 					colType = TdsColumnType.Image;
+			} else if (colType == TdsColumnType.DateTime2 ||
+				   colType == TdsColumnType.DateTimeOffset) {
+				// HACK: Wire-level DateTime{2,Offset}
+				// require TDS 7.3, which this driver
+				// does not implement correctly--so we
+				// serialize to ASCII instead.
+				colType = TdsColumnType.Char;
 			}
 			// Calculation of TypeInfo field
 			/* 
@@ -715,6 +722,8 @@ namespace Mono.Data.Tds.Protocol
 				case "nchar" :
 				case "text" :
 				case "ntext" :
+				case "datetime2":
+				case "datetimeoffset":
 					byte [] tmp = param.GetBytes ();
 					Comm.Append (tmp);
 					break;
diff --git a/mcs/class/Mono.Data.Tds/Mono.Data.Tds.Protocol/TdsColumnType.cs b/mcs/class/Mono.Data.Tds/Mono.Data.Tds.Protocol/TdsColumnType.cs
index af3f2271647..bc1f7fb0ba1 100644
--- a/mcs/class/Mono.Data.Tds/Mono.Data.Tds.Protocol/TdsColumnType.cs
+++ b/mcs/class/Mono.Data.Tds/Mono.Data.Tds.Protocol/TdsColumnType.cs
@@ -35,6 +35,8 @@ namespace Mono.Data.Tds.Protocol {
 		Char = 0x2f,		// SYBCHAR
 		DateTime = 0x3d,	// SYBDATETIME
 		DateTime4 = 0x3a,	// SYBDATETIME4
+		DateTime2 = 0x2a,	// SYBMSDATETIME2
+		DateTimeOffset = 0x2b,	// SYBMSDATETIMEOFFSET
 		DateTimeN = 0x6f,	// SYBDATETIMN
 		Decimal = 0x6a,		// SYBDECIMAL
 		Real = 0x3b,		// SYBREAL
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 b569f643072..e00b9e4e236 100644
--- a/mcs/class/Mono.Data.Tds/Mono.Data.Tds/TdsMetaParameter.cs
+++ b/mcs/class/Mono.Data.Tds/Mono.Data.Tds/TdsMetaParameter.cs
@@ -287,18 +287,31 @@ namespace Mono.Data.Tds {
 		internal string Prepare ()
 		{
 			string typeName = TypeName;
-			
-			if (typeName == "varbinary") {
-				int size = Size;
+			// Cf. GetDateTimeString
+			TdsColumnType actualType = TdsColumnType.Char;
+			int size;
+
+			switch (typeName) {
+			case "varbinary":
+				size = Size;
 				if (size <= 0) {
 					size = GetActualSize ();
 				}
-				
+
 				if (size > 8000) {
 					typeName = "varbinary(max)";
 				}
+				break;
+			case "datetime2":
+				actualType = TdsColumnType.DateTime2;
+				typeName = "char";
+				break;
+			case "datetimeoffset":
+				actualType = TdsColumnType.DateTimeOffset;
+				typeName = "char";
+				break;
 			}
-			
+
 			string includeAt = "@";
 			if (ParameterName [0] == '@')
 				includeAt = "";
@@ -313,7 +326,7 @@ namespace Mono.Data.Tds {
 			case "varchar":
 			case "varbinary":
 				//A size of 0 is not allowed in declarations.
-				int size = Size;
+				size = Size;
 				if (size <= 0) {
 					size = GetActualSize ();
 					if (size <= 0)
@@ -326,6 +339,14 @@ namespace Mono.Data.Tds {
 				result.Append (paramSize > 0 ? (paramSize > 4000 ? "(max)" : String.Format ("({0})", paramSize)) : "(4000)");
 				break;
 			case "char":
+				size = -1;
+				if (actualType != TdsColumnType.Char)
+					size = GetDateTimeStringLength (actualType);
+				else if (isSizeSet)
+					size = Size;
+				if (size > 0)
+					result.Append (String.Format ("({0})", size));
+				break;
 			case "nchar":
 			case "binary":
 				if (isSizeSet && Size > 0)
@@ -366,6 +387,10 @@ namespace Mono.Data.Tds {
 			case "float":
 			case "money":
 				return 8;
+			case "datetime2":
+				return GetDateTimeStringLength (TdsColumnType.DateTime2);
+			case "datetimeoffset":
+				return GetDateTimeStringLength (TdsColumnType.DateTimeOffset);
 			case "int":
 			case "real":
 			case "smalldatetime":
@@ -386,6 +411,53 @@ namespace Mono.Data.Tds {
 			return size;
 		}
 
+		private int GetDateTimePrecision ()
+		{
+			int precision = Precision;
+
+			// http://msdn.microsoft.com/en-us/library/bb677335.aspx
+			// says that default precision is 7.  How do
+			// we distinguish that from zero?
+			if (precision == 0 || precision > 7)
+				precision = 7;
+
+			return precision;
+		}
+
+		private int GetDateTimeStringLength (TdsColumnType type)
+		{
+			int precision = GetDateTimePrecision ();
+			int len = precision == 0 ? 19 : 20 + precision;
+
+			if (type == TdsColumnType.DateTimeOffset)
+				len += 6;
+
+			return len;
+		}
+
+		// HACK: Wire-level DateTime{2,Offset} require TDS
+		// 7.3, which this driver does not implement
+		// correctly--so we serialize to ASCII instead.
+		private string GetDateTimeString (TdsColumnType type)
+		{
+			int precision = GetDateTimePrecision ();
+			string fmt = "yyyy-MM-dd'T'HH':'mm':'ss";
+
+			if (precision > 0)
+				fmt += ".fffffff".Substring(0, precision + 1);
+
+			switch (type) {
+			case TdsColumnType.DateTime2:
+				DateTime dt = (DateTime)Value;
+				return dt.ToString(fmt);
+			case TdsColumnType.DateTimeOffset:
+				DateTimeOffset dto = (DateTimeOffset)Value;
+				return dto.ToString(fmt + "zzz");
+			}
+
+			throw new ApplicationException("Should be unreachable");
+		}
+
 		internal byte[] GetBytes ()
 		{
 			byte[] result = {};
@@ -403,6 +475,10 @@ namespace Mono.Data.Tds {
 				case "char" :
 				case "text" :
 					return Encoding.Default.GetBytes ((string)Value);
+				case "datetime2":
+					return Encoding.Default.GetBytes (GetDateTimeString (TdsColumnType.DateTime2));
+				case "datetimeoffset":
+					return Encoding.Default.GetBytes (GetDateTimeString (TdsColumnType.DateTimeOffset));
 				default :
 					return ((byte[]) Value);
 			}
@@ -441,6 +517,10 @@ namespace Mono.Data.Tds {
 				if (IsNullable)
 					return TdsColumnType.DateTimeN;
 				return TdsColumnType.DateTime4;
+			case "datetime2":
+				return TdsColumnType.DateTime2;
+			case "datetimeoffset":
+				return TdsColumnType.DateTimeOffset;
 			case "float":
 				if (IsNullable)
 					return TdsColumnType.FloatN ;