//\r
-// Test/SqlDataRead.cs\r
+// Test/SqlDataReader.cs - to test Mono.Data.PostgreSqlClient/PgSqlDataReader.cs\r
//\r
// Test to do read a simple forward read only record set.\r
-// Using SqlCommand.ExecuteReader() to return a SqlDataReader\r
+// Using PgSqlCommand.ExecuteReader() to return a PgSqlDataReader\r
// which can be used to Read a row\r
// and Get a String or Int32.\r
//\r
//\r
// (C) 2002 Daniel Morgan\r
//\r
+
+//
+// Copyright (C) 2004 Novell, Inc (http://www.novell.com)
+//
+// Permission is hereby granted, free of charge, to any person obtaining
+// a copy of this software and associated documentation files (the
+// "Software"), to deal in the Software without restriction, including
+// without limitation the rights to use, copy, modify, merge, publish,
+// distribute, sublicense, and/or sell copies of the Software, and to
+// permit persons to whom the Software is furnished to do so, subject to
+// the following conditions:
+//
+// The above copyright notice and this permission notice shall be
+// included in all copies or substantial portions of the Software.
+//
+// THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
+// EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
+// MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
+// NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE
+// LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION
+// OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION
+// WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
+//
\r
using System;\r
using System.Data;\r
-using System.Data.SqlClient;\r
+using Mono.Data.PostgreSqlClient;\r
\r
-namespace TestSystemDataSqlClient\r
-{\r
- class TestSqlDataReader\r
- {\r
+namespace Test.Mono.Data.PostgreSqlClient {\r
+ class TestPgSqlDataReader {\r
\r
- static void Test() { \r
- SqlConnection con = null;\r
- SqlCommand cmd = null;\r
- SqlDataReader rdr = null;\r
+ static void Test(PgSqlConnection con, string sql, \r
+ CommandType cmdType, CommandBehavior behavior,\r
+ string testDesc) \r
+ { \r
+ PgSqlCommand cmd = null;\r
+ PgSqlDataReader rdr = null;\r
\r
- String connectionString = null;\r
- String sql = null;\r
+ int c;\r
+ int results = 0;\r
+\r
+ Console.WriteLine("Test: " + testDesc);\r
+ Console.WriteLine("[BEGIN SQL]");\r
+ Console.WriteLine(sql);\r
+ Console.WriteLine("[END SQL]");\r
+\r
+ cmd = new PgSqlCommand(sql, con);\r
+ cmd.CommandType = cmdType;\r
+ \r
+ Console.WriteLine("ExecuteReader...");\r
+ rdr = cmd.ExecuteReader(behavior);\r
+\r
+ if(rdr == null) {
+
+ Console.WriteLine("IDataReader has a Null Reference.");
+ }
+ else {
+\r
+ do {\r
+ // get the DataTable that holds\r
+ // the schema\r
+ DataTable dt = rdr.GetSchemaTable();\r
+\r
+ if(rdr.RecordsAffected != -1) {\r
+ // Results for \r
+ // SQL INSERT, UPDATE, DELETE Commands \r
+ // have RecordsAffected >= 0\r
+ Console.WriteLine("Result is from a SQL Command (INSERT,UPDATE,DELETE). Records Affected: " + rdr.RecordsAffected);\r
+ }\r
+ else if (dt == null)\r
+ Console.WriteLine("Result is from a SQL Command not (INSERT,UPDATE,DELETE). Records Affected: " + rdr.RecordsAffected);\r
+ else {\r
+ // Results for\r
+ // SQL not INSERT, UPDATE, nor DELETE\r
+ // have RecordsAffected = -1\r
+ Console.WriteLine("Result is from a SQL SELECT Query. Records Affected: " + rdr.RecordsAffected);\r
+ \r
+ // Results for a SQL Command (CREATE TABLE, SET, etc)\r
+ // will have a null reference returned from GetSchemaTable()\r
+ // \r
+ // Results for a SQL SELECT Query\r
+ // will have a DataTable returned from GetSchemaTable()\r
+\r
+ results++;\r
+ Console.WriteLine("Result Set " + results + "...");\r
+ \r
+ // number of columns in the table\r
+ Console.WriteLine(" Total Columns: " +\r
+ dt.Columns.Count);\r
+\r
+ // display the schema\r
+ foreach (DataRow schemaRow in dt.Rows) {\r
+ foreach (DataColumn schemaCol in dt.Columns)\r
+ Console.WriteLine(schemaCol.ColumnName + \r
+ " = " + \r
+ schemaRow[schemaCol]);\r
+ Console.WriteLine();\r
+ }\r
+\r
+ int nRows = 0;\r
+ string output, metadataValue, dataValue;\r
+ // Read and display the rows\r
+ Console.WriteLine("Gonna do a Read() now...");\r
+ while(rdr.Read()) {\r
+ Console.WriteLine(" Row " + nRows + ": ");\r
+ \r
+ for(c = 0; c < rdr.FieldCount; c++) {\r
+ // column meta data \r
+ DataRow dr = dt.Rows[c];\r
+ metadataValue = \r
+ " Col " + \r
+ c + ": " + \r
+ dr["ColumnName"];\r
+ \r
+ // column data\r
+ if(rdr.IsDBNull(c) == true)\r
+ dataValue = " is NULL";\r
+ else\r
+ dataValue = \r
+ ": " + \r
+ rdr.GetValue(c);\r
+ \r
+ // display column meta data and data\r
+ output = metadataValue + dataValue; \r
+ Console.WriteLine(output);\r
+ }\r
+ nRows++;\r
+ }\r
+ Console.WriteLine(" Total Rows: " + \r
+ nRows);\r
+ } \r
+ } while(rdr.NextResult());\r
+ Console.WriteLine("Total Result sets: " + results);\r
+ \r
+ rdr.Close();\r
+ }\r
+ \r
+ }\r
\r
+ [STAThread]\r
+ static void Main(string[] args) {\r
+ String connectionString = null;\r
connectionString =
"host=localhost;" +
"dbname=test;" +
"user=postgres";
- \r
- sql = "select tid, tdesc, aint4, abpchar " + \r
- "from sometable";\r
- \r
- con = new SqlConnection(connectionString);\r
+ \r
+ PgSqlConnection con;\r
+ con = new PgSqlConnection(connectionString);\r
con.Open();\r
\r
- Console.WriteLine("sql: " +\r
- sql);\r
+ string sql;\r
\r
- cmd = new SqlCommand(sql, con);\r
- rdr = cmd.ExecuteReader();\r
+ // Text - only has one query (single query behavior)\r
+ sql = "select * from pg_tables";\r
+ Test(con, sql, CommandType.Text, \r
+ CommandBehavior.SingleResult, "Text1");\r
\r
- // get the DataTable that holds\r
- // the schema\r
- DataTable dt = rdr.GetSchemaTable();\r
+ // Text - only has one query (default behavior)\r
+ sql = "select * from pg_tables";\r
+ Test(con, sql, CommandType.Text, \r
+ CommandBehavior.Default, "Text2");\r
\r
- // number of columns in the table\r
- Console.WriteLine("dt.Columns.Count: " +\r
- dt.Columns.Count);\r
-\r
- // display the schema\r
- for(int c = 0; c < dt.Columns.Count; c++) {\r
- Console.WriteLine("* Column Name: " + \r
- dt.Columns[c].ColumnName);\r
- Console.WriteLine(" MaxLength: " +\r
- dt.Columns[c].MaxLength);\r
- Console.WriteLine(" Type: " +\r
- dt.Columns[c].DataType);\r
- }\r
- int nRows = 0;\r
- // Read and display the rows\r
- while(rdr.Read()) {\r
- Console.WriteLine("Row: " +\r
- rdr["tid"].ToString() + ", " + \r
- rdr["tdesc"].ToString() + ", " + \r
- rdr["aint4"].ToString() + ", " + \r
- rdr["abpchar"].ToString()\r
- );\r
-\r
- Console.WriteLine("1: " + rdr.GetString(0));\r
- Console.WriteLine("2: " + rdr.GetString(1));\r
- Console.WriteLine("3: " + rdr.GetInt32(2));\r
- Console.WriteLine("4: " + rdr.GetString(3));\r
- nRows++;\r
- }\r
- Console.WriteLine("Rows: " + \r
- nRows);\r
+ // Text - has three queries\r
+ sql =\r
+ "select * from pg_user;" + \r
+ "select * from pg_tables;" + \r
+ "select * from pg_database";\r
+ Test(con, sql, CommandType.Text, \r
+ CommandBehavior.Default, "Text3Queries");\r
\r
- rdr.Close();\r
- con.Close();\r
- }\r
+ // Table Direct\r
+ sql = "pg_tables";\r
+ Test(con, sql, CommandType.TableDirect, \r
+ CommandBehavior.Default, "TableDirect1");\r
\r
- [STAThread]\r
- static void Main(string[] args)\r
- {\r
- Test();\r
- }\r
+ // Stored Procedure\r
+ sql = "version";\r
+ Test(con, sql, CommandType.StoredProcedure, \r
+ CommandBehavior.Default, "SP1");\r
\r
+ // Text - test a SQL Command (default behavior)\r
+ // Note: this not a SQL Query\r
+ sql = "SET DATESTYLE TO 'ISO'";\r
+ Test(con, sql, CommandType.Text, \r
+ CommandBehavior.Default, "TextCmd1");\r
+\r
+ con.Close();\r
+ }\r
}\r
}\r