Clear Asp.net (9): Ado. Net command/datareader

I. Command class

The command class can execute any type of SQL statements.

1. main attributes of the command object

Before using any command object, you must set the following three attributes of the command object.

(1). commandtype

The value is an enumeration. The enumerated values are: Command. Text; command. storedprocedure; command. tabledirect;

The default commandtype of the command object is command. Text.

(2). commandtext

The value is an SQL statement or a stored procedure name.

(3). Connection

Command initialization example:

C #

1 sqlcommand cmd = new sqlcommand ();
2 cmd. Connection = connection;
3 cmd. commandtype = commandtype. text;
4 cmd. commandtext = "select * from productions ";
5
6 sqlcommand cmd1 = new sqlcommand ("select * from productions", connection );
7
8 sqlcommand cmd2 = new sqlcommand ("selectallproductions", connection );
9 Export 2.commandtype = commandtype. storedprocedure;

 

2. Main Methods of command object

(1). executenonquery ()

Execute non-select statements, such as insert, delete, and update. Returns the number of rows affected by the command execution.

(2). executescalar ()

Point to select query and return the data in the first column of the first row of the result set. An aggregate SELECT statement that is used to calculate a single value.

(3). executereader ()

Execute the SELECT query and return a datareader object that encapsulates the read-only and forward-only cursor.

 

Ii. datareader class

Datareader allows you to read records returned by a SELECT command in a forward-only and read-only manner each time.

The main method of datareader

(1). Read ()

Move the row cursor to the next row of the stream. This method must also be called before reading the first row of record. If there are other rows, return true; if it is the last row, return false.

(2). getvalue ()

Returns the field value of the serial number specified in the current row.

(3). getvalues ()

Save the values in the current row to an array. You can use datareader. fieldcount to determine the number of fields in the row.

(4). getxxx ()

Such as getint32. The function is like getvalue (), but the return value is of the type. Note that the field is protected by null, and dbnull. value is returned.

(5). nextresult ()

If datareader contains more than one row set, this method moves the cursor to the first row of the next row set.

(6). Close ()

Disable datareader. If the original command executes a stored procedure with an output parameter, the parameter is readable only after reader is disabled.

 

Iii. Example of joint use of command and datareader

Html

1 <div>
2 <asp: literal id = "litinfo" runat = "server"> </ASP: literal>
3 <asp: Label id = "lblerror" runat = "server" text = ""> </ASP: Label>
4 <br/>
5 <ul> <li> <B> total commodities: </B> <% = This. commandscalar () %> </LI> </ul>
6 <ul> <li> <B> total updates: </B> <% = This. commandnonquery () %> records </LI> </ul>
7 </div>

Background code

C #

1 protected void sqlcommondselect ()
2 {
3 string connectionstring = configurationmanager. connectionstrings ["mydemo"]. connectionstring;
4 sqlconnection connection = new sqlconnection (connectionstring );
5 sqlcommand cmd2 = new sqlcommand ("selectallproductions", connection );
6. Export 2.commandtype = commandtype. storedprocedure;
7 try
8 {
9 connection. open ();
10 sqldatareader reader = pai2.executereader (commandbehavior. closeconnection );
11 stringbuilder htmlstr = new stringbuilder ();
12 htmlstr. append ("<ul> ");
13 while (reader. Read ())
14 {
15 htmlstr. append ("<li> <B> production name: </B>" + reader. getstring (2 ));
16 htmlstr. append ("<B> price: </B>" + (decimal) Reader ["defaultprice"] + "</LI> ");
17}
18 htmlstr. append ("</ul> ");
19 reader. Close ();
20 connection. Close ();
21 This. litinfo. Text = htmlstr. tostring ();
22}
23 catch (exception ERR)
24 {
25 this. lblerror. Text = err. message;
26}
27}
28
29 protected int commandscalar ()
30 {
31 string connectionstring = configurationmanager. connectionstrings ["mydemo"]. connectionstring;
32 sqlconnection con = new sqlconnection (connectionstring );
33 sqlcommand cmd = new sqlcommand ("select count (*) from productions", con );
34 con. open ();
35 int COUNT = (INT) cmd. executescalar ();
36 con. Close ();
37 return count;
38}
39
40 protected int commandnonquery ()
41 {
42 string connectionstring = configurationmanager. connectionstrings ["mydemo"]. connectionstring;
43 sqlconnection con = new sqlconnection (connectionstring );
44 sqlcommand cmd = new sqlcommand ("Update productions set productionname = productionname where categoryid = 1", con );
45 con. open ();
46 int COUNT = cmd. executenonquery ();
47 con. Close ();
48 return count;
49}

 

 

 

 

Clear Asp.net series learning blog directory 

 

Reference: Pro ASP. NET 3.5 in C #2008