Execute database command object -- ADO. Net Learning & Application Note 3

The command class of the data provider is the implementation of the idbcommand interface. The command is used to execute database commands. The query, update, and insertion of database data are all implemented by command.
The command constructor generally has the following three forms:
1, public xxxcommand ();
2, public xxxcommand (string );
3, Public xxxcommand (string, xxxconnection );
Generally, the command object is created through a statement similar to the following:
Xxxconnection conn = new xxxconnection ("mystring ");
Xxxcommand mycmd = new xxxcommand ("select * from orders", Conn );
The two parameters of the constructor are SQL statements and connection objects, and the command object is created.

The following describes the execution of the command object. The program example uses the sqlclient data provider to access the database northwind of an SQL Server.

1. Set connection and SQL commands
The commandtext attribute of the command class is used to set command statements, and the connection attribute is used to set connection objects. You can set the data connection and command statement of the command object in addition to setting and changing these two attributes when creating the object through the constructor definition.
Sqlconnection conn = new sqlconnection ("Server = localhost; database = northwind; user id = sa; Pwd = sa ");
Conn. open ();
Sqlcommand cmd = new sqlcommand ("select * from [orders]", Conn );
Cmd. commandtext = "Delete [orders] Where [orderid] = 10248 ";

Ii. execute commands
After connecting to the data source and setting commands, the command object can execute SQL commands in three ways: executenonquery, executereader, and executescalar.
Executenonquery does not return the result set, but only returns the number of records affected by the statement. It is suitable for executing commands that do not return the result set, such as insert, update, and delete. If a SELECT statement is used, the returned result is-1. If a rollback occurs, the returned result is-1. The following program example updates and queries the orders table.

Using system;
Using system. Data;
Using system. Data. sqlclient;
Public class mydataaccess {
Public static void main (){
Sqlconnection conn = new sqlconnection ("Server = localhost; database = northwind; user id = sa; Pwd = sa ");
Sqlcommand cmd = new sqlcommand ("Update [orders] Set [orderdate] = '2017-9-1 'Where [orderid] = 2004", Conn );
Try {
Conn. open ();
Int I = cmd. executenonquery ();
Console. writeline (I. tostring () + "rows affected by Update ");
Cmd. commandtext = "select * from [orders]";
I = cmd. executenonquery ();
Console. writeline (I. tostring () + "rows affected by select ");
}
Catch (exception ex ){
Console. writeline (ex. Message );
}
Finally {
Conn. Close ();
}
}
}

A command executed using the executereader method can return the result set of a typed datareader instance or idatareader interface. The datareader object can be used to obtain the row set of data. This article will not discuss datareader in detail. We will describe the use of datareader in the future. The following is an example.
Using system;
Using system. Data;
Using system. Data. sqlclient;
Public class mydataaccess {
Public static void main (){
Sqlconnection conn = new sqlconnection ("Server = localhost; database = northwind; user id = sa; Pwd = sa ");
Sqlcommand cmd = new sqlcommand ("select top 20 * from [orders]", Conn );
Sqldatareader reader; // or idatareader reader;
Try {
Conn. open ();
Reader = cmd. executereader ();
While (reader. Read ()){
Console. writeline (Reader [0]. tostring ());
}
Reader. Close ();
}
Catch (exception ex ){
Console. writeline (ex. Message );
}
Finally {
Conn. Close ();
}
}
}

For the executereader method, if you want to obtain the number of record rows of data, you can use statements such as select count (*) to obtain an aggregate set of rows. The command object has a more efficient way for such statements to evaluate a single value-executescalar. It can return the objects (system. Object) corresponding to the first column of the First row. It is usually used to obtain the aggregate query results. Note that if you need to convert the returned results to a precise type, the database must forcibly convert the returned results in the query; otherwise, an exception is thrown. The following is an example:

Using system;
Using system. Data;
Using system. Data. sqlclient;
Public class mydataaccess {
Public static void main (){
Sqlconnection conn = new sqlconnection ("Server = localhost; database = northwind; user id = sa; Pwd = sa ");
Sqlcommand cmd = new sqlcommand ("select count (*) from [orders]", Conn );
Try {
Conn. open ();
Int I = (INT) cmd. executescalar ();
Console. writeline ("record num:" + I. tostring ());
Cmd. commandtext = "select cast (AVG ([freight]) as INT) from [orders]";
Int AVG = (INT) cmd. executescalar ();
Console. writeline ("AVG:" + avg. tostring ());
Cmd. commandtext = "select AVG ([freight]) from [orders]";
AVG = (INT) cmd. executescalar (); // raises an exception
Console. writeline ("AVG:" + avg. tostring ());
}
Catch (exception ex ){
Console. writeline (ex. Message );
}
Finally {
Conn. Close ();
}
}
}

In this program, the last query will cause an exception because the results returned by the aggregation are of the float type and cannot be converted.

Iii. parameterized Query
Parameterized query can optimize the performance, because the SQL statements with parameters only need to be analyzed once by the SQL Execution engine. The parameters of command can set the parameter values for parameterized queries. Parameters is a set of parameters that implement the idataparamtercollection interface.
Commands of different data providers use different parameters. sqlclient and oracleclient only support naming parameters in SQL statements, but do not support question mark placeholders. You must use naming parameters, the oledb and ODBC data providers only support question mark placeholders, but do not support named parameters.
For a query statement, sqlclient must use named parameters, similar to the following statement:
Select * from MERs where customerid = @ customerid -- the name parameter of Oracle is not preceded by @. Use (:) and write it as (: customerid)
What is required for oledb or ODBC? Placeholder, similar to the following statement:
Select * from MERs where customerid =?

