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;
}
}
