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;
40 using System.Security;
42 using Mono.Security.Protocol.Ntlm;
44 namespace Mono.Data.Tds.Protocol
46 public class Tds70 : Tds
50 //public readonly static TdsVersion Version = TdsVersion.tds70;
51 static readonly decimal SMALLMONEY_MIN = -214748.3648m;
52 static readonly decimal SMALLMONEY_MAX = 214748.3647m;
58 [Obsolete ("Use the constructor that receives a lifetime parameter")]
59 public Tds70 (string server, int port)
60 : this (server, port, 512, 15, 0)
64 [Obsolete ("Use the constructor that receives a lifetime parameter")]
65 public Tds70 (string server, int port, int packetSize, int timeout)
66 : this (server, port, packetSize, timeout, 0, TdsVersion.tds70)
70 [Obsolete ("Use the constructor that receives a lifetime parameter")]
71 public Tds70 (string server, int port, int packetSize, int timeout, TdsVersion version)
72 : this (server, port, packetSize, timeout, 0, version)
76 public Tds70 (string server, int port, int lifetime)
77 : this (server, port, 512, 15, lifetime)
81 public Tds70 (string server, int port, int packetSize, int timeout, int lifeTime)
82 : base (server, port, packetSize, timeout, lifeTime, TdsVersion.tds70)
86 public Tds70 (string server, int port, int packetSize, int timeout, int lifeTime, TdsVersion version)
87 : base (server, port, packetSize, timeout, lifeTime, version)
91 #endregion // Constructors
95 protected virtual byte[] ClientVersion {
96 get { return new byte[] {0x00, 0x0, 0x0, 0x70};}
99 // Default precision is 28 for a 7.0 server. Unless and
100 // otherwise the server is started with /p option - which would be 38
101 protected virtual byte Precision {
105 #endregion // Properties
109 protected string BuildExec (string sql)
111 string esql = sql.Replace ("'", "''"); // escape single quote
112 if (Parameters != null && Parameters.Count > 0)
113 return BuildProcedureCall (String.Format ("sp_executesql N'{0}', N'{1}', ", esql, BuildPreparedParameters ()));
115 return BuildProcedureCall (String.Format ("sp_executesql N'{0}'", esql));
118 private string BuildParameters ()
120 if (Parameters == null || Parameters.Count == 0)
123 StringBuilder result = new StringBuilder ();
124 foreach (TdsMetaParameter p in Parameters) {
125 string parameterName = p.ParameterName;
126 if (parameterName [0] == '@') {
127 parameterName = parameterName.Substring (1);
129 if (p.Direction != TdsParameterDirection.ReturnValue) {
130 if (result.Length > 0)
131 result.Append (", ");
132 if (p.Direction == TdsParameterDirection.InputOutput)
133 result.AppendFormat ("@{0}={0} output", parameterName);
135 result.Append (FormatParameter (p));
138 return result.ToString ();
141 private string BuildPreparedParameters ()
143 StringBuilder parms = new StringBuilder ();
144 foreach (TdsMetaParameter p in Parameters) {
145 if (parms.Length > 0)
148 // Set default precision according to the TdsVersion
149 // Current default is 29 for Tds80
150 if (p.TypeName == "decimal")
151 p.Precision = (p.Precision !=0 ? p.Precision : (byte) Precision);
153 parms.Append (p.Prepare ());
154 if (p.Direction == TdsParameterDirection.Output)
155 parms.Append (" output");
157 return parms.ToString ();
160 private string BuildPreparedQuery (string id)
162 return BuildProcedureCall (String.Format ("sp_execute {0},", id));
165 private string BuildProcedureCall (string procedure)
167 string exec = String.Empty;
169 StringBuilder declare = new StringBuilder ();
170 StringBuilder select = new StringBuilder ();
171 StringBuilder set = new StringBuilder ();
174 if (Parameters != null) {
175 foreach (TdsMetaParameter p in Parameters) {
176 string parameterName = p.ParameterName;
177 if (parameterName [0] == '@') {
178 parameterName = parameterName.Substring (1);
181 if (p.Direction != TdsParameterDirection.Input) {
183 select.Append ("select ");
185 select.Append (", ");
186 select.Append ("@" + parameterName);
188 if (p.TypeName == "decimal")
189 p.Precision = (p.Precision !=0 ? p.Precision : (byte) Precision);
191 declare.Append (String.Format ("declare {0}\n", p.Prepare ()));
193 if (p.Direction != TdsParameterDirection.ReturnValue) {
194 if (p.Direction == TdsParameterDirection.InputOutput)
195 set.Append (String.Format ("set {0}\n", FormatParameter(p)));
197 set.Append (String.Format ("set @{0}=NULL\n", parameterName));
202 if (p.Direction == TdsParameterDirection.ReturnValue)
203 exec = "@" + parameterName + "=";
206 exec = "exec " + exec;
208 return String.Format ("{0}{1}{2}{3} {4}\n{5}",
209 declare.ToString (), set.ToString (), exec,
210 procedure, BuildParameters (), select.ToString ());
213 public override bool Connect (TdsConnectionParameters connectionParameters)
216 throw new InvalidOperationException ("The connection is already open.");
218 connectionParms = connectionParameters;
220 SetLanguage (connectionParameters.Language);
221 SetCharset ("utf-8");
223 byte[] empty = new byte[0];
227 byte[] domainMagic = { 6, 0x7d, 0x0f, 0xfd, 0xff, 0x0, 0x0, 0x0,
228 0x0, 0xe0, 0x83, 0x0, 0x0,
229 0x68, 0x01, 0x00, 0x00, 0x09, 0x04, 0x00, 0x00 };
230 byte[] sqlserverMagic = { 6, 0x0, 0x0, 0x0,
232 0x0, 0xe0, 0x03, 0x0,
233 0x0, 0x0, 0x0, 0x0, 0x0, 0x0,
237 if (connectionParameters.DomainLogin)
240 magic = sqlserverMagic;
242 string username = connectionParameters.User;
243 string domain = null;
245 int idx = username.IndexOf ("\\");
247 domain = username.Substring (0, idx);
248 username = username.Substring (idx + 1);
250 connectionParameters.DefaultDomain = domain;
251 connectionParameters.User = username;
253 domain = Environment.UserDomainName;
254 connectionParameters.DefaultDomain = domain;
257 short partialPacketSize = (short) (86 + (
258 connectionParameters.Hostname.Length +
259 connectionParameters.ApplicationName.Length +
261 connectionParameters.LibraryName.Length +
263 connectionParameters.Database.Length +
264 connectionParameters.AttachDBFileName.Length) * 2);
266 if (connectionParameters.DomainLogin) {
267 authLen = ((short) (32 + (connectionParameters.Hostname.Length +
269 partialPacketSize += authLen;
271 partialPacketSize += ((short) ((username.Length + connectionParameters.Password.Length) * 2));
273 int totalPacketSize = partialPacketSize;
275 Comm.StartPacket (TdsPacketType.Logon70);
277 Comm.Append (totalPacketSize);
279 //Comm.Append (empty, 3, pad);
280 //byte[] version = {0x00, 0x0, 0x0, 0x71};
281 //Console.WriteLine ("Version: {0}", ClientVersion[3]);
282 Comm.Append (ClientVersion); // TDS Version 7
283 Comm.Append ((int)this.PacketSize); // Set the Block Size
284 Comm.Append (empty, 3, pad);
290 Comm.Append (curPos);
291 Comm.Append ((short) connectionParameters.Hostname.Length);
292 curPos += (short) (connectionParameters.Hostname.Length * 2);
294 if (connectionParameters.DomainLogin) {
295 Comm.Append((short)0);
296 Comm.Append((short)0);
297 Comm.Append((short)0);
298 Comm.Append((short)0);
301 Comm.Append (curPos);
302 Comm.Append ((short) username.Length);
303 curPos += ((short) (username.Length * 2));
306 Comm.Append (curPos);
307 Comm.Append ((short) connectionParameters.Password.Length);
308 curPos += (short) (connectionParameters.Password.Length * 2);
312 Comm.Append (curPos);
313 Comm.Append ((short) connectionParameters.ApplicationName.Length);
314 curPos += (short) (connectionParameters.ApplicationName.Length * 2);
317 Comm.Append (curPos);
318 Comm.Append ((short) DataSource.Length);
319 curPos += (short) (DataSource.Length * 2);
322 Comm.Append ((short) curPos);
323 Comm.Append ((short) 0);
326 Comm.Append (curPos);
327 Comm.Append ((short) connectionParameters.LibraryName.Length);
328 curPos += (short) (connectionParameters.LibraryName.Length * 2);
331 Comm.Append (curPos);
332 Comm.Append ((short) Language.Length);
333 curPos += (short) (Language.Length * 2);
336 Comm.Append (curPos);
337 Comm.Append ((short) connectionParameters.Database.Length);
338 curPos += (short) (connectionParameters.Database.Length * 2);
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);
346 Comm.Append((byte) 0);
348 // Authentication Stuff
349 Comm.Append ((short) curPos);
350 if (connectionParameters.DomainLogin) {
351 Comm.Append ((short) authLen);
352 curPos += (short) authLen;
354 Comm.Append ((short) 0);
357 Comm.Append (curPos);
358 Comm.Append ((short)( connectionParameters.AttachDBFileName.Length));
359 curPos += (short)(connectionParameters.AttachDBFileName.Length*2);
361 // Connection Parameters
362 Comm.Append (connectionParameters.Hostname);
363 if (!connectionParameters.DomainLogin) {
364 // SQL Server Authentication
365 Comm.Append (connectionParameters.User);
366 string scrambledPwd = EncryptPassword (connectionParameters.Password);
367 Comm.Append (scrambledPwd);
369 Comm.Append (connectionParameters.ApplicationName);
370 Comm.Append (DataSource);
371 Comm.Append (connectionParameters.LibraryName);
372 Comm.Append (Language);
373 Comm.Append (connectionParameters.Database);
375 if (connectionParameters.DomainLogin) {
376 // the rest of the packet is NTLMSSP authentication
377 Type1Message msg = new Type1Message ();
379 msg.Host = connectionParameters.Hostname;
380 msg.Flags = NtlmFlags.NegotiateUnicode |
381 NtlmFlags.NegotiateNtlm |
382 NtlmFlags.NegotiateDomainSupplied |
383 NtlmFlags.NegotiateWorkstationSupplied |
384 NtlmFlags.NegotiateAlwaysSign; // 0xb201
385 Comm.Append (msg.GetBytes ());
388 Comm.Append (connectionParameters.AttachDBFileName);
396 private static string EncryptPassword (SecureString secPass)
398 int xormask = 0x5a5a;
399 int len = secPass.Length;
400 char[] chars = new char[len];
401 string pass = GetPlainPassword(secPass);
403 for (int i = 0; i < len; ++i) {
404 int c = ((int) (pass[i])) ^ xormask;
405 int m1 = (c >> 4) & 0x0f0f;
406 int m2 = (c << 4) & 0xf0f0;
407 chars[i] = (char) (m1 | m2);
410 return new String (chars);
413 public override bool Reset ()
415 // Check validity of the connection - a false removes
416 // the connection from the pool
417 // NOTE: MS implementation will throw a connection-reset error as it will
418 // try to use the same connection
419 if (!Comm.IsConnected ())
422 // Set "reset-connection" bit for the next message packet
423 Comm.ResetConnection = true;
428 public override void ExecPrepared (string commandText, TdsMetaParameterCollection parameters, int timeout, bool wantResults)
430 Parameters = parameters;
431 ExecuteQuery (BuildPreparedQuery (commandText), timeout, wantResults);
434 public override void ExecProc (string commandText, TdsMetaParameterCollection parameters, int timeout, bool wantResults)
436 Parameters = parameters;
437 ExecRPC (commandText, parameters, timeout, wantResults);
440 private void WriteRpcParameterInfo (TdsMetaParameterCollection parameters)
442 if (parameters != null) {
443 foreach (TdsMetaParameter param in parameters) {
444 if (param.Direction == TdsParameterDirection.ReturnValue)
446 string pname = param.ParameterName;
447 if (pname != null && pname.Length > 0 && pname [0] == '@') {
448 Comm.Append ( (byte) pname.Length);
451 Comm.Append ( (byte) (pname.Length + 1));
452 Comm.Append ("@" + pname);
454 short status = 0; // unused
455 if (param.Direction != TdsParameterDirection.Input)
456 status |= 0x01; // output
457 Comm.Append ( (byte) status);
458 WriteParameterInfo (param);
463 private void WritePreparedParameterInfo (TdsMetaParameterCollection parameters)
465 if (parameters == null)
468 string param = BuildPreparedParameters ();
469 Comm.Append ((byte) 0x00); // no param meta data name
470 Comm.Append ((byte) 0x00); // no status flags
472 // Type_info - parameter info
473 WriteParameterInfo (new TdsMetaParameter ("prep_params",
474 param.Length > 4000 ? "ntext" : "nvarchar",
478 protected void ExecRPC (TdsRpcProcId rpcId, string sql,
479 TdsMetaParameterCollection parameters,
480 int timeout, bool wantResults)
484 Comm.StartPacket (TdsPacketType.RPC);
486 Comm.Append ((ushort) 0xFFFF);
487 Comm.Append ((ushort) rpcId);
488 Comm.Append ((short) 0x02); // no meta data
490 Comm.Append ((byte) 0x00); // no param meta data name
491 Comm.Append ((byte) 0x00); // no status flags
493 // Convert BigNVarChar values larger than 4000 chars to nvarchar(max)
494 // Need to do this here so WritePreparedParameterInfo emit the
496 foreach (TdsMetaParameter param2 in parameters) {
497 var colType = param2.GetMetaType ();
499 if (colType == TdsColumnType.BigNVarChar) {
500 int size = param2.GetActualSize ();
501 if ((size >> 1) > 4000)
506 // Write sql as a parameter value - UCS2
507 TdsMetaParameter param = new TdsMetaParameter ("sql",
508 sql.Length > 4000 ? "ntext":"nvarchar",
510 WriteParameterInfo (param);
512 // Write Parameter infos - name and type
513 WritePreparedParameterInfo (parameters);
515 // Write parameter/value info
516 WriteRpcParameterInfo (parameters);
518 CheckForData (timeout);
523 protected override void ExecRPC (string rpcName, TdsMetaParameterCollection parameters,
524 int timeout, bool wantResults)
528 Comm.StartPacket (TdsPacketType.RPC);
530 Comm.Append ( (short) rpcName.Length);
531 Comm.Append (rpcName);
532 Comm.Append ( (short) 0); //no meta data
533 WriteRpcParameterInfo (parameters);
535 CheckForData (timeout);
540 private void WriteParameterInfo (TdsMetaParameter param)
543 Ms.net send non-nullable datatypes as nullable and allows setting null values
544 to int/float etc.. So, using Nullable form of type for all data
546 param.IsNullable = true;
547 TdsColumnType colType = param.GetMetaType ();
548 param.IsNullable = false;
550 bool partLenType = false;
551 int size = param.Size;
555 size = param.GetActualSize ();
559 * If the value is null, not setting the size to 0 will cause varchar
560 * fields to get inserted as an empty string rather than an null.
562 if (param.Value == null || param.Value == DBNull.Value)
565 // Change colType according to the following table
567 * Original Type Maxlen New Type
569 * NVarChar 4000 UCS2 NText
570 * BigVarChar 8000 ASCII Text
571 * BigVarBinary 8000 bytes Image
574 TdsColumnType origColType = colType;
575 if (colType == TdsColumnType.BigNVarChar) {
576 // param.GetActualSize() returns len*2
577 if (size == param.Size)
579 if ((size >> 1) > 4000)
580 colType = TdsColumnType.NText;
581 } else if (colType == TdsColumnType.BigVarChar) {
583 colType = TdsColumnType.Text;
584 } else if (colType == TdsColumnType.BigVarBinary) {
586 colType = TdsColumnType.Image;
588 // Calculation of TypeInfo field
590 * orig size value TypeInfo field
592 * >= 0 <= Maxlen origColType + content len
593 * > Maxlen NewType as per above table + content len
594 * -1 origColType + USHORTMAXLEN (0xFFFF) + content len (TDS 9)
597 // Write updated colType, iff partLenType == false
598 if (TdsVersion > TdsVersion.tds81 && partLenType) {
599 Comm.Append ((byte)origColType);
600 Comm.Append ((short)-1);
601 } else if (ServerTdsVersion > TdsVersion.tds70
602 && origColType == TdsColumnType.Decimal) {
603 Comm.Append ((byte)TdsColumnType.Numeric);
605 Comm.Append ((byte)colType);
608 if (IsLargeType (colType))
609 Comm.Append ((short)size); // Parameter size passed in SqlParameter
610 else if (IsBlobType (colType))
611 Comm.Append (size); // Parameter size passed in SqlParameter
613 Comm.Append ((byte)size);
615 // Precision and Scale are non-zero for only decimal/numeric
616 if ( param.TypeName == "decimal" || param.TypeName == "numeric") {
617 Comm.Append ((param.Precision !=0 ) ? param.Precision : Precision);
618 Comm.Append (param.Scale);
619 // Convert the decimal value according to Scale
620 if (param.Value != null && param.Value != DBNull.Value &&
621 ((decimal)param.Value) != Decimal.MaxValue &&
622 ((decimal)param.Value) != Decimal.MinValue &&
623 ((decimal)param.Value) != long.MaxValue &&
624 ((decimal)param.Value) != long.MinValue &&
625 ((decimal)param.Value) != ulong.MaxValue &&
626 ((decimal)param.Value) != ulong.MinValue) {
627 long expo = (long)new Decimal (System.Math.Pow (10, (double)param.Scale));
628 long pVal = (long)(((decimal)param.Value) * expo);
634 /* VARADHAN: TDS 8 Debugging */
636 if (Collation != null) {
637 Console.WriteLine ("Collation is not null");
638 Console.WriteLine ("Column Type: {0}", colType);
639 Console.WriteLine ("Collation bytes: {0} {1} {2} {3} {4}", Collation[0], Collation[1], Collation[2],
640 Collation[3], Collation[4]);
642 Console.WriteLine ("Collation is null");
646 // Tds > 7.0 uses collation
647 if (Collation != null &&
648 (colType == TdsColumnType.BigChar || colType == TdsColumnType.BigNVarChar ||
649 colType == TdsColumnType.BigVarChar || colType == TdsColumnType.NChar ||
650 colType == TdsColumnType.NVarChar || colType == TdsColumnType.Text ||
651 colType == TdsColumnType.NText))
652 Comm.Append (Collation);
654 // LAMESPEC: size should be 0xFFFF for any bigvarchar, bignvarchar and bigvarbinary
655 // types if param value is NULL
656 if ((colType == TdsColumnType.BigVarChar ||
657 colType == TdsColumnType.BigNVarChar ||
658 colType == TdsColumnType.BigVarBinary ||
659 colType == TdsColumnType.Image) &&
660 (param.Value == null || param.Value == DBNull.Value))
663 size = param.GetActualSize ();
665 if (IsLargeType (colType))
666 Comm.Append ((short)size);
667 else if (IsBlobType (colType))
670 Comm.Append ((byte)size);
673 switch (param.TypeName) {
675 // 4 == SqlMoney::MoneyFormat.NumberDecimalDigits
676 Decimal val = Decimal.Round ((decimal) param.Value, 4);
677 int[] arr = Decimal.GetBits (val);
680 Comm.Append (arr[1]);
681 Comm.Append (arr[0]);
683 Comm.Append (~arr[1]);
684 Comm.Append (~arr[0] + 1);
689 // 4 == SqlMoney::MoneyFormat.NumberDecimalDigits
690 Decimal val = Decimal.Round ((decimal) param.Value, 4);
691 if (val < SMALLMONEY_MIN || val > SMALLMONEY_MAX)
692 throw new OverflowException (string.Format (
693 CultureInfo.InvariantCulture,
694 "Value '{0}' is not valid for SmallMoney."
695 + " Must be between {1:N4} and {2:N4}.",
699 val.ToString (CultureInfo.CurrentCulture),
701 SMALLMONEY_MIN, SMALLMONEY_MAX));
703 int[] arr = Decimal.GetBits (val);
704 int sign = (val>0 ? 1: -1);
705 Comm.Append (sign * arr[0]);
709 Comm.Append ((DateTime)param.Value, 8);
711 case "smalldatetime":
712 Comm.Append ((DateTime)param.Value, 4);
720 byte [] tmp = param.GetBytes ();
723 case "uniqueidentifier" :
724 Comm.Append (((Guid)param.Value).ToByteArray());
727 Comm.Append (param.Value);
734 public override void Execute (string commandText, TdsMetaParameterCollection parameters, int timeout, bool wantResults)
736 Parameters = parameters;
737 string sql = commandText;
738 if (wantResults || (Parameters != null && Parameters.Count > 0))
739 sql = BuildExec (commandText);
740 ExecuteQuery (sql, timeout, wantResults);
743 private string FormatParameter (TdsMetaParameter parameter)
745 string parameterName = parameter.ParameterName;
746 if (parameterName [0] == '@') {
747 parameterName = parameterName.Substring (1);
749 if (parameter.Direction == TdsParameterDirection.Output)
750 return String.Format ("@{0}=@{0} output", parameterName);
751 if (parameter.Value == null || parameter.Value == DBNull.Value)
752 return String.Format ("@{0}=NULL", parameterName);
755 switch (parameter.TypeName) {
756 case "smalldatetime":
758 DateTime d = Convert.ToDateTime (parameter.Value);
759 value = String.Format (base.Locale,
760 "'{0:MMM dd yyyy hh:mm:ss.fff tt}'", d);
771 object paramValue = parameter.Value;
772 Type paramType = paramValue.GetType ();
773 if (paramType.IsEnum)
774 paramValue = Convert.ChangeType (paramValue,
775 Type.GetTypeCode (paramType));
776 value = paramValue.ToString ();
780 value = String.Format ("N'{0}'", parameter.Value.ToString ().Replace ("'", "''"));
782 case "uniqueidentifier":
783 value = String.Format ("'{0}'", ((Guid) parameter.Value).ToString (string.Empty));
786 if (parameter.Value.GetType () == typeof (bool))
787 value = (((bool) parameter.Value) ? "0x1" : "0x0");
789 value = parameter.Value.ToString ();
794 byte[] byteArray = (byte[]) parameter.Value;
795 // In 1.0 profile, BitConverter.ToString() throws ArgumentOutOfRangeException when passed a 0-length
796 // array, so handle that as a special case.
797 if (byteArray.Length == 0)
800 value = String.Format ("0x{0}", BitConverter.ToString (byteArray).Replace ("-", string.Empty).ToLower ());
803 value = String.Format ("'{0}'", parameter.Value.ToString ().Replace ("'", "''"));
807 return "@" + parameterName + "=" + value;
810 public override string Prepare (string commandText, TdsMetaParameterCollection parameters)
812 Parameters = parameters;
814 TdsMetaParameterCollection parms = new TdsMetaParameterCollection ();
815 // Tested with MS SQL 2008 RC2 Express and MS SQL 2012 Express:
816 // You may pass either -1 or 0, but not null as initial value of @Handle,
817 // which is an output parameter.
818 TdsMetaParameter parm = new TdsMetaParameter ("@Handle", "int", -1);
819 parm.Direction = TdsParameterDirection.Output;
822 parms.Add (new TdsMetaParameter ("@VarDecl", "nvarchar", BuildPreparedParameters ()));
823 parms.Add (new TdsMetaParameter ("@Query", "nvarchar", commandText));
825 ExecProc ("sp_prepare", parms, 0, true);
827 return OutputParameters[0].ToString () ;
828 //if (ColumnValues == null || ColumnValues [0] == null || ColumnValues [0] == DBNull.Value)
829 // throw new TdsInternalException ();
830 //return string.Empty;
831 //return ColumnValues [0].ToString ();
834 protected override void ProcessColumnInfo ()
836 int numColumns = Comm.GetTdsShort ();
837 for (int i = 0; i < numColumns; i += 1) {
838 byte[] flagData = new byte[4];
839 for (int j = 0; j < 4; j += 1)
840 flagData[j] = Comm.GetByte ();
842 bool nullable = (flagData[2] & 0x01) > 0;
843 //bool caseSensitive = (flagData[2] & 0x02) > 0;
844 bool writable = (flagData[2] & 0x0c) > 0;
845 bool autoIncrement = (flagData[2] & 0x10) > 0;
846 bool isIdentity = (flagData[2] & 0x10) > 0;
848 TdsColumnType columnType = (TdsColumnType) ((Comm.GetByte () & 0xff));
850 byte xColumnType = 0;
851 if (IsLargeType (columnType)) {
852 xColumnType = (byte) columnType;
853 if (columnType != TdsColumnType.NChar)
858 string tableName = null;
860 if (IsBlobType (columnType)) {
861 columnSize = Comm.GetTdsInt ();
862 tableName = Comm.GetString (Comm.GetTdsShort ());
863 } else if (IsFixedSizeColumn (columnType)) {
864 columnSize = LookupBufferSize (columnType);
865 } else if (IsLargeType ((TdsColumnType) xColumnType)) {
866 columnSize = Comm.GetTdsShort ();
868 columnSize = Comm.GetByte () & 0xff;
871 if (IsWideType ((TdsColumnType) columnType))
877 if (columnType == TdsColumnType.Decimal || columnType == TdsColumnType.Numeric) {
878 precision = Comm.GetByte ();
879 scale = Comm.GetByte ();
881 precision = GetPrecision (columnType, columnSize);
882 scale = GetScale (columnType, columnSize);
885 string columnName = Comm.GetString (Comm.GetByte ());
887 TdsDataColumn col = new TdsDataColumn ();
890 col.ColumnType = columnType;
891 col.ColumnName = columnName;
892 col.IsAutoIncrement = autoIncrement;
893 col.IsIdentity = isIdentity;
894 col.ColumnSize = columnSize;
895 col.NumericPrecision = precision;
896 col.NumericScale = scale;
897 col.IsReadOnly = !writable;
898 col.AllowDBNull = nullable;
899 col.BaseTableName = tableName;
900 col.DataTypeName = Enum.GetName (typeof (TdsColumnType), xColumnType);
902 col ["ColumnType"] = columnType;
903 col ["ColumnName"] = columnName;
904 col ["IsAutoIncrement"] = autoIncrement;
905 col ["IsIdentity"] = isIdentity;
906 col ["ColumnSize"] = columnSize;
907 col ["NumericPrecision"] = precision;
908 col ["NumericScale"] = scale;
909 col ["IsReadOnly"] = !writable;
910 col ["AllowDBNull"] = nullable;
911 col ["BaseTableName"] = tableName;
912 col ["DataTypeName"] = Enum.GetName (typeof (TdsColumnType), xColumnType);
917 public override void Unprepare (string statementId)
919 TdsMetaParameterCollection parms = new TdsMetaParameterCollection ();
920 parms.Add (new TdsMetaParameter ("@P1", "int", Int32.Parse (statementId)));
921 ExecProc ("sp_unprepare", parms, 0, false);
924 protected override bool IsValidRowCount (byte status, byte op)
926 if ((status & (byte)0x10) == 0 || op == (byte)0xc1)
931 protected override void ProcessReturnStatus ()
933 int result = Comm.GetTdsInt ();
934 if (Parameters != null) {
935 foreach (TdsMetaParameter param in Parameters) {
936 if (param.Direction == TdsParameterDirection.ReturnValue) {
937 param.Value = result;
944 byte GetScale (TdsColumnType type, int columnSize)
947 case TdsColumnType.DateTime:
949 case TdsColumnType.DateTime4:
951 case TdsColumnType.DateTimeN:
952 switch (columnSize) {
963 throw new NotSupportedException (string.Format (
964 CultureInfo.InvariantCulture,
965 "Fixed scale not defined for column " +
966 "type '{0}' with size {1}.", type, columnSize));
969 byte GetPrecision (TdsColumnType type, int columnSize)
972 case TdsColumnType.Binary:
974 case TdsColumnType.Bit:
976 case TdsColumnType.Char:
978 case TdsColumnType.DateTime:
980 case TdsColumnType.DateTime4:
982 case TdsColumnType.DateTimeN:
983 switch (columnSize) {
990 case TdsColumnType.Real:
992 case TdsColumnType.Float8:
994 case TdsColumnType.FloatN:
995 switch (columnSize) {
1002 case TdsColumnType.Image:
1004 case TdsColumnType.Int1:
1006 case TdsColumnType.Int2:
1008 case TdsColumnType.Int4:
1010 case TdsColumnType.IntN:
1011 switch (columnSize) {
1020 case TdsColumnType.Void:
1022 case TdsColumnType.Text:
1024 case TdsColumnType.UniqueIdentifier:
1026 case TdsColumnType.VarBinary:
1028 case TdsColumnType.VarChar:
1030 case TdsColumnType.Money:
1032 case TdsColumnType.NText:
1034 case TdsColumnType.NVarChar:
1036 case TdsColumnType.BitN:
1038 case TdsColumnType.MoneyN:
1039 switch (columnSize) {
1046 case TdsColumnType.Money4:
1048 case TdsColumnType.NChar:
1050 case TdsColumnType.BigBinary:
1052 case TdsColumnType.BigVarBinary:
1054 case TdsColumnType.BigVarChar:
1056 case TdsColumnType.BigNVarChar:
1058 case TdsColumnType.BigChar:
1060 case TdsColumnType.SmallMoney:
1062 case TdsColumnType.Variant:
1064 case TdsColumnType.BigInt:
1068 throw new NotSupportedException (string.Format (
1069 CultureInfo.InvariantCulture,
1070 "Fixed precision not defined for column " +
1071 "type '{0}' with size {1}.", type, columnSize));
1074 #endregion // Methods
1077 #region Asynchronous Methods
1079 public override IAsyncResult BeginExecuteNonQuery (string cmdText,
1080 TdsMetaParameterCollection parameters,
1081 AsyncCallback callback,
1084 Parameters = parameters;
1085 string sql = cmdText;
1086 if (Parameters != null && Parameters.Count > 0)
1087 sql = BuildExec (cmdText);
1089 IAsyncResult ar = BeginExecuteQueryInternal (sql, false, callback, state);
1093 public override void EndExecuteNonQuery (IAsyncResult ar)
1095 EndExecuteQueryInternal (ar);
1098 public override IAsyncResult BeginExecuteQuery (string cmdText,
1099 TdsMetaParameterCollection parameters,
1100 AsyncCallback callback,
1103 Parameters = parameters;
1104 string sql = cmdText;
1105 if (Parameters != null && Parameters.Count > 0)
1106 sql = BuildExec (cmdText);
1108 IAsyncResult ar = BeginExecuteQueryInternal (sql, true, callback, state);
1112 public override void EndExecuteQuery (IAsyncResult ar)
1114 EndExecuteQueryInternal (ar);
1117 public override IAsyncResult BeginExecuteProcedure (string prolog,
1121 TdsMetaParameterCollection parameters,
1122 AsyncCallback callback,
1125 Parameters = parameters;
1126 string pcall = BuildProcedureCall (cmdText);
1127 string sql = String.Format ("{0};{1};{2};", prolog, pcall, epilog);
1129 IAsyncResult ar = BeginExecuteQueryInternal (sql, !IsNonQuery, callback, state);
1133 public override void EndExecuteProcedure (IAsyncResult ar)
1135 EndExecuteQueryInternal (ar);
1138 #endregion // Asynchronous Methods