2 // Mono.Data.Tds.Protocol.Tds70.cs
5 // Tim Coleman (tim@timcoleman.com)
6 // Diego Caravana (diego@toth.it)
7 // Sebastien Pouliot (sebastien@ximian.com)
8 // Daniel Morgan (danielmorgan@verizon.net)
9 // Gert Driesen (drieseng@users.sourceforge.net)
10 // Veerapuram Varadhan (vvaradhan@novell.com)
12 // Copyright (C) 2002 Tim Coleman
13 // Portions (C) 2003 Motus Technologies Inc. (http://www.motus.com)
14 // Portions (C) 2003 Daniel Morgan
17 // Permission is hereby granted, free of charge, to any person obtaining
18 // a copy of this software and associated documentation files (the
19 // "Software"), to deal in the Software without restriction, including
20 // without limitation the rights to use, copy, modify, merge, publish,
21 // distribute, sublicense, and/or sell copies of the Software, and to
22 // permit persons to whom the Software is furnished to do so, subject to
23 // the following conditions:
25 // The above copyright notice and this permission notice shall be
26 // included in all copies or substantial portions of the Software.
28 // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
29 // EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
30 // MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
31 // NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE
32 // LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION
33 // OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION
34 // WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
38 using System.Globalization;
41 using Mono.Security.Protocol.Ntlm;
43 namespace Mono.Data.Tds.Protocol
45 public class Tds70 : Tds
49 //public readonly static TdsVersion Version = TdsVersion.tds70;
50 static readonly decimal SMALLMONEY_MIN = -214748.3648m;
51 static readonly decimal SMALLMONEY_MAX = 214748.3647m;
57 [Obsolete ("Use the constructor that receives a lifetime parameter")]
58 public Tds70 (string server, int port)
59 : this (server, port, 512, 15, 0)
63 [Obsolete ("Use the constructor that receives a lifetime parameter")]
64 public Tds70 (string server, int port, int packetSize, int timeout)
65 : this (server, port, packetSize, timeout, 0, TdsVersion.tds70)
69 [Obsolete ("Use the constructor that receives a lifetime parameter")]
70 public Tds70 (string server, int port, int packetSize, int timeout, TdsVersion version)
71 : this (server, port, packetSize, timeout, 0, version)
75 public Tds70 (string server, int port, int lifetime)
76 : this (server, port, 512, 15, lifetime)
80 public Tds70 (string server, int port, int packetSize, int timeout, int lifeTime)
81 : base (server, port, packetSize, timeout, lifeTime, TdsVersion.tds70)
85 public Tds70 (string server, int port, int packetSize, int timeout, int lifeTime, TdsVersion version)
86 : base (server, port, packetSize, timeout, lifeTime, version)
90 #endregion // Constructors
94 protected virtual byte[] ClientVersion {
95 get { return new byte[] {0x00, 0x0, 0x0, 0x70};}
98 // Default precision is 28 for a 7.0 server. Unless and
99 // otherwise the server is started with /p option - which would be 38
100 protected virtual byte Precision {
104 #endregion // Properties
108 protected string BuildExec (string sql)
110 string esql = sql.Replace ("'", "''"); // escape single quote
111 if (Parameters != null && Parameters.Count > 0)
112 return BuildProcedureCall (String.Format ("sp_executesql N'{0}', N'{1}', ", esql, BuildPreparedParameters ()));
114 return BuildProcedureCall (String.Format ("sp_executesql N'{0}'", esql));
117 private string BuildParameters ()
119 if (Parameters == null || Parameters.Count == 0)
122 StringBuilder result = new StringBuilder ();
123 foreach (TdsMetaParameter p in Parameters) {
124 string parameterName = p.ParameterName;
125 if (parameterName [0] == '@') {
126 parameterName = parameterName.Substring (1);
128 if (p.Direction != TdsParameterDirection.ReturnValue) {
129 if (result.Length > 0)
130 result.Append (", ");
131 if (p.Direction == TdsParameterDirection.InputOutput)
132 result.AppendFormat ("@{0}={0} output", parameterName);
134 result.Append (FormatParameter (p));
137 return result.ToString ();
140 private string BuildPreparedParameters ()
142 StringBuilder parms = new StringBuilder ();
143 foreach (TdsMetaParameter p in Parameters) {
144 if (parms.Length > 0)
147 // Set default precision according to the TdsVersion
148 // Current default is 29 for Tds80
149 if (p.TypeName == "decimal")
150 p.Precision = (p.Precision !=0 ? p.Precision : (byte) Precision);
152 parms.Append (p.Prepare ());
153 if (p.Direction == TdsParameterDirection.Output)
154 parms.Append (" output");
156 return parms.ToString ();
159 private string BuildPreparedQuery (string id)
161 return BuildProcedureCall (String.Format ("sp_execute {0},", id));
164 private string BuildProcedureCall (string procedure)
166 string exec = String.Empty;
168 StringBuilder declare = new StringBuilder ();
169 StringBuilder select = new StringBuilder ();
170 StringBuilder set = new StringBuilder ();
173 if (Parameters != null) {
174 foreach (TdsMetaParameter p in Parameters) {
175 string parameterName = p.ParameterName;
176 if (parameterName [0] == '@') {
177 parameterName = parameterName.Substring (1);
180 if (p.Direction != TdsParameterDirection.Input) {
182 select.Append ("select ");
184 select.Append (", ");
185 select.Append ("@" + parameterName);
187 if (p.TypeName == "decimal")
188 p.Precision = (p.Precision !=0 ? p.Precision : (byte) Precision);
190 declare.Append (String.Format ("declare {0}\n", p.Prepare ()));
192 if (p.Direction != TdsParameterDirection.ReturnValue) {
193 if (p.Direction == TdsParameterDirection.InputOutput)
194 set.Append (String.Format ("set {0}\n", FormatParameter(p)));
196 set.Append (String.Format ("set @{0}=NULL\n", parameterName));
201 if (p.Direction == TdsParameterDirection.ReturnValue)
202 exec = "@" + parameterName + "=";
205 exec = "exec " + exec;
207 return String.Format ("{0}{1}{2}{3} {4}\n{5}",
208 declare.ToString (), set.ToString (), exec,
209 procedure, BuildParameters (), select.ToString ());
212 public override bool Connect (TdsConnectionParameters connectionParameters)
215 throw new InvalidOperationException ("The connection is already open.");
217 connectionParms = connectionParameters;
219 SetLanguage (connectionParameters.Language);
220 SetCharset ("utf-8");
222 byte[] empty = new byte[0];
226 byte[] domainMagic = { 6, 0x7d, 0x0f, 0xfd, 0xff, 0x0, 0x0, 0x0,
227 0x0, 0xe0, 0x83, 0x0, 0x0,
228 0x68, 0x01, 0x00, 0x00, 0x09, 0x04, 0x00, 0x00 };
229 byte[] sqlserverMagic = { 6, 0x0, 0x0, 0x0,
231 0x0, 0xe0, 0x03, 0x0,
232 0x0, 0x0, 0x0, 0x0, 0x0, 0x0,
236 if (connectionParameters.DomainLogin)
239 magic = sqlserverMagic;
241 string username = connectionParameters.User;
242 string domain = null;
244 int idx = username.IndexOf ("\\");
246 domain = username.Substring (0, idx);
247 username = username.Substring (idx + 1);
249 connectionParameters.DefaultDomain = domain;
250 connectionParameters.User = username;
252 domain = Environment.UserDomainName;
253 connectionParameters.DefaultDomain = domain;
256 short partialPacketSize = (short) (86 + (
257 connectionParameters.Hostname.Length +
258 connectionParameters.ApplicationName.Length +
260 connectionParameters.LibraryName.Length +
262 connectionParameters.Database.Length +
263 connectionParameters.AttachDBFileName.Length) * 2);
265 if (connectionParameters.DomainLogin) {
266 authLen = ((short) (32 + (connectionParameters.Hostname.Length +
268 partialPacketSize += authLen;
270 partialPacketSize += ((short) ((username.Length + connectionParameters.Password.Length) * 2));
272 int totalPacketSize = partialPacketSize;
274 Comm.StartPacket (TdsPacketType.Logon70);
276 Comm.Append (totalPacketSize);
278 //Comm.Append (empty, 3, pad);
279 //byte[] version = {0x00, 0x0, 0x0, 0x71};
280 //Console.WriteLine ("Version: {0}", ClientVersion[3]);
281 Comm.Append (ClientVersion); // TDS Version 7
282 Comm.Append ((int)this.PacketSize); // Set the Block Size
283 Comm.Append (empty, 3, pad);
289 Comm.Append (curPos);
290 Comm.Append ((short) connectionParameters.Hostname.Length);
291 curPos += (short) (connectionParameters.Hostname.Length * 2);
293 if (connectionParameters.DomainLogin) {
294 Comm.Append((short)0);
295 Comm.Append((short)0);
296 Comm.Append((short)0);
297 Comm.Append((short)0);
300 Comm.Append (curPos);
301 Comm.Append ((short) username.Length);
302 curPos += ((short) (username.Length * 2));
305 Comm.Append (curPos);
306 Comm.Append ((short) connectionParameters.Password.Length);
307 curPos += (short) (connectionParameters.Password.Length * 2);
311 Comm.Append (curPos);
312 Comm.Append ((short) connectionParameters.ApplicationName.Length);
313 curPos += (short) (connectionParameters.ApplicationName.Length * 2);
316 Comm.Append (curPos);
317 Comm.Append ((short) DataSource.Length);
318 curPos += (short) (DataSource.Length * 2);
321 Comm.Append ((short) curPos);
322 Comm.Append ((short) 0);
325 Comm.Append (curPos);
326 Comm.Append ((short) connectionParameters.LibraryName.Length);
327 curPos += (short) (connectionParameters.LibraryName.Length * 2);
330 Comm.Append (curPos);
331 Comm.Append ((short) Language.Length);
332 curPos += (short) (Language.Length * 2);
335 Comm.Append (curPos);
336 Comm.Append ((short) connectionParameters.Database.Length);
337 curPos += (short) (connectionParameters.Database.Length * 2);
340 Comm.Append((byte) 0);
341 Comm.Append((byte) 0);
342 Comm.Append((byte) 0);
343 Comm.Append((byte) 0);
344 Comm.Append((byte) 0);
345 Comm.Append((byte) 0);
347 // Authentication Stuff
348 Comm.Append ((short) curPos);
349 if (connectionParameters.DomainLogin) {
350 Comm.Append ((short) authLen);
351 curPos += (short) authLen;
353 Comm.Append ((short) 0);
356 Comm.Append (curPos);
357 Comm.Append ((short)( connectionParameters.AttachDBFileName.Length));
358 curPos += (short)(connectionParameters.AttachDBFileName.Length*2);
360 // Connection Parameters
361 Comm.Append (connectionParameters.Hostname);
362 if (!connectionParameters.DomainLogin) {
363 // SQL Server Authentication
364 Comm.Append (connectionParameters.User);
365 string scrambledPwd = EncryptPassword (connectionParameters.Password);
366 Comm.Append (scrambledPwd);
368 Comm.Append (connectionParameters.ApplicationName);
369 Comm.Append (DataSource);
370 Comm.Append (connectionParameters.LibraryName);
371 Comm.Append (Language);
372 Comm.Append (connectionParameters.Database);
374 if (connectionParameters.DomainLogin) {
375 // the rest of the packet is NTLMSSP authentication
376 Type1Message msg = new Type1Message ();
378 msg.Host = connectionParameters.Hostname;
379 msg.Flags = NtlmFlags.NegotiateUnicode |
380 NtlmFlags.NegotiateNtlm |
381 NtlmFlags.NegotiateDomainSupplied |
382 NtlmFlags.NegotiateWorkstationSupplied |
383 NtlmFlags.NegotiateAlwaysSign; // 0xb201
384 Comm.Append (msg.GetBytes ());
387 Comm.Append (connectionParameters.AttachDBFileName);
395 private static string EncryptPassword (string pass)
397 int xormask = 0x5a5a;
398 int len = pass.Length;
399 char[] chars = new char[len];
401 for (int i = 0; i < len; ++i) {
402 int c = ((int) (pass[i])) ^ xormask;
403 int m1 = (c >> 4) & 0x0f0f;
404 int m2 = (c << 4) & 0xf0f0;
405 chars[i] = (char) (m1 | m2);
408 return new String (chars);
411 public override bool Reset ()
413 // Check validity of the connection - a false removes
414 // the connection from the pool
415 // NOTE: MS implementation will throw a connection-reset error as it will
416 // try to use the same connection
417 if (!Comm.IsConnected ())
420 // Set "reset-connection" bit for the next message packet
421 Comm.ResetConnection = true;
426 public override void ExecPrepared (string commandText, TdsMetaParameterCollection parameters, int timeout, bool wantResults)
428 Parameters = parameters;
429 ExecuteQuery (BuildPreparedQuery (commandText), timeout, wantResults);
432 public override void ExecProc (string commandText, TdsMetaParameterCollection parameters, int timeout, bool wantResults)
434 Parameters = parameters;
435 ExecRPC (commandText, parameters, timeout, wantResults);
438 private void WriteRpcParameterInfo (TdsMetaParameterCollection parameters)
440 if (parameters != null) {
441 foreach (TdsMetaParameter param in parameters) {
442 if (param.Direction == TdsParameterDirection.ReturnValue)
444 string pname = param.ParameterName;
445 if (pname != null && pname.Length > 0 && pname [0] == '@') {
446 Comm.Append ( (byte) pname.Length);
449 Comm.Append ( (byte) (pname.Length + 1));
450 Comm.Append ("@" + pname);
452 short status = 0; // unused
453 if (param.Direction != TdsParameterDirection.Input)
454 status |= 0x01; // output
455 Comm.Append ( (byte) status);
456 WriteParameterInfo (param);
461 private void WritePreparedParameterInfo (TdsMetaParameterCollection parameters)
463 if (parameters == null)
466 string param = BuildPreparedParameters ();
467 Comm.Append ((byte) 0x00); // no param meta data name
468 Comm.Append ((byte) 0x00); // no status flags
470 // Type_info - parameter info
471 WriteParameterInfo (new TdsMetaParameter ("prep_params",
472 param.Length > 4000 ? "ntext" : "nvarchar",
476 protected void ExecRPC (TdsRpcProcId rpcId, string sql,
477 TdsMetaParameterCollection parameters,
478 int timeout, bool wantResults)
482 Comm.StartPacket (TdsPacketType.RPC);
484 Comm.Append ((ushort) 0xFFFF);
485 Comm.Append ((ushort) rpcId);
486 Comm.Append ((short) 0x02); // no meta data
488 Comm.Append ((byte) 0x00); // no param meta data name
489 Comm.Append ((byte) 0x00); // no status flags
491 // Write sql as a parameter value - UCS2
492 TdsMetaParameter param = new TdsMetaParameter ("sql",
493 sql.Length > 4000 ? "ntext":"nvarchar",
495 WriteParameterInfo (param);
497 // Write Parameter infos - name and type
498 WritePreparedParameterInfo (parameters);
500 // Write parameter/value info
501 WriteRpcParameterInfo (parameters);
503 CheckForData (timeout);
508 protected override void ExecRPC (string rpcName, TdsMetaParameterCollection parameters,
509 int timeout, bool wantResults)
513 Comm.StartPacket (TdsPacketType.RPC);
515 Comm.Append ( (short) rpcName.Length);
516 Comm.Append (rpcName);
517 Comm.Append ( (short) 0); //no meta data
518 WriteRpcParameterInfo (parameters);
520 CheckForData (timeout);
525 private void WriteParameterInfo (TdsMetaParameter param)
528 Ms.net send non-nullable datatypes as nullable and allows setting null values
529 to int/float etc.. So, using Nullable form of type for all data
531 param.IsNullable = true;
532 TdsColumnType colType = param.GetMetaType ();
533 param.IsNullable = false;
535 bool partLenType = false;
536 int size = param.Size;
540 size = param.GetActualSize ();
544 * If the value is null, not setting the size to 0 will cause varchar
545 * fields to get inserted as an empty string rather than an null.
547 if (param.Value == null || param.Value == DBNull.Value)
550 // Change colType according to the following table
552 * Original Type Maxlen New Type
554 * NVarChar 4000 UCS2 NText
555 * BigVarChar 8000 ASCII Text
556 * BigVarBinary 8000 bytes Image
559 TdsColumnType origColType = colType;
560 if (colType == TdsColumnType.BigNVarChar) {
561 // param.GetActualSize() returns len*2
562 if (size == param.Size)
564 if ((size >> 1) > 4000)
565 colType = TdsColumnType.NText;
566 } else if (colType == TdsColumnType.BigVarChar) {
568 colType = TdsColumnType.Text;
569 } else if (colType == TdsColumnType.BigVarBinary) {
571 colType = TdsColumnType.Image;
573 // Calculation of TypeInfo field
575 * orig size value TypeInfo field
577 * >= 0 <= Maxlen origColType + content len
578 * > Maxlen NewType as per above table + content len
579 * -1 origColType + USHORTMAXLEN (0xFFFF) + content len (TDS 9)
582 // Write updated colType, iff partLenType == false
583 if (TdsVersion > TdsVersion.tds81 && partLenType) {
584 Comm.Append ((byte)origColType);
585 Comm.Append ((short)-1);
586 } else if (ServerTdsVersion > TdsVersion.tds70
587 && origColType == TdsColumnType.Decimal) {
588 Comm.Append ((byte)TdsColumnType.Numeric);
590 Comm.Append ((byte)colType);
593 if (IsLargeType (colType))
594 Comm.Append ((short)size); // Parameter size passed in SqlParameter
595 else if (IsBlobType (colType))
596 Comm.Append (size); // Parameter size passed in SqlParameter
598 Comm.Append ((byte)size);
600 // Precision and Scale are non-zero for only decimal/numeric
601 if ( param.TypeName == "decimal" || param.TypeName == "numeric") {
602 Comm.Append ((param.Precision !=0 ) ? param.Precision : Precision);
603 Comm.Append (param.Scale);
604 // Convert the decimal value according to Scale
605 if (param.Value != null && param.Value != DBNull.Value &&
606 ((decimal)param.Value) != Decimal.MaxValue &&
607 ((decimal)param.Value) != Decimal.MinValue &&
608 ((decimal)param.Value) != long.MaxValue &&
609 ((decimal)param.Value) != long.MinValue &&
610 ((decimal)param.Value) != ulong.MaxValue &&
611 ((decimal)param.Value) != ulong.MinValue) {
612 long expo = (long)new Decimal (System.Math.Pow (10, (double)param.Scale));
613 long pVal = (long)(((decimal)param.Value) * expo);
619 /* VARADHAN: TDS 8 Debugging */
621 if (Collation != null) {
622 Console.WriteLine ("Collation is not null");
623 Console.WriteLine ("Column Type: {0}", colType);
624 Console.WriteLine ("Collation bytes: {0} {1} {2} {3} {4}", Collation[0], Collation[1], Collation[2],
625 Collation[3], Collation[4]);
627 Console.WriteLine ("Collation is null");
631 // Tds > 7.0 uses collation
632 if (Collation != null &&
633 (colType == TdsColumnType.BigChar || colType == TdsColumnType.BigNVarChar ||
634 colType == TdsColumnType.BigVarChar || colType == TdsColumnType.NChar ||
635 colType == TdsColumnType.NVarChar || colType == TdsColumnType.Text ||
636 colType == TdsColumnType.NText))
637 Comm.Append (Collation);
639 // LAMESPEC: size should be 0xFFFF for any bigvarchar, bignvarchar and bigvarbinary
640 // types if param value is NULL
641 if ((colType == TdsColumnType.BigVarChar ||
642 colType == TdsColumnType.BigNVarChar ||
643 colType == TdsColumnType.BigVarBinary ||
644 colType == TdsColumnType.Image) &&
645 (param.Value == null || param.Value == DBNull.Value))
648 size = param.GetActualSize ();
650 if (IsLargeType (colType))
651 Comm.Append ((short)size);
652 else if (IsBlobType (colType))
655 Comm.Append ((byte)size);
658 switch (param.TypeName) {
660 // 4 == SqlMoney::MoneyFormat.NumberDecimalDigits
661 Decimal val = Decimal.Round ((decimal) param.Value, 4);
662 int[] arr = Decimal.GetBits (val);
665 Comm.Append (arr[1]);
666 Comm.Append (arr[0]);
668 Comm.Append (~arr[1]);
669 Comm.Append (~arr[0] + 1);
674 // 4 == SqlMoney::MoneyFormat.NumberDecimalDigits
675 Decimal val = Decimal.Round ((decimal) param.Value, 4);
676 if (val < SMALLMONEY_MIN || val > SMALLMONEY_MAX)
677 throw new OverflowException (string.Format (
678 CultureInfo.InvariantCulture,
679 "Value '{0}' is not valid for SmallMoney."
680 + " Must be between {1:N4} and {2:N4}.",
684 val.ToString (CultureInfo.CurrentCulture),
686 SMALLMONEY_MIN, SMALLMONEY_MAX));
688 int[] arr = Decimal.GetBits (val);
689 int sign = (val>0 ? 1: -1);
690 Comm.Append (sign * arr[0]);
694 Comm.Append ((DateTime)param.Value, 8);
696 case "smalldatetime":
697 Comm.Append ((DateTime)param.Value, 4);
705 byte [] tmp = param.GetBytes ();
708 case "uniqueidentifier" :
709 Comm.Append (((Guid)param.Value).ToByteArray());
712 Comm.Append (param.Value);
719 public override void Execute (string commandText, TdsMetaParameterCollection parameters, int timeout, bool wantResults)
721 Parameters = parameters;
722 string sql = commandText;
723 if (wantResults || (Parameters != null && Parameters.Count > 0))
724 sql = BuildExec (commandText);
725 ExecuteQuery (sql, timeout, wantResults);
728 private string FormatParameter (TdsMetaParameter parameter)
730 string parameterName = parameter.ParameterName;
731 if (parameterName [0] == '@') {
732 parameterName = parameterName.Substring (1);
734 if (parameter.Direction == TdsParameterDirection.Output)
735 return String.Format ("@{0}=@{0} output", parameterName);
736 if (parameter.Value == null || parameter.Value == DBNull.Value)
737 return String.Format ("@{0}=NULL", parameterName);
740 switch (parameter.TypeName) {
741 case "smalldatetime":
743 DateTime d = Convert.ToDateTime (parameter.Value);
744 value = String.Format (base.Locale,
745 "'{0:MMM dd yyyy hh:mm:ss.fff tt}'", d);
756 object paramValue = parameter.Value;
757 Type paramType = paramValue.GetType ();
758 if (paramType.IsEnum)
759 paramValue = Convert.ChangeType (paramValue,
760 Type.GetTypeCode (paramType));
761 value = paramValue.ToString ();
765 value = String.Format ("N'{0}'", parameter.Value.ToString ().Replace ("'", "''"));
767 case "uniqueidentifier":
768 value = String.Format ("'{0}'", ((Guid) parameter.Value).ToString (string.Empty));
771 if (parameter.Value.GetType () == typeof (bool))
772 value = (((bool) parameter.Value) ? "0x1" : "0x0");
774 value = parameter.Value.ToString ();
779 byte[] byteArray = (byte[]) parameter.Value;
780 // In 1.0 profile, BitConverter.ToString() throws ArgumentOutOfRangeException when passed a 0-length
781 // array, so handle that as a special case.
782 if (byteArray.Length == 0)
785 value = String.Format ("0x{0}", BitConverter.ToString (byteArray).Replace ("-", string.Empty).ToLower ());
788 value = String.Format ("'{0}'", parameter.Value.ToString ().Replace ("'", "''"));
792 return "@" + parameterName + "=" + value;
795 public override string Prepare (string commandText, TdsMetaParameterCollection parameters)
797 Parameters = parameters;
799 TdsMetaParameterCollection parms = new TdsMetaParameterCollection ();
800 // Tested with MS SQL 2008 RC2 Express and MS SQL 2012 Express:
801 // You may pass either -1 or 0, but not null as initial value of @Handle,
802 // which is an output parameter.
803 TdsMetaParameter parm = new TdsMetaParameter ("@Handle", "int", -1);
804 parm.Direction = TdsParameterDirection.Output;
807 parms.Add (new TdsMetaParameter ("@VarDecl", "nvarchar", BuildPreparedParameters ()));
808 parms.Add (new TdsMetaParameter ("@Query", "nvarchar", commandText));
810 ExecProc ("sp_prepare", parms, 0, true);
812 return OutputParameters[0].ToString () ;
813 //if (ColumnValues == null || ColumnValues [0] == null || ColumnValues [0] == DBNull.Value)
814 // throw new TdsInternalException ();
815 //return string.Empty;
816 //return ColumnValues [0].ToString ();
819 protected override void ProcessColumnInfo ()
821 int numColumns = Comm.GetTdsShort ();
822 for (int i = 0; i < numColumns; i += 1) {
823 byte[] flagData = new byte[4];
824 for (int j = 0; j < 4; j += 1)
825 flagData[j] = Comm.GetByte ();
827 bool nullable = (flagData[2] & 0x01) > 0;
828 //bool caseSensitive = (flagData[2] & 0x02) > 0;
829 bool writable = (flagData[2] & 0x0c) > 0;
830 bool autoIncrement = (flagData[2] & 0x10) > 0;
831 bool isIdentity = (flagData[2] & 0x10) > 0;
833 TdsColumnType columnType = (TdsColumnType) ((Comm.GetByte () & 0xff));
835 byte xColumnType = 0;
836 if (IsLargeType (columnType)) {
837 xColumnType = (byte) columnType;
838 if (columnType != TdsColumnType.NChar)
843 string tableName = null;
845 if (IsBlobType (columnType)) {
846 columnSize = Comm.GetTdsInt ();
847 tableName = Comm.GetString (Comm.GetTdsShort ());
848 } else if (IsFixedSizeColumn (columnType)) {
849 columnSize = LookupBufferSize (columnType);
850 } else if (IsLargeType ((TdsColumnType) xColumnType)) {
851 columnSize = Comm.GetTdsShort ();
853 columnSize = Comm.GetByte () & 0xff;
856 if (IsWideType ((TdsColumnType) columnType))
862 if (columnType == TdsColumnType.Decimal || columnType == TdsColumnType.Numeric) {
863 precision = Comm.GetByte ();
864 scale = Comm.GetByte ();
866 precision = GetPrecision (columnType, columnSize);
867 scale = GetScale (columnType, columnSize);
870 string columnName = Comm.GetString (Comm.GetByte ());
872 TdsDataColumn col = new TdsDataColumn ();
875 col.ColumnType = columnType;
876 col.ColumnName = columnName;
877 col.IsAutoIncrement = autoIncrement;
878 col.IsIdentity = isIdentity;
879 col.ColumnSize = columnSize;
880 col.NumericPrecision = precision;
881 col.NumericScale = scale;
882 col.IsReadOnly = !writable;
883 col.AllowDBNull = nullable;
884 col.BaseTableName = tableName;
885 col.DataTypeName = Enum.GetName (typeof (TdsColumnType), xColumnType);
887 col ["ColumnType"] = columnType;
888 col ["ColumnName"] = columnName;
889 col ["IsAutoIncrement"] = autoIncrement;
890 col ["IsIdentity"] = isIdentity;
891 col ["ColumnSize"] = columnSize;
892 col ["NumericPrecision"] = precision;
893 col ["NumericScale"] = scale;
894 col ["IsReadOnly"] = !writable;
895 col ["AllowDBNull"] = nullable;
896 col ["BaseTableName"] = tableName;
897 col ["DataTypeName"] = Enum.GetName (typeof (TdsColumnType), xColumnType);
902 public override void Unprepare (string statementId)
904 TdsMetaParameterCollection parms = new TdsMetaParameterCollection ();
905 parms.Add (new TdsMetaParameter ("@P1", "int", Int32.Parse (statementId)));
906 ExecProc ("sp_unprepare", parms, 0, false);
909 protected override bool IsValidRowCount (byte status, byte op)
911 if ((status & (byte)0x10) == 0 || op == (byte)0xc1)
916 protected override void ProcessReturnStatus ()
918 int result = Comm.GetTdsInt ();
919 if (Parameters != null) {
920 foreach (TdsMetaParameter param in Parameters) {
921 if (param.Direction == TdsParameterDirection.ReturnValue) {
922 param.Value = result;
929 byte GetScale (TdsColumnType type, int columnSize)
932 case TdsColumnType.DateTime:
934 case TdsColumnType.DateTime4:
936 case TdsColumnType.DateTimeN:
937 switch (columnSize) {
948 throw new NotSupportedException (string.Format (
949 CultureInfo.InvariantCulture,
950 "Fixed scale not defined for column " +
951 "type '{0}' with size {1}.", type, columnSize));
954 byte GetPrecision (TdsColumnType type, int columnSize)
957 case TdsColumnType.Binary:
959 case TdsColumnType.Bit:
961 case TdsColumnType.Char:
963 case TdsColumnType.DateTime:
965 case TdsColumnType.DateTime4:
967 case TdsColumnType.DateTimeN:
968 switch (columnSize) {
975 case TdsColumnType.Real:
977 case TdsColumnType.Float8:
979 case TdsColumnType.FloatN:
980 switch (columnSize) {
987 case TdsColumnType.Image:
989 case TdsColumnType.Int1:
991 case TdsColumnType.Int2:
993 case TdsColumnType.Int4:
995 case TdsColumnType.IntN:
996 switch (columnSize) {
1005 case TdsColumnType.Void:
1007 case TdsColumnType.Text:
1009 case TdsColumnType.UniqueIdentifier:
1011 case TdsColumnType.VarBinary:
1013 case TdsColumnType.VarChar:
1015 case TdsColumnType.Money:
1017 case TdsColumnType.NText:
1019 case TdsColumnType.NVarChar:
1021 case TdsColumnType.BitN:
1023 case TdsColumnType.MoneyN:
1024 switch (columnSize) {
1031 case TdsColumnType.Money4:
1033 case TdsColumnType.NChar:
1035 case TdsColumnType.BigBinary:
1037 case TdsColumnType.BigVarBinary:
1039 case TdsColumnType.BigVarChar:
1041 case TdsColumnType.BigNVarChar:
1043 case TdsColumnType.BigChar:
1045 case TdsColumnType.SmallMoney:
1047 case TdsColumnType.Variant:
1049 case TdsColumnType.BigInt:
1053 throw new NotSupportedException (string.Format (
1054 CultureInfo.InvariantCulture,
1055 "Fixed precision not defined for column " +
1056 "type '{0}' with size {1}.", type, columnSize));
1059 #endregion // Methods
1062 #region Asynchronous Methods
1064 public override IAsyncResult BeginExecuteNonQuery (string cmdText,
1065 TdsMetaParameterCollection parameters,
1066 AsyncCallback callback,
1069 Parameters = parameters;
1070 string sql = cmdText;
1071 if (Parameters != null && Parameters.Count > 0)
1072 sql = BuildExec (cmdText);
1074 IAsyncResult ar = BeginExecuteQueryInternal (sql, false, callback, state);
1078 public override void EndExecuteNonQuery (IAsyncResult ar)
1080 EndExecuteQueryInternal (ar);
1083 public override IAsyncResult BeginExecuteQuery (string cmdText,
1084 TdsMetaParameterCollection parameters,
1085 AsyncCallback callback,
1088 Parameters = parameters;
1089 string sql = cmdText;
1090 if (Parameters != null && Parameters.Count > 0)
1091 sql = BuildExec (cmdText);
1093 IAsyncResult ar = BeginExecuteQueryInternal (sql, true, callback, state);
1097 public override void EndExecuteQuery (IAsyncResult ar)
1099 EndExecuteQueryInternal (ar);
1102 public override IAsyncResult BeginExecuteProcedure (string prolog,
1106 TdsMetaParameterCollection parameters,
1107 AsyncCallback callback,
1110 Parameters = parameters;
1111 string pcall = BuildProcedureCall (cmdText);
1112 string sql = String.Format ("{0};{1};{2};", prolog, pcall, epilog);
1114 IAsyncResult ar = BeginExecuteQueryInternal (sql, !IsNonQuery, callback, state);
1118 public override void EndExecuteProcedure (IAsyncResult ar)
1120 EndExecuteQueryInternal (ar);
1123 #endregion // Asynchronous Methods