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 ();
2 cmd = new sqlcommand (); // Directly use the new keyword to create cmd.commandtext = strqry; cmd.connection = con; // sets the connection to 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.
While (Reader.Read ()) {response.write (Reader.GetInt32 (0) .tostring () "" Reader.getstring (1) .tostring () "
");} DataReader's GetInt32 ( ) And getString () By receiving a list of values, these are the most commonly used, which 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 that are 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 results, and ignores other rows and columns, and returns an Object type, which must be forced before use. Convert 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
