2 // Test/SqlDataReader.cs - to test Mono.Data.PostgreSqlClient/PgSqlDataReader.cs
\r
4 // Test to do read a simple forward read only record set.
\r
5 // Using PgSqlCommand.ExecuteReader() to return a PgSqlDataReader
\r
6 // which can be used to Read a row
\r
7 // and Get a String or Int32.
\r
10 // Daniel Morgan <danmorg@sc.rr.com>
\r
12 // (C) 2002 Daniel Morgan
\r
16 // Copyright (C) 2004 Novell, Inc (http://www.novell.com)
18 // Permission is hereby granted, free of charge, to any person obtaining
19 // a copy of this software and associated documentation files (the
20 // "Software"), to deal in the Software without restriction, including
21 // without limitation the rights to use, copy, modify, merge, publish,
22 // distribute, sublicense, and/or sell copies of the Software, and to
23 // permit persons to whom the Software is furnished to do so, subject to
24 // the following conditions:
26 // The above copyright notice and this permission notice shall be
27 // included in all copies or substantial portions of the Software.
29 // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
30 // EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
31 // MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
32 // NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE
33 // LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION
34 // OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION
35 // WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
40 using Mono.Data.PostgreSqlClient;
\r
42 namespace Test.Mono.Data.PostgreSqlClient {
\r
43 class TestPgSqlDataReader {
\r
45 static void Test(PgSqlConnection con, string sql,
\r
46 CommandType cmdType, CommandBehavior behavior,
\r
49 PgSqlCommand cmd = null;
\r
50 PgSqlDataReader rdr = null;
\r
55 Console.WriteLine("Test: " + testDesc);
\r
56 Console.WriteLine("[BEGIN SQL]");
\r
57 Console.WriteLine(sql);
\r
58 Console.WriteLine("[END SQL]");
\r
60 cmd = new PgSqlCommand(sql, con);
\r
61 cmd.CommandType = cmdType;
\r
63 Console.WriteLine("ExecuteReader...");
\r
64 rdr = cmd.ExecuteReader(behavior);
\r
68 Console.WriteLine("IDataReader has a Null Reference.");
73 // get the DataTable that holds
\r
75 DataTable dt = rdr.GetSchemaTable();
\r
77 if(rdr.RecordsAffected != -1) {
\r
79 // SQL INSERT, UPDATE, DELETE Commands
\r
80 // have RecordsAffected >= 0
\r
81 Console.WriteLine("Result is from a SQL Command (INSERT,UPDATE,DELETE). Records Affected: " + rdr.RecordsAffected);
\r
83 else if (dt == null)
\r
84 Console.WriteLine("Result is from a SQL Command not (INSERT,UPDATE,DELETE). Records Affected: " + rdr.RecordsAffected);
\r
87 // SQL not INSERT, UPDATE, nor DELETE
\r
88 // have RecordsAffected = -1
\r
89 Console.WriteLine("Result is from a SQL SELECT Query. Records Affected: " + rdr.RecordsAffected);
\r
91 // Results for a SQL Command (CREATE TABLE, SET, etc)
\r
92 // will have a null reference returned from GetSchemaTable()
\r
94 // Results for a SQL SELECT Query
\r
95 // will have a DataTable returned from GetSchemaTable()
\r
98 Console.WriteLine("Result Set " + results + "...");
\r
100 // number of columns in the table
\r
101 Console.WriteLine(" Total Columns: " +
\r
104 // display the schema
\r
105 foreach (DataRow schemaRow in dt.Rows) {
\r
106 foreach (DataColumn schemaCol in dt.Columns)
\r
107 Console.WriteLine(schemaCol.ColumnName +
\r
109 schemaRow[schemaCol]);
\r
110 Console.WriteLine();
\r
114 string output, metadataValue, dataValue;
\r
115 // Read and display the rows
\r
116 Console.WriteLine("Gonna do a Read() now...");
\r
117 while(rdr.Read()) {
\r
118 Console.WriteLine(" Row " + nRows + ": ");
\r
120 for(c = 0; c < rdr.FieldCount; c++) {
\r
121 // column meta data
\r
122 DataRow dr = dt.Rows[c];
\r
129 if(rdr.IsDBNull(c) == true)
\r
130 dataValue = " is NULL";
\r
136 // display column meta data and data
\r
137 output = metadataValue + dataValue;
\r
138 Console.WriteLine(output);
\r
142 Console.WriteLine(" Total Rows: " +
\r
145 } while(rdr.NextResult());
\r
146 Console.WriteLine("Total Result sets: " + results);
\r
154 static void Main(string[] args) {
\r
155 String connectionString = null;
\r
161 PgSqlConnection con;
\r
162 con = new PgSqlConnection(connectionString);
\r
167 // Text - only has one query (single query behavior)
\r
168 sql = "select * from pg_tables";
\r
169 Test(con, sql, CommandType.Text,
\r
170 CommandBehavior.SingleResult, "Text1");
\r
172 // Text - only has one query (default behavior)
\r
173 sql = "select * from pg_tables";
\r
174 Test(con, sql, CommandType.Text,
\r
175 CommandBehavior.Default, "Text2");
\r
177 // Text - has three queries
\r
179 "select * from pg_user;" +
\r
180 "select * from pg_tables;" +
\r
181 "select * from pg_database";
\r
182 Test(con, sql, CommandType.Text,
\r
183 CommandBehavior.Default, "Text3Queries");
\r
187 Test(con, sql, CommandType.TableDirect,
\r
188 CommandBehavior.Default, "TableDirect1");
\r
190 // Stored Procedure
\r
192 Test(con, sql, CommandType.StoredProcedure,
\r
193 CommandBehavior.Default, "SP1");
\r
195 // Text - test a SQL Command (default behavior)
\r
196 // Note: this not a SQL Query
\r
197 sql = "SET DATESTYLE TO 'ISO'";
\r
198 Test(con, sql, CommandType.Text,
\r
199 CommandBehavior.Default, "TextCmd1");
\r