[. Net] C #. Net ADO. NET data access model Overview (2)

Ii. Execute SQL statements

1. Use sqlcommand to execute SQL statements

Generally, we divide Database Operations into two types:

(1) the record set command is not returned. It is usually an SQL statement that executes operations such as adding, inserting, and modifying to update records.

(2) return the record set command. Generally, a query SQL statement is executed to obtain the dataset for rendering.

Any operation requires the command object to execute the SQL statement.

Methods for overloading a sqlcommand object:

(1) sqlcommand ()

(2) sqlcommand (string plain text)

(3) sqlcommand (string plain text, sqlconnection connection)

(4) sqlcommand (string plain text, sqlconnection connection, sqltransaction transaction)

The sqlcommand object must accept a valid sqlconnection. It is used to tell the data source of the command object that we need to interact.

You can also directly input the SQL statement to be executed in the sqlcommand constructor. You can also input the sqltransaction parameter to implement transactions.

Therefore, the following steps are required to use a command object:

(1) declare a valid connection object.

(2) declare a valid command object.

(3) Specify the SQL statement string to be operated and the instance of the associated connection object for the command object.

(4) Open the connection object.

(5) execute various methods of the command object for database operations.

(6) Close the connection object.

 

2. Use the parameter to prevent SQL Injection

Sqlcommand cmd = new sqlcommand ("select count (*) from tbclass where classname = @ classname", Conn)

Cmd. commandtype = commandtype. storedprocedure;

Cmd. Parameters. Add ("@ Classname", Sqldbtype. varchar, 50 );

Cmd. Parameters ["@ Classname"]. Value = txtclass. text;

Cmd. Parameters ["@ Classname"]. Direction = parameterdirection. input;

 

3. Access Data Using datareader

Datareader is read-only. Therefore, loop statements are often used to read record sets.

The following instances must be referenced:

Using system. Data;
Using system. Data. sqlclient;
Using system. text;

(1) Use datareader to read a single record set

String sconnectionstring = @ "Server = (local) \ sqlexpress; database = guestbook; trusted_connection = true ";
Using (sqlconnection conn = new sqlconnection (sconnectionstring ))
{
Conn. open ();
Using (sqlcommand cmd = new sqlcommand ("select * From tbguestbook", Conn ))
{
Using (sqldatareader DR = cmd. executereader ())
{
If (dr. hasrows)
{
Stringbuilder htmlstr = new stringbuilder ();
// Use stringbuilder to construct strings with High Efficiency
Htmlstr. append ("<Table border = '1' cellpadding = '5' cellspacing = '0' style = 'font-size: 9pt; Font: '> "); // start the table
Htmlstr. append ("<tr style = 'background-color = # f0f0f0 '>"); // start the header
For (INT I = 0; I <dr. fieldcount; I ++)
{
Htmlstr. append (string. format ("<TD> <strong> {0} </strong> </TD>", dr. getname (I); // construct the header
}
Htmlstr. append ("</tr>"); // The End Of the header.
While (dr. Read ())
{
Htmlstr. append ("<tr>"); // record row start
For (INT I = 0; I <dr. fieldcount; I ++)
{
Htmlstr. append (string. Format ("<TD> {0} </TD>", dr. getvalue (I); // construct a record row
}
Htmlstr. append ("</tr>"); // end of record row
}
Htmlstr. append ("</table>"); // the end of the table.
Response. Write (htmlstr );
}
}
}
}

(2) Use datareader to read multiple Record Sets

String sconnectionstring = @ "Server = (local) \ sqlexpress; database = BBS; trusted_connection = true ";
Using (sqlconnection conn = new sqlconnection (sconnectionstring ))
{
Conn. open ();
Using (sqlcommand cmd = new sqlcommand ("select * From bbs_link; select * From bbs_menubig", Conn ))
{
Using (sqldatareader DR = cmd. executereader (commandbehavior. closeconnection) // obtain a datareader.
{
If (dr. hasrows) // whether the record set is null
{
Do
{
Stringbuilder htmlstr = new stringbuilder ();
// Use stringbuilder to construct strings with High Efficiency
Htmlstr. append ("<Table border = '1' cellpadding = '5' cellspacing = '0' style = 'font-size: 9pt; Font: '> "); // start the table
Htmlstr. append ("<tr style = 'background-color = # f0f0f0 '>"); // start the header
For (INT I = 0; I <dr. fieldcount; I ++)
{
Htmlstr. append (string. format ("<TD> <strong> {0} </strong> </TD>", dr. getname (I); // construct the header
}
Htmlstr. append ("</tr>"); // The End Of the header.
While (dr. Read ())
{
Htmlstr. append ("<tr>"); // record row start
For (INT I = 0; I <dr. fieldcount; I ++)
{
If (! Dr. isdbnull (I ))
Htmlstr. append (string. Format ("<TD> {0} </TD>", dr. getvalue (I); // construct a record row
}
Htmlstr. append ("</tr>"); // end of record row
}
Htmlstr. append ("</table>"); // the end of the table.
Response. Write (htmlstr );
} While (dr. nextresult ());
}
}
}
}