mcs/class/System.Data/Test/TestExecuteScalar.cs

   1 //\r
   2 // Test/ExecuteScalar.cs\r
   3 //\r
   4 // Test the ExecuteScalar method in the \r
   5 // System.Data.SqlClient.SqlCommand class\r
   6 //\r
   7 // ExecuteScalar is meant to be lightweight\r
   8 // compared to ExecuteReader and only\r
   9 // returns one column and one row as one object.\r
  10 //\r
  11 // It is meant for SELECT SQL statements that\r
  12 // use an aggregate/group by function, such as,\r
  13 // count(), sum(), avg(), min(), max(), etc...\r
  14 // \r
  15 // The object that is returned you do an\r
  16 // explicit cast.  For instance, to retrieve a\r
  17 // Count of rows in a PostgreSQL table, you\r
  18 // would use "SELECT COUNT(*) FROM SOMETABLE"\r
  19 // which returns a number of oid type 20 which is \r
  20 // a PostgreSQL int8 which maps to \r
  21 // the .NET type System.Int64.  You\r
  22 // have to explicitly convert this returned object\r
  23 // to the type you are expecting, such as, an Int64\r
  24 // is returned for a COUNT().\r
  25 // would be:\r
  26 //      Int64 myCount = (Int64) cmd.ExecuteScalar(selectStatement);\r
  27 //\r
  28 // Author:\r
  29 //      Daniel Morgan <danmorg@sc.rr.com>\r
  30 //\r
  31 // (C) 2002 Daniel Morgan\r
  32 //\r
  33
  34 //
  35 // Copyright (C) 2004 Novell, Inc (http://www.novell.com)
  36 //
  37 // Permission is hereby granted, free of charge, to any person obtaining
  38 // a copy of this software and associated documentation files (the
  39 // "Software"), to deal in the Software without restriction, including
  40 // without limitation the rights to use, copy, modify, merge, publish,
  41 // distribute, sublicense, and/or sell copies of the Software, and to
  42 // permit persons to whom the Software is furnished to do so, subject to
  43 // the following conditions:
  44 //
  45 // The above copyright notice and this permission notice shall be
  46 // included in all copies or substantial portions of the Software.
  47 //
  48 // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
  49 // EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
  50 // MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
  51 // NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE
  52 // LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION
  53 // OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION
  54 // WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
  55 //
  56 \r
  57 using System;\r
  58 using System.Data;\r
  59 using System.Data.SqlClient;\r
  60 \r
  61 namespace TestSystemDataSqlClient\r
  62 {\r
  63         class TestSqlDataReader\r
  64         {\r
  65 \r
  66                 static void Test() { \r
  67                         SqlConnection con = null;\r
  68                         SqlCommand cmd = null;\r
  69                                                 \r
  70                         String connectionString = null;\r
  71                         String sql = null;\r
  72 \r
  73                         connectionString =
  74                                 "host=localhost;" +
  75                                 "dbname=test;" +
  76                                 "user=postgres";
  77                         \r
  78                         try {\r
  79                                 string maxStrValue;\r
  80 \r
  81                                 con = new SqlConnection(connectionString);\r
  82                                 con.Open();\r
  83 \r
  84                                 // test SQL Query for an aggregate count(*)\r
  85                                 sql =   "select count(*) " + \r
  86                                         "from sometable";\r
  87                                 cmd = new SqlCommand(sql,con);\r
  88                                 Console.WriteLine("Executing: " + sql);\r
  89                                 Int64 rowCount = (Int64) cmd.ExecuteScalar();\r
  90                                 Console.WriteLine("Row Count: " + rowCount);\r
  91 \r
  92                                 // test SQL Query for an aggregate min(text)\r
  93                                 sql =   "select max(tdesc) " + \r
  94                                         "from sometable";\r
  95                                 cmd = new SqlCommand(sql,con);\r
  96                                 Console.WriteLine("Executing: " + sql);\r
  97                                 string minValue = (string) cmd.ExecuteScalar();\r
  98                                 Console.WriteLine("Max Value: " + minValue);\r
  99 \r
 100                                 // test SQL Query for an aggregate max(text)\r
 101                                 sql =   "select min(tdesc) " + \r
 102                                         "from sometable";\r
 103                                 cmd = new SqlCommand(sql,con);\r
 104                                 Console.WriteLine("Executing: " + sql);\r
 105                                 maxStrValue = (string) cmd.ExecuteScalar();\r
 106                                 Console.WriteLine("Max Value: " + maxStrValue);\r
 107 \r
 108                                 // test SQL Query for an aggregate max(int)\r
 109                                 sql =   "select min(aint4) " + \r
 110                                         "from sometable";\r
 111                                 cmd = new SqlCommand(sql,con);\r
 112                                 Console.WriteLine("Executing: " + sql);\r
 113                                 int maxIntValue = (int) cmd.ExecuteScalar();\r
 114                                 Console.WriteLine("Max Value: " + maxIntValue.ToString());\r
 115 \r
 116                                 // test SQL Query for an aggregate avg(int)\r
 117                                 sql =   "select avg(aint4) " + \r
 118                                         "from sometable";\r
 119                                 cmd = new SqlCommand(sql,con);\r
 120                                 Console.WriteLine("Executing: " + sql);\r
 121                                 decimal avgDecValue = (decimal) cmd.ExecuteScalar();\r
 122                                 Console.WriteLine("Max Value: " + avgDecValue.ToString());\r
 123 \r
 124                                 // test SQL Query for an aggregate sum(int)\r
 125                                 sql =   "select sum(aint4) " + \r
 126                                         "from sometable";\r
 127                                 cmd = new SqlCommand(sql,con);\r
 128                                 Console.WriteLine("Executing: " + sql);\r
 129                                 Int64 summed = (Int64) cmd.ExecuteScalar();\r
 130                                 Console.WriteLine("Max Value: " + summed);\r
 131 \r
 132                                 // test a SQL Command is (INSERT, UPDATE, DELETE)\r
 133                                 sql =   "insert into sometable " +\r
 134                                         "(tid,tdesc,aint4,atimestamp) " +\r
 135                                         "values('qqq','www',234,NULL)";\r
 136                                 cmd = new SqlCommand(sql,con);\r
 137                                 Console.WriteLine("Executing: " + sql);\r
 138                                 object objResult1 = cmd.ExecuteScalar();\r
 139                                 if(objResult1 == null)\r
 140                                         Console.WriteLine("Result is null. (correct)");\r
 141                                 else\r
 142                                         Console.WriteLine("Result is not null. (not correct)");\r
 143 \r
 144                                 // test a SQL Command is not (INSERT, UPDATE, DELETE)\r
 145                                 sql =   "SET DATESTYLE TO 'ISO'";\r
 146                                 cmd = new SqlCommand(sql,con);\r
 147                                 Console.WriteLine("Executing: " + sql);\r
 148                                 object objResult2 = cmd.ExecuteScalar();\r
 149                                 if(objResult2 == null)\r
 150                                         Console.WriteLine("Result is null. (correct)");\r
 151                                 else\r
 152                                         Console.WriteLine("Result is not null. (not correct)");\r
 153 \r
 154                         }\r
 155                         catch(Exception e) {\r
 156                                 Console.WriteLine(e.ToString());\r
 157                         }\r
 158                         finally {\r
 159                                 if(con != null)
 160                                         if(con.State == ConnectionState.Open)
 161                                                 con.Close();\r
 162                         }\r
 163                 }\r
 164 \r
 165                 [STAThread]\r
 166                 static void Main(string[] args)\r
 167                 {\r
 168                         Test();\r
 169                 }\r
 170 \r
 171         }\r
 172 }\r