class/System.Data/Test/TestExecuteScalar.cs

   1 //
   2 // Test/ExecuteScalar.cs
   3 //
   4 // Test the ExecuteScalar method in the
   5 // System.Data.SqlClient.SqlCommand class
   6 //
   7 // ExecuteScalar is meant to be lightweight
   8 // compared to ExecuteReader and only
   9 // returns one column and one row as one object.
  10 //
  11 // It is meant for SELECT SQL statements that
  12 // use an aggregate/group by function, such as,
  13 // count(), sum(), avg(), min(), max(), etc...
  14 //
  15 // The object that is returned you do an
  16 // explicit cast.  For instance, to retrieve a
  17 // Count of rows in a PostgreSQL table, you
  18 // would use "SELECT COUNT(*) FROM SOMETABLE"
  19 // which returns a number of oid type 20 which is
  20 // a PostgreSQL int8 which maps to
  21 // the .NET type System.Int64.  You
  22 // have to explicitly convert this returned object
  23 // to the type you are expecting, such as, an Int64
  24 // is returned for a COUNT().
  25 // would be:
  26 //      Int64 myCount = (Int64) cmd.ExecuteScalar(selectStatement);
  27 //
  28 // Author:
  29 //      Daniel Morgan <danmorg@sc.rr.com>
  30 //
  31 // (C) 2002 Daniel Morgan
  32 //
  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
  57 using System;
  58 using System.Data;
  59 using System.Data.SqlClient;
  60
  61 namespace TestSystemDataSqlClient
  62 {
  63         class TestSqlDataReader
  64         {
  65
  66                 static void Test() {
  67                         SqlConnection con = null;
  68                         SqlCommand cmd = null;
  69
  70                         String connectionString = null;
  71                         String sql = null;
  72
  73                         connectionString =
  74                                 "host=localhost;" +
  75                                 "dbname=test;" +
  76                                 "user=postgres";
  77
  78                         try {
  79                                 string maxStrValue;
  80
  81                                 con = new SqlConnection(connectionString);
  82                                 con.Open();
  83
  84                                 // test SQL Query for an aggregate count(*)
  85                                 sql =   "select count(*) " +
  86                                         "from sometable";
  87                                 cmd = new SqlCommand(sql,con);
  88                                 Console.WriteLine("Executing: " + sql);
  89                                 Int64 rowCount = (Int64) cmd.ExecuteScalar();
  90                                 Console.WriteLine("Row Count: " + rowCount);
  91
  92                                 // test SQL Query for an aggregate min(text)
  93                                 sql =   "select max(tdesc) " +
  94                                         "from sometable";
  95                                 cmd = new SqlCommand(sql,con);
  96                                 Console.WriteLine("Executing: " + sql);
  97                                 string minValue = (string) cmd.ExecuteScalar();
  98                                 Console.WriteLine("Max Value: " + minValue);
  99
 100                                 // test SQL Query for an aggregate max(text)
 101                                 sql =   "select min(tdesc) " +
 102                                         "from sometable";
 103                                 cmd = new SqlCommand(sql,con);
 104                                 Console.WriteLine("Executing: " + sql);
 105                                 maxStrValue = (string) cmd.ExecuteScalar();
 106                                 Console.WriteLine("Max Value: " + maxStrValue);
 107
 108                                 // test SQL Query for an aggregate max(int)
 109                                 sql =   "select min(aint4) " +
 110                                         "from sometable";
 111                                 cmd = new SqlCommand(sql,con);
 112                                 Console.WriteLine("Executing: " + sql);
 113                                 int maxIntValue = (int) cmd.ExecuteScalar();
 114                                 Console.WriteLine("Max Value: " + maxIntValue.ToString());
 115
 116                                 // test SQL Query for an aggregate avg(int)
 117                                 sql =   "select avg(aint4) " +
 118                                         "from sometable";
 119                                 cmd = new SqlCommand(sql,con);
 120                                 Console.WriteLine("Executing: " + sql);
 121                                 decimal avgDecValue = (decimal) cmd.ExecuteScalar();
 122                                 Console.WriteLine("Max Value: " + avgDecValue.ToString());
 123
 124                                 // test SQL Query for an aggregate sum(int)
 125                                 sql =   "select sum(aint4) " +
 126                                         "from sometable";
 127                                 cmd = new SqlCommand(sql,con);
 128                                 Console.WriteLine("Executing: " + sql);
 129                                 Int64 summed = (Int64) cmd.ExecuteScalar();
 130                                 Console.WriteLine("Max Value: " + summed);
 131
 132                                 // test a SQL Command is (INSERT, UPDATE, DELETE)
 133                                 sql =   "insert into sometable " +
 134                                         "(tid,tdesc,aint4,atimestamp) " +
 135                                         "values('qqq','www',234,NULL)";
 136                                 cmd = new SqlCommand(sql,con);
 137                                 Console.WriteLine("Executing: " + sql);
 138                                 object objResult1 = cmd.ExecuteScalar();
 139                                 if(objResult1 == null)
 140                                         Console.WriteLine("Result is null. (correct)");
 141                                 else
 142                                         Console.WriteLine("Result is not null. (not correct)");
 143
 144                                 // test a SQL Command is not (INSERT, UPDATE, DELETE)
 145                                 sql =   "SET DATESTYLE TO 'ISO'";
 146                                 cmd = new SqlCommand(sql,con);
 147                                 Console.WriteLine("Executing: " + sql);
 148                                 object objResult2 = cmd.ExecuteScalar();
 149                                 if(objResult2 == null)
 150                                         Console.WriteLine("Result is null. (correct)");
 151                                 else
 152                                         Console.WriteLine("Result is not null. (not correct)");
 153
 154                         }
 155                         catch(Exception e) {
 156                                 Console.WriteLine(e.ToString());
 157                         }
 158                         finally {
 159                                 if(con != null)
 160                                         if(con.State == ConnectionState.Open)
 161                                                 con.Close();
 162                         }
 163                 }
 164
 165                 [STAThread]
 166                 static void Main(string[] args)
 167                 {
 168                         Test();
 169                 }
 170
 171         }
 172 }