I. Features
⒈SQLCOMMAND: Indicates a Transact-SQL statement or stored procedure to be executed on the SQL Server database.
⒉SQLDataReader: A way to provide only a row of travel from the database. To create SqlDataReader, you must call the EXECUTEREADER method for the SQLCommand object without direct use of constructor. The SQLDataReader object should be closed as quickly as possible.
⒊SQLPARAMETER: Indicates the parameters of SQLCommand, or it can be mapping it to the DataSet column.
Second, use introduction
⒈ Create a SQLCommand object:
String strconn, strsql;
StrConn = "...........";
strsql = "Select Customerid, CompanyName from Customers";
SqlConnection CN = New SqlConnection (STRCONN);
Cn.open ();
SQLCommand CMD;
CMD = New SQLCOMMAND ();
cmd.commandtext = strsql;
CMD.Connection = CN;
CMD = New SQLCOMMAND (strsql, cn);
⒉ Execute the query without returning row:
String strconn, strsql;
StrConn = "...";
strsql = "Update Customers Set CompanyNME = 'NewName'Where Customersid =' Alfki '";
SqlConnection CN = New SqlConnection (STRCONN);
Cn.open ();
SQLCommand cmd = new SQLCOMMAND (strsql, cn);
Int Recordsaffercted = cmd.executenonquery ();
IF (RecordSaffercted == 1)
......
Else
..........
⒊ Check the query results with the SqlDataReader object:
1 acquisition result
String strconn, strsql;
StrConn = "...";
SqlConnection CN = New SqlConnection (STRCONN);
Cn.open ();
strsql = "Select Customerid, CompanyName from Customers";
SQLCommand cmd = new SQLCOMMAND (strsql, cn);
SqlDataReader rdr = cmd.executeReader ();
While (Rdr.Read ())
Console.WriteLine (RDR ["Customerid"] RDR ["CompanyName"]);
Rdr.close ();
2 get faster
I uses the serial number-based look
......
SqlDataReader rdr = cmd.executeReader ();
INT Customeridordinal = rdr.getordinal ("Customerid");
INT companyNameRinal = rdr.getordinal ("CompanyName");
While (Rdr.Read ())
Console.writeline (RDR [Customeridordinal] RDR [CompanyNameRDinal];
Rdr.close ();
II Specify the GET method using the appropriate type
......
SqlDataReader rdr = cmd.executeReader ();
INT Customeridordinal = rdr.getordinal ("Customerid"); int companyNameordinal = rdr.getordinal ("CompanyNameRDinal");
While (Rdr.Read ())
Console.writeline (Rdr.getstring (Customeridordinal) Rdr.getstring (CompanyNameRINAL);
Rdr.close ();
3 acquisition multiple results
...
Cn.open ();
String strsql = "Select Customerid, CompanyName from Customers;" "Select ORDERID, Customerid from Orders;"
SQLCommand cmd = new SQLCOMMAND (strsql, cn);
SqlDataReader rdr = cmd.executeReader ();
DO
{
While (Rdr.Read ())
Console.writeLine (RDR [0] RDR [1]);
Console.writeLine ();
} while (rdr.nextresult ());
⒋ Execute the query for returning single value:
...
Cn.open ();
SQLCommand cmd = new SQLCOMMAND ("Select Count (*) from Customers", CN);
INT Customres = Convert.Toint32 (cmd.executescalar ());
⒌ Execute the parameterized query and call the stored procedure:
Sqlcommand cmd = new sqlcommand ("delqxry", cn);
cmd.commandtype = commandtype.storedProcedure;
Sqlparameter parameterroleid = new sqlparameter ("@ id", sqldbtype.int, 4);
ParameterroleID.value = 444;
Cmd.Parameters.Add (parameterroleid);
Cn.open ();
cmd.executenonquery ();
⒍ Get data from the output parameters:
Create Procedure getCustomer (@companyname nvarchar (30) Output, @ Customerid nchar (5)) AS SELECT @ companyName = CompanyName from customers where customerid = @ CustomerID
...
CMD.Parameters ["@ companiesName"]. Direction = parameterDirection.output;
⒎ In Transaction, execute queries:
...
Cn.open ();
SQLTransaction TXN = cn.begintransaction ();
String strsql = "INSERT INTO CUSTOMERS (...) VALUES (...)";
SQLCommand cmd = new SQLCOMMAND (strsql, cn, txn);
INT Recordaffected = cmd.executenonquery ();
IF (RecordAffected == 1)
{
Txn.commit ();
}
Else
{
Txn.rollback ();
}
Third, the attribute method introduction
⒈SQLCommand
I attribute
1CommandText: The Transact-SQL statement or stored procedure to be executed. The default is an empty string. 2CommandTimeout: The time (in seconds) waited for the command. The default is 30 seconds.
3CommandType: one of the commandtype value. The default is TEXT.
4Connection: Connection to the data source. The default is empty reference
5Parameters: Transact-SQL statement or parameter of the stored procedure. The default is empty collection.
6Transaction: Specifies transaction processing for query
7Updaterowsource: If you use Command by calling the Update method of the DataAdapter object, then this property is used to control the results of the current DATAROW (the default is BOTH)
II method
1Cancel: Try to cancel the execution of SQLCommand. If there is no content to be canceled, it will happen anything. However, if there is a command being executed, the cancellation of the trial fails, it will not generate an exception. The CANCEL method also causes the Command object to delete all unread rows on the DataReader object.
2CREATEPARAMETER: Create a new parameter for the query.
3ExecutenonQuery: Execute the transact-sql statement to the connection and return the number of rows that are affected. For Update, Insert, and DELETE statements, the return value is the number of rows affected by this command. For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -1.
4ExecuteReader: Send CommandText to Connection and generates a SqlDataReader.
Public SqlDataReader ExecuteReader (Commandbehavior);
5EXECUTESCALAR: Execute the query and return to the first column of the first line of the result of the query returned. Ignore additional columns or rows.
6executexmlreader: Send CommandText to Connection and generate an XMLReader.
7prepare: A preparation version of the command is created on the instance of SQL Server. Before calling prepare, you should specify the data type of each parameter in the statement you want to prepare.
8RESETCOMMANDTIMEOUT: Set the CommandTimeout property to the default for 30 seconds.
III incident
⒉SQLDataReader
I attribute
1Depth: Gets a value that indicates the nesting depth of the current line. The depth of the outermost table is zero. SQL Server .NET Framework data provider does not support nested and always returns zero.
2fieldcount: Get the number of columns in the current row. If it is not placed in a valid record set, it is 0; otherwise, the number of columns in the current row. The default is -1. After performing the query that does not return, FieldCount returns 0.
3HASROWS: If SqlDataReader contains a row or more, true; otherwise false.
4isclosed: if the SqlDataReader is turned off, TRUE; otherwise false.
5Item: Get the value of the column represented by the local format.
6Recordsaffected: The number of rows that have been changed, inserted or deleted; if there is no row being affected or the statement fails, it is 0; -1 represents the SELECT statement.
II method
1Close: Close the SqlDataReader object. If the number of records affected by the return value and query is not important, you can reduce the time required to turn off the SqlDataReader before calling the Close method.
2GetName: Get the name of the specified column.
3Getordinal: Get the column serial number when the column name is given.
4GetValue: Get the value of the specified column represented in this machine format. 5GetValues: Get all attribute columns in the collection of the current line.
6ISDBNULL: Gets a value that indicates whether the column contains an existing or missing value. If the specified column value is equivalent to DBNULL; otherwise, false.
7nextResult: When reading the results of the approval of the Transact-SQL statement, the data reader proceeds to the next result. TRUE if there are multiple result sets; otherwise, false.
8Read: Make SqlDataReader to the next record, if there is a plurality of rows; otherwise, false. You must call Read to start accessing any data. At a certain time, each associated SqlConnection can only open a SqldataReader, and any attempt to open another will fail before the previous off.
III incident
⒊SQLParameter
I attribute
4DBTYPE: Gets or sets the DBTYPE of the parameters. The default is String.
2DIRECTION: Get or set a value, the value indication parameter is only input, only output, the two-way or stored procedure returns value parameters. The default is INPUT.
3InULlable: Get or set up a value indicating whether the parameter accepts null value. TRUE if you receive null value; otherwise, false. The default is false.
4ParameterName: Gets or sets the name of SQLParameter.
5Size: Gets or sets the maximum size (in bytes) of the data in the column. The default is derived from the parameter value.
6SQLDBTYPE: Gets or sets the SQLDBTYPE for the parameters. The default is nvarchar. SQLDBTYPE and DBTYPE are interconnected. Setting DBType will change SqldbType to supported SQLDBTYPE.
7Value: Gets or sets the value of this parameter. The default is empty.
II method
III incident
