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 \r
  34 using System;\r
  35 using System.Data;\r
  36 using System.Data.SqlClient;\r
  37 \r
  38 namespace TestSystemDataSqlClient\r
  39 {\r
  40         class TestSqlDataReader\r
  41         {\r
  42 \r
  43                 static void Test() { \r
  44                         SqlConnection con = null;\r
  45                         SqlCommand cmd = null;\r
  46                                                 \r
  47                         String connectionString = null;\r
  48                         String sql = null;\r
  49 \r
  50                         connectionString =
  51                                 "host=localhost;" +
  52                                 "dbname=test;" +
  53                                 "user=postgres";
  54                         \r
  55                         try {\r
  56                                 string maxStrValue;\r
  57 \r
  58                                 con = new SqlConnection(connectionString);\r
  59                                 con.Open();\r
  60 \r
  61                                 // test SQL Query for an aggregate count(*)\r
  62                                 sql =   "select count(*) " + \r
  63                                         "from sometable";\r
  64                                 cmd = new SqlCommand(sql,con);\r
  65                                 Console.WriteLine("Executing: " + sql);\r
  66                                 Int64 rowCount = (Int64) cmd.ExecuteScalar();\r
  67                                 Console.WriteLine("Row Count: " + rowCount);\r
  68 \r
  69                                 // test SQL Query for an aggregate min(text)\r
  70                                 sql =   "select max(tdesc) " + \r
  71                                         "from sometable";\r
  72                                 cmd = new SqlCommand(sql,con);\r
  73                                 Console.WriteLine("Executing: " + sql);\r
  74                                 string minValue = (string) cmd.ExecuteScalar();\r
  75                                 Console.WriteLine("Max Value: " + minValue);\r
  76 \r
  77                                 // test SQL Query for an aggregate max(text)\r
  78                                 sql =   "select min(tdesc) " + \r
  79                                         "from sometable";\r
  80                                 cmd = new SqlCommand(sql,con);\r
  81                                 Console.WriteLine("Executing: " + sql);\r
  82                                 maxStrValue = (string) cmd.ExecuteScalar();\r
  83                                 Console.WriteLine("Max Value: " + maxStrValue);\r
  84 \r
  85                                 // test SQL Query for an aggregate max(int)\r
  86                                 sql =   "select min(aint4) " + \r
  87                                         "from sometable";\r
  88                                 cmd = new SqlCommand(sql,con);\r
  89                                 Console.WriteLine("Executing: " + sql);\r
  90                                 int maxIntValue = (int) cmd.ExecuteScalar();\r
  91                                 Console.WriteLine("Max Value: " + maxIntValue.ToString());\r
  92 \r
  93                                 // test SQL Query for an aggregate avg(int)\r
  94                                 sql =   "select avg(aint4) " + \r
  95                                         "from sometable";\r
  96                                 cmd = new SqlCommand(sql,con);\r
  97                                 Console.WriteLine("Executing: " + sql);\r
  98                                 decimal avgDecValue = (decimal) cmd.ExecuteScalar();\r
  99                                 Console.WriteLine("Max Value: " + avgDecValue.ToString());\r
 100 \r
 101                                 // test SQL Query for an aggregate sum(int)\r
 102                                 sql =   "select sum(aint4) " + \r
 103                                         "from sometable";\r
 104                                 cmd = new SqlCommand(sql,con);\r
 105                                 Console.WriteLine("Executing: " + sql);\r
 106                                 Int64 summed = (Int64) cmd.ExecuteScalar();\r
 107                                 Console.WriteLine("Max Value: " + summed);\r
 108 \r
 109                                 // test a SQL Command is (INSERT, UPDATE, DELETE)\r
 110                                 sql =   "insert into sometable " +\r
 111                                         "(tid,tdesc,aint4,atimestamp) " +\r
 112                                         "values('qqq','www',234,NULL)";\r
 113                                 cmd = new SqlCommand(sql,con);\r
 114                                 Console.WriteLine("Executing: " + sql);\r
 115                                 object objResult1 = cmd.ExecuteScalar();\r
 116                                 if(objResult1 == null)\r
 117                                         Console.WriteLine("Result is null. (correct)");\r
 118                                 else\r
 119                                         Console.WriteLine("Result is not null. (not correct)");\r
 120 \r
 121                                 // test a SQL Command is not (INSERT, UPDATE, DELETE)\r
 122                                 sql =   "SET DATESTYLE TO 'ISO'";\r
 123                                 cmd = new SqlCommand(sql,con);\r
 124                                 Console.WriteLine("Executing: " + sql);\r
 125                                 object objResult2 = cmd.ExecuteScalar();\r
 126                                 if(objResult2 == null)\r
 127                                         Console.WriteLine("Result is null. (correct)");\r
 128                                 else\r
 129                                         Console.WriteLine("Result is not null. (not correct)");\r
 130 \r
 131                         }\r
 132                         catch(Exception e) {\r
 133                                 Console.WriteLine(e.ToString());\r
 134                         }\r
 135                         finally {\r
 136                                 if(con != null)
 137                                         if(con.State == ConnectionState.Open)
 138                                                 con.Close();\r
 139                         }\r
 140                 }\r
 141 \r
 142                 [STAThread]\r
 143                 static void Main(string[] args)\r
 144                 {\r
 145                         Test();\r
 146                 }\r
 147 \r
 148         }\r
 149 }\r