The Command class of the data provider is the implementation of the IDBCommand interface, performs database commands through Command, and the query, update, and insert are implemented through Command. The constructor of Command usually has three forms: 1, public xxxcommand (); 2, public xxxcommand (string); 3, public xxxcommand (string, xxxconnection); usually we create a Command object through the following statement Implementation: xxxConnection conn = new xxxConnection ("mystring"); xxxcommand mycmd = new xxxcommand ("select * from orderrs", two parameters of constructor are SQL statements and Connection objects, creating a Command object.
Hereinafter, the execution of the Command object, the program example will use the SQLClient data provider to access a SQL Server database Northwind.
First, set the attribute of the connection and SQL command Command class CommandText to set the command statement, and the connection property is used to set the connection object. Settings the data connection and command statement of the command object and the command statement can be set and changed by the constructor definition by creating an object. 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";
Second, the execution command establishes the connection and setting the command, there are three ways to execute the SQL command: ExecuteNonQuery, ExecuteReader and ExecuteScalar. Using the ExecuteNonquery Execute Command Will not return a result set, will only return the number of records affected by the statement, it is suitable for insertion, update, and delete the commands that are not returned to the result set. If it is a SELECT statement, then the result of the return is -1, if the result is rolling this result is also -1. The following program example has executed updates and queries for 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] = '2004-9-1' where [Orderid] = 10248", Conn; Try {conn.open (); int i = cmd.executenonQuery (); I.TOString () "Rows Affected By Update"; cmd.commandtext = "Select * from [ORDERS]"; i = cmd.executenonQuery (); console.writeline (I.toT7tring ) "Rows Affected by SELECT");} Catch (Exception EX) {Console.Writeline (EX.MESSAGE);} Finally {Conn.close ();}}} After compiling execution, return:
With the command executed by the ExecuteReader method, you can return a type of DataReader instance or the result set of the iDataReader interface. With the DataReader object, you can get the data set, this article is not prepared to discuss DataReader in detail, and will be explained in the future of DataReader. Below 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 (); Reader = cmd.executeReader (); while (Reader) .Read ()) {console.writeline (Reader [0] .tostring ());} reader.close ();} catch (exception ex) {console.writeline (ex. measureage);} finally {conn.close }}}
The compilation execution results are as follows:
For the ExecuteReader method, if you want to get the number of records of the data, you can obtain a aggregated row collection via a statement such as select count (*). For the statement of such a single value, the Command object also has more efficient methods --ExecuteScalar. It returns the object corresponding to the first column of the first row, usually uses it to seek the results of the aggregate query. It should be noted that if you need to convert the return result into an accurate type, the database must be forced to convert the return result in the query, otherwise an exception is triggered. Here is an example: using System; using System.Data; using System.Data.SqlClient; public class myDataAccess {public static void Main () {SqlConnection conn = new ( "Server = localhost SqlConnection; Database = Northwind; User ID = sa; PWD = sa "); SQLCOMMAND CMD = New SQLCOMMAND (" SELECT Count (*) "; Try {Conn (); INT i = (int) cmd.executescalar (); console.writeline ("Record Num:" i.totring ()); 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 (); // triggered unusual console. WriteLine ("AVG:" Avg.toString ());} catch (exception ex) {console.writeline (ex.Message);} finally {conn.close ();}}}
The compilation execution is as follows: In this program, the last query will lead to an exception, because the result returned by the aggregate is the float type, cannot be converted.
Third, the parameterized query parameterized query can be optimized for performance, because the SQL statement with parameters only needs to be analyzed by SQL execution engine. Command's Parameters sets the parameter value for the parameterized query. Parameters is a set of parameters that implement the IdataParamterCollection interface. Different data providers' Command's use of the parameters is not only the same, where SQLClient and OracleClient only support the named parameters in the SQL statement without supporting the question mark placeholder. You must use the named parameters, while the OLEDB and ODBC data provider only support question mark. The placeholder does not support naming parameters. For query statements, SQLClient must use named parameters, similar to the following: SELECT * from customerid - OLEDB or ODBC Must use? Placeholder, similar to the following: SELECT *WUSTOMERS WHERE CUSTOMERID =? The following uses SQL Server as an example, the method is used: use system; use system.data; use 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 naming Parameter cmd.Parameters [0] .direction = parameterDirection.input; conn.open (); reader = cmd.executeReader (); while (ready.read ()) {console.writeline (Reader [0] .tostring ()) } Reader.close ();} catch (exception ex) {console.writeline;} finally {conn.close ();}}}
After compiling, the execution results are as follows: For the command parameters of the OLEDB or ODBC data provider, you only need to match the parameters from the left to right order, and match the parameters collection. Below is the 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 "=? Or [Employeeid] =?", Conn); OLEDBDATAREADER Reader; Try {int param1 = conver. TOINT32 (Args [0]); int param2 = convert.toint32 (args [1]); cmd.Parameters.Add ("aaa", param1); cmd.parameters.add ("bbb", param2); // parameter The object also needs the name, but the parameter name in the query statement is independent of 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. measureage); After compiling, the execution results are as follows:
4. Execute the stored procedure to access the database of the database using the Command object, you need to specify the CommandType property. This is a CommandType enumeration type. By default, CommandType indicates that the commandText command is SQL batch, and the commandType.StoredProcedure value specifies the execution command is a stored procedure . Similar to the parameterized query, the parameters of the stored procedure can also be set using the parameters collection, where the parameter object's Direction property is used to indicate that the parameter is only input, only output, two-way or stored procedures return value parameters. It should be noted that the output parameters cannot be used unless DataReader is returned using ExecuteReader. Here is an example: stored procedure ----------------------------- --CREATE procedure myProTest (@orderID as int, @elyTitle as varchar (50) output) asselect @ 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; 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.RtrunValue; SqlDataReader Reader; Try {Conn (); 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.writelin e ("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);
} Catches (exception ex) {console.writeline (ex.Message);} finally {conn.close ();}}} ---------------------- ----------------------- The results of the compiled results are as follows: Put the parameters in order from left to right, match the parameters collection.
