ADO.NET provides Connection to connect to the database, and also provides a Command object to query the database. Like the Connection object, there are two types: oledbcommand and sqlcommand. It distinguishes with the Connection object.
To manipulate the database, you must first use the connection to connect to the database, create a Command to query. There are several ways to create, examples:
SQLCommand CMD;
string strCon = "server = localhost; database = Northwind; Trusted_Connection = Yes;"; string strqry = "select * from Categories"; SqlConnection con = new SqlConnection (strCon); con.Open (); ¹cmd = con.CreateCommand () Use the CreateCommand method with the Connection object here with the CREATECMMAND method with the Connection object. CMD.comMandtext = strqry; // sqldataarereader reader = cmd.executeReader ();
2cmd = new sqlcommand (); ?? // Directly use the new keyword to create cmd.commandtext = strqry;? Cmd.connection = con; ?? // Setting up with the database
3cmd = new sqlcommand (strqry, con); // Bring two parameters directly in New
execution way:
(Mainly there are such a few, cmd.executeReader (); cmd.executenonquery (); cmd.executescalar (); cmd.executexmlreader ();)
1, executeReader (); return a SqlDataReader object or OLEDBDATAREADER object, this look at your program needs to do. You can check the result of the query by this object, which provides the "travel" mode of execution, that is, after reading a line from the results, move to another, then the front line cannot be used again. One thing to note is that after execution, wait until you call the read () method, the DataReader object will move to the first line of the result set, and this method also returns a BOOL value, indicating whether the next line is available, return TRUE Then, return false, the result is the end of the result.
Using DataReader can improve the performance efficiency, there are two ways to improve the performance of the code: one is based on the sequence number lookup, one is to find the appropriate GET method. Because the results of the query general will generally do not change unless the query statement is changed again, the record can be found by the location of the list. With this method, there is a problem that you may know the name of a column and don't know its location. This problem is to receive a column name by calling the DataReader object. This method receives a column name and returns this column name. The number. example:
INT ID = Reader.Getordinal ("categoryName"); while (Reader.Read ()) {response.write (Reader [ID]); reader.close ();
? As for the second way, it is very intuitive, for example:
While (Reader.Read ()) {? response.write (Reader.GetInt32 (0) .tostring () "" Reader.getstring (1) .tostring () ");} DataReader Get32 () and GetString () Returns a column value by receiving a column, both of which are most common, there are many other types.
(Note: The DataReader object is invoking the close () method to close the connection with the database. If the second connection is reopened before no shutdown, an exception information is generated)
2. EXECUTENONQUERY ()? This method does not return a DataReader object, but returns an int type value, that is, the number of rows affected in the database after execution.
example:
Int affected = cmd.executenonquery (); response.write (affectrows "strip record is affected);
3, ExecuteScalar () This method does not accept any parameters, only returns the first line of the first line of the query result set, and ignores other rows and columns, and returns an Object type, which must first put it before use. Forced conversion to the desired type. If returned is just a separate data element, this method can be used to increase the performance of the code. 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 ()); // must be forced to convert
4, ExecutexmlReader () This method is used for XML operations, returns an XMLReader object, because the system does not reference the System.xml name space by default, so 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); // Get the number of attributes on the current node?
Xr.Close ();
After the execution is completed, it is necessary to explicitly call the close () method, otherwise an exception will be thrown.
Use parameterized query
First see a SQL statement: SELECT CATEGORYID, Description from categories where categoryId =? The question mark is a parameter. However, when you are using, you must have a naming parameter with an @ prefix because .NET data provider does not support this universal parameter tag "?". Using the parameterized query can greatly simplify programming, and the execution efficiency is also directly query The string is high, it is more convenient, and in many cases, you need to change the query string, which provides convenient, just changing the value of the parameter. 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); // Give parameters to the same type in the same type of CMD.Parameters ["@Categoryid"]. Value = "3"; // assigns the value to the parameter, can be flexible to change SqlDataReader R = cmd.executeReader (); while (R.Read ()) {response.write (r.getstring (2) "" "; // Remove the value of the specified parameter column} Con. close (); // Remember to close
Use the stored procedure to query
First see the form of the stored procedure: Create Procedure CateProc (@categoryid Int (4)) AS SELECT * from categories where categoryId = @ categoryid? Return.
This is a stored procedure implementation in the database. To call stored procedures in the program, a method is to implement the CommandType property of the Command object. CommandType has three enumeration values: Text, TableDirect, StoredProcedure. Simpse the COMMANDTYPE attribute to be invoked to the third value. 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 (); actually in the way to call the stored procedure in the program is very similar to the parameterized query, a bit old Flavor of shoes.
CMD.CommandType = commandType.StoredProcedure; this way is a shortcoming, that is, if there is a special character (such as space) in the name of the table, view, or stored procedure, will not be identified. So there is still a way:
cmd.comMandtext = "{CALL CATEPROC (?)}"; // Here is the call stored procedure, the question mark is the parameter cmd.commandtype = commandType.text; // The key is here.
Setting command execution timeout
The command timeout refers to the time of the COMMAND object in the waiting result, (default is 30 seconds) If the query is not executed within 30 seconds, Command throws an exception. You can also set it yourself. Example: cmd.commandtimeout = 60;
Cancel execution query
Sometimes, for some reason, the execution of the temporary cancellation is required, and the Cancel () method of the Command object can be called to exit execution. If you have anything before you don't perform the query, Cancel () will not do anything.
