Ado. NET reading Notes series------Sqlcommander and SqlDataReader objects

Ado|command| Notes | objects
First, the characteristics of the introduction

⒈sqlcommand: Represents a Transact-SQL statement or stored procedure to be executed on a SQL Server database.

⒉sqldatareader: Provides a way to read a forward-only row stream from a database. To create a SqlDataReader, you must call the ExecuteReader method of the SqlCommand object without using the constructor directly. You should close the SqlDataReader object as quickly as possible.

⒊sqlparameter: A parameter that represents a SqlCommand, or it can be a mapping to a dataset column.



Second, the use of the introduction

⒈ Create 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);

⒉ executes a query that does not return rows:

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:

① Get Results

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 ();

② Faster Access

Ⅰ using an ordinal lookup

......

SqlDataReader Rdr=cmd. ExecuteReader ();

int customeridordinal = rdr. GetOrdinal ("CustomerID");

int companynameordinal = rdr. GetOrdinal ("CompanyName");

while (RDR. Read ())

Console.WriteLine (Rdr[customeridordinal]+rdr[companynameordinal]);

Rdr. Close ();

Ⅱ specifies the Get method using the appropriate type

......

SqlDataReader Rdr=cmd. ExecuteReader ();

int customeridordinal = rdr. GetOrdinal ("CustomerID");

int companynameordinal = rdr. GetOrdinal ("companynameordinal");

while (RDR. Read ())

Console.WriteLine (RDR. GetString (customeridordinal) +rdr. GetString (companynameordinal));

Rdr. Close ();

③ Get 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 ();

Todo

{

while (RDR. Read ())

Console.WriteLine (Rdr[0]+rdr[1]);

Console.WriteLine ();

}while (RDR. NextResult ());

⒋ executes a query that returns a single value:

...

cn. Open ();

SqlCommand cmd=new SqlCommand ("SELECT count (*) from customers", CN);

int customres = Convert.ToInt32 (cmd. ExecuteScalar ());

⒌ performs parameterized queries and calls to stored procedures:

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 ();

⒍ gets the data from the output parameter:

Create Procedure getcustomer (@CompanyName nvarchar () output, @CustomerID nchar (5)) as SELECT @CompanyName =companyname From Customers where customerid= @CustomerID

...

Cmd. parameters["@CompanyName"]. Direction =parameterdirection.output;

　

⒎ execute the query in transaction:

...

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 ();

}



Introduction of Property Method events

⒈sqlcommand

Ⅰ Property

①COMMANDTEXT: Transact-SQL statement or stored procedure to execute. The default is an empty string.

②commandtimeout: The time (in seconds) to wait for the command to execute. The default is 30 seconds.

One of the ③commandtype:commandtype values. The default value is Text.



④connection: Connection to the data source. The default value is a null reference

⑤parameters:transact-sql the parameters of the statement or stored procedure. The default is an empty collection.

⑥transaction: Specify transaction processing for queries

⑦updaterowsource: If you use the command by calling the Update method of the DataAdapter object, the property is used to control the query results that affect the current DataRow (the default is both)



Ⅱ method

①cancel: Attempted to cancel the execution of SqlCommand. Nothing happens if there is no content to be canceled. However, if a command is being executed and the attempt to cancel fails, no exception is generated. The Cancel method also causes the command object to delete any unread rows on the DataReader object.

②createparameter: Creates a new parameter for the query.

③executenonquery: Executes Transact-SQL statements on the connection and returns the number of rows affected. For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. For all other types of statements, the return value is-1. If a rollback occurs, the return value is also-1.

④executereader: Sends the CommandText to the connection and generates a SqlDataReader.

Public SqlDataReader ExecuteReader (CommandBehavior);



⑤executescalar: Executes the query and returns the first column of the first row in the result set returned by the query. Ignore the extra columns or rows.

⑥executexmlreader: Sends the CommandText to the connection and generates a XmlReader.

⑦prepare: Creates a prepared version of the command on an instance of SQL Server. You should specify the data type of each parameter in the statement that you want to prepare before calling Prepare.

⑧resetcommandtimeout: Set the CommandTimeout property to the default value of 30 seconds.



Ⅲ Events

⒉sqldatareader

Ⅰ Property

①depth: Gets a value that indicates the nesting depth of the current row. The outermost table has a depth of zero. The SQL Server. NET Framework Data Provider does not support nesting and always returns a value of 0.

②fieldcount: Gets the number of columns in the current row. 0 if it is not placed in a valid recordset, otherwise the number of columns in the current row. The default value is-1. FieldCount returns 0 after executing a query that does not return rows.

③hasrows: True if SqlDataReader contains one or more rows; false otherwise.

④isclosed: True if SqlDataReader is turned off; otherwise, false.

⑤item: Gets the value of the column that is represented in the native format.

⑥recordsaffected: The number of rows that have been changed, inserted, or deleted, or the SELECT statement for 0;-1 if no rows are affected or the statement fails.

Ⅱ method

①close: Closes the SqlDataReader object. If the return value and the number of records affected by the query are unimportant, you can reduce the time that is required to close the SqlDataReader by calling the Cancel method of the associated SqlCommand object before the closing method is called.

②getname: Gets the name of the specified column.

③getordinal: Gets the column ordinal with the given column name.

④getvalue: Gets the value of the specified column in its native format.

⑤getvalues: Gets all the property columns in the collection of the current row.

⑥isdbnull: Gets a value that indicates whether the column contains nonexistent or missing values. True if the specified column value is equivalent to DBNull, or false.

⑦nextresult: Causes the data reader to advance to the next result when reading the results of a batch Transact-SQL statement. True if there are multiple result sets, otherwise false.

⑧read: Causes SqlDataReader to advance to the next record, true if there are multiple rows, or false. You must call read to begin accessing any data. At some point, each associated SqlConnection can only open one SqlDataReader, and any attempt to open another will fail before the previous shutdown.

Ⅲ Events

⒊sqlparameter

Ⅰ Property

①dbtype: Gets or sets the DbType of the parameter. The default value is string.

②direction: Gets or sets a value that indicates whether the parameter is input-only, output-only, bidirectional, or stored procedure return-value parameters. The default value is Input.



③isnullable: Gets or sets a value that indicates whether the parameter accepts null values. True if a null value is accepted, otherwise false. The default is False.

④parametername: Gets or sets the name of the SqlParameter.

⑤size: Gets or sets the maximum size, in bytes, of the data in the column. The default value is derived from the parameter value.

⑥sqldbtype: Gets or sets the SqlDbType of the parameter. default is nvarchar. SqlDbType and DbType are interconnected. Setting DbType changes the SqlDbType to a supported SqlDbType.

⑦value: Gets or sets the value of this parameter. The default is empty.

Ⅱ method

Ⅲ Events