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
35 // Copyright (C) 2004 Novell, Inc (http://www.novell.com)
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:
45 // The above copyright notice and this permission notice shall be
46 // included in all copies or substantial portions of the Software.
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.
59 using System.Data.SqlClient;
\r
61 namespace TestSystemDataSqlClient
\r
63 class TestSqlDataReader
\r
66 static void Test() {
\r
67 SqlConnection con = null;
\r
68 SqlCommand cmd = null;
\r
70 String connectionString = null;
\r
81 con = new SqlConnection(connectionString);
\r
84 // test SQL Query for an aggregate count(*)
\r
85 sql = "select count(*) " +
\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
92 // test SQL Query for an aggregate min(text)
\r
93 sql = "select max(tdesc) " +
\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
100 // test SQL Query for an aggregate max(text)
\r
101 sql = "select min(tdesc) " +
\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
108 // test SQL Query for an aggregate max(int)
\r
109 sql = "select min(aint4) " +
\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
116 // test SQL Query for an aggregate avg(int)
\r
117 sql = "select avg(aint4) " +
\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
124 // test SQL Query for an aggregate sum(int)
\r
125 sql = "select sum(aint4) " +
\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
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
142 Console.WriteLine("Result is not null. (not correct)");
\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
152 Console.WriteLine("Result is not null. (not correct)");
\r
155 catch(Exception e) {
\r
156 Console.WriteLine(e.ToString());
\r
160 if(con.State == ConnectionState.Open)
166 static void Main(string[] args)
\r