2 // Mono.Data.SybaseClient.SybaseCommand.cs
5 // Rodrigo Moya (rodrigo@ximian.com)
6 // Daniel Morgan (danmorg@sc.rr.com)
7 // Tim Coleman (tim@timcoleman.com)
9 // (C) Ximian, Inc 2002 http://www.ximian.com/
10 // (C) Daniel Morgan, 2002
11 // Copyright (C) Tim Coleman, 2002
14 using Mono.Data.TdsClient.Internal;
16 using System.Collections;
17 using System.Collections.Specialized;
18 using System.ComponentModel;
20 using System.Data.Common;
21 using System.Runtime.InteropServices;
25 namespace Mono.Data.SybaseClient {
26 public sealed class SybaseCommand : Component, IDbCommand, ICloneable
30 bool disposed = false;
33 bool designTimeVisible;
36 CommandType commandType;
37 SybaseConnection connection;
38 SybaseTransaction transaction;
39 UpdateRowSource updatedRowSource;
41 CommandBehavior behavior = CommandBehavior.Default;
42 NameValueCollection preparedStatements = new NameValueCollection ();
43 SybaseParameterCollection parameters;
49 public SybaseCommand()
50 : this (String.Empty, null, null)
54 public SybaseCommand (string commandText)
55 : this (commandText, null, null)
57 commandText = commandText;
60 public SybaseCommand (string commandText, SybaseConnection connection)
61 : this (commandText, connection, null)
63 Connection = connection;
66 public SybaseCommand (string commandText, SybaseConnection connection, SybaseTransaction transaction)
68 this.commandText = commandText;
69 this.connection = connection;
70 this.transaction = transaction;
71 this.commandType = CommandType.Text;
72 this.updatedRowSource = UpdateRowSource.Both;
74 this.designTimeVisible = false;
75 this.commandTimeout = 30;
76 parameters = new SybaseParameterCollection (this);
79 #endregion // Constructors
83 internal CommandBehavior CommandBehavior {
84 get { return behavior; }
87 public string CommandText {
88 get { return commandText; }
89 set { commandText = value; }
92 public int CommandTimeout {
93 get { return commandTimeout; }
95 if (commandTimeout < 0)
96 throw new ArgumentException ("The property value assigned is less than 0.");
97 commandTimeout = value;
101 public CommandType CommandType {
102 get { return commandType; }
104 if (value == CommandType.TableDirect)
105 throw new ArgumentException ("CommandType.TableDirect is not supported by the Mono SybaseClient Data Provider.");
110 public SybaseConnection Connection {
111 get { return connection; }
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.");
120 public bool DesignTimeVisible {
121 get { return designTimeVisible; }
122 set { designTimeVisible = value; }
125 public SybaseParameterCollection Parameters {
126 get { return parameters; }
130 get { return Connection.Tds; }
133 IDbConnection IDbCommand.Connection {
134 get { return Connection; }
136 if (!(value is SybaseConnection))
137 throw new InvalidCastException ("The value was not a valid SybaseConnection.");
138 Connection = (SybaseConnection) value;
142 IDataParameterCollection IDbCommand.Parameters {
143 get { return Parameters; }
146 IDbTransaction IDbCommand.Transaction {
147 get { return Transaction; }
149 if (!(value is SybaseTransaction))
150 throw new ArgumentException ();
151 Transaction = (SybaseTransaction) value;
155 public SybaseTransaction Transaction {
156 get { return transaction; }
157 set { transaction = value; }
160 public UpdateRowSource UpdatedRowSource {
161 get { return updatedRowSource; }
162 set { updatedRowSource = value; }
169 private string BuildCommand ()
171 string statementHandle = preparedStatements [commandText];
172 if (statementHandle != null) {
173 string proc = String.Format ("sp_execute {0}", statementHandle);
174 if (parameters.Count > 0)
176 return BuildProcedureCall (proc, parameters);
179 if (commandType == CommandType.StoredProcedure)
180 return BuildProcedureCall (commandText, parameters);
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;";
188 switch (commandType) {
189 case CommandType.Text :
193 throw new InvalidOperationException ("The CommandType was invalid.");
195 return BuildExec (sql);
198 [MonoTODO ("This throws a SybaseException.")]
199 private string BuildExec (string sql)
201 StringBuilder declare = new StringBuilder ();
202 StringBuilder assign = new StringBuilder ();
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)));
214 return String.Format ("{0}{1}{2}", declare.ToString (), assign.ToString (), sql);
217 private string BuildPrepare ()
219 StringBuilder parms = new StringBuilder ();
220 foreach (SybaseParameter parameter in parameters) {
221 if (parms.Length > 0)
223 parms.Append (parameter.Prepare (parameter.ParameterName));
224 if (parameter.Direction == ParameterDirection.Output)
225 parms.Append (" output");
228 SybaseParameterCollection localParameters = new SybaseParameterCollection (this);
229 SybaseParameter parm;
231 parm = new SybaseParameter ("@P1", SybaseType.Int);
232 parm.Direction = ParameterDirection.Output;
233 localParameters.Add (parm);
235 parm = new SybaseParameter ("@P2", SybaseType.NVarChar);
236 parm.Value = parms.ToString ();
237 parm.Size = ((string) parm.Value).Length;
238 localParameters.Add (parm);
240 parm = new SybaseParameter ("@P3", SybaseType.NVarChar);
241 parm.Value = commandText;
242 parm.Size = ((string) parm.Value).Length;
243 localParameters.Add (parm);
245 return BuildProcedureCall ("sp_prepare", localParameters);
248 private static string BuildProcedureCall (string procedure, SybaseParameterCollection parameters)
250 StringBuilder parms = new StringBuilder ();
251 StringBuilder declarations = new StringBuilder ();
252 StringBuilder outParms = new StringBuilder ();
253 StringBuilder set = new StringBuilder ();
256 foreach (SybaseParameter parameter in parameters) {
257 string parmName = String.Format ("@P{0}", index);
259 switch (parameter.Direction) {
260 case ParameterDirection.Input :
261 if (parms.Length > 0)
263 parms.Append (FormatParameter (parameter));
265 case ParameterDirection.Output :
266 if (parms.Length > 0)
268 parms.Append (parmName);
269 parms.Append (" output");
271 if (outParms.Length > 0) {
272 outParms.Append (", ");
273 declarations.Append (", ");
276 outParms.Append ("select ");
277 declarations.Append ("declare ");
280 declarations.Append (parameter.Prepare (parmName));
281 set.Append (String.Format ("set {0}=NULL\n", parmName));
282 outParms.Append (parmName);
285 throw new NotImplementedException ("Only support input and output parameters.");
289 if (declarations.Length > 0)
290 declarations.Append ('\n');
292 return String.Format ("{0}{1}{2} {3}\n{4}", declarations.ToString (), set.ToString (), procedure, parms.ToString (), outParms.ToString ());
295 public void Cancel ()
297 if (Connection == null || Connection.Tds == null)
299 Connection.Tds.Cancel ();
302 internal void CloseDataReader (bool moreResults)
304 GetOutputParameters ();
305 Connection.DataReader = null;
307 if ((behavior & CommandBehavior.CloseConnection) != 0)
311 public SybaseParameter CreateParameter ()
313 return new SybaseParameter ();
316 internal void DeriveParameters ()
318 if (commandType != CommandType.StoredProcedure)
319 throw new InvalidOperationException (String.Format ("SybaseCommand DeriveParameters only supports CommandType.StoredProcedure, not CommandType.{0}", commandType));
320 ValidateCommand ("DeriveParameters");
322 SybaseParameterCollection localParameters = new SybaseParameterCollection (this);
323 localParameters.Add ("@P1", SybaseType.NVarChar, commandText.Length).Value = commandText;
325 Connection.Tds.ExecuteQuery (BuildProcedureCall ("sp_procedure_params_rowset", localParameters));
326 SybaseDataReader reader = new SybaseDataReader (this);
328 object[] dbValues = new object[reader.FieldCount];
330 while (reader.Read ()) {
331 reader.GetValues (dbValues);
332 parameters.Add (new SybaseParameter (dbValues));
337 public int ExecuteNonQuery ()
339 ValidateCommand ("ExecuteNonQuery");
340 string sql = String.Empty;
343 if (Parameters.Count > 0)
344 sql = BuildCommand ();
349 result = Connection.Tds.ExecuteNonQuery (sql, CommandTimeout);
351 catch (TdsTimeoutException e) {
352 throw SybaseException.FromTdsInternalException ((TdsInternalException) e);
355 GetOutputParameters ();
359 public SybaseDataReader ExecuteReader ()
361 return ExecuteReader (CommandBehavior.Default);
364 public SybaseDataReader ExecuteReader (CommandBehavior behavior)
366 ValidateCommand ("ExecuteReader");
367 this.behavior = behavior;
370 Connection.Tds.ExecuteQuery (BuildCommand (), CommandTimeout);
372 catch (TdsTimeoutException e) {
373 throw SybaseException.FromTdsInternalException ((TdsInternalException) e);
376 Connection.DataReader = new SybaseDataReader (this);
377 return Connection.DataReader;
380 public object ExecuteScalar ()
382 ValidateCommand ("ExecuteScalar");
384 Connection.Tds.ExecuteQuery (BuildCommand (), CommandTimeout);
386 catch (TdsTimeoutException e) {
387 throw SybaseException.FromTdsInternalException ((TdsInternalException) e);
390 if (!Connection.Tds.NextResult () || !Connection.Tds.NextRow ())
393 object result = Connection.Tds.ColumnValues [0];
394 CloseDataReader (true);
398 [MonoTODO ("Include offset from SybaseParameter for binary/string types.")]
399 static string FormatParameter (SybaseParameter parameter)
401 if (parameter.Value == null)
404 switch (parameter.SybaseType) {
405 case SybaseType.BigInt :
406 case SybaseType.Decimal :
407 case SybaseType.Float :
408 case SybaseType.Int :
409 case SybaseType.Money :
410 case SybaseType.Real :
411 case SybaseType.SmallInt :
412 case SybaseType.SmallMoney :
413 case SybaseType.TinyInt :
414 return parameter.Value.ToString ();
415 case SybaseType.NVarChar :
416 case SybaseType.NChar :
417 return String.Format ("N'{0}'", parameter.Value.ToString ().Replace ("'", "''"));
418 case SybaseType.UniqueIdentifier :
419 return String.Format ("0x{0}", ((Guid) parameter.Value).ToString ("N"));
421 if (parameter.Value.GetType () == typeof (bool))
422 return (((bool) parameter.Value) ? "0x1" : "0x0");
423 return parameter.Value.ToString ();
424 case SybaseType.Image:
425 case SybaseType.Binary:
426 case SybaseType.VarBinary:
427 return String.Format ("0x{0}", BitConverter.ToString ((byte[]) parameter.Value).Replace ("-", "").ToLower ());
429 return String.Format ("'{0}'", parameter.Value.ToString ().Replace ("'", "''"));
433 private void GetOutputParameters ()
435 Connection.Tds.SkipToEnd ();
437 IList list = Connection.Tds.ColumnValues;
439 if (list != null && list.Count > 0) {
441 foreach (SybaseParameter parameter in parameters) {
442 if (parameter.Direction != ParameterDirection.Input) {
443 parameter.Value = list [index];
446 if (index >= list.Count)
452 object ICloneable.Clone ()
454 return new SybaseCommand (commandText, Connection);
457 IDbDataParameter IDbCommand.CreateParameter ()
459 return CreateParameter ();
462 IDataReader IDbCommand.ExecuteReader ()
464 return ExecuteReader ();
467 IDataReader IDbCommand.ExecuteReader (CommandBehavior behavior)
469 return ExecuteReader (behavior);
472 public void Prepare ()
474 ValidateCommand ("Prepare");
475 Connection.Tds.ExecuteNonQuery (BuildPrepare ());
477 if (Connection.Tds.OutputParameters.Count == 0 || Connection.Tds.OutputParameters[0] == null)
478 throw new Exception ("Could not prepare the statement.");
480 preparedStatements [commandText] = ((int) Connection.Tds.OutputParameters [0]).ToString ();
483 public void ResetCommandTimeout ()
488 private void ValidateCommand (string method)
490 if (Connection == null)
491 throw new InvalidOperationException (String.Format ("{0} requires a Connection object to continue.", method));
492 if (Connection.Transaction != null && transaction != Connection.Transaction)
493 throw new InvalidOperationException ("The Connection object does not have the same transaction as the command object.");
494 if (Connection.State != ConnectionState.Open)
495 throw new InvalidOperationException (String.Format ("ExecuteNonQuery requires an open Connection object to continue. This connection is closed.", method));
496 if (commandText == String.Empty || commandText == null)
497 throw new InvalidOperationException ("The command text for this Command has not been set.");
498 if (Connection.DataReader != null)
499 throw new InvalidOperationException ("There is already an open DataReader associated with this Connection which must be closed first.");
502 #endregion // Methods