mcs/class/Mono.Data.Tds/Mono.Data.Tds.Protocol/Tds70.cs

   1 //
   2 // Mono.Data.Tds.Protocol.Tds70.cs
   3 //
   4 // Author:
   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)
  11 //
  12 // Copyright (C) 2002 Tim Coleman
  13 // Portions (C) 2003 Motus Technologies Inc. (http://www.motus.com)
  14 // Portions (C) 2003 Daniel Morgan
  15 //
  16 //
  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:
  24 //
  25 // The above copyright notice and this permission notice shall be
  26 // included in all copies or substantial portions of the Software.
  27 //
  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.
  35 //
  36
  37 using System;
  38 using System.Globalization;
  39 using System.Text;
  40
  41 using Mono.Security.Protocol.Ntlm;
  42
  43 namespace Mono.Data.Tds.Protocol
  44 {
  45         public class Tds70 : Tds
  46         {
  47                 #region Fields
  48
  49                 //public readonly static TdsVersion Version = TdsVersion.tds70;
  50                 static readonly decimal SMALLMONEY_MIN = -214748.3648m;
  51                 static readonly decimal SMALLMONEY_MAX = 214748.3647m;
  52
  53                 #endregion // Fields
  54
  55                 #region Constructors
  56
  57                 public Tds70 (string server, int port)
  58                         : this (server, port, 512, 15)
  59                 {
  60                 }
  61
  62                 public Tds70 (string server, int port, int packetSize, int timeout)
  63                         : base (server, port, packetSize, timeout, TdsVersion.tds70)
  64                 {
  65                 }
  66
  67                 public Tds70 (string server, int port, int packetSize, int timeout, TdsVersion version)
  68                         : base (server, port, packetSize, timeout, version)
  69                 {
  70                 }
  71
  72                 #endregion // Constructors
  73
  74                 #region Properties
  75
  76                 protected virtual byte[] ClientVersion {
  77                         get { return new byte[] {0x00, 0x0, 0x0, 0x70};}
  78                 }
  79
  80                 // Default precision is 28 for a 7.0 server. Unless and
  81                 // otherwise the server is started with /p option - which would be 38
  82                 protected virtual byte Precision {
  83                         get { return 28; }
  84                 }
  85
  86                 #endregion // Properties
  87
  88                 #region Methods
  89
  90                 protected string BuildExec (string sql)
  91                 {
  92                         string esql = sql.Replace ("'", "''"); // escape single quote
  93                         if (Parameters != null && Parameters.Count > 0)
  94                                 return BuildProcedureCall (String.Format ("sp_executesql N'{0}', N'{1}', ", esql, BuildPreparedParameters ()));
  95                         else
  96                                 return BuildProcedureCall (String.Format ("sp_executesql N'{0}'", esql));
  97                 }
  98
  99                 private string BuildParameters ()
 100                 {
 101                         if (Parameters == null || Parameters.Count == 0)
 102                                 return String.Empty;
 103
 104                         StringBuilder result = new StringBuilder ();
 105                         foreach (TdsMetaParameter p in Parameters) {
 106                                 string parameterName = p.ParameterName;
 107                                 if (parameterName [0] == '@') {
 108                                         parameterName = parameterName.Substring (1);
 109                                 }
 110                                 if (p.Direction != TdsParameterDirection.ReturnValue) {
 111                                         if (result.Length > 0)
 112                                                 result.Append (", ");
 113                                         if (p.Direction == TdsParameterDirection.InputOutput)
 114                                                 result.AppendFormat ("@{0}={0} output", parameterName);
 115                                         else
 116                                                 result.Append (FormatParameter (p));
 117                                 }
 118                         }
 119                         return result.ToString ();
 120                 }
 121
 122                 private string BuildPreparedParameters ()
 123                 {
 124                         StringBuilder parms = new StringBuilder ();
 125                         foreach (TdsMetaParameter p in Parameters) {
 126                                 if (parms.Length > 0)
 127                                         parms.Append (", ");
 128
 129                                 // Set default precision according to the TdsVersion
 130                                 // Current default is 29 for Tds80
 131                                 if (p.TypeName == "decimal")
 132                                         p.Precision = (p.Precision !=0  ? p.Precision : (byte) Precision);
 133
 134                                 parms.Append (p.Prepare ());
 135                                 if (p.Direction == TdsParameterDirection.Output)
 136                                         parms.Append (" output");
 137                         }
 138                         return parms.ToString ();
 139                 }
 140
 141                 private string BuildPreparedQuery (string id)
 142                 {
 143                         return BuildProcedureCall (String.Format ("sp_execute {0},", id));
 144                 }
 145
 146                 private string BuildProcedureCall (string procedure)
 147                 {
 148                         string exec = String.Empty;
 149
 150                         StringBuilder declare = new StringBuilder ();
 151                         StringBuilder select = new StringBuilder ();
 152                         StringBuilder set = new StringBuilder ();
 153
 154                         int count = 0;
 155                         if (Parameters != null) {
 156                                 foreach (TdsMetaParameter p in Parameters) {
 157                                         string parameterName = p.ParameterName;
 158                                         if (parameterName [0] == '@') {
 159                                                 parameterName = parameterName.Substring (1);
 160                                         }
 161
 162                                         if (p.Direction != TdsParameterDirection.Input) {
 163                                                 if (count == 0)
 164                                                         select.Append ("select ");
 165                                                 else
 166                                                         select.Append (", ");
 167                                                 select.Append ("@" + parameterName);
 168
 169                                                 if (p.TypeName == "decimal")
 170                                                         p.Precision = (p.Precision !=0 ? p.Precision : (byte) Precision);
 171
 172                                                 declare.Append (String.Format ("declare {0}\n", p.Prepare ()));
 173
 174                                                 if (p.Direction != TdsParameterDirection.ReturnValue) {
 175                                                         if (p.Direction == TdsParameterDirection.InputOutput)
 176                                                                 set.Append (String.Format ("set {0}\n", FormatParameter(p)));
 177                                                         else
 178                                                                 set.Append (String.Format ("set @{0}=NULL\n", parameterName));
 179                                                 }
 180
 181                                                 count++;
 182                                         }
 183                                         if (p.Direction == TdsParameterDirection.ReturnValue)
 184                                                 exec = "@" + parameterName + "=";
 185                                 }
 186                         }
 187                         exec = "exec " + exec;
 188
 189                         return String.Format ("{0}{1}{2}{3} {4}\n{5}",
 190                                 declare.ToString (), set.ToString (), exec,
 191                                 procedure, BuildParameters (), select.ToString ());
 192                 }
 193
 194                 public override bool Connect (TdsConnectionParameters connectionParameters)
 195                 {
 196                         if (IsConnected)
 197                                 throw new InvalidOperationException ("The connection is already open.");
 198
 199                         connectionParms = connectionParameters;
 200
 201                         SetLanguage (connectionParameters.Language);
 202                         SetCharset ("utf-8");
 203
 204                         byte[] empty = new byte[0];
 205                         short authLen = 0;
 206                         byte pad = (byte) 0;
 207
 208                         byte[] domainMagic = { 6, 0x7d, 0x0f, 0xfd, 0xff, 0x0, 0x0, 0x0,
 209                                                                         0x0, 0xe0, 0x83, 0x0, 0x0,
 210                                                                         0x68, 0x01, 0x00, 0x00, 0x09, 0x04, 0x00, 0x00 };
 211                         byte[] sqlserverMagic = { 6, 0x0, 0x0, 0x0,
 212                                                                                 0x0, 0x0, 0x0, 0x0,
 213                                                                                 0x0, 0xe0, 0x03, 0x0,
 214                                                                                 0x0, 0x0, 0x0, 0x0, 0x0, 0x0,
 215                                                                                 0x0, 0x0, 0x0 };
 216                         byte[] magic = null;
 217
 218                         if (connectionParameters.DomainLogin)
 219                                 magic = domainMagic;
 220                         else
 221                                 magic = sqlserverMagic;
 222
 223                         string username = connectionParameters.User;
 224                         string domain = null;
 225
 226                         int idx = username.IndexOf ("\\");
 227                         if (idx != -1) {
 228                                 domain = username.Substring (0, idx);
 229                                 username = username.Substring (idx + 1);
 230
 231                                 connectionParameters.DefaultDomain = domain;
 232                                 connectionParameters.User = username;
 233                         } else {
 234                                 domain = Environment.UserDomainName;
 235                                 connectionParameters.DefaultDomain = domain;
 236                         }
 237
 238                         short partialPacketSize = (short) (86 + (
 239                                 connectionParameters.Hostname.Length +
 240                                 connectionParameters.ApplicationName.Length +
 241                                 DataSource.Length +
 242                                 connectionParameters.LibraryName.Length +
 243                                 Language.Length +
 244                                 connectionParameters.Database.Length +
 245                                 connectionParameters.AttachDBFileName.Length) * 2);
 246
 247                         if (connectionParameters.DomainLogin) {
 248                                 authLen = ((short) (32 + (connectionParameters.Hostname.Length +
 249                                         domain.Length)));
 250                                 partialPacketSize += authLen;
 251                         } else
 252                                 partialPacketSize += ((short) ((username.Length + connectionParameters.Password.Length) * 2));
 253
 254                         int totalPacketSize = partialPacketSize;
 255
 256                         Comm.StartPacket (TdsPacketType.Logon70);
 257
 258                         Comm.Append (totalPacketSize);
 259
 260                         //Comm.Append (empty, 3, pad);
 261                         //byte[] version = {0x00, 0x0, 0x0, 0x71};
 262                         //Console.WriteLine ("Version: {0}", ClientVersion[3]);
 263                         Comm.Append (ClientVersion); // TDS Version 7
 264                         Comm.Append ((int)this.PacketSize); // Set the Block Size
 265                         Comm.Append (empty, 3, pad);
 266                         Comm.Append (magic);
 267
 268                         short curPos = 86;
 269
 270                         // Hostname
 271                         Comm.Append (curPos);
 272                         Comm.Append ((short) connectionParameters.Hostname.Length);
 273                         curPos += (short) (connectionParameters.Hostname.Length * 2);
 274
 275                         if (connectionParameters.DomainLogin) {
 276                                 Comm.Append((short)0);
 277                                 Comm.Append((short)0);
 278                                 Comm.Append((short)0);
 279                                 Comm.Append((short)0);
 280                         } else {
 281                                 // Username
 282                                 Comm.Append (curPos);
 283                                 Comm.Append ((short) username.Length);
 284                                 curPos += ((short) (username.Length * 2));
 285
 286                                 // Password
 287                                 Comm.Append (curPos);
 288                                 Comm.Append ((short) connectionParameters.Password.Length);
 289                                 curPos += (short) (connectionParameters.Password.Length * 2);
 290                         }
 291
 292                         // AppName
 293                         Comm.Append (curPos);
 294                         Comm.Append ((short) connectionParameters.ApplicationName.Length);
 295                         curPos += (short) (connectionParameters.ApplicationName.Length * 2);
 296
 297                         // Server Name
 298                         Comm.Append (curPos);
 299                         Comm.Append ((short) DataSource.Length);
 300                         curPos += (short) (DataSource.Length * 2);
 301
 302                         // Unknown
 303                         Comm.Append ((short) curPos);
 304                         Comm.Append ((short) 0);
 305
 306                         // Library Name
 307                         Comm.Append (curPos);
 308                         Comm.Append ((short) connectionParameters.LibraryName.Length);
 309                         curPos += (short) (connectionParameters.LibraryName.Length * 2);
 310
 311                         // Language
 312                         Comm.Append (curPos);
 313                         Comm.Append ((short) Language.Length);
 314                         curPos += (short) (Language.Length * 2);
 315
 316                         // Database
 317                         Comm.Append (curPos);
 318                         Comm.Append ((short) connectionParameters.Database.Length);
 319                         curPos += (short) (connectionParameters.Database.Length * 2);
 320
 321                         // MAC Address
 322                         Comm.Append((byte) 0);
 323                         Comm.Append((byte) 0);
 324                         Comm.Append((byte) 0);
 325                         Comm.Append((byte) 0);
 326                         Comm.Append((byte) 0);
 327                         Comm.Append((byte) 0);
 328
 329                         // Authentication Stuff
 330                         Comm.Append ((short) curPos);
 331                         if (connectionParameters.DomainLogin) {
 332                                 Comm.Append ((short) authLen);
 333                                 curPos += (short) authLen;
 334                         } else
 335                                 Comm.Append ((short) 0);
 336
 337                         // Unknown
 338                         Comm.Append (curPos);
 339                         Comm.Append ((short)( connectionParameters.AttachDBFileName.Length));
 340                         curPos += (short)(connectionParameters.AttachDBFileName.Length*2);
 341
 342                         // Connection Parameters
 343                         Comm.Append (connectionParameters.Hostname);
 344                         if (!connectionParameters.DomainLogin) {
 345                                 // SQL Server Authentication
 346                                 Comm.Append (connectionParameters.User);
 347                                 string scrambledPwd = EncryptPassword (connectionParameters.Password);
 348                                 Comm.Append (scrambledPwd);
 349                         }
 350                         Comm.Append (connectionParameters.ApplicationName);
 351                         Comm.Append (DataSource);
 352                         Comm.Append (connectionParameters.LibraryName);
 353                         Comm.Append (Language);
 354                         Comm.Append (connectionParameters.Database);
 355
 356                         if (connectionParameters.DomainLogin) {
 357                                 // the rest of the packet is NTLMSSP authentication
 358                                 Type1Message msg = new Type1Message ();
 359                                 msg.Domain = domain;
 360                                 msg.Host = connectionParameters.Hostname;
 361                                 msg.Flags = NtlmFlags.NegotiateUnicode |
 362                                         NtlmFlags.NegotiateNtlm |
 363                                         NtlmFlags.NegotiateDomainSupplied |
 364                                         NtlmFlags.NegotiateWorkstationSupplied |
 365                                         NtlmFlags.NegotiateAlwaysSign; // 0xb201
 366                                 Comm.Append (msg.GetBytes ());
 367                         }
 368
 369                         Comm.Append (connectionParameters.AttachDBFileName);
 370                         Comm.SendPacket ();
 371                         MoreResults = true;
 372                         SkipToEnd ();
 373
 374                         return IsConnected;
 375                 }
 376
 377                 private static string EncryptPassword (string pass)
 378                 {
 379                         int xormask = 0x5a5a;
 380                         int len = pass.Length;
 381                         char[] chars = new char[len];
 382
 383                         for (int i = 0; i < len; ++i) {
 384                                 int c = ((int) (pass[i])) ^ xormask;
 385                                 int m1 = (c >> 4) & 0x0f0f;
 386                                 int m2 = (c << 4) & 0xf0f0;
 387                                 chars[i] = (char) (m1 | m2);
 388                         }
 389
 390                         return new String (chars);
 391                 }
 392
 393                 public override bool Reset ()
 394                 {
 395                         // Check validity of the connection - a false removes
 396                         // the connection from the pool
 397                         // NOTE: MS implementation will throw a connection-reset error as it will
 398                         // try to use the same connection
 399                         if (!Comm.IsConnected ())
 400                                 return false;
 401
 402                         // Set "reset-connection" bit for the next message packet
 403                         Comm.ResetConnection = true;
 404                         base.Reset ();
 405                         return true;
 406                 }
 407
 408                 public override void ExecPrepared (string commandText, TdsMetaParameterCollection parameters, int timeout, bool wantResults)
 409                 {
 410                         Parameters = parameters;
 411                         ExecuteQuery (BuildPreparedQuery (commandText), timeout, wantResults);
 412                 }
 413
 414                 public override void ExecProc (string commandText, TdsMetaParameterCollection parameters, int timeout, bool wantResults)
 415                 {
 416                         Parameters = parameters;
 417                         ExecRPC (commandText, parameters, timeout, wantResults);
 418                 }
 419
 420                 private void WriteRpcParameterInfo (TdsMetaParameterCollection parameters)
 421                 {
 422                         if (parameters != null) {
 423                                 foreach (TdsMetaParameter param in parameters) {
 424                                         if (param.Direction == TdsParameterDirection.ReturnValue)
 425                                                 continue;
 426                                         string pname = param.ParameterName;
 427                                         if (pname != null && pname.Length > 0 && pname [0] == '@') {
 428                                                 Comm.Append ( (byte) pname.Length);
 429                                                 Comm.Append (pname);
 430                                         } else {
 431                                                 Comm.Append ( (byte) (pname.Length + 1));
 432                                                 Comm.Append ("@" + pname);
 433                                         }
 434                                         short status = 0; // unused
 435                                         if (param.Direction != TdsParameterDirection.Input)
 436                                                 status |= 0x01; // output
 437                                         Comm.Append ( (byte) status);
 438                                         WriteParameterInfo (param);
 439                                 }
 440                         }
 441                 }
 442
 443                 private void WritePreparedParameterInfo (TdsMetaParameterCollection parameters)
 444                 {
 445                         if (parameters == null)
 446                                 return;
 447
 448                         string param = BuildPreparedParameters ();
 449                         Comm.Append ((byte) 0x00); // no param meta data name
 450                         Comm.Append ((byte) 0x00); // no status flags
 451
 452                         // Type_info - parameter info
 453                         WriteParameterInfo (new TdsMetaParameter ("prep_params",
 454                                                                   param.Length > 4000 ? "ntext" : "nvarchar",
 455                                                                   param));
 456                 }
 457
 458                 protected void ExecRPC (TdsRpcProcId rpcId, string sql,
 459                                         TdsMetaParameterCollection parameters,
 460                                         int timeout, bool wantResults)
 461                 {
 462                         // clean up
 463                         InitExec ();
 464                         Comm.StartPacket (TdsPacketType.RPC);
 465
 466                         Comm.Append ((ushort) 0xFFFF);
 467                         Comm.Append ((ushort) rpcId);
 468                         Comm.Append ((short) 0x02); // no meta data
 469
 470                         Comm.Append ((byte) 0x00); // no param meta data name
 471                         Comm.Append ((byte) 0x00); // no status flags
 472
 473                         // Write sql as a parameter value - UCS2
 474                         TdsMetaParameter param = new TdsMetaParameter ("sql",
 475                                                                        sql.Length > 4000 ? "ntext":"nvarchar",
 476                                                                        sql);
 477                         WriteParameterInfo (param);
 478
 479                         // Write Parameter infos - name and type
 480                         WritePreparedParameterInfo (parameters);
 481
 482                         // Write parameter/value info
 483                         WriteRpcParameterInfo (parameters);
 484                         Comm.SendPacket ();
 485                         CheckForData (timeout);
 486                         if (!wantResults)
 487                                 SkipToEnd ();
 488                 }
 489
 490                 protected override void ExecRPC (string rpcName, TdsMetaParameterCollection parameters,
 491                                                  int timeout, bool wantResults)
 492                 {
 493                         // clean up
 494                         InitExec ();
 495                         Comm.StartPacket (TdsPacketType.RPC);
 496
 497                         Comm.Append ( (short) rpcName.Length);
 498                         Comm.Append (rpcName);
 499                         Comm.Append ( (short) 0); //no meta data
 500                         WriteRpcParameterInfo (parameters);
 501                         Comm.SendPacket ();
 502                         CheckForData (timeout);
 503                         if (!wantResults)
 504                                 SkipToEnd ();
 505                 }
 506
 507                 private void WriteParameterInfo (TdsMetaParameter param)
 508                 {
 509                         /*
 510                         Ms.net send non-nullable datatypes as nullable and allows setting null values
 511                         to int/float etc.. So, using Nullable form of type for all data
 512                         */
 513                         param.IsNullable = true;
 514                         TdsColumnType colType = param.GetMetaType ();
 515                         param.IsNullable = false;
 516
 517                         bool partLenType = false;
 518                         int size = param.Size;
 519                         if (size < 1) {
 520                                 if (size < 0)
 521                                         partLenType = true;
 522                                 size = param.GetActualSize ();
 523                         }
 524
 525                         /*
 526                          * If the value is null, not setting the size to 0 will cause varchar
 527                          * fields to get inserted as an empty string rather than an null.
 528                          */
 529                         if (param.Value == null || param.Value == DBNull.Value)
 530                                 size = 0;
 531
 532                         // Change colType according to the following table
 533                         /*
 534                          * Original Type        Maxlen          New Type
 535                          *
 536                          * NVarChar             4000 UCS2       NText
 537                          * BigVarChar           8000 ASCII      Text
 538                          * BigVarBinary         8000 bytes      Image
 539                          *
 540                          */
 541                         TdsColumnType origColType = colType;
 542                         if (colType == TdsColumnType.BigNVarChar) {
 543                                 // param.GetActualSize() returns len*2
 544                                 if (size == param.Size)
 545                                         size <<= 1;
 546                                 if ((size >> 1) > 4000)
 547                                         colType = TdsColumnType.NText;
 548                         } else if (colType == TdsColumnType.BigVarChar) {
 549                                 if (size > 8000)
 550                                         colType = TdsColumnType.Text;
 551                         } else if (colType == TdsColumnType.BigVarBinary) {
 552                                 if (size > 8000)
 553                                         colType = TdsColumnType.Image;
 554                         }
 555                         // Calculation of TypeInfo field
 556                         /*
 557                          * orig size value              TypeInfo field
 558                          *
 559                          * >= 0 <= Maxlen               origColType + content len
 560                          * > Maxlen             NewType as per above table + content len
 561                          * -1           origColType + USHORTMAXLEN (0xFFFF) + content len (TDS 9)
 562                          *
 563                          */
 564                         // Write updated colType, iff partLenType == false
 565                         if (TdsVersion > TdsVersion.tds81 && partLenType) {
 566                                 Comm.Append ((byte)origColType);
 567                                 Comm.Append ((short)-1);
 568                         } else if (ServerTdsVersion > TdsVersion.tds70
 569                                    && origColType == TdsColumnType.Decimal) {
 570                                 Comm.Append ((byte)TdsColumnType.Numeric);
 571                         } else {
 572                                 Comm.Append ((byte)colType);
 573                         }
 574
 575                         if (IsLargeType (colType))
 576                                 Comm.Append ((short)size); // Parameter size passed in SqlParameter
 577                         else if (IsBlobType (colType))
 578                                 Comm.Append (size); // Parameter size passed in SqlParameter
 579                         else
 580                                 Comm.Append ((byte)size);
 581
 582                         // Precision and Scale are non-zero for only decimal/numeric
 583                         if ( param.TypeName == "decimal" || param.TypeName == "numeric") {
 584                                 Comm.Append ((param.Precision !=0 ) ? param.Precision : Precision);
 585                                 Comm.Append (param.Scale);
 586                                 // Convert the decimal value according to Scale
 587                                 if (param.Value != null && param.Value != DBNull.Value &&
 588                                     ((decimal)param.Value) != Decimal.MaxValue &&
 589                                     ((decimal)param.Value) != Decimal.MinValue &&
 590                                     ((decimal)param.Value) != long.MaxValue &&
 591                                     ((decimal)param.Value) != long.MinValue &&
 592                                     ((decimal)param.Value) != ulong.MaxValue &&
 593                                     ((decimal)param.Value) != ulong.MinValue) {
 594                                         long expo = (long)new Decimal (System.Math.Pow (10, (double)param.Scale));
 595                                         long pVal = (long)(((decimal)param.Value) * expo);
 596                                         param.Value = pVal;
 597                                 }
 598                         }
 599
 600
 601                         /* VARADHAN: TDS 8 Debugging */
 602                         /*
 603                         if (Collation != null) {
 604                                 Console.WriteLine ("Collation is not null");
 605                                 Console.WriteLine ("Column Type: {0}", colType);
 606                                 Console.WriteLine ("Collation bytes: {0} {1} {2} {3} {4}", Collation[0], Collation[1], Collation[2],
 607                                                    Collation[3], Collation[4]);
 608                         } else {
 609                                 Console.WriteLine ("Collation is null");
 610                         }
 611                         */
 612
 613                         // Tds > 7.0 uses collation
 614                         if (Collation != null &&
 615                             (colType == TdsColumnType.BigChar || colType == TdsColumnType.BigNVarChar ||
 616                              colType == TdsColumnType.BigVarChar || colType == TdsColumnType.NChar ||
 617                              colType == TdsColumnType.NVarChar || colType == TdsColumnType.Text ||
 618                              colType == TdsColumnType.NText))
 619                                 Comm.Append (Collation);
 620
 621                         // LAMESPEC: size should be 0xFFFF for any bigvarchar, bignvarchar and bigvarbinary
 622                         // types if param value is NULL
 623                         if ((colType == TdsColumnType.BigVarChar ||
 624                              colType == TdsColumnType.BigNVarChar ||
 625                              colType == TdsColumnType.BigVarBinary ||
 626                              colType == TdsColumnType.Image) &&
 627                             (param.Value == null || param.Value == DBNull.Value))
 628                                 size = -1;
 629                         else
 630                                 size = param.GetActualSize ();
 631
 632                         if (IsLargeType (colType))
 633                                 Comm.Append ((short)size);
 634                         else if (IsBlobType (colType))
 635                                 Comm.Append (size);
 636                         else
 637                                 Comm.Append ((byte)size);
 638
 639                         if (size > 0) {
 640                                 switch (param.TypeName) {
 641                                 case "money" : {
 642                                         // 4 == SqlMoney::MoneyFormat.NumberDecimalDigits
 643                                         Decimal val = Decimal.Round ((decimal) param.Value, 4);
 644                                         int[] arr = Decimal.GetBits (val);
 645
 646                                         if (val >= 0) {
 647                                                 Comm.Append (arr[1]);
 648                                                 Comm.Append (arr[0]);
 649                                         } else {
 650                                                 Comm.Append (~arr[1]);
 651                                                 Comm.Append (~arr[0] + 1);
 652                                         }
 653                                         break;
 654                                 }
 655                                 case "smallmoney": {
 656                                         // 4 == SqlMoney::MoneyFormat.NumberDecimalDigits
 657                                         Decimal val = Decimal.Round ((decimal) param.Value, 4);
 658                                         if (val < SMALLMONEY_MIN || val > SMALLMONEY_MAX)
 659                                                 throw new OverflowException (string.Format (
 660                                                         CultureInfo.InvariantCulture,
 661                                                         "Value '{0}' is not valid for SmallMoney."
 662                                                         + "  Must be between {1:N4} and {2:N4}.",
 663 #if NET_2_0
 664                                                         val,
 665 #else
 666                                                         val.ToString (CultureInfo.CurrentCulture),
 667 #endif
 668                                                         SMALLMONEY_MIN, SMALLMONEY_MAX));
 669
 670                                         int[] arr = Decimal.GetBits (val);
 671                                         int sign = (val>0 ? 1: -1);
 672                                         Comm.Append (sign * arr[0]);
 673                                         break;
 674                                 }
 675                                 case "datetime":
 676                                         Comm.Append ((DateTime)param.Value, 8);
 677                                         break;
 678                                 case "smalldatetime":
 679                                         Comm.Append ((DateTime)param.Value, 4);
 680                                         break;
 681                                 case "varchar" :
 682                                 case "nvarchar" :
 683                                 case "char" :
 684                                 case "nchar" :
 685                                 case "text" :
 686                                 case "ntext" :
 687                                         byte [] tmp = param.GetBytes ();
 688                                         Comm.Append (tmp);
 689                                         break;
 690                                 case "uniqueidentifier" :
 691                                         Comm.Append (((Guid)param.Value).ToByteArray());
 692                                         break;
 693                                 default :
 694                                         Comm.Append (param.Value);
 695                                         break;
 696                                 }
 697                         }
 698                         return;
 699                 }
 700
 701                 public override void Execute (string commandText, TdsMetaParameterCollection parameters, int timeout, bool wantResults)
 702                 {
 703                         Parameters = parameters;
 704                         string sql = commandText;
 705                         if (wantResults || (Parameters != null && Parameters.Count > 0))
 706                                 sql = BuildExec (commandText);
 707                         ExecuteQuery (sql, timeout, wantResults);
 708                 }
 709
 710                 private string FormatParameter (TdsMetaParameter parameter)
 711                 {
 712                         string parameterName = parameter.ParameterName;
 713                         if (parameterName [0] == '@') {
 714                                 parameterName = parameterName.Substring (1);
 715                         }
 716                         if (parameter.Direction == TdsParameterDirection.Output)
 717                                 return String.Format ("@{0}=@{0} output", parameterName);
 718                         if (parameter.Value == null || parameter.Value == DBNull.Value)
 719                                 return String.Format ("@{0}=NULL", parameterName);
 720
 721                         string value = null;
 722                         switch (parameter.TypeName) {
 723                         case "smalldatetime":
 724                         case "datetime":
 725                                 DateTime d = Convert.ToDateTime (parameter.Value);
 726                                 value = String.Format (base.Locale,
 727                                         "'{0:MMM dd yyyy hh:mm:ss.fff tt}'", d);
 728                                 break;
 729                         case "bigint":
 730                         case "decimal":
 731                         case "float":
 732                         case "int":
 733                         case "money":
 734                         case "real":
 735                         case "smallint":
 736                         case "smallmoney":
 737                         case "tinyint":
 738                                 object paramValue = parameter.Value;
 739                                 Type paramType = paramValue.GetType ();
 740                                 if (paramType.IsEnum)
 741                                         paramValue = Convert.ChangeType (paramValue,
 742                                                 Type.GetTypeCode (paramType));
 743                                 value = paramValue.ToString ();
 744                                 break;
 745                         case "nvarchar":
 746                         case "nchar":
 747                                 value = String.Format ("N'{0}'", parameter.Value.ToString ().Replace ("'", "''"));
 748                                 break;
 749                         case "uniqueidentifier":
 750                                 value = String.Format ("'{0}'", ((Guid) parameter.Value).ToString (string.Empty));
 751                                 break;
 752                         case "bit":
 753                                 if (parameter.Value.GetType () == typeof (bool))
 754                                         value = (((bool) parameter.Value) ? "0x1" : "0x0");
 755                                 else
 756                                         value = parameter.Value.ToString ();
 757                                 break;
 758                         case "image":
 759                         case "binary":
 760                         case "varbinary":
 761                                 byte[] byteArray = (byte[]) parameter.Value;
 762                                 // In 1.0 profile, BitConverter.ToString() throws ArgumentOutOfRangeException when passed a 0-length
 763                                 // array, so handle that as a special case.
 764                                 if (byteArray.Length == 0)
 765                                         value = "0x";
 766                                 else
 767                                         value = String.Format ("0x{0}", BitConverter.ToString (byteArray).Replace ("-", string.Empty).ToLower ());
 768                                 break;
 769                         default:
 770                                 value = String.Format ("'{0}'", parameter.Value.ToString ().Replace ("'", "''"));
 771                                 break;
 772                         }
 773
 774                         return "@" + parameterName + "=" + value;
 775                 }
 776
 777                 public override string Prepare (string commandText, TdsMetaParameterCollection parameters)
 778                 {
 779                         Parameters = parameters;
 780
 781                         TdsMetaParameterCollection parms = new TdsMetaParameterCollection ();
 782                         TdsMetaParameter parm = new TdsMetaParameter ("@Handle", "int", null);
 783                         parm.Direction = TdsParameterDirection.Output;
 784                         parms.Add (parm);
 785
 786                         parms.Add (new TdsMetaParameter ("@VarDecl", "nvarchar", BuildPreparedParameters ()));
 787                         parms.Add (new TdsMetaParameter ("@Query", "nvarchar", commandText));
 788
 789                         ExecProc ("sp_prepare", parms, 0, true);
 790                         SkipToEnd ();
 791                         return OutputParameters[0].ToString () ;
 792                         //if (ColumnValues == null || ColumnValues [0] == null || ColumnValues [0] == DBNull.Value)
 793                         //      throw new TdsInternalException ();
 794                         //return string.Empty;
 795                         //return ColumnValues [0].ToString ();
 796                 }
 797
 798                 protected override void ProcessColumnInfo ()
 799                 {
 800                         int numColumns = Comm.GetTdsShort ();
 801                         for (int i = 0; i < numColumns; i += 1) {
 802                                 byte[] flagData = new byte[4];
 803                                 for (int j = 0; j < 4; j += 1)
 804                                         flagData[j] = Comm.GetByte ();
 805
 806                                 bool nullable = (flagData[2] & 0x01) > 0;
 807                                 //bool caseSensitive = (flagData[2] & 0x02) > 0;
 808                                 bool writable = (flagData[2] & 0x0c) > 0;
 809                                 bool autoIncrement = (flagData[2] & 0x10) > 0;
 810                                 bool isIdentity = (flagData[2] & 0x10) > 0;
 811
 812                                 TdsColumnType columnType = (TdsColumnType) ((Comm.GetByte () & 0xff));
 813
 814                                 byte xColumnType = 0;
 815                                 if (IsLargeType (columnType)) {
 816                                         xColumnType = (byte) columnType;
 817                                         if (columnType != TdsColumnType.NChar)
 818                                                 columnType -= 128;
 819                                 }
 820
 821                                 int columnSize;
 822                                 string tableName = null;
 823
 824                                 if (IsBlobType (columnType)) {
 825                                         columnSize = Comm.GetTdsInt ();
 826                                         tableName = Comm.GetString (Comm.GetTdsShort ());
 827                                 } else if (IsFixedSizeColumn (columnType)) {
 828                                         columnSize = LookupBufferSize (columnType);
 829                                 } else if (IsLargeType ((TdsColumnType) xColumnType)) {
 830                                         columnSize = Comm.GetTdsShort ();
 831                                 } else {
 832                                         columnSize = Comm.GetByte () & 0xff;
 833                                 }
 834
 835                                 if (IsWideType ((TdsColumnType) columnType))
 836                                         columnSize /= 2;
 837
 838                                 byte precision = 0;
 839                                 byte scale = 0;
 840
 841                                 if (columnType == TdsColumnType.Decimal || columnType == TdsColumnType.Numeric) {
 842                                         precision = Comm.GetByte ();
 843                                         scale = Comm.GetByte ();
 844                                 } else {
 845                                         precision = GetPrecision (columnType, columnSize);
 846                                         scale = GetScale (columnType, columnSize);
 847                                 }
 848
 849                                 string columnName = Comm.GetString (Comm.GetByte ());
 850
 851                                 TdsDataColumn col = new TdsDataColumn ();
 852                                 Columns.Add (col);
 853 #if NET_2_0
 854                                 col.ColumnType = columnType;
 855                                 col.ColumnName = columnName;
 856                                 col.IsAutoIncrement = autoIncrement;
 857                                 col.IsIdentity = isIdentity;
 858                                 col.ColumnSize = columnSize;
 859                                 col.NumericPrecision = precision;
 860                                 col.NumericScale = scale;
 861                                 col.IsReadOnly = !writable;
 862                                 col.AllowDBNull = nullable;
 863                                 col.BaseTableName = tableName;
 864                                 col.DataTypeName = Enum.GetName (typeof (TdsColumnType), xColumnType);
 865 #else
 866                                 col ["ColumnType"] = columnType;
 867                                 col ["ColumnName"] = columnName;
 868                                 col ["IsAutoIncrement"] = autoIncrement;
 869                                 col ["IsIdentity"] = isIdentity;
 870                                 col ["ColumnSize"] = columnSize;
 871                                 col ["NumericPrecision"] = precision;
 872                                 col ["NumericScale"] = scale;
 873                                 col ["IsReadOnly"] = !writable;
 874                                 col ["AllowDBNull"] = nullable;
 875                                 col ["BaseTableName"] = tableName;
 876                                 col ["DataTypeName"] = Enum.GetName (typeof (TdsColumnType), xColumnType);
 877 #endif
 878                         }
 879                 }
 880
 881                 public override void Unprepare (string statementId)
 882                 {
 883                         TdsMetaParameterCollection parms = new TdsMetaParameterCollection ();
 884                         parms.Add (new TdsMetaParameter ("@P1", "int", Int32.Parse (statementId)));
 885                         ExecProc ("sp_unprepare", parms, 0, false);
 886                 }
 887
 888                 protected override bool IsValidRowCount (byte status, byte op)
 889                 {
 890                         if ((status & (byte)0x10) == 0 || op == (byte)0xc1)
 891                                 return false;
 892                         return true;
 893                 }
 894
 895                 protected override void ProcessReturnStatus ()
 896                 {
 897                         int result = Comm.GetTdsInt ();
 898                         if (Parameters != null) {
 899                                 foreach (TdsMetaParameter param in Parameters) {
 900                                         if (param.Direction == TdsParameterDirection.ReturnValue) {
 901                                                 param.Value = result;
 902                                                 break;
 903                                         }
 904                                 }
 905                         }
 906                 }
 907
 908                 byte GetScale (TdsColumnType type, int columnSize)
 909                 {
 910                         switch (type) {
 911                         case TdsColumnType.DateTime:
 912                                 return 0x03;
 913                         case TdsColumnType.DateTime4:
 914                                 return 0x00;
 915                         case TdsColumnType.DateTimeN:
 916                                 switch (columnSize) {
 917                                 case 4:
 918                                         return 0x00;
 919                                 case 8:
 920                                         return 0x03;
 921                                 }
 922                                 break;
 923                         default:
 924                                 return 0xff;
 925                         }
 926
 927                         throw new NotSupportedException (string.Format (
 928                                 CultureInfo.InvariantCulture,
 929                                 "Fixed scale not defined for column " +
 930                                 "type '{0}' with size {1}.", type, columnSize));
 931                 }
 932
 933                 byte GetPrecision (TdsColumnType type, int columnSize)
 934                 {
 935                         switch (type) {
 936                         case TdsColumnType.Binary:
 937                                 return 0xff;
 938                         case TdsColumnType.Bit:
 939                                 return 0xff;
 940                         case TdsColumnType.Char:
 941                                 return 0xff;
 942                         case TdsColumnType.DateTime:
 943                                 return 0x17;
 944                         case TdsColumnType.DateTime4:
 945                                 return 0x10;
 946                         case TdsColumnType.DateTimeN:
 947                                 switch (columnSize) {
 948                                 case 4:
 949                                         return 0x10;
 950                                 case 8:
 951                                         return 0x17;
 952                                 }
 953                                 break;
 954                         case TdsColumnType.Real:
 955                                 return 0x07;
 956                         case TdsColumnType.Float8:
 957                                 return 0x0f;
 958                         case TdsColumnType.FloatN:
 959                                 switch (columnSize) {
 960                                 case 4:
 961                                         return 0x07;
 962                                 case 8:
 963                                         return 0x0f;
 964                                 }
 965                                 break;
 966                         case TdsColumnType.Image:
 967                                 return 0xff;
 968                         case TdsColumnType.Int1:
 969                                 return 0x03;
 970                         case TdsColumnType.Int2:
 971                                 return 0x05;
 972                         case TdsColumnType.Int4:
 973                                 return 0x0a;
 974                         case TdsColumnType.IntN:
 975                                 switch (columnSize) {
 976                                 case 1:
 977                                         return 0x03;
 978                                 case 2:
 979                                         return 0x05;
 980                                 case 4:
 981                                         return 0x0a;
 982                                 }
 983                                 break;
 984                         case TdsColumnType.Void:
 985                                 return 0x01;
 986                         case TdsColumnType.Text:
 987                                 return 0xff;
 988                         case TdsColumnType.UniqueIdentifier:
 989                                 return 0xff;
 990                         case TdsColumnType.VarBinary:
 991                                 return 0xff;
 992                         case TdsColumnType.VarChar:
 993                                 return 0xff;
 994                         case TdsColumnType.Money:
 995                                 return 19;
 996                         case TdsColumnType.NText:
 997                                 return 0xff;
 998                         case TdsColumnType.NVarChar:
 999                                 return 0xff;
1000                         case TdsColumnType.BitN:
1001                                 return 0xff;
1002                         case TdsColumnType.MoneyN:
1003                                 switch (columnSize) {
1004                                 case 4:
1005                                         return 0x0a;
1006                                 case 8:
1007                                         return 0x13;
1008                                 }
1009                                 break;
1010                         case TdsColumnType.Money4:
1011                                 return 0x0a;
1012                         case TdsColumnType.NChar:
1013                                 return 0xff;
1014                         case TdsColumnType.BigBinary:
1015                                 return 0xff;
1016                         case TdsColumnType.BigVarBinary:
1017                                 return 0xff;
1018                         case TdsColumnType.BigVarChar:
1019                                 return 0xff;
1020                         case TdsColumnType.BigNVarChar:
1021                                 return 0xff;
1022                         case TdsColumnType.BigChar:
1023                                 return 0xff;
1024                         case TdsColumnType.SmallMoney:
1025                                 return 0x0a;
1026                         case TdsColumnType.Variant:
1027                                 return 0xff;
1028                         case TdsColumnType.BigInt:
1029                                 return 0xff;
1030                         }
1031
1032                         throw new NotSupportedException (string.Format (
1033                                 CultureInfo.InvariantCulture,
1034                                 "Fixed precision not defined for column " +
1035                                 "type '{0}' with size {1}.", type, columnSize));
1036                 }
1037
1038                 #endregion // Methods
1039
1040 #if NET_2_0
1041                 #region Asynchronous Methods
1042
1043                 public override IAsyncResult BeginExecuteNonQuery (string cmdText,
1044                                                           TdsMetaParameterCollection parameters,
1045                                                           AsyncCallback callback,
1046                                                           object state)
1047                 {
1048                         Parameters = parameters;
1049                         string sql = cmdText;
1050                         if (Parameters != null && Parameters.Count > 0)
1051                                 sql = BuildExec (cmdText);
1052
1053                         IAsyncResult ar = BeginExecuteQueryInternal (sql, false, callback, state);
1054                         return ar;
1055                 }
1056
1057                 public override void EndExecuteNonQuery (IAsyncResult ar)
1058                 {
1059                         EndExecuteQueryInternal (ar);
1060                 }
1061
1062                 public override IAsyncResult BeginExecuteQuery (string cmdText,
1063                                                                 TdsMetaParameterCollection parameters,
1064                                                                 AsyncCallback callback,
1065                                                                 object state)
1066                 {
1067                         Parameters = parameters;
1068                         string sql = cmdText;
1069                         if (Parameters != null && Parameters.Count > 0)
1070                                 sql = BuildExec (cmdText);
1071
1072                         IAsyncResult ar = BeginExecuteQueryInternal (sql, true, callback, state);
1073                         return ar;
1074                 }
1075
1076                 public override void EndExecuteQuery (IAsyncResult ar)
1077                 {
1078                         EndExecuteQueryInternal (ar);
1079                 }
1080
1081                 public override IAsyncResult BeginExecuteProcedure (string prolog,
1082                                                                     string epilog,
1083                                                                     string cmdText,
1084                                                                     bool IsNonQuery,
1085                                                                     TdsMetaParameterCollection parameters,
1086                                                                     AsyncCallback callback,
1087                                                                     object state)
1088                 {
1089                         Parameters = parameters;
1090                         string pcall = BuildProcedureCall (cmdText);
1091                         string sql = String.Format ("{0};{1};{2};", prolog, pcall, epilog);
1092
1093                         IAsyncResult ar = BeginExecuteQueryInternal (sql, !IsNonQuery, callback, state);
1094                         return ar;
1095                 }
1096
1097                 public override void EndExecuteProcedure (IAsyncResult ar)
1098                 {
1099                         EndExecuteQueryInternal (ar);
1100                 }
1101
1102                 #endregion // Asynchronous Methods
1103 #endif // NET_2_0
1104         }
1105 }