Use ADO. Net to manipulate Databases

ADO. NET provides connection to connect to the database and command object to query the database. Like the connection object, there are two types of commands: oledbcommand and sqlcommand. The difference is the same as that of the connection object.

To manipulate the database, you must first use connection to connect to the database, and then create a command to query. There are several creation methods, for example:

Sqlcommand cmd;

String strcon = "Server = localhost; database = northwind; trusted_connection = yes ;";
String strqry = "select * from categories ";
Sqlconnection con = new sqlconnection (strcon );
Con. open ();
Cmdcmd = con. createcommand (); // use the createcommand method of the connection object to create a command object.
Cmd. commandtext = strqry;
// Sqldatareader reader = cmd. executereader ();

2. cmd = new sqlcommand ();?? // Directly use the new keyword to create
Cmd. commandtext = strqry;
Cmd. Connection = con ;?? // Set the connection to the database

Cmdcmd = new sqlcommand (strqry, con); // create a new SQL command with two parameters.

Execution method:

(There are several main types: cmd. executereader (); cmd. executenonquery (); cmd. executescalar (); cmd. executexmlreader ();)

1. executereader (); returns a sqldatareader object or oledbdatareader object, which depends on your program. You can use this object to check the query results. It provides a "swimming" execution method, that is, after reading a row from the result and moving it to another row, the previous row cannot be reused. One thing to note is that after execution, the datareader object will be moved to the first row of the result set after the read () method is manually called, And a bool value will be returned for this method, indicates whether the next row is available, true is available, and false is returned to the end of the result set.

Datareader can improve the execution efficiency. There are two ways to improve the Code Performance: one is serial number-based search, and the other is to use the appropriate get Method for search. Because the query results are generally not changed, unless the query statement is modified again, you can locate the column to find the record. One problem with this method is that you may know the name of a column without knowing its location. The solution to this problem is to call the getordinal () method of the datareader object, this method receives a column name and returns the column number of the column name. Example:

Int id = reader. getordinal ("categoryname ");
While (reader. Read ())
{
Response. Write (Reader [ID]);

Reader. Close ();

The second method is intuitive, for example:

While (reader. Read ())
{
? Response. Write (reader. getint32 (0). tostring () + "" + reader. getstring (1). tostring () +"
");
}

Getint32 () and getstring () of datareader return the value of a column by receiving a column number. These two types are the most commonly used, and there are many other types.

(Note: The datareader object closes the connection to the database by calling the close () method. If the second connection is re-opened before it is closed, an exception message is generated)

2. executenonquery ()? This method does not return a datareader object, but returns an int value, that is, the number of rows affected in the database after execution.

Example:

Int affectrows = cmd. executenonquery ();
Response. Write (affectrows + "affected records ");

? 3. The executescalar () method does not accept any parameters. It only returns the first column of the First row in the query result set, ignoring other rows and columns, and returns an object type, before use, you must forcibly convert it to the required type. If only one data element is returned, you can use this method to improve the Code performance. Example:

String strcon = "Server = localhost; database = northwind; trusted_connection = yes ;";
String strqry = "select count (*) from categories ";
Sqlconnection con = new sqlconnection (strcon );
Con. open ();
Sqlcommand cmd = con. createcommand ();
Int I = convert. toint32 (CMD. executescalar (); // mandatory Conversion

4. executexmlreader () This method is used for XML operations and returns an xmlreader object. Because the system does not reference the system. xml namespace by default, it must be introduced before use. Example:

String strcon = "Server = localhost; database = northwind; trusted_connection = yes ;";
Sqlconnection con = new sqlconnection (strcon );
Con. open ();
Sqlcommand cmd = new sqlcommand ("select * from categories for XML auto, xmldata", con );
Xmlreader xr = cmd. executexmlreader ();
Response. Write (XR. attributecount); // obtain the number of attributes on the current node?

XR. Close ();

After the execution is complete, you must call the close () method explicitly. Otherwise, an exception is thrown.

Use parameterized Query

First look at an SQL statement: Select categoryid, description from categories where categoryid =? The question mark is a parameter. However, the name parameter with the @ prefix must be used, because the. NET data provider does not support this generic parameter mark "?". Using parameterized queries can greatly simplify programming, and the execution efficiency is higher than that of directly querying strings. In many cases, you need to change the query string. This method provides convenience, you only need to change the parameter value. Example:

String strcon = "Server = localhost; database = northwind; trusted_connection = yes ;";
Sqlconnection con = new sqlconnection (strcon );
Con. open ();
String strqry = "select * from categories where categoryid = @ categoryid"; // query with Parameters
Sqlcommand cmd = new sqlcommand (strqry, con );
Cmd. Parameters. Add ("@ categoryid", sqldbtype. Int, 4); // assign the parameter to the same type in the same database
Cmd. Parameters ["@ categoryid"]. value = "3"; // assign a value to the parameter, which can be changed flexibly.
Sqldatareader r = cmd. executereader ();
While (R. Read ())
{
Response. Write (R. getstring (2) +"
"); // Retrieve the value of the specified parameter Column
}
Con. Close (); // remember to close

Query Using Stored Procedures

First look at the format of the stored procedure: Create procedure cateproc (@ categoryid int (4) as select * from categories where categoryid = @ categoryid? Return.

This is the implementation method of the stored procedure in the database. To call the stored procedure in a program, you can use the commandtype attribute of the command object. Commandtype has three enumerated values: Text, tabledirect, and storedprocedure. You only need to set the commandtype attribute to the third value to call the stored procedure. Example:

String strcon = "Server = localhost; database = northwind; trusted_connection = yes ;";
Sqlconnection con = new sqlconnection (strcon );
Con. open ();
Sqlcommand cmd = con. createcommand ();
Cmd. commandtext = "cateproc ";
Cmd. commandtype = commandtype. storedprocedure;
Cmd. Parameters. Add ("@ categoryid", sqldbtype. Int, 4 );
Cmd. Parameters ["categoryid"]. value = "2 ";
Sqldatareader r = cmd. executereader ();
While (R. Read ())
{
Response. Write (R. getstring (2) +"
");
}
Con. Close ();

In fact, the method of calling the stored procedure in the program is similar to that of parametric query, which is a bit of a taste of old shoes.

Cmd. commandtype = commandtype. storedprocedure; the disadvantage of this method is that when the table, view, or stored procedure name to be queried contains special characters (such as spaces), it cannot be identified. Therefore, another method is:

Cmd. commandtext = "{call cateproc (?)} "; // The stored procedure is called. The question mark is a parameter.
Cmd. commandtype = commandtype. Text; // The Key is here.

SET command execution timeout

Command timeout refers to the time when the command object is waiting for the result (30 seconds by default). If the query is not executed within 30 seconds, the command throws an exception. You can also set it yourself. Example: cmd. commandtimeout = 60;

Cancel query execution

Sometimes, for some reason, you need to temporarily cancel command execution. You can call the cancel () method of the command object to exit execution. Cancel () will not do anything before the query is executed.