4 // Part of the Mono class libraries at
5 // mcs/class/System.Data.OracleClient/System.Data.OracleClient
7 // Assembly: System.Data.OracleClient.dll
8 // Namespace: System.Data.OracleClient
11 // Daniel Morgan <danielmorgan@verizon.net>
12 // Tim Coleman <tim@timcoleman.com>
14 // Copyright (C) Daniel Morgan, 2002, 2004-2005
15 // Copyright (C) Tim Coleman , 2003
17 // Licensed under the MIT/X11 License.
21 using System.ComponentModel;
23 using System.Data.OracleClient.Oci;
24 using System.Drawing.Design;
27 namespace System.Data.OracleClient {
28 [Designer ("Microsoft.VSDesigner.Data.VS.OracleCommandDesigner, " + Consts.AssemblyMicrosoft_VSDesigner)]
30 public sealed class OracleCommand : Component, ICloneable, IDbCommand
34 CommandBehavior behavior;
36 CommandType commandType;
37 OracleConnection connection;
38 bool designTimeVisible;
39 OracleParameterCollection parameters;
40 OracleTransaction transaction;
41 UpdateRowSource updatedRowSource;
43 private OciStatementHandle preparedStatement;
44 OciStatementType statementType;
50 public OracleCommand ()
51 : this (String.Empty, null, null)
55 public OracleCommand (string commandText)
56 : this (commandText, null, null)
60 public OracleCommand (string commandText, OracleConnection connection)
61 : this (commandText, connection, null)
65 public OracleCommand (string commandText, OracleConnection connection, OracleTransaction tx)
67 preparedStatement = null;
68 CommandText = commandText;
69 Connection = connection;
71 CommandType = CommandType.Text;
72 UpdatedRowSource = UpdateRowSource.Both;
73 DesignTimeVisible = false;
75 parameters = new OracleParameterCollection (this);
78 #endregion // Constructors
83 [RefreshProperties (RefreshProperties.All)]
84 [Editor ("Microsoft.VSDesigner.Data.Oracle.Design.OracleCommandTextEditor, " + Consts.AssemblyMicrosoft_VSDesigner, typeof(UITypeEditor))]
85 public string CommandText {
86 get { return commandText; }
87 set { commandText = value; }
90 [RefreshProperties (RefreshProperties.All)]
91 [DefaultValue (CommandType.Text)]
92 public CommandType CommandType {
93 get { return commandType; }
95 if (value == CommandType.TableDirect)
96 throw new ArgumentException ("OracleClient provider does not support TableDirect CommandType.");
101 [DefaultValue (null)]
102 [Editor ("Microsoft.VSDesigner.Data.Design.DbConnectionEditor, " + Consts.AssemblyMicrosoft_VSDesigner, typeof(UITypeEditor))]
103 public OracleConnection Connection {
104 get { return connection; }
105 set { connection = value; }
108 [DefaultValue (true)]
111 public bool DesignTimeVisible {
112 get { return designTimeVisible; }
113 set { designTimeVisible = value; }
116 internal OciEnvironmentHandle Environment {
117 get { return Connection.Environment; }
120 internal OciErrorHandle ErrorHandle {
121 get { return Connection.ErrorHandle; }
124 int IDbCommand.CommandTimeout {
129 [Editor ("Microsoft.VSDesigner.Data.Design.DbConnectionEditor, " + Consts.AssemblyMicrosoft_VSDesigner, typeof(UITypeEditor))]
130 [DefaultValue (null)]
131 IDbConnection IDbCommand.Connection {
132 get { return Connection; }
134 // InvalidCastException is expected when types do not match
135 Connection = (OracleConnection) value;
139 IDataParameterCollection IDbCommand.Parameters {
140 get { return Parameters; }
143 IDbTransaction IDbCommand.Transaction {
144 get { return Transaction; }
146 // InvalidCastException is expected when types do not match
147 Transaction = (OracleTransaction) value;
151 [DesignerSerializationVisibility(DesignerSerializationVisibility.Content)]
152 public OracleParameterCollection Parameters {
153 get { return parameters; }
157 [DesignerSerializationVisibility (DesignerSerializationVisibility.Hidden)]
158 public OracleTransaction Transaction {
159 get { return transaction; }
160 set { transaction = value; }
163 [DefaultValue (UpdateRowSource.Both)]
164 public UpdateRowSource UpdatedRowSource {
165 get { return updatedRowSource; }
166 set { updatedRowSource = value; }
173 private void AssertCommandTextIsSet ()
175 if (CommandText == String.Empty || CommandText == null)
176 throw new InvalidOperationException ("The command text for this Command has not been set.");
179 private void AssertConnectionIsOpen ()
181 if (Connection == null || Connection.State == ConnectionState.Closed)
182 throw new InvalidOperationException ("An open Connection object is required to continue.");
185 private void AssertNoDataReader ()
187 if (Connection.DataReader != null)
188 throw new InvalidOperationException ("There is already an open DataReader associated with this Connection which must be closed first.");
191 private void AssertTransactionMatch ()
193 if (Connection.Transaction != null && Transaction != Connection.Transaction)
194 throw new InvalidOperationException ("Execute requires the Command object to have a Transaction object when the Connection object assigned to the command is in a pending local transaction. The Transaction property of the Command has not been initialized.");
197 private void BindParameters (OciStatementHandle statement)
199 for (int p = 0; p < Parameters.Count; p++)
200 Parameters[p].Bind (statement, Connection, (uint) p);
204 public void Cancel ()
206 throw new NotImplementedException ();
210 public object Clone ()
212 // create a new OracleCommand object with the same properties
214 OracleCommand cmd = new OracleCommand ();
216 cmd.CommandText = this.CommandText;
217 cmd.CommandType = this.CommandType;
219 // FIXME: not sure if I should set the same object here
220 // or get a clone of these too
221 cmd.Connection = this.Connection;
222 cmd.Transaction = this.Transaction;
224 foreach (OracleParameter parm in this.Parameters) {
226 OracleParameter newParm = cmd.CreateParameter ();
228 newParm.DbType = parm.DbType;
229 newParm.Direction = parm.Direction;
230 newParm.IsNullable = parm.IsNullable;
231 newParm.Offset = parm.Offset;
232 newParm.OracleType = parm.OracleType;
233 newParm.ParameterName = parm.ParameterName;
234 newParm.Precision = parm.Precision;
235 newParm.Scale = parm.Scale;
236 newParm.SourceColumn = parm.SourceColumn;
237 newParm.SourceVersion = parm.SourceVersion;
238 newParm.Value = parm.Value;
240 cmd.Parameters.Add (newParm);
243 //cmd.Container = this.Container;
244 cmd.DesignTimeVisible = this.DesignTimeVisible;
245 //cmd.DesignMode = this.DesignMode;
246 cmd.Site = this.Site;
247 //cmd.UpdateRowSource = this.UpdateRowSource;
252 internal void UpdateParameterValues ()
254 if (Parameters.Count > 0) {
255 foreach (OracleParameter parm in Parameters)
260 internal void CloseDataReader ()
262 UpdateParameterValues ();
264 Connection.DataReader = null;
265 if ((behavior & CommandBehavior.CloseConnection) != 0)
269 public OracleParameter CreateParameter ()
271 return new OracleParameter ();
274 internal void DeriveParameters ()
276 if (commandType != CommandType.StoredProcedure)
277 throw new InvalidOperationException (String.Format ("OracleCommandBuilder DeriveParameters only supports CommandType.StoredProcedure, not CommandType.{0}", commandType));
279 //OracleParameterCollection localParameters = new OracleParameterCollection (this);
281 throw new NotImplementedException ();
284 private int ExecuteNonQueryInternal (OciStatementHandle statement, bool useAutoCommit)
286 if (preparedStatement == null)
287 PrepareStatement (statement);
289 bool isNonQuery = IsNonQuery (statement);
291 BindParameters (statement);
292 if (isNonQuery == true)
293 statement.ExecuteNonQuery (useAutoCommit);
295 statement.ExecuteQuery (false);
297 UpdateParameterValues ();
299 int rowsAffected = statement.GetAttributeInt32 (OciAttributeType.RowCount, ErrorHandle);
304 public int ExecuteNonQuery ()
306 AssertConnectionIsOpen ();
307 AssertTransactionMatch ();
308 AssertCommandTextIsSet ();
309 bool useAutoCommit = false;
311 if (Transaction != null)
312 Transaction.AttachToServiceContext ();
314 useAutoCommit = true;
316 OciStatementHandle statement = GetStatementHandle ();
318 return ExecuteNonQueryInternal (statement, useAutoCommit);
321 SafeDisposeHandle (statement);
325 public int ExecuteOracleNonQuery (out OracleString rowid)
327 AssertConnectionIsOpen ();
328 AssertTransactionMatch ();
329 AssertCommandTextIsSet ();
330 bool useAutoCommit = false;
332 if (Transaction != null)
333 Transaction.AttachToServiceContext ();
335 useAutoCommit = true;
337 OciStatementHandle statement = GetStatementHandle ();
340 int retval = ExecuteNonQueryInternal (statement, useAutoCommit);
342 OciRowIdDescriptor descriptor = (OciRowIdDescriptor) Environment.Allocate (OciHandleType.RowId);
343 descriptor.SetHandle (statement.GetAttributeIntPtr (OciAttributeType.RowId, ErrorHandle));
345 rowid = new OracleString (descriptor.GetRowId (ErrorHandle));
350 SafeDisposeHandle (statement);
355 public object ExecuteOracleScalar ()
357 object output = DBNull.Value;
359 AssertConnectionIsOpen ();
360 AssertTransactionMatch ();
361 AssertCommandTextIsSet ();
363 if (Transaction != null)
364 Transaction.AttachToServiceContext ();
366 OciStatementHandle statement = GetStatementHandle ();
368 if (preparedStatement == null)
369 PrepareStatement (statement);
371 bool isNonQuery = IsNonQuery (statement);
373 BindParameters (statement);
375 if (isNonQuery == true)
376 ExecuteNonQueryInternal (statement, false);
378 statement.ExecuteQuery (false);
380 if (statement.Fetch ()) {
381 OciDefineHandle defineHandle = (OciDefineHandle) statement.Values [0];
382 if (!defineHandle.IsNull)
383 output = defineHandle.GetOracleValue (Connection.SessionFormatProvider);
384 switch (defineHandle.DataType) {
385 case OciDataType.Blob:
386 case OciDataType.Clob:
387 ((OracleLob) output).connection = Connection;
391 UpdateParameterValues ();
397 SafeDisposeHandle (statement);
401 private bool IsNonQuery (OciStatementHandle statementHandle)
403 // assumes Prepare() has been called prior to calling this function
405 OciStatementType statementType = statementHandle.GetStatementType ();
406 if (statementType.Equals (OciStatementType.Select))
412 public OracleDataReader ExecuteReader ()
414 return ExecuteReader (CommandBehavior.Default);
417 public OracleDataReader ExecuteReader (CommandBehavior behavior)
419 AssertConnectionIsOpen ();
420 AssertTransactionMatch ();
421 AssertCommandTextIsSet ();
422 AssertNoDataReader ();
423 bool hasRows = false;
425 this.behavior = behavior;
427 if (Transaction != null)
428 Transaction.AttachToServiceContext ();
430 OciStatementHandle statement = GetStatementHandle ();
431 OracleDataReader rd = null;
434 if (preparedStatement == null)
435 PrepareStatement (statement);
437 preparedStatement = null; // OracleDataReader releases the statement handle
439 bool isNonQuery = IsNonQuery (statement);
441 BindParameters (statement);
444 ExecuteNonQueryInternal (statement, false);
446 if ((behavior & CommandBehavior.SchemaOnly) != 0)
447 statement.ExecuteQuery (true);
449 hasRows = statement.ExecuteQuery (false);
452 rd = new OracleDataReader (this, statement, hasRows, behavior);
455 if (statement != null && rd == null)
462 public object ExecuteScalar ()
464 object output = null;//if we find nothing we return this
466 AssertConnectionIsOpen ();
467 AssertTransactionMatch ();
468 AssertCommandTextIsSet ();
470 if (Transaction != null)
471 Transaction.AttachToServiceContext ();
473 OciStatementHandle statement = GetStatementHandle ();
475 if (preparedStatement == null)
476 PrepareStatement (statement);
478 bool isNonQuery = IsNonQuery (statement);
480 BindParameters (statement);
482 if (isNonQuery == true)
483 ExecuteNonQueryInternal (statement, false);
485 statement.ExecuteQuery (false);
487 if (statement.Fetch ()) {
488 OciDefineHandle defineHandle = (OciDefineHandle) statement.Values [0];
489 if (!defineHandle.IsNull)
491 switch (defineHandle.DataType) {
492 case OciDataType.Blob:
493 case OciDataType.Clob:
494 OracleLob lob = (OracleLob) defineHandle.GetValue (
495 Connection.SessionFormatProvider);
496 lob.connection = Connection;
501 output = defineHandle.GetValue (
502 Connection.SessionFormatProvider);
507 UpdateParameterValues ();
511 SafeDisposeHandle (statement);
517 private OciStatementHandle GetStatementHandle ()
519 AssertConnectionIsOpen ();
520 if (preparedStatement != null)
521 return preparedStatement;
523 OciStatementHandle h = (OciStatementHandle) Connection.Environment.Allocate (OciHandleType.Statement);
524 h.ErrorHandle = Connection.ErrorHandle;
525 h.Service = Connection.ServiceContext;
530 private void SafeDisposeHandle (OciStatementHandle h)
532 if (h != null && h != preparedStatement)
536 IDbDataParameter IDbCommand.CreateParameter ()
538 return CreateParameter ();
541 IDataReader IDbCommand.ExecuteReader ()
543 return ExecuteReader ();
546 IDataReader IDbCommand.ExecuteReader (CommandBehavior behavior)
548 return ExecuteReader (behavior);
551 void PrepareStatement (OciStatementHandle statement)
553 if (commandType == CommandType.StoredProcedure) {
554 StringBuilder sb = new StringBuilder ();
555 if (Parameters.Count > 0)
556 foreach (OracleParameter parm in Parameters) {
559 sb.Append (":" + parm.ParameterName);
562 string sql = "call " + commandText + "(" + sb.ToString() + ")";
563 statement.Prepare (sql);
566 statement.Prepare (commandText);
569 public void Prepare ()
571 AssertConnectionIsOpen ();
572 OciStatementHandle statement = GetStatementHandle ();
573 PrepareStatement (statement);
574 preparedStatement = statement;
577 protected override void Dispose (bool disposing)
580 if (Parameters.Count > 0)
581 foreach (OracleParameter parm in Parameters)
583 base.Dispose (disposing);
586 #endregion // Methods