static void Test(PgSqlConnection con, string sql, CommandType cmdType, CommandBehavior behavior, string testDesc) { PgSqlCommand cmd = null; PgSqlDataReader rdr = null; int c; int results = 0; Console.WriteLine("Test: " + testDesc); Console.WriteLine("[BEGIN SQL]"); Console.WriteLine(sql); Console.WriteLine("[END SQL]"); cmd = new PgSqlCommand(sql, con); cmd.CommandType = cmdType; Console.WriteLine("ExecuteReader..."); rdr = cmd.ExecuteReader(behavior); if (rdr == null) { Console.WriteLine("IDataReader has a Null Reference."); } else { do { // get the DataTable that holds // the schema DataTable dt = rdr.GetSchemaTable(); if (rdr.RecordsAffected != -1) { // Results for // SQL INSERT, UPDATE, DELETE Commands // have RecordsAffected >= 0 Console.WriteLine("Result is from a SQL Command (INSERT,UPDATE,DELETE). Records Affected: " + rdr.RecordsAffected); } else if (dt == null) { Console.WriteLine("Result is from a SQL Command not (INSERT,UPDATE,DELETE). Records Affected: " + rdr.RecordsAffected); } else { // Results for // SQL not INSERT, UPDATE, nor DELETE // have RecordsAffected = -1 Console.WriteLine("Result is from a SQL SELECT Query. Records Affected: " + rdr.RecordsAffected); // Results for a SQL Command (CREATE TABLE, SET, etc) // will have a null reference returned from GetSchemaTable() // // Results for a SQL SELECT Query // will have a DataTable returned from GetSchemaTable() results++; Console.WriteLine("Result Set " + results + "..."); // number of columns in the table Console.WriteLine(" Total Columns: " + dt.Columns.Count); // display the schema foreach (DataRow schemaRow in dt.Rows) { foreach (DataColumn schemaCol in dt.Columns) { Console.WriteLine(schemaCol.ColumnName + " = " + schemaRow[schemaCol]); } Console.WriteLine(); } int nRows = 0; string output, metadataValue, dataValue; // Read and display the rows Console.WriteLine("Gonna do a Read() now..."); while (rdr.Read()) { Console.WriteLine(" Row " + nRows + ": "); for (c = 0; c < rdr.FieldCount; c++) { // column meta data DataRow dr = dt.Rows[c]; metadataValue = " Col " + c + ": " + dr["ColumnName"]; // column data if (rdr.IsDBNull(c) == true) { dataValue = " is NULL"; } else { dataValue = ": " + rdr.GetValue(c); } // display column meta data and data output = metadataValue + dataValue; Console.WriteLine(output); } nRows++; } Console.WriteLine(" Total Rows: " + nRows); } } while(rdr.NextResult()); Console.WriteLine("Total Result sets: " + results); rdr.Close(); } }
private bool GetRd(String strSQL) { int results = 0; int c; try { connPG.Open(); cmdPG.CommandText = strSQL;// "select * from users"; cmdPG.Connection = connPG; rdr = cmdPG.ExecuteReader(); if (rdr == null) { Console.WriteLine("IDataReader has a Null Reference."); } else { do { // get the DataTable that holds // the schema DataTable dt = rdr.GetSchemaTable(); DataTable dtData = new DataTable(); DataView dv = new DataView(); dv.Table = dt; if (rdr.RecordsAffected != -1) { // Results for // SQL INSERT, UPDATE, DELETE Commands // have RecordsAffected >= 0 System.Diagnostics.Debug.WriteLine("Result is from a SQL Command (INSERT,UPDATE,DELETE). Records Affected: " + rdr.RecordsAffected); } else if (dt == null) { System.Diagnostics.Debug.WriteLine("Result is from a SQL Command not (INSERT,UPDATE,DELETE). Records Affected: " + rdr.RecordsAffected); } else { // Results for // SQL not INSERT, UPDATE, nor DELETE // have RecordsAffected = -1 System.Diagnostics.Debug.WriteLine("Result is from a SQL SELECT Query. Records Affected: " + rdr.RecordsAffected); // Results for a SQL Command (CREATE TABLE, SET, etc) // will have a null reference returned from GetSchemaTable() // // Results for a SQL SELECT Query // will have a DataTable returned from GetSchemaTable() results++; System.Diagnostics.Debug.WriteLine("Result Set " + results + "..."); // number of columns in the table System.Diagnostics.Debug.WriteLine(" Total Columns: " + dt.Columns.Count); // display the schema foreach (DataRow schemaRow in dt.Rows) { foreach (DataColumn schemaCol in dt.Columns) { System.Diagnostics.Debug.WriteLine(schemaCol.ColumnName + " = " + schemaRow[schemaCol]); } // System.Diagnostics.Debug.WriteLine(); } int nRows = 0; string output, metadataValue, dataValue; // Read and display the rows System.Diagnostics.Debug.WriteLine("Gonna do a Read() now..."); while (rdr.Read()) { System.Diagnostics.Debug.WriteLine(" Row " + nRows + ": "); for (c = 0; c < rdr.FieldCount; c++) { // column meta data DataRow dr = dt.Rows[c]; metadataValue = " Col " + c + ": " + dr["ColumnName"]; // column data if (rdr.IsDBNull(c) == true) { dataValue = " is NULL"; } else { dataValue = ": " + rdr.GetValue(c); } // display column meta data and data output = metadataValue + dataValue; System.Diagnostics.Debug.WriteLine(output); } nRows++; } System.Diagnostics.Debug.WriteLine(" Total Rows: " + nRows); } } while (rdr.NextResult()); Console.WriteLine("Total Result sets: " + results); rdr.Close(); connPG.Close(); } getMessage = "Успешно!"; return(true); } catch (Exception ex) { getMessage = "Неудачно!" + ex; return(false); } }
private void btExecute_Click(object sender, System.EventArgs e) { const int len = 10; int recCount = 0; PgSqlCommand cursorCmd = new PgSqlCommand("fetch all in \"dept\"", pgSqlCommand.Connection); // need to start transaction, because cursor is accessible only in transaction PgSqlTransaction tr = pgSqlCommand.Connection.BeginTransaction(); try { // executes stored procedure that opens cursor pgSqlCommand.ExecuteScalar(); // fetch data from named cursor PgSqlDataReader dataReader = cursorCmd.ExecuteReader(); if (dataReader.FieldCount > 0) { for (int i = 0; i < dataReader.FieldCount; i++) { tbResult.AppendText(dataReader.GetName(i).PadRight(len).Substring(0, len) + " "); } tbResult.AppendText("\r\n"); for (int i = 0; i < dataReader.FieldCount; i++) { tbResult.AppendText(String.Empty.PadRight(len, '-').Substring(0, len) + " "); } tbResult.AppendText("\r\n"); while (dataReader.Read()) { for (int i = 0; i < dataReader.FieldCount; i++) { tbResult.AppendText(dataReader.GetValue(i).ToString().PadRight(len).Substring(0, len) + " "); } tbResult.AppendText("\r\n"); recCount++; } tbResult.AppendText("\r\n"); tbResult.AppendText(recCount.ToString() + " rows selected.\r\n"); } else { tbResult.AppendText("Statement executed.\r\n"); } tbResult.AppendText("\r\n"); dataReader.Close(); // commit transaction tr.Commit(); } catch (PgSqlException exception) { // rollback transaction on error tr.Rollback(); tbResult.AppendText(exception.Message + "\r\n\r\n"); throw; } }