The following uses SQL Server as an example to describe how to use it:
Using system;
Using system. Data;
Using system. Data. sqlclient;
Public class mydataaccess {
Public static void main (string [] ARGs ){
Sqlconnection conn = new sqlconnection ("Server = localhost; database = northwind; user id = sa; Pwd = sa ");
Sqlcommand cmd = new sqlcommand ("select * from [orders] Where [orderid] = @ oid", Conn );
Sqldatareader reader;
Try {
Int Param = convert. toint32 (ARGs [0]);
Cmd. Parameters. Add ("@ oid", Param); // use the named Parameter
Cmd. Parameters [0]. Direction = parameterdirection. input;
Conn. open ();
Reader = cmd. executereader ();
While (reader. Read ()){
Console. writeline (Reader [0]. tostring ());
}
Reader. Close ();
}
Catch (exception ex ){
Console. writeline (ex. Message );
}
Finally {
Conn. Close ();
}
}
}

For the command parameters of the oledb or ODBC data provider, you only need to match the parameters to the parameters set in the order of placeholders from left to right. The following is a program example:

Using system;
Using system. Data;
Using system. Data. oledb;
Public class mydataaccess {
Public static void main (string [] ARGs ){
Oledbconnection conn = new oledbconnection ("provider = sqloledb; server = localhost; database = northwind; user id = sa; Pwd = sa ");
Oledbcommand cmd = new oledbcommand ("select * from [orders] Where [orderid] =? Or [employeeid] =? ", Conn );
Oledbdatareader reader;
Try {
Int param1 = convert. toint32 (ARGs [0]);
Int param2 = convert. toint32 (ARGs [1]);
Cmd. Parameters. Add ("AAA", param1 );
Cmd. Parameters. Add ("BBB", param2); // The parameter object also needs a name, but it is not related to the parameter name in the query statement.
Cmd. Parameters [0]. Direction = parameterdirection. input;
Cmd. Parameters [1]. Direction = parameterdirection. input;
Conn. open ();
Reader = cmd. executereader ();
While (reader. Read ()){
Console. writeline (Reader [0]. tostring ());
}
Reader. Close ();
}
Catch (exception ex ){
Console. writeline (ex. Message );
}
Finally {
Conn. Close ();
}
}
}

4. Execute the Stored Procedure
You must specify the commandtype attribute when using the command object to access the database. This is a commandtype Enumeration type. By default, commandtype indicates that the commandtext command is SQL batch processing and commandtype. the storedprocedure value specifies that the executed command is a stored procedure. Similar to parameterized query, parameters of stored procedures can also be set using the parameters set, the direction attribute of the parameter object indicates whether the parameter can be input, output, bidirectional, or stored procedure return value.
Note that if executereader is used to return the result set of the stored procedure, output parameters cannot be used unless datareader is disabled. The following is an example:
Stored Procedure
---------------------------------------------
Create procedure myprotest (
@ Orderid as int,
@ Elytitle as varchar (50) Output
)
As
Select @ elytitle = Ely. Title from [orders] O join [employees] Ely on Ely. employeeid = O. employeeid where O. orderid = @ orderid
Select * from [orders] Where orderid = @ orderid
Return 1
---------------------------------------------

Program
---------------------------------------------
Using system;
Using system. Data;
Using system. Data. sqlclient;
Public class mydataaccess {
Public static void main (){
Sqlconnection conn = new sqlconnection ("Server = localhost; database = northwind; user id = sa; Pwd = sa ");
Sqlcommand cmd = new sqlcommand ("myprotest", Conn );
Cmd. commandtype = commandtype. storedprocedure;
Cmd. Parameters. Add ("@ orderid", 10252 );
Cmd. Parameters. Add ("@ elytitle", sqldbtype. varchar, 50 );
Cmd. Parameters. Add ("@ return", sqldbtype. INT );
Cmd. Parameters [0]. Direction = parameterdirection. input;
Cmd. Parameters [1]. Direction = parameterdirection. output;
Cmd. Parameters [2]. Direction = parameterdirection. returnvalue;
Sqldatareader reader;
Try {
Conn. open ();
Console. writeline ("execute reader ...");
Reader = cmd. executereader ();
Console. writeline ("@ orderid = {0}", cmd. Parameters [0]. value );
Console. writeline ("@ elytitle = {0}", cmd. Parameters [1]. value );
Console. writeline ("return = {0}", cmd. Parameters [2]. value );
Console. writeline ("Reader close ...");
Reader. Close ();
Console. writeline ("@ orderid = {0}", cmd. Parameters [0]. value );
Console. writeline ("@ elytitle = {0}", cmd. Parameters [1]. value );
Console. writeline ("return = {0}", cmd. Parameters [2]. value );
Console. writeline ("execute none query ...");
Cmd. executenonquery ();
Console. writeline ("@ orderid = {0}", cmd. Parameters [0]. value );
Console. writeline ("@ elytitle = {0}", cmd. Parameters [1]. value );
Console. writeline ("return = {0}", cmd. Parameters [2]. value );
}
Catch (exception ex ){
Console. writeline (ex. Message );
}
Finally {
Conn. Close ();
}
}
}

Like parameterized queries, oledb or ODBC data providers do not support Stored Procedure naming parameters. You need to match the parameters in the left-to-right order to the parameters set.