C # and database Access Technology Summary (V) Common methods of command objects

Common Methods for Command objects

Description: The previous section summarizes several data members of the Command object, which summarizes the common methods of command objects.

Similarly, within different data providers, the name of the command object is different, called SqlCommand in SQL Server data provider, and OleDbCommand in OLE DB data provider.

The following is a detailed description of common methods for command type objects, including constructors, execution of SQL statement methods without return result sets, execution of SQL statement methods with returned result sets, and methods for populating DataReader objects with query results.

1. Constructors

constructor is used to construct the command object . For an object of type SqlCommand, its constructor description is shown in table 2-6.

function definition	Parameter description	Function description
SqlCommand ()	With no parameters	Create a SqlCommand object
SqlCommand (String cmdtext)	Cmdtext:sql statement String	Create a SqlCommand object based on the SQL statement string
SqlCommand (String cmdtext, SqlConnection connection)	Cmdtext:sql statement String Connection: Data source to connect to	Create a SqlCommand object based on data sources and SQL statements
SqlCommand (String cmdtext, SqlConnection connection, sqltransaction transaction)	Cmdtext:sql statement String Connection: Data source to connect to Transaction: Transaction Object	Create a SqlCommand object based on data sources and SQL statements and transaction objects

(1) The first constructor does not take any arguments

  SqlCommand  cmd=newe SqlCommand ();  Cmd. Connection=connectionobject;

  string commandtext= "select *from studentinfo";  Cmd.commandtext=commandtext;

The preceding code snippet creates a SqlCommand object using the default constructor. Then, the existing Connection object Connectionobject and the named text CommandText are assigned to the command object's connection attribute and CommandText attribute respectively.

In addition, many relational databases, such as SQL Server and Oracle, support stored procedures. You can specify the name of the stored procedure as named text . For example, use the write getallstudent stored procedure for named text:

string commandtext=""; cmd.commandtype=commandtype.storedprocedure;

Cmd.commandtext=commandtext;

(2) The second constructor can accept a command (SQL) literal

string commandtext="";   SqlCommand  cmd=newe SqlCommand (CommandText);  Cmd. Connection=connectionobject;

The above code instantiates a Command object and initializes the CommandText property of the Command object with the given commands text.

Then, an existing connection object is used to assign the connection property of the Command object. (This method was said in the previous article, is not recommended, the most used is the following method)

(3) The third constructor accepts a connection and a named text

  SqlCommand  Cmd=newe SqlCommand (CommandText, Connectionobject);

Note the order of the two arguments, the first is a string of type command text, and the second is the connection object.

(4) The fourth constructor accepts three parameters and the third argument is the SqlTransaction object, which is not discussed here.

In addition , the Connection object provides a CreateCommand method that instantiates a command object and assigns its Connection property to the Connection object that establishes the Command object.

In any case, when assigning a connection object to the Connection property of the Command object, the connection object is not required to be open. However, if the connection is not open, you must first open the connection before the command executes.

For an object of type OleDbCommand, its constructor is shown in the following table. As you can see, they are very similar to the constructors of the SqlCommand class.

function definition	Parameter description	Function description
OleDbCommand ()	With no parameters	Create a OleDbCommand Object
OleDbCommand (String cmdtext)	Cmdtext:sql statement String	Create a OleDbCommand object based on the SQL statement string
oledbcommand (String cmdtext,oledbconnection Connection)	cmdtext:sql statement string connection: Data source to connect to
OleDbCommand (Stringcmdtext, OleDbConnection connection, OleDbTransaction transaction)	Cmdtext:sql statement String Connection: Data source to connect to Transaction: Transaction Object	Create a OleDbCommand object based on data sources and SQL statements and transaction objects

Once the command object is constructed, you can execute the command to manipulate the database. There are many ways that a command object can be used to execute a command, depending on what data is returned by the result of the execution of the command.

SqlCommand provides 4 methods of execution:ExecuteNonQuery (), ExecuteScalar (), ExecuteReader (), ExecuteXmlReader ().

See the relevant section below for details.

Command objects provide methods for executing commands and their meanings, such as table

Method	Meaning
Cancel	Attempt to cancel execution of a command
ExecuteNonQuery	Executes the SQL statement on the connection and returns the number of rows affected
ExecuteReader	Executes a query that returns the results of the query to the data Reader (DataReader)
ExecuteScalar	Executes the query and returns the first column of the first row in the result set returned by the query. Ignore extra columns or rows
ExecuteXmlReader	Executes the query, returning the query results to a XmlReader object

2.ExecuteNonQUery Method

The ExecuteNonQuery method is used to execute non-query statements such as INSERT, Update, delete, and other s that do not return a result set QL statement, and returns the number of rows affected after the command is executed.

Returns 0 if the target record corresponding to the update and delete commands does not exist. If there is an error, return-1.

String cnstr="server= (local);d atabase=student; Integrated security=true"; SqlConnection cn=New  SqlConnection (CNSTR); CN. Open (); string sqlstr="update student set  "; SqlCommand cmd=new  SqlCommand (Sqlstr, cn); cmd. ExecuteNonQuery (); CN. Close ();

3.ExecuteScalar () Method

In many cases, you need to return a result from the SQL statement, such as the number of records in the Customer table, the current database server time, and so on.

The ExecuteScalar method executes an SQL command and returns the first row column in the result set (a command that returns a single value). If the result set is larger than one row, the other parts are ignored.

Based on this attribute, this method is typically used to execute SQL statements that contain aggregate functions such as count, sum, and so on.

The following code reads the number of records from the table student in the database and outputs it to the console. String cnstr="server= (local);d atabase=student; Integrated security=true"; SqlConnection cn=NewSqlConnection (CNSTR); CN. Open ();stringSqlstr="Select COUNT (*) from student"; SqlCommand cmd=NewSqlCommand (Sqlstr, CN);ObjectCount=cmd. ExecuteScalar (); Console.WriteLine (count. ToString ()); CN. Close (); The return value type of the ExecuteScalar () method is object, which can be converted to the appropriate type, depending on the needs. 

4.ExecuteReader () Method

The ExecuteReader () method executes the command and populates the DataReader object with the result set .

The ExecuteReader () method is used to perform a query operation that returns a DataReader object that can read the data from the query.

The ExecuteReader () method is used more in the command object, and through the DataReader type of object, the application is able to obtain the result set after executing the SQL query statement.

The two definitions of this method are:

? ExecuteReader (), without parameters, returns a DataReader result set directly.

? ExecuteReader (CommandBehavior behavior), determines the type of DataReader based on the value type of the behavior.

If the behavior value is CommandBehavior. SingleRow This enumeration value , the returned ExecuteReader only obtains the first data in the result set.

If the value is CommandBehavior. Singleresult, it means that only the first of multiple result sets in the query results is returned .

In general, the application code can randomly access the returned ExecuteReader column,

But if the behavior value is CommandBehavior. SequentialAccess, the returned ExecuteReader object can only read sequentially the columns it contains.

That is, once you have read the columns in the object, you can no longer return to read them. This operation is a convenience for the code in exchange for the efficiency of reading data, you need to use caution.

String cnstr="server= (local);d atabase=student; Integrated security=true"; SqlConnection cn=NewSqlConnection (CNSTR); CN. Open ();stringSqlstr="SELECT * FROM Student"; SqlCommand cmd=NewSqlCommand (Sqlstr, CN); SqlDataReader Dr=cmd. ExecuteReader ();//Build SqlDataReader object while(Dr. Read ())//loop output each record {String name=dr["name"]. ToString ();//Read name sub-segment Console.WriteLine (name);//console output}DR. Close ();//closes result set CN. Close ();//Closing database connection
This code reads all the data from the student table in the database and outputs all the data from the table's "Name" field to the console. 

ExecuteXmlReader

SqlCommand unique method, OleDbCommand no this method. The method executes a command that returns an XML string. It returns a System.Xml.XmlReader object that contains the returned XML.

C # and database Access Technology Summary (V) Common methods of command objects