[Posting & Correction] detailed research on ado.net (III)

This time we will end datareader, and some of the techniques we mentioned have nothing to do with datareader, but they are basic and useful.
1. parameterized Query
After posting in the previous article, many netizens commented that the Code is not standardized and SQL parameters are not used. This is indeed a major vulnerability, so I will first talk about parameterized query.
Advantages of parameterized query: it can prevent SQL injection attacks and improve program execution efficiency.
For SQL Server. NET data provider, @ can be used as the prefix parameter. For example:
Const string connstr = "Data Source = bineon; user = sa; Password = test; initial catalog = northwind ;";
String SQL = "select productid, productname from products ";
SQL + = "where categoryid = @ categoryid and productid <@ categoryid ";
Sqlconnection conn = new sqlconnection (connstr );
Sqlcommand cmd = new sqlcommand (SQL, Conn );
Cmd. Parameters. Add ("@ categoryid", categoryidvalue );
Cmd. Parameters. Add ("@ maxproductid", maxproductidvalue );
Conn. open ();
Sqldatareader reader = cmd. executereader ();
The above code snippet uses two parameters @ categoryid and @ categoryid when defining an SQL statement. To obtain the specific value of a parameter during execution, we use the prarmeter object to add the parameter to the command object and obtain the parameterized query.
Of course, the add method used above has other overloaded versions. For example, we can define a parameter object and add it again:
Sqlparameter para = new sqlparameter ("@ categoryid", categoryidvalue );
Cmd. Parameters. Add (para );
The above sqlparameter constructor also has multiple overloaded versions. For details, refer to msdn.
Note: The above parameters must use the @ prefix. In addition, parameters are not only used for queries, but can be used for other operations similar to updating databases.
We have provided a method for SQL Server parameterized query. Now we will discuss specifying parameters in oledb and ODBC.
In fact, neither of these providers supports the method of specifying parameters, but we can use (?) in the query (?) As a placeholder, specify the location where the parameter will appear.
SQL = "select productid, productname from products ";
SQL + = "where categoryid =? And productid <? ";
Next, we should also add the parameter object to the parameters set of command, but at this time, note that the order of parameter addition must be the same as what you use? In the same order as SQL Server. NET data provider.
Oledbcommand cmd = new oledbcommand (SQL, Conn );
Cmd. Parameters. Add ("catid", categoryidvalue );
Cmd. Parameters. Add ("maxproductid", maxproductidvalue );
If the order of the added parameters is reversed, maxproductidvalue will be specified to the first? Then, an error occurs. In addition, the above parameter names catid and maxproductid do not matter. You can name them all, or even empty strings.
Note: The above parameter names do not matter, but the order of adding parameters is very important and cannot be reversed. Other database update operations are also supported (?) Placeholder.

2. Use output parameters to retrieve data
The premise of this method is to use the stored procedure. In fact, for DBMS that support stored procedures, such as SQL Server, all operations on it should use stored procedures for better execution efficiency.
For example, I need to find the name of a contact with the same ID in my contact database (for details about the contact database, see my previous article). What should I do? One way is to use datareader, But how efficient is it? In addition, we may be able to choose a better executescalar (), but what if I want to know the contact name and phone number? Executescalar () is indeed more efficient than datareader, but it can only return a single value, which cannot meet the requirements at this time. Here we use the Stored Procedure output parameters to solve this problem. The stored procedure is as follows:
Create procedure getinfo
(
@ FID int,
@ Fname varchar (8) output,
@ Fphone varchar (12) Output
)
As
Select @ fname = fname, @ fphone = fphone
From friend
Where FID = @ FID
Go
The output keyword above indicates that the parameter is an output parameter.
Then we write the code:
Sqlconnection conn = new sqlconnection (connstr );
Sqlcommand cmd = conn. createcommand ();
Cmd. commandtext = "getinfo ";
Cmd. commandtype = commandtype. storedprocedure;
The code above creates a conn object and a cmd object, and specifies the execution command of the CMD object as the Stored Procedure named getinfo. Next, we need to add a parameter object to the parameters set of the CMD object.
Sqlparameter Param = cmd. Parameters. Add ("@ FID", 16 );
Param = cmd. Parameters. Add ("@ fname", sqldbtype. varchar, 8 );
Param. Direction = parameterdirection. output;
Param = cmd. Parameters. Add ("@ fphone", sqldbtype. varchar, 8 );
Param. Direction = parameterdirection. output;
We noticed that when the above @ fname and @ fphone parameters are added, the parameter is specified as the output direction, which is consistent with the parameter direction in the stored procedure. Run the following command to obtain the corresponding value.
Conn. open ();
Cmd. executenonquery ();
String fname = cmd. Parameters ["@ fname"]. value. tostring ();
String fphone = cmd. Parameters ["@ fphone"]. value. tostring ();
Conn. Close ();
3. Retrieve multiple irrelevant result sets
Sometimes we need to perform irrelevant queries on different tables (or tables with the same content but different queries). For example, if I want to view the names of all contacts, view the addresses of all contacts. Of course, this requires a single SQL statement and does not require multiple record sets. However, please allow me to demonstrate the operations of multiple record sets with this requirement.
Separate multiple query statements. The Code is as follows:
Sqlconnection conn = new sqlconnection (connstr );
Sqlcommand cmd = conn. createcommand ();
String sqla = "select fname from friend ";
String sqlb = "select fphone from friend ";
Cmd. commandtext = sqla + ";" + sqlb;
Then we can obtain the datareader as before. But since it is multiple record sets, what if we use the next record set after reading the first record set? The answer is the nextresult () method, which is of the bool type. If the next record set exists, the returned result is true. Otherwise, the returned result is false.
Conn. open ();
Sqldatareader reader = cmd. executereader ();
Int I = 1;
Do
{
Console. writeline ("no." + I. tostring () + "record set content:/N ");
While (reader. Read ())
{
Console. writeline (Reader [0]. tostring () + "/t ");
}
I ++;
} While (reader. nextresult ());
Note: Due to the read-only feature of datareader, you cannot compare the content of multiple record sets or move back and forth between multiple record sets. When multiple result sets are returned, the data reader locates on the first record set, which is different from the read () method of the data reader (the method is before the record set by default ).
In addition, this example only demonstrates the use of multiple irrelevant record sets. Therefore, do not investigate the actual meaning of the example. In addition, when we need to retrieve relevant record information, multi-table join queries are also a good choice, that is, SQL join queries.
4. Other Related Technologies
Retrieving binary data
When retrieving binary data, we must pass the commandbehavior. sequentialaccess enumeration value to the executereader method. In addition, you must read the information from the record set in the order of your SQL statements. For example:
SQL = "select pub_id, pr_info, logo from pub_info where pub_id = '2013'";
Then you must obtain pub_id first, then pr_info, and then the logo. If you want to read pub_info after reading pr_info, you will get an exception. In addition, it should be noted that the getbytes method is a better way to read a large amount of binary data. The following code demonstrates how to read the binary data of the logo.
System. Io. memorystream stream = new system. Io. memorystream ();
System. Io. binarywriter writer = new system. Io. binarywriter (Stream );
Int buffer size = 1024;
Byte [] buffer = new byte [buffersize];
Long offset = 0;
Long bytesread = 0;
Do
{
Bytesread = reader. getbytes (2, offset, buffer, 0, buffersize );
Writer. Writer (buffer, 0, (INT) bytesread );
Writer. Flush ();
Offset + = bytesread;
}
While (bytesread = buffersize );
The getbytes method has many parameters. For details, see msdn:
MS-help: // Ms. msdnqtr.2003feb. 2052/cpref/html/frlrfsystemdatasqlclientsqldatareaderclassgetbytestopic.htm
Retrieval mode information
What if we only want to obtain the schema information of the database table? The getschematable method of datareader can meet our requirements.
String SQL = "select FID, fname, fphone from friend ";
Cmd. commandtext = SQL;
Conn. open ();
Sqldatareader reader = cmd. executereader ();
Datatable schematable = reader. getschematable ();
Then we can traverse the datatable to get all the mode information.
Datarowcollection schemacolumns = schematable. Rows;
Datacolumncollection schemaprops = schematable. columns;
Foreach (datarow schemacolumn in schemacolumns)
{
Foreach (datacolumn schemacolumnprop in schemaprops)
{
Console. writeline (schemacolumnprop. columnname + "=" + schemacolumn [schemacolumnprop. columnname]. tostring ());
}
}
But the above efficiency is not high, because we do not need to read the dataset, but the program actually did this work. A feasible solution is to pass the commandbehavior. schemaonly enumeration to the executerader method. The other method is to construct special SQL commands, such:
SQL = "select FID, fname, fphone from friend where FID = 0"

The datareader function is basically complete. For more details, see msdn. Next we will discuss the simple functions of dataset.