Asp.net database query method summary

"Ado.net 2.0 technical insider

Execute the query of returned rows

Used classes and Methods: sqlconnection, sqlcommand, executereader, and sqldatareader

The executereader method returns a sqldatareader object. The sqldatareader object displays a row of data in the result and is unidirectional.

Example:

 

 String  Strconn, strsql;

Strconn  =     @"  Data Source =. \ sqlexpress;  "     +    "  Initial catalog = northwind; trusted_connection = yes;  "  ;

Strsql  =     "  Select orderid, customerid, orderdate  "     +          "  From orders where shipcountry = 'Canada' "  ;


Sqlconnection CN  =     New  Sqlconnection (strconn );

CN. open ();



Sqlcommand cmd  =     New  Sqlcommand (strsql, CN );

Sqldatareader RDR  =  Cmd. executereader ();

  While  (RDR. Read ())

Console. writeline ( "  {0} {1} {2: d}  "  , RDR [  "  Orderid  "  ], RDR  "  Customerid  "  ], RDR [  "  Orderdate  "  ]);



RDR. clode (); 

Obtain a single value

Used classes and Methods: sqlconnection, sqlcommand, executescalar

The executescalar method does not return a sqldatareader, but returns the value of the first column in the first row of the general object data type. When you want to return a single value, you can use this syntax. Although it also creates sqldatareader and obtains the expected value, you do not need to write allCode

Example:

 

 String  Strconn, strsql;

Strconn  =     @"  Data Source =. \ sqlexpress;  "     +    "  Initial catalog = northwing; trusted_connection = yes;  "  ;

Strsql  =     "  Select sum (unitprice * quantity)  "  +  
     "  From orders inner join [Order Details]  "    +  

  "  On orders. orderid = [Order Details]. orderid  "     +  

  "  Where customerid-'alfki'  "  ;

Sqlconnertion CN  =     New Sqlconnection (strconn );

CN. open ();

Sqlcommand cmd  =     New  Sqlcommand (strsql, CN );



  Decimal  Decordertotal  =  (  Decimal  ) Cmd. executescalar ();

Console. writeline (  "  Order Total: {0: c}  "  , Decordertotal ); 

Decimal is the floating point type format of SQL Server, and ": C" is the output formatted using the system currency format

 

Execute a query that does not return a result set (data update and modification)

Used classes and Methods: sqlconnection, sqlcommand, executenonquery

You can call the executereader method of sqlcommand to execute these queries. However, because these queries do not return any rows, it seems unnecessary overhead. Fortunately, there are simpler methods. The sqlcommand class exposes the executenonquery method, which executes the query without returning the sqldatareader object.

Example:

 

 String  Strconn, strsql;

Strconn  =     @"  Data Source =. \ sqlexpress; "     +  "  Initial catalog = northwind; trusted_connection = yes;  "  ;

Strsql  =     "  Update MERs set companyName = 'newvalue'  "     +  

  " Where customerid = 'alfki'  "  ;

Sqlconnection CN  =     New  Sqlconnection (strconn );

CN. open ();



Sqlcommand cmd  =     New  Sqlcommand (strsql, CN );

  If  (CMD. executenonquery ())

Console. writeline (  " Update succeede  "  );

  Else  

Console. writeline (  "  Update failed  "  ); 

 

For update, insert, and delete statements, the returned value is the number of rows affected by the command. For all other types of statements, the return value is-1. If rollback occurs, the returned value is-1.

 

// Perform batch operation Query

 

// Execute the query to obtain XML data

 

// Execute the query in the transaction

 

// Asynchronously execute the query

"