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