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.Specialized;
  17 using System.ComponentModel;
  18 using System.Data;
  19 using System.Data.Common;
  20 using System.Runtime.InteropServices;
  21 using System.Text;
  22 using System.Xml;
  23
  24 namespace Mono.Data.SybaseClient {
  25         public sealed class SybaseCommand : Component, IDbCommand, ICloneable
  26         {
  27                 #region Fields
  28
  29                 int commandTimeout;
  30                 bool designTimeVisible;
  31                 string commandText;
  32
  33                 CommandType commandType;
  34                 SybaseConnection connection;
  35                 SybaseTransaction transaction;
  36
  37                 SybaseParameterCollection parameters = new SybaseParameterCollection ();
  38                 private CommandBehavior behavior = CommandBehavior.Default;
  39
  40                 NameValueCollection procedureCache = new NameValueCollection ();
  41
  42                 #endregion // Fields
  43
  44                 #region Constructors
  45
  46                 public SybaseCommand()
  47                         : this (String.Empty, null, null)
  48                 {
  49                 }
  50
  51                 public SybaseCommand (string commandText)
  52                         : this (commandText, null, null)
  53                 {
  54                         commandText = commandText;
  55                 }
  56
  57                 public SybaseCommand (string commandText, SybaseConnection connection)
  58                         : this (commandText, connection, null)
  59                 {
  60                         Connection = connection;
  61                 }
  62
  63                 public SybaseCommand (string commandText, SybaseConnection connection, SybaseTransaction transaction)
  64                 {
  65                         this.commandText = commandText;
  66                         this.connection = connection;
  67                         this.transaction = transaction;
  68                         this.commandType = CommandType.Text;
  69                         this.designTimeVisible = false;
  70                         this.commandTimeout = 30;
  71                 }
  72
  73                 #endregion // Constructors
  74
  75                 #region Properties
  76
  77                 internal CommandBehavior CommandBehavior {
  78                         get { return behavior; }
  79                 }
  80
  81                 public string CommandText {
  82                         get { return CommandText; }
  83                         set { commandText = value; }
  84                 }
  85
  86                 public int CommandTimeout {
  87                         get { return commandTimeout;  }
  88                         set {
  89                                 if (commandTimeout < 0)
  90                                         throw new ArgumentException ("The property value assigned is less than 0.");
  91                                 commandTimeout = value;
  92                         }
  93                 }
  94
  95                 public CommandType CommandType  {
  96                         get { return commandType; }
  97                         [MonoTODO ("Validate")]
  98                         set { commandType = value; }
  99                 }
 100
 101                 public SybaseConnection Connection {
 102                         get { return connection; }
 103                         set {
 104                                 if (transaction != null && connection.Transaction != null && connection.Transaction.IsOpen)
 105                                         throw new InvalidOperationException ("The Connection property was changed while a transaction was in progress.");
 106                                 transaction = null;
 107                                 connection = value;
 108                         }
 109                 }
 110
 111                 public bool DesignTimeVisible {
 112                         get { return designTimeVisible; }
 113                         set { designTimeVisible = value; }
 114                 }
 115
 116                 public SybaseParameterCollection Parameters {
 117                         get { return parameters; }
 118                 }
 119
 120                 internal ITds Tds {
 121                         get { return connection.Tds; }
 122                 }
 123
 124                 IDbConnection IDbCommand.Connection {
 125                         get { return Connection; }
 126                         set {
 127                                 if (!(value is SybaseConnection))
 128                                         throw new InvalidCastException ("The value was not a valid SybaseConnection.");
 129                                 Connection = (SybaseConnection) value;
 130                         }
 131                 }
 132
 133                 IDataParameterCollection IDbCommand.Parameters  {
 134                         get { return Parameters; }
 135                 }
 136
 137                 IDbTransaction IDbCommand.Transaction {
 138                         get { return Transaction; }
 139                         set {
 140                                 if (!(value is SybaseTransaction))
 141                                         throw new ArgumentException ();
 142                                 Transaction = (SybaseTransaction) value;
 143                         }
 144                 }
 145
 146                 public SybaseTransaction Transaction {
 147                         get { return transaction; }
 148                         set { transaction = value; }
 149                 }
 150
 151                 [MonoTODO]
 152                 public UpdateRowSource UpdatedRowSource {
 153                         get { throw new NotImplementedException (); }
 154                         set { throw new NotImplementedException (); }
 155                 }
 156
 157                 #endregion // Fields
 158
 159                 #region Methods
 160
 161                 public void Cancel ()
 162                 {
 163                         if (connection == null || connection.Tds == null)
 164                                 return;
 165                         connection.Tds.Cancel ();
 166                         connection.CheckForErrors ();
 167                 }
 168
 169                 internal void CloseDataReader (bool moreResults)
 170                 {
 171                         while (moreResults)
 172                                 moreResults = connection.Tds.NextResult ();
 173
 174                         if (connection.Tds.OutputParameters.Count > 0) {
 175                                 int index = 0;
 176                                 foreach (SybaseParameter parameter in parameters) {
 177                                         if (parameter.Direction != ParameterDirection.Input)
 178                                                 parameter.Value = connection.Tds.OutputParameters[index];
 179                                         index += 1;
 180                                         if (index >= connection.Tds.OutputParameters.Count)
 181                                                 break;
 182                                 }
 183                         }
 184                         connection.DataReaderOpen = false;
 185                         if ((behavior & CommandBehavior.CloseConnection) != 0)
 186                                 connection.Close ();
 187                 }
 188
 189                 public SybaseParameter CreateParameter ()
 190                 {
 191                         return new SybaseParameter ();
 192                 }
 193
 194                 public int ExecuteNonQuery ()
 195                 {
 196                         int result = connection.Tds.ExecuteNonQuery (ValidateQuery ("ExecuteNonQuery"));
 197                         connection.CheckForErrors ();
 198                         return result;
 199                 }
 200
 201                 public SybaseDataReader ExecuteReader ()
 202                 {
 203                         return ExecuteReader (CommandBehavior.Default);
 204                 }
 205
 206                 public SybaseDataReader ExecuteReader (CommandBehavior behavior)
 207                 {
 208                         this.behavior = behavior;
 209                         connection.Tds.ExecuteQuery (ValidateQuery ("ExecuteReader"));
 210                         connection.CheckForErrors ();
 211                         connection.DataReaderOpen = true;
 212                         return new SybaseDataReader (this);
 213                 }
 214
 215                 public object ExecuteScalar ()
 216                 {
 217                         connection.Tds.ExecuteQuery (ValidateQuery ("ExecuteScalar"));
 218
 219                         bool moreResults = connection.Tds.NextResult ();
 220                         connection.CheckForErrors ();
 221
 222                         if (!moreResults)
 223                                 return null;
 224
 225                         moreResults = connection.Tds.NextRow ();
 226                         connection.CheckForErrors ();
 227
 228                         if (!moreResults)
 229                                 return null;
 230
 231                         object result = connection.Tds.ColumnValues[0];
 232                         CloseDataReader (true);
 233                         return result;
 234                 }
 235
 236                 static string FormatParameter (SybaseParameter parameter)
 237                 {
 238                         if (parameter.Value == null)
 239                                 return "NULL";
 240
 241                         switch (parameter.SybaseType) {
 242                                 case SybaseType.BigInt :
 243                                 case SybaseType.Bit :
 244                                 case SybaseType.Decimal :
 245                                 case SybaseType.Float :
 246                                 case SybaseType.Int :
 247                                 case SybaseType.Money :
 248                                 case SybaseType.Real :
 249                                 case SybaseType.SmallInt :
 250                                 case SybaseType.SmallMoney :
 251                                 case SybaseType.TinyInt :
 252                                         return parameter.Value.ToString ();
 253                                 default:
 254                                         return String.Format ("'{0}'", parameter.Value.ToString ().Replace ("'", "''"));
 255                         }
 256                 }
 257
 258                 static string FormatQuery (string commandText, CommandType commandType, SybaseParameterCollection parameters)
 259                 {
 260                         StringBuilder result = new StringBuilder ();
 261
 262                         switch (commandType) {
 263                         case CommandType.Text :
 264                                 return commandText;
 265                         case CommandType.TableDirect :
 266                                 return String.Format ("SELECT * FROM {0}", commandText);
 267                         case CommandType.StoredProcedure :
 268
 269                                 StringBuilder parms = new StringBuilder ();
 270                                 StringBuilder declarations = new StringBuilder ();
 271
 272                                 foreach (SybaseParameter parameter in parameters) {
 273                                         switch (parameter.Direction) {
 274                                         case ParameterDirection.Input :
 275                                                 if (parms.Length > 0)
 276                                                         result.Append (",");
 277                                                 parms.Append (FormatParameter (parameter));
 278                                                 break;
 279                                         case ParameterDirection.Output :
 280                                                 if (parms.Length > 0)
 281                                                         parms.Append (",");
 282                                                 parms.Append (parameter.ParameterName);
 283                                                 parms.Append (" OUT");
 284
 285                                                 if (declarations.Length == 0)
 286                                                         declarations.Append ("DECLARE ");
 287                                                 else
 288                                                         declarations.Append (",");
 289
 290                                                 declarations.Append (parameter.Prepare ());
 291                                                 break;
 292                                         default :
 293                                                 throw new NotImplementedException ("Only support input and output parameters.");
 294                                         }
 295                                 }
 296                                 result.Append (declarations.ToString ());
 297                                 result.Append (" EXEC ");
 298                                 result.Append (commandText);
 299                                 result.Append (" ");
 300                                 result.Append (parms);
 301                                 return result.ToString ();
 302                         default:
 303                                 throw new InvalidOperationException ("The CommandType was not recognized.");
 304                         }
 305                 }
 306
 307                 [MonoTODO]
 308                 object ICloneable.Clone ()
 309                 {
 310                         throw new NotImplementedException ();
 311                 }
 312
 313                 IDbDataParameter IDbCommand.CreateParameter ()
 314                 {
 315                         return CreateParameter ();
 316                 }
 317
 318                 IDataReader IDbCommand.ExecuteReader ()
 319                 {
 320                         return ExecuteReader ();
 321                 }
 322
 323                 IDataReader IDbCommand.ExecuteReader (CommandBehavior behavior)
 324                 {
 325                         return ExecuteReader (behavior);
 326                 }
 327
 328                 void IDisposable.Dispose ()
 329                 {
 330                         Dispose (true);
 331                 }
 332
 333                 public void Prepare ()
 334                 {
 335                         bool prependComma = false;
 336                         Guid uniqueId = Guid.NewGuid ();
 337                         string procedureName = String.Format ("#mono#{0}", uniqueId.ToString ("N"));
 338                         StringBuilder procedureString = new StringBuilder ();
 339
 340                         procedureString.Append ("CREATE PROC ");
 341                         procedureString.Append (procedureName);
 342                         procedureString.Append (" (");
 343
 344                         foreach (SybaseParameter parameter in parameters) {
 345                                 if (prependComma)
 346                                         procedureString.Append (", ");
 347                                 else
 348                                         prependComma = true;
 349                                 procedureString.Append (parameter.Prepare ());
 350                                 if (parameter.Direction == ParameterDirection.Output)
 351                                         procedureString.Append (" OUT");
 352                         }
 353
 354                         procedureString.Append (") AS ");
 355                         procedureString.Append (commandText);
 356                         string cmdText = FormatQuery (procedureName, CommandType.StoredProcedure, parameters);
 357                         connection.Tds.ExecuteNonQuery (procedureString.ToString ());
 358                         procedureCache[commandText] = cmdText;
 359                 }
 360
 361                 public void ResetCommandTimeout ()
 362                 {
 363                         commandTimeout = 30;
 364                 }
 365
 366                 string ValidateQuery (string methodName)
 367                 {
 368                         if (connection == null)
 369                                 throw new InvalidOperationException (String.Format ("{0} requires a Connection object to continue.", methodName));
 370                         if (connection.Transaction != null && transaction != connection.Transaction)
 371                                 throw new InvalidOperationException ("The Connection object does not have the same transaction as the command object.");
 372                         if (connection.State != ConnectionState.Open)
 373                                 throw new InvalidOperationException (String.Format ("ExecuteNonQuery requires an open Connection object to continue. This connection is closed.", methodName));
 374                         if (commandText == String.Empty || commandText == null)
 375                                 throw new InvalidOperationException ("The command text for this Command has not been set.");
 376
 377                         string sql = procedureCache[commandText];
 378                         if (sql == null)
 379                                 sql = FormatQuery (commandText, commandType, parameters);
 380
 381                         if ((behavior & CommandBehavior.KeyInfo) != 0)
 382                                 sql += " FOR BROWSE";
 383
 384                         return sql;
 385                 }
 386
 387                 #endregion // Methods
 388         }
 389 }