mcs/class/Mono.Data.SybaseClient/Mono.Data.SybaseClient/SybaseCommand.cs

   1 //
   2 // Mono.Data.SybaseClient.SybaseCommand.cs
   3 //
   4 // Author:
   5 //   Rodrigo Moya (rodrigo@ximian.com)
   6 //   Daniel Morgan (danmorg@sc.rr.com)
   7 //   Tim Coleman (tim@timcoleman.com)
   8 //
   9 // (C) Ximian, Inc 2002 http://www.ximian.com/
  10 // (C) Daniel Morgan, 2002
  11 // Copyright (C) Tim Coleman, 2002
  12 //
  13
  14 using Mono.Data.TdsClient.Internal;
  15 using System;
  16 using System.Collections;
  17 using System.Collections.Specialized;
  18 using System.ComponentModel;
  19 using System.Data;
  20 using System.Data.Common;
  21 using System.Runtime.InteropServices;
  22 using System.Text;
  23 using System.Xml;
  24
  25 namespace Mono.Data.SybaseClient {
  26         public sealed class SybaseCommand : Component, IDbCommand, ICloneable
  27         {
  28                 #region Fields
  29
  30                 bool disposed = false;
  31
  32                 int commandTimeout;
  33                 bool designTimeVisible;
  34                 string commandText;
  35
  36                 CommandType commandType;
  37                 SybaseConnection connection;
  38                 SybaseTransaction transaction;
  39                 UpdateRowSource updatedRowSource;
  40
  41                 CommandBehavior behavior = CommandBehavior.Default;
  42                 NameValueCollection preparedStatements = new NameValueCollection ();
  43                 SybaseParameterCollection parameters;
  44
  45                 #endregion // Fields
  46
  47                 #region Constructors
  48
  49                 public SybaseCommand()
  50                         : this (String.Empty, null, null)
  51                 {
  52                 }
  53
  54                 public SybaseCommand (string commandText)
  55                         : this (commandText, null, null)
  56                 {
  57                         commandText = commandText;
  58                 }
  59
  60                 public SybaseCommand (string commandText, SybaseConnection connection)
  61                         : this (commandText, connection, null)
  62                 {
  63                         Connection = connection;
  64                 }
  65
  66                 public SybaseCommand (string commandText, SybaseConnection connection, SybaseTransaction transaction)
  67                 {
  68                         this.commandText = commandText;
  69                         this.connection = connection;
  70                         this.transaction = transaction;
  71                         this.commandType = CommandType.Text;
  72                         this.updatedRowSource = UpdateRowSource.Both;
  73
  74                         this.designTimeVisible = false;
  75                         this.commandTimeout = 30;
  76                         parameters = new SybaseParameterCollection (this);
  77                 }
  78
  79                 #endregion // Constructors
  80
  81                 #region Properties
  82
  83                 internal CommandBehavior CommandBehavior {
  84                         get { return behavior; }
  85                 }
  86
  87                 public string CommandText {
  88                         get { return commandText; }
  89                         set { commandText = value; }
  90                 }
  91
  92                 public int CommandTimeout {
  93                         get { return commandTimeout;  }
  94                         set {
  95                                 if (commandTimeout < 0)
  96                                         throw new ArgumentException ("The property value assigned is less than 0.");
  97                                 commandTimeout = value;
  98                         }
  99                 }
 100
 101                 public CommandType CommandType  {
 102                         get { return commandType; }
 103                         set {
 104                                 if (value == CommandType.TableDirect)
 105                                         throw new ArgumentException ("CommandType.TableDirect is not supported by the Mono SybaseClient Data Provider.");
 106                                 commandType = value;
 107                         }
 108                 }
 109
 110                 public SybaseConnection Connection {
 111                         get { return connection; }
 112                         set {
 113                                 if (transaction != null && connection.Transaction != null && connection.Transaction.IsOpen)
 114                                         throw new InvalidOperationException ("The Connection property was changed while a transaction was in progress.");
 115                                 transaction = null;
 116                                 connection = value;
 117                         }
 118                 }
 119
 120                 public bool DesignTimeVisible {
 121                         get { return designTimeVisible; }
 122                         set { designTimeVisible = value; }
 123                 }
 124
 125                 public SybaseParameterCollection Parameters {
 126                         get { return parameters; }
 127                 }
 128
 129                 internal ITds Tds {
 130                         get { return Connection.Tds; }
 131                 }
 132
 133                 IDbConnection IDbCommand.Connection {
 134                         get { return Connection; }
 135                         set {
 136                                 if (!(value is SybaseConnection))
 137                                         throw new InvalidCastException ("The value was not a valid SybaseConnection.");
 138                                 Connection = (SybaseConnection) value;
 139                         }
 140                 }
 141
 142                 IDataParameterCollection IDbCommand.Parameters  {
 143                         get { return Parameters; }
 144                 }
 145
 146                 IDbTransaction IDbCommand.Transaction {
 147                         get { return Transaction; }
 148                         set {
 149                                 if (!(value is SybaseTransaction))
 150                                         throw new ArgumentException ();
 151                                 Transaction = (SybaseTransaction) value;
 152                         }
 153                 }
 154
 155                 public SybaseTransaction Transaction {
 156                         get { return transaction; }
 157                         set { transaction = value; }
 158                 }
 159
 160                 public UpdateRowSource UpdatedRowSource {
 161                         get { return updatedRowSource; }
 162                         set { updatedRowSource = value; }
 163                 }
 164
 165                 #endregion // Fields
 166
 167                 #region Methods
 168
 169                 private string BuildCommand ()
 170                 {
 171                         string statementHandle = preparedStatements [commandText];
 172                         if (statementHandle != null) {
 173                                 string proc = String.Format ("sp_execute {0}", statementHandle);
 174                                 if (parameters.Count > 0)
 175                                         proc += ",";
 176                                 return BuildProcedureCall (proc, parameters);
 177                         }
 178
 179                         if (commandType == CommandType.StoredProcedure)
 180                                 return BuildProcedureCall (commandText, parameters);
 181
 182                         string sql = String.Empty;
 183                         if ((behavior & CommandBehavior.KeyInfo) > 0)
 184                                 sql += "SET FMTONLY OFF; SET NO_BROWSETABLE ON;";
 185                         if ((behavior & CommandBehavior.SchemaOnly) > 0)
 186                                 sql += "SET FMTONLY ON;";
 187
 188                         switch (commandType) {
 189                         case CommandType.Text :
 190                                 sql += commandText;
 191                                 break;
 192                         default:
 193                                 throw new InvalidOperationException ("The CommandType was invalid.");
 194                         }
 195                         return BuildExec (sql);
 196                 }
 197
 198                 [MonoTODO ("This throws a SybaseException.")]
 199                 private string BuildExec (string sql)
 200                 {
 201                         StringBuilder declare = new StringBuilder ();
 202                         StringBuilder assign = new StringBuilder ();
 203
 204                         sql = sql.Replace ("'", "''");
 205                         foreach (SybaseParameter parameter in parameters) {
 206                                 declare.Append ("declare ");
 207                                 declare.Append (parameter.Prepare (parameter.ParameterName));
 208                                 if (parameter.Direction == ParameterDirection.Output)
 209                                         declare.Append (" output");
 210                                 declare.Append ('\n');
 211                                 assign.Append (String.Format ("select {0}={1}\n", parameter.ParameterName, FormatParameter (parameter)));
 212                                 sql = sql.Replace (parameter.ParameterName, String.Format ("' + {0} + '", parameter.ParameterName));
 213                         }
 214
 215                         return String.Format ("{0}{1}execute ({2})", declare.ToString (), assign.ToString (), sql);
 216                 }
 217
 218                 private string BuildPrepare ()
 219                 {
 220                         StringBuilder parms = new StringBuilder ();
 221                         foreach (SybaseParameter parameter in parameters) {
 222                                 if (parms.Length > 0)
 223                                         parms.Append (", ");
 224                                 parms.Append (parameter.Prepare (parameter.ParameterName));
 225                                 if (parameter.Direction == ParameterDirection.Output)
 226                                         parms.Append (" output");
 227                         }
 228
 229                         SybaseParameterCollection localParameters = new SybaseParameterCollection (this);
 230                         SybaseParameter parm;
 231
 232                         parm = new SybaseParameter ("@P1", SybaseType.Int);
 233                         parm.Direction = ParameterDirection.Output;
 234                         localParameters.Add (parm);
 235
 236                         parm = new SybaseParameter ("@P2", SybaseType.NVarChar);
 237                         parm.Value = parms.ToString ();
 238                         parm.Size = ((string) parm.Value).Length;
 239                         localParameters.Add (parm);
 240
 241                         parm = new SybaseParameter ("@P3", SybaseType.NVarChar);
 242                         parm.Value = commandText;
 243                         parm.Size = ((string) parm.Value).Length;
 244                         localParameters.Add (parm);
 245
 246                         return BuildProcedureCall ("sp_prepare", localParameters);
 247                 }
 248
 249                 private static string BuildProcedureCall (string procedure, SybaseParameterCollection parameters)
 250                 {
 251                         StringBuilder parms = new StringBuilder ();
 252                         StringBuilder declarations = new StringBuilder ();
 253                         StringBuilder outParms = new StringBuilder ();
 254                         StringBuilder set = new StringBuilder ();
 255
 256                         int index = 1;
 257                         foreach (SybaseParameter parameter in parameters) {
 258                                 string parmName = String.Format ("@P{0}", index);
 259
 260                                 switch (parameter.Direction) {
 261                                 case ParameterDirection.Input :
 262                                         if (parms.Length > 0)
 263                                                 parms.Append (", ");
 264                                         parms.Append (FormatParameter (parameter));
 265                                         break;
 266                                 case ParameterDirection.Output :
 267                                         if (parms.Length > 0)
 268                                                 parms.Append (", ");
 269                                         parms.Append (parmName);
 270                                         parms.Append (" output");
 271
 272                                         if (outParms.Length > 0) {
 273                                                 outParms.Append (", ");
 274                                                 declarations.Append (", ");
 275                                         }
 276                                         else {
 277                                                 outParms.Append ("select ");
 278                                                 declarations.Append ("declare ");
 279                                         }
 280
 281                                         declarations.Append (parameter.Prepare (parmName));
 282                                         set.Append (String.Format ("set {0}=NULL\n", parmName));
 283                                         outParms.Append (parmName);
 284                                         break;
 285                                 default :
 286                                         throw new NotImplementedException ("Only support input and output parameters.");
 287                                 }
 288                                 index += 1;
 289                         }
 290                         if (declarations.Length > 0)
 291                                 declarations.Append ('\n');
 292
 293                         return String.Format ("{0}{1}{2} {3}\n{4}", declarations.ToString (), set.ToString (), procedure, parms.ToString (), outParms.ToString ());
 294                 }
 295
 296                 public void Cancel ()
 297                 {
 298                         if (Connection == null || Connection.Tds == null)
 299                                 return;
 300                         Connection.Tds.Cancel ();
 301                 }
 302
 303                 internal void CloseDataReader (bool moreResults)
 304                 {
 305                         GetOutputParameters ();
 306                         Connection.DataReader = null;
 307
 308                         if ((behavior & CommandBehavior.CloseConnection) != 0)
 309                                 Connection.Close ();
 310                 }
 311
 312                 public SybaseParameter CreateParameter ()
 313                 {
 314                         return new SybaseParameter ();
 315                 }
 316
 317                 internal void DeriveParameters ()
 318                 {
 319                         if (commandType != CommandType.StoredProcedure)
 320                                 throw new InvalidOperationException (String.Format ("SybaseCommand DeriveParameters only supports CommandType.StoredProcedure, not CommandType.{0}", commandType));
 321                         ValidateCommand ("DeriveParameters");
 322
 323                         SybaseParameterCollection localParameters = new SybaseParameterCollection (this);
 324                         localParameters.Add ("@P1", SybaseType.NVarChar, commandText.Length).Value = commandText;
 325
 326                         Connection.Tds.ExecuteQuery (BuildProcedureCall ("sp_procedure_params_rowset", localParameters));
 327                         SybaseDataReader reader = new SybaseDataReader (this);
 328                         parameters.Clear ();
 329                         object[] dbValues = new object[reader.FieldCount];
 330
 331                         while (reader.Read ()) {
 332                                 reader.GetValues (dbValues);
 333                                 parameters.Add (new SybaseParameter (dbValues));
 334                         }
 335                         reader.Close ();
 336                 }
 337
 338                 public int ExecuteNonQuery ()
 339                 {
 340                         ValidateCommand ("ExecuteNonQuery");
 341                         string sql = String.Empty;
 342                         int result = 0;
 343
 344                         if (Parameters.Count > 0)
 345                                 sql = BuildCommand ();
 346                         else
 347                                 sql = CommandText;
 348
 349                         try {
 350                                 result = Connection.Tds.ExecuteNonQuery (sql, CommandTimeout);
 351                         }
 352                         catch (TdsTimeoutException e) {
 353                                 throw SybaseException.FromTdsInternalException ((TdsInternalException) e);
 354                         }
 355
 356                         GetOutputParameters ();
 357                         return result;
 358                 }
 359
 360                 public SybaseDataReader ExecuteReader ()
 361                 {
 362                         return ExecuteReader (CommandBehavior.Default);
 363                 }
 364
 365                 public SybaseDataReader ExecuteReader (CommandBehavior behavior)
 366                 {
 367                         ValidateCommand ("ExecuteReader");
 368                         this.behavior = behavior;
 369
 370                         try {
 371                                 Connection.Tds.ExecuteQuery (BuildCommand (), CommandTimeout);
 372                         }
 373                         catch (TdsTimeoutException e) {
 374                                 throw SybaseException.FromTdsInternalException ((TdsInternalException) e);
 375                         }
 376
 377                         Connection.DataReader = new SybaseDataReader (this);
 378                         return Connection.DataReader;
 379                 }
 380
 381                 public object ExecuteScalar ()
 382                 {
 383                         ValidateCommand ("ExecuteScalar");
 384                         try {
 385                                 Connection.Tds.ExecuteQuery (BuildCommand (), CommandTimeout);
 386                         }
 387                         catch (TdsTimeoutException e) {
 388                                 throw SybaseException.FromTdsInternalException ((TdsInternalException) e);
 389                         }
 390
 391                         if (!Connection.Tds.NextResult () || !Connection.Tds.NextRow ())
 392                                 return null;
 393
 394                         object result = Connection.Tds.ColumnValues [0];
 395                         CloseDataReader (true);
 396                         return result;
 397                 }
 398
 399                 [MonoTODO ("Include offset from SybaseParameter for binary/string types.")]
 400                 static string FormatParameter (SybaseParameter parameter)
 401                 {
 402                         if (parameter.Value == null)
 403                                 return "NULL";
 404
 405                         switch (parameter.SybaseType) {
 406                                 case SybaseType.BigInt :
 407                                 case SybaseType.Decimal :
 408                                 case SybaseType.Float :
 409                                 case SybaseType.Int :
 410                                 case SybaseType.Money :
 411                                 case SybaseType.Real :
 412                                 case SybaseType.SmallInt :
 413                                 case SybaseType.SmallMoney :
 414                                 case SybaseType.TinyInt :
 415                                         return parameter.Value.ToString ();
 416                                 case SybaseType.NVarChar :
 417                                 case SybaseType.NChar :
 418                                         return String.Format ("N'{0}'", parameter.Value.ToString ().Replace ("'", "''"));
 419                                 case SybaseType.UniqueIdentifier :
 420                                         return String.Format ("0x{0}", ((Guid) parameter.Value).ToString ("N"));
 421                                 case SybaseType.Bit:
 422                                         if (parameter.Value.GetType () == typeof (bool))
 423                                                 return (((bool) parameter.Value) ? "0x1" : "0x0");
 424                                         return parameter.Value.ToString ();
 425                                 case SybaseType.Image:
 426                                 case SybaseType.Binary:
 427                                 case SybaseType.VarBinary:
 428                                         return String.Format ("0x{0}", BitConverter.ToString ((byte[]) parameter.Value).Replace ("-", "").ToLower ());
 429                                 default:
 430                                         return String.Format ("'{0}'", parameter.Value.ToString ().Replace ("'", "''"));
 431                         }
 432                 }
 433
 434                 private void GetOutputParameters ()
 435                 {
 436                         Connection.Tds.SkipToEnd ();
 437
 438                         IList list = Connection.Tds.ColumnValues;
 439
 440                         if (list != null && list.Count > 0) {
 441                                 int index = 0;
 442                                 foreach (SybaseParameter parameter in parameters) {
 443                                         if (parameter.Direction != ParameterDirection.Input) {
 444                                                 parameter.Value = list [index];
 445                                                 index += 1;
 446                                         }
 447                                         if (index >= list.Count)
 448                                                 break;
 449                                 }
 450                         }
 451                 }
 452
 453                 object ICloneable.Clone ()
 454                 {
 455                         return new SybaseCommand (commandText, Connection);
 456                 }
 457
 458                 IDbDataParameter IDbCommand.CreateParameter ()
 459                 {
 460                         return CreateParameter ();
 461                 }
 462
 463                 IDataReader IDbCommand.ExecuteReader ()
 464                 {
 465                         return ExecuteReader ();
 466                 }
 467
 468                 IDataReader IDbCommand.ExecuteReader (CommandBehavior behavior)
 469                 {
 470                         return ExecuteReader (behavior);
 471                 }
 472
 473                 public void Prepare ()
 474                 {
 475                         ValidateCommand ("Prepare");
 476                         Connection.Tds.ExecuteNonQuery (BuildPrepare ());
 477
 478                         if (Connection.Tds.OutputParameters.Count == 0 || Connection.Tds.OutputParameters[0] == null)
 479                                 throw new Exception ("Could not prepare the statement.");
 480
 481                         preparedStatements [commandText] = ((int) Connection.Tds.OutputParameters [0]).ToString ();
 482                 }
 483
 484                 public void ResetCommandTimeout ()
 485                 {
 486                         commandTimeout = 30;
 487                 }
 488
 489                 private void ValidateCommand (string method)
 490                 {
 491                         if (Connection == null)
 492                                 throw new InvalidOperationException (String.Format ("{0} requires a Connection object to continue.", method));
 493                         if (Connection.Transaction != null && transaction != Connection.Transaction)
 494                                 throw new InvalidOperationException ("The Connection object does not have the same transaction as the command object.");
 495                         if (Connection.State != ConnectionState.Open)
 496                                 throw new InvalidOperationException (String.Format ("ExecuteNonQuery requires an open Connection object to continue. This connection is closed.", method));
 497                         if (commandText == String.Empty || commandText == null)
 498                                 throw new InvalidOperationException ("The command text for this Command has not been set.");
 499                         if (Connection.DataReader != null)
 500                                 throw new InvalidOperationException ("There is already an open DataReader associated with this Connection which must be closed first.");
 501                 }
 502
 503                 #endregion // Methods
 504         }
 505 }