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