2 // Test/ExecuteScalar.cs
\r
4 // Test the ExecuteScalar method in the
\r
5 // System.Data.SqlClient.SqlCommand class
\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
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
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
26 // Int64 myCount = (Int64) cmd.ExecuteScalar(selectStatement);
\r
29 // Daniel Morgan <danmorg@sc.rr.com>
\r
31 // (C) 2002 Daniel Morgan
\r
36 using System.Data.SqlClient;
\r
38 namespace TestSystemDataSqlClient
\r
40 class TestSqlDataReader
\r
43 static void Test() {
\r
44 SqlConnection con = null;
\r
45 SqlCommand cmd = null;
\r
47 String connectionString = null;
\r
58 con = new SqlConnection(connectionString);
\r
61 // test SQL Query for an aggregate count(*)
\r
62 sql = "select count(*) " +
\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
69 // test SQL Query for an aggregate min(text)
\r
70 sql = "select max(tdesc) " +
\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
77 // test SQL Query for an aggregate max(text)
\r
78 sql = "select min(tdesc) " +
\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
85 // test SQL Query for an aggregate max(int)
\r
86 sql = "select min(aint4) " +
\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
93 // test SQL Query for an aggregate avg(int)
\r
94 sql = "select avg(aint4) " +
\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
101 // test SQL Query for an aggregate sum(int)
\r
102 sql = "select sum(aint4) " +
\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
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
119 Console.WriteLine("Result is not null. (not correct)");
\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
129 Console.WriteLine("Result is not null. (not correct)");
\r
132 catch(Exception e) {
\r
133 Console.WriteLine(e.ToString());
\r
137 if(con.State == ConnectionState.Open)
143 static void Main(string[] args)
\r