Tutorials to .com

Tutorials to .com » Dotnet » Database » Detailed study of ADO.NET (e) - DataReader

Detailed study of ADO.NET (e) - DataReader

Print View , by: iSee ,Total views: 17 ,Word Count: 1776 ,Date: Tue, 9 Jun 2009 Time: 12:23 AM

This time we will end DataReader, and we mentioned some of the skills has nothing to do with the DataReader is a very basic but also very useful skills.
A, parameter query
An article published in the last after a lot of friends suggested that non-standard code, there is no use of sql parameters, this is indeed an enormous a loophole, so first of all I would like to talk about the issue of query parameters.
The use of parameterized queries advantages: preventing sql injection attacks, to improve the efficiency of program execution.
For sql server. Net data Provider, we can use the @ as a prefix tag parameters. 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 code segment above sql statement in the definition of when the use of two parameters @ CategoryID and @ CategoryID. Parameters to be obtained in the course of specific value, we use Prarmeter object, through which the parameters added to the Command object, so that access to query parameters.
Of course, add the above methods used in other heavy-duty version, for example, we can define its own object and then add Parameter:
SqlParameter para = new SqlParameter ( "@ CategoryID", CategoryIDValue);
cmd.Parameters.Add (para);
SqlParameter constructor above there are a number of heavy-duty version. Can view specific msdn.
Note: the above parameters must use the @ prefix, not just inquiries can also use parameters to update the operation of the database can be used similar parameters.
Above, we give the parameters for the sql server query methods, we are now discussing the OLEDB and ODBC specified parameters.
Provider in fact, these two parameters do not support the specified method, but we can use in the query (?) As a placeholder to specify the location of the parameters will appear.
sql = "select ProductID, ProductName from Products";
sql + = "where CategoryID =? and ProductID <?";
Then we Parameter object should be added to the Command's Parameters collection inside, but this time pay attention to the order of parameters must add you use? Same order, and above this is sql server. Net data Provider difference.
OleDbCommand cmd = new OleDbCommand (sql, conn);
cmd.Parameters.Add ( "CatID", CategoryIDValue);
cmd.Parameters.Add ( "MaxProductID", MaxProductIDValue);
If the above parameters in order to add anti-get, then MaxProductIDValue will be assigned to the first? There, then the error. In addition the above parameters were CatID and MaxProductID does not matter, how can you can be named, or even empty string is required.
Note: The above parameters were indifferent, but add a very important parameter of the order can not be reversed. The same operation to update the database also supports the (?) Placeholder.

Second, the use of output parameters to retrieve data
The premise of this approach is the use of stored procedures. In fact, in support of the DBMS stored procedure sql server for example, its operations are all stored procedure should be used to obtain better performance.
For example, I now need to contact me to find a database and specify the same ID of the contact's name (on the contact database see my previous article), how should I do? One way is to use the DataReader, but how efficient? Also maybe we can choose a better ExecuteScalar (), but if I want to know the contact name and phone? ExecuteScalar () is indeed better than the efficiency of the DataReader, but it can only return a single value, this time it can not meet the requirements. Here we use the stored procedure output parameter to solve the problem. 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
Keyword above output parameter is the output parameter specified.
Then we write code:
SqlConnection conn = new SqlConnection (connStr);
SqlCommand cmd = conn.CreateCommand ();
cmd.CommandText = "GetInfo";
cmd.CommandType = CommandType.StoredProcedure;
Code above the new object and cmd object conn and cmd the command object is designated as the implementation of the stored procedure called GetInfo. Next we need to object to the Parameters collection of cmd to add a Parameter 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 have taken note of the above @ Fname and add @ Fphone parameters when the parameters specified for the output direction, the stored procedure is and the parameters inside the same direction. We have implemented the following command to obtain the corresponding value at the same time.
conn.Open ();
cmd.ExecuteNonQuery ();
string Fname = cmd.Parameters [ "@ Fname"]. Value.ToString ();
string Fphone = cmd.Parameters [ "@ Fphone"]. Value.ToString ();
conn.Close ();
Third, a number of non-search result sets
Sometimes we need a different table (or table may be the same, but different query) for unrelated information, such as I would like to see the names of all of your contacts, and then see all of your contacts in the address. Of course, this requires that we can get a sql statement, do not need more than the so-called records set, but allow me to demonstrate to the needs of the operation of a number of recordsets.
Between the use of multiple query; separately. 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 past the same DataReader, but the number of records is set, we read the record after the first set after the next record if you use this set? The answer is NextResult () method for the bool type, if there is a record set on the return true, otherwise false.
conn.Open ();
SqlDataReader reader = cmd.ExecuteReader ();
int i = 1;
do
(
Console.WriteLine ( "No." + i.ToString () + "set records as follows: \ n");
while (reader.Read ())
(
Console.WriteLine (reader [0]. ToString () + "\ t");
)
i + +;
) while (reader.NextResult ());
Note: DataReader itself forward as a result of the characteristics of read-only, you can not compare the contents of a number of recordsets can not be concentrated in a number of records back and forth movement. The results of multiple result sets, the data reader positioning record in the first set, the data reader and the read () methods (the method set in the record before the default).
In addition this example has nothing to do more than just record when the demonstration of the use of sets, so please do not hold examples of practical significance. In addition, when we need to retrieve records related to information, multi-table join queries is a good choice is to use Sql join query.
Other related technologies
Binary data retrieval
Retrieval of binary data when we CommandBehavior.SequentialAccess enumeration value must be passed to the ExecuteReader method. Another is to pay attention to read information from the record set when you must, in accordance with the order of the sql statement to read. For example:
Sql = "select pub_id, pr_info, logo from pub_info where pub_id ='0763 '";
When you read must first obtain a pub_id, then pr_info, then again when the logo, if you want to read after pr_info read pub_info, then you will be abnormal. Another thing to note is read when a large number of binary data through the use of better methods GetBytes. If the following code presentation of the logo to read binary data.
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;
do
(
BytesRead = reader.GetBytes (2, Offset, Buffer, 0, BufferSize);
writer.Writer (Buffer, 0, (int) BytesRead);
writer.Flush ();
Offset + = BytesRead;
)
while (BytesRead == BufferSize);
GetBytes method parameters which are more specific, please refer to msdn:
ms-help: / / MS.MSDNQTR.2003FEB.2052/cpref/html/frlrfsystemdatasqlclientsqldatareaderclassgetbytestopic.htm
Information retrieval model
If we just want to get the model information database table, how should I do? GetSchemaTable method of the DataReader to meet our requirements.
string sql = "select Fid, Fname, Fphone from friend";
cmd.CommandText = sql;
conn.Open ();
SqlDataReader reader = cmd.ExecuteReader ();
DataTable SchemaTable = reader.GetSchemaTable ();
DataTable and then we can traverse all the model 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 ());
)
)
However, the efficiency of the above is not high, because we do not need to read the data set, but the procedure to do this work in practice. One possible solution is to transfer to CommandBehavior.SchemaOnly enumeration ExecuteRader methods, another way is the special structure of the sql command, such as:
Sql = "select Fid, Fname, Fphone from friend where Fid = 0"

DataReader is basically on the introduction of functional end, if you need more detailed information please see the msdn. Next time we will discuss a simple functional DataSet.



.Net Database Articles


Can't Find What You're Looking For?


Rating: Not yet rated

Comments

No comments posted.