Ado. NET detailed study (v)--datareader final article

ADO this time we will end the DataReader, and some of the techniques we mentioned are not related to DataReader but are very basic and useful techniques.
One, parameterized query
In the last article after the publication of many netizens to say that the code is not standardized, no SQL use parameters, this is really a big loophole, so I am here first to talk about the parametric query.
The benefits of using parameterized queries: You can prevent SQL injection attacks and improve program execution efficiency.
For SQL Server. NET data Provider, we can use the @ as a prefix tag parameter. Like what:
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 code snippet above uses two parameters, @categoryid and @categoryid, when defining the SQL statement. In order for the parameter to get a specific value during execution, we use the Prarmeter object to add the parameter to the command object, thus obtaining the parameterized query.
Of course, the Add method used above has other overloaded versions, such as we can define the parameter object ourselves and then add:
SqlParameter para = new SqlParameter ("@CategoryID", Categoryidvalue);
Cmd. Parameters.Add (para);
The SqlParameter constructor above also has multiple overloaded versions. You can view MSDN in detail.
Note: The above parameters must use the @ prefix, but also not only query to use parameters, other update database operations similar to the use of parameters.
Above we give a method for parameterized queries for SQL Server, and now we discuss specifying parameters in OLE DB and ODBC.
In fact, neither of these provider support the method of specifying parameters, but we can use (?) in queries. As a placeholder, specify 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 command's parameters collection, but at this point the order of the parameter additions must be communicated in the order in which you use it, which is the SQL Server. NET Data Provider a different place.
OleDbCommand cmd = new OleDbCommand (sql,conn);
Cmd. Parameters.Add ("CatID", Categoryidvalue);
Cmd. Parameters.Add ("Maxproductid", Maxproductidvalue);
If the order in which the arguments are added is reversed, then Maxproductidvalue will be assigned to the first? There, then there is a mistake. In addition to the above parameter name CATID and Maxproductid indifferent, you how to name all can, even empty string also line.
Note: The above parameter names don't matter, but the order in which you add the parameters is important and cannot be reversed. The same operations for updating the database also support (?) Placeholders.

Second, retrieving data using output parameters
This approach is premised on the use of stored procedures. In fact, all operations on a DBMS that supports stored procedures, such as SQL Server, should use stored procedures for better execution efficiency.
For example, I now need to find the name of a contact in my contact database with the same ID (about the contact database please see my last article), what should I do? One approach is to use DataReader, but how efficient? Plus maybe we can choose a better executescalar (), but what if I want to know the name and phone number of the contact person? ExecuteScalar () is certainly more efficient than DataReader, but it can only return a single value, and it 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 () output
)
As
Select @Fname = Fname, @Fphone = Fphone
From friend
where Fid = @Fid
Go
The above keyword output indicates that the parameter is an input parameter.
And then we write code:
SqlConnection conn = new SqlConnection (CONNSTR);
SqlCommand cmd = conn. CreateCommand ();
Cmd.commandtext = "GetInfo";
Cmd.commandtype = CommandType.StoredProcedure;
The code above creates a new Conn object and a Cmd object and designates the execution command of the CMD object as a stored procedure named GetInfo. Next we need to add the parameter object to the Parameters collection 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 note that the above @fname and @fphone parameters are added with the parameter specified as the output direction, which is the same as the parameter direction in the stored procedure. Here we execute the command and get the corresponding value.
Conn. Open ();
Cmd. ExecuteNonQuery ();
String Fname = cmd. parameters["@Fname"]. Value.tostring ();
String fphone = cmd. parameters["@Fphone"]. Value.tostring ();
Conn. Close ();
Third, retrieving multiple unrelated result sets
Sometimes we need to do unrelated queries to different tables (and possibly the same table, but different query content), such as I want to see the names of all the contacts, and then look at the addresses of all the contact people. Of course this requires that we can do a SQL statement, and do not need the so-called multiple recordsets, but let me use this requirement to demonstrate the operation of more than one recordset.
Use between multiple query statements; The specific 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 get DataReader as usual, but since it's more than one recordset, what if we use the next recordset after we read the first recordset? The answer is the NextResult () method, which is type bool, or False if the next Recordset returns True.
Conn. Open ();
SqlDataReader reader= cmd. ExecuteReader ();
int i = 1;
Todo
{
Console.WriteLine ("First" + i.tostring () + "Recordset reads as follows: \ n");
while (reader. Read ())
{
Console.WriteLine (Reader[0]. ToString () + "T");
}
i++;
}while (reader. NextResult ());
Note: Because the DataReader itself is read-only, you cannot compare the contents of multiple recordsets, or move back and forth across multiple Recordset sets. When the result is multiple result sets, the data reader is positioned on the first Recordset, which is different from the read () method of the data reader (the method defaults to the Recordset).
In addition, this example only demonstrates the use of multiple unrelated recordsets, so please do not investigate the actual meaning of the example. In addition, when we need to retrieve relevant record information, the multiple table join query is also a good choice, that is, the use of SQL join query.
Iv. Other Related technologies
Retrieving binary data
When retrieving binary data, we must pass the CommandBehavior.SequentialAccess enumeration value to the ExecuteReader method. It is also important to note that reading information from a recordset must be read in the order of your SQL statements. Like what:
SQL = "Select Pub_id,pr_info,logo from pub_info where pub_id= ' 0763 '";
Then you must first get the pub_id, then the pr_info, then the logo, if you read the pr_info and then want to read pub_info, then you will get an exception. Also note that reading a large number of binary data when the better way is to use the GetBytes method. The following code shows if you read the binary data for the logo.
System.IO.MemoryStream stream = new System.IO.MemoryStream ();
System.IO.BinaryWriter writer = new System.IO.BinaryWriter (stream);
int buffersize = 1024;
byte[] Buffer = new Byte[buffersize];
Long Offset = 0;
Long bytesread = 0;
Todo
{
Bytesread = reader. GetBytes (2,offset,buffer,0,buffersize);
Writer. Writer (buffer,0, (int) bytesread);
Writer. Flush ();
Offset + + bytesread;
}
while (bytesread = = buffersize);
The GetBytes method parameters are more detailed, see MSDN:
Ms-help://ms. Msdnqtr.2003feb.2052/cpref/html/frlrfsystemdatasqlclientsqldatareaderclassgetbytestopic.htm
Retrieving schema information
What if we just want to get schema information for the database table? DataReader's GetSchemaTable method 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 schema 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 it's not efficient because we don't need to read the dataset, but the program actually does the work. A possible solution is to pass the Commandbehavior.schemaonly enumeration to the Executerader method, and the other way is to construct special SQL commands, such as:
SQL = "Select Fid,fname,fphone from friend where Fid = 0"

DataReader's function is basically finished, if you need more detailed information please check MSDN. Next time we'll discuss the simple features of the dataset.