[pgsqlclient-checkins] SF.net SVN: pgsqlclient: [145] trunk/PostgreSqlClient/source/PostgreSql/Data/
Status: Inactive
Brought to you by:
carlosga_fb
Menu
▾
▴
[pgsqlclient-checkins] SF.net SVN: pgsqlclient: [145] trunk/PostgreSqlClient/source/PostgreSql/Data/PostgreSqlClient
|
From: <car...@us...> - 2006-05-18 19:51:34
|
Revision: 145 Author: carlosga_fb Date: 2006-05-18 12:51:21 -0700 (Thu, 18 May 2006) ViewCVS: http://svn.sourceforge.net/pgsqlclient/?rev=145&view=rev Log Message: ----------- 2006-05-18 Carlos Guzman Alvarez (car...@gm...) * Added initial refcursor support for StoredProcedure calls to the PgDataReader class ( multiple refcursor's supported ). Modified Paths: -------------- trunk/PostgreSqlClient/source/PostgreSql/Data/PostgreSqlClient/PgCommand.cs trunk/PostgreSqlClient/source/PostgreSql/Data/PostgreSqlClient/PgDataReader.cs trunk/PostgreSqlClient/source/PostgreSql/Data/PostgreSqlClient/PgDbType.cs Modified: trunk/PostgreSqlClient/source/PostgreSql/Data/PostgreSqlClient/PgCommand.cs =================================================================== --- trunk/PostgreSqlClient/source/PostgreSql/Data/PostgreSqlClient/PgCommand.cs 2006-05-18 19:49:14 UTC (rev 144) +++ trunk/PostgreSqlClient/source/PostgreSql/Data/PostgreSqlClient/PgCommand.cs 2006-05-18 19:51:21 UTC (rev 145) @@ -371,7 +371,7 @@ public new PgDataReader ExecuteReader() { - return ExecuteReader(CommandBehavior.Default); + return this.ExecuteReader(CommandBehavior.Default); } public new PgDataReader ExecuteReader(CommandBehavior behavior) @@ -391,7 +391,7 @@ this.InternalExecute(); } - this.activeDataReader = new PgDataReader(this, this.connection); + this.activeDataReader = new PgDataReader(this.connection, this); return this.activeDataReader; } @@ -443,8 +443,9 @@ sql = this.BuildStoredProcedureSql(sql); } - string prepareName = "PS" + this.GetStmtName(); - string portalName = "PR" + this.GetStmtName(); + string statementName = this.GetStmtName(); + string prepareName = String.Format("PS{0}", statementName); + string portalName = String.Format("PR{0}", statementName); this.statement = conn.Database.CreateStatement(prepareName, portalName, this.ParseNamedParameters(sql)); Modified: trunk/PostgreSqlClient/source/PostgreSql/Data/PostgreSqlClient/PgDataReader.cs =================================================================== --- trunk/PostgreSqlClient/source/PostgreSql/Data/PostgreSqlClient/PgDataReader.cs 2006-05-18 19:49:14 UTC (rev 144) +++ trunk/PostgreSqlClient/source/PostgreSql/Data/PostgreSqlClient/PgDataReader.cs 2006-05-18 19:51:21 UTC (rev 145) @@ -38,11 +38,13 @@ private int position; private int recordsAffected; private int fieldCount; + private object[] row; private DataTable schemaTable; private CommandBehavior behavior; private PgCommand command; private PgConnection connection; - private object[] row; + private PgStatement statement; + private Queue refCursors; #endregion @@ -62,20 +64,19 @@ #region \xB7 Constructors \xB7 - private PgDataReader() + internal PgDataReader(PgConnection connection, PgCommand command) { - this.open = true; - this.position = STARTPOS; - this.recordsAffected = -1; - this.fieldCount = -1; - } + this.open = true; + this.recordsAffected = -1; + this.position = STARTPOS; + this.fieldCount = -1; + this.refCursors = new Queue(); + this.connection = connection; + this.command = command; + this.behavior = this.command.CommandBehavior; + this.statement = this.command.Statement; - internal PgDataReader(PgCommand command, PgConnection connection) : this() - { - this.command = command; - this.behavior = this.command.CommandBehavior; - this.connection = connection; - this.fieldCount = this.command.Statement.RowDescriptor.Fields.Length; + this.Initialize(); } #endregion @@ -102,6 +103,16 @@ { // release any managed resources this.Close(); + + this.command = null; + this.statement = null; + this.connection = null; + this.refCursors = null; + this.row = null; + this.schemaTable = null; + this.fieldCount = -1; + this.recordsAffected = -1; + this.position = -1; } // release any unmanaged resources @@ -167,33 +178,60 @@ } } + this.refCursors.Clear(); + this.open = false; this.position = STARTPOS; } public override bool NextResult() { - return false; + bool hasMoreResults = false; + + if (this.refCursors.Count != 0 && this.connection.InternalConnection.HasActiveTransaction) + { + string sql = String.Format("fetch all in \"{0}\"", (string)this.refCursors.Dequeue()); + + // Reset position and field count + this.position = STARTPOS; + this.fieldCount = -1; + + // Close the active statement + this.statement.Close(); + + // Create a new statement to fetch the current refcursor + string statementName = Guid.NewGuid().ToString(); + string prepareName = String.Format("PS{0}", statementName); + string portalName = String.Format("PR{0}", statementName); + + this.statement = this.connection.InternalConnection.Database.CreateStatement(prepareName, portalName, sql); + this.statement.Parse(); + this.statement.Describe(); + this.statement.Bind(); + this.statement.Execute(); + + // Allow the DataReader to process more refcursors + hasMoreResults = true; + } + + return hasMoreResults; } public override bool Read() { bool read = false; - if ((this.behavior == CommandBehavior.SingleRow && - this.position != STARTPOS) || - !this.command.Statement.HasRows) + if ((this.behavior == CommandBehavior.SingleRow && this.position != STARTPOS) || + !this.command.Statement.HasRows) { } else { try { - this.fieldCount = this.command.Statement.RowDescriptor.Fields.Length; - this.position++; - row = this.command.Statement.FetchRow(); + row = this.statement.FetchRow(); read = (this.row == null) ? false : true; } @@ -212,11 +250,11 @@ public override DataTable GetSchemaTable() { - if (schemaTable == null) - { - schemaTable = this.GetSchemaTableStructure(); + if (this.schemaTable == null) + { + this.schemaTable = this.GetSchemaTableStructure(); - schemaTable.BeginLoadData(); + this.schemaTable.BeginLoadData(); PgCommand columnsCmd = new PgCommand(this.GetColumnsSql(), this.connection); columnsCmd.Parameters.Add("@OidNumber", PgDbType.Int4); @@ -225,16 +263,16 @@ PgCommand primaryKeyCmd = new PgCommand(this.GetPrimaryKeysSql(), this.connection); primaryKeyCmd.Parameters.Add("@OidTable", PgDbType.Int4); - for (int i = 0; i < command.Statement.RowDescriptor.Fields.Length; i++) + for (int i = 0; i < this.statement.RowDescriptor.Fields.Length; i++) { object[] columnInfo = null; Array pKeyInfo = null; // Execute commands - columnsCmd.Parameters[0].Value = command.Statement.RowDescriptor.Fields[i].OidNumber; - columnsCmd.Parameters[1].Value = command.Statement.RowDescriptor.Fields[i].OidTable; + columnsCmd.Parameters[0].Value = this.statement.RowDescriptor.Fields[i].OidNumber; + columnsCmd.Parameters[1].Value = this.statement.RowDescriptor.Fields[i].OidTable; - primaryKeyCmd.Parameters[0].Value = command.Statement.RowDescriptor.Fields[i].OidTable; + primaryKeyCmd.Parameters[0].Value = this.statement.RowDescriptor.Fields[i].OidTable; columnsCmd.InternalPrepare(); // First time it will prepare the command, next times it will close the open portal columnsCmd.InternalExecute(); @@ -256,7 +294,7 @@ } // Add row information - DataRow schemaRow = schemaTable.NewRow(); + DataRow schemaRow = this.schemaTable.NewRow(); schemaRow["ColumnName"] = this.GetName(i); schemaRow["ColumnOrdinal"] = (i + 1); @@ -301,16 +339,16 @@ schemaRow["BaseColumnName"] = System.DBNull.Value; } - schemaTable.Rows.Add(schemaRow); + this.schemaTable.Rows.Add(schemaRow); } - schemaTable.EndLoadData(); + this.schemaTable.EndLoadData(); columnsCmd.Dispose(); primaryKeyCmd.Dispose(); - } + } - return schemaTable; + return this.schemaTable; } private string GetColumnsSql() @@ -372,14 +410,22 @@ public override int FieldCount { - get { return this.command.Statement.RowDescriptor.Fields.Length; } + get + { + if (this.statement != null) + { + return this.statement.RowDescriptor.Fields.Length; + } + + return -1; + } } public override String GetName(int i) { this.CheckIndex(i); - return this.command.Statement.RowDescriptor.Fields[i].FieldName; + return this.statement.RowDescriptor.Fields[i].FieldName; } [EditorBrowsable(EditorBrowsableState.Never)] @@ -387,14 +433,14 @@ { this.CheckIndex(i); - return this.command.Statement.RowDescriptor.Fields[i].DataType.Name; + return this.statement.RowDescriptor.Fields[i].DataType.Name; } public override Type GetFieldType(int i) { this.CheckIndex(i); - return this.command.Statement.RowDescriptor.Fields[i].DataType.SystemType; + return this.statement.RowDescriptor.Fields[i].DataType.SystemType; } public override object GetValue(int i) @@ -411,7 +457,7 @@ for (int i = 0; i < this.FieldCount; i++) { - values[i] = GetValue(i); + values[i] = this.GetValue(i); } return values.Length; @@ -426,7 +472,7 @@ for (int i = 0; i < this.FieldCount; i++) { - if (this.CultureAwareCompare(command.Statement.RowDescriptor.Fields[i].FieldName, name)) + if (this.CultureAwareCompare(this.statement.RowDescriptor.Fields[i].FieldName, name)) { return i; } @@ -725,9 +771,40 @@ #region \xB7 Private Methods \xB7 + private void Initialize() + { + if (this.connection.InternalConnection.HasActiveTransaction) + { + // Ref cursors can be fetched only if there are an active transaction + if (this.command.CommandType == CommandType.StoredProcedure && + this.command.Statement.RowDescriptor.Fields.Length == 1 && + this.command.Statement.RowDescriptor.Fields[0].DataType.IsRefCursor) + { + // Clear refcursor's queue + this.refCursors.Clear(); + + // Add refcrusr's names to the queue + object[] row = new object[0]; + + while (row != null) + { + row = this.statement.FetchRow(); + + if (row != null) + { + this.refCursors.Enqueue(row[0]); + } + } + + // Grab information of the first refcursor + this.NextResult(); + } + } + } + private void CheckIndex(int i) { - if (i < 0 || i >= this.fieldCount) + if (i < 0 || i >= this.FieldCount) { throw new IndexOutOfRangeException("Could not find specified column in results."); } @@ -770,7 +847,7 @@ throw new IndexOutOfRangeException("Could not find specified column in results."); } - return this.command.Statement.RowDescriptor.Fields[i].DataType.IsNumeric(); + return this.command.Statement.RowDescriptor.Fields[i].DataType.IsNumeric; } private bool IsLong(int i) @@ -780,7 +857,7 @@ throw new IndexOutOfRangeException("Could not find specified column in results."); } - return this.command.Statement.RowDescriptor.Fields[i].DataType.IsLong(); + return this.command.Statement.RowDescriptor.Fields[i].DataType.IsLong; } private bool IsAliased(int i) Modified: trunk/PostgreSqlClient/source/PostgreSql/Data/PostgreSqlClient/PgDbType.cs =================================================================== --- trunk/PostgreSqlClient/source/PostgreSql/Data/PostgreSqlClient/PgDbType.cs 2006-05-18 19:49:14 UTC (rev 144) +++ trunk/PostgreSqlClient/source/PostgreSql/Data/PostgreSqlClient/PgDbType.cs 2006-05-18 19:51:21 UTC (rev 145) @@ -44,6 +44,7 @@ Path , Point , Polygon , + Refcursor , Text , Time , TimeWithTZ , This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site. |
