This time we will take DataReader, and some of the skills we mentioned are not related to DataReader but it is very useful. First, the parameterized query is published in the previous article, and the code is not standardized. There is no use parameter for SQL, which is really a big vulnerability, so I first talk about the parameterized query problem. Benefits to use parameterized queries: You can prevent SQL injection attacks, improve program execution efficiency. For SQL Server .NET Data Provider, we can use @ as a parameter of the prefix tag. 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 (); sqldataareader reader = cmd.executeReader (); The above code segment uses two parameters @categoryID and @categoryID when defining the SQL statement. In order to be a specific value during the execution process, we use the Prarameter object, and the parameters are added to the Command object, so that the parameterized query is obtained. Of course, the Add method used above has other overload versions. There are also multiple overload versions of the function. You can view the MSDN. Note: The above parameters must use the @ prefix, but it is more than just a query to use parameters, and other update databases can be used to use parameters. Above we give the method for the SQL Server parameterized query, we now discuss the specified parameters in OLEDB and ODBC. In fact, both of these Provider do not support the specified parameters, but can we use (?) As placeholders in the query, and the specified parameter will appear. SQL = "SELECT ProductID, ProductName from Products"; SQL = "Where categoryid =? and productID
OleDbCommand cmd = new OleDbCommand (sql, conn); cmd.Parameters.Add ( "CatID", CategoryIDValue); cmd.Parameters.Add ( "MaxProductID", MaxProductIDValue); if the order of adding the above parameters backwards, it will MaxProductIDValue It is assigned to the first one? There is an error. In addition, the above parameter name Catid and MaxProductID don't matter, how you are naming, or even an empty string is also. Note: The above parameter name does not matter, but the order of the additional parameters is very important and cannot be reversed. The same other update database operation also supports (?) Placeholders.
Second, the premise of using the output parameter to retrieve data is to use the stored procedure. In fact, for the DBMS that supports the stored procedures, such as SQL Server, all of them should use stored procedures to achieve better execution efficiency. For example, I now need to find a name and the same contact with the same ID in my contact database (regarding the contact database, please see my last article), what should I do? One way is to use DataReader, but how efficient? In addition, we can choose better ExecuteScalar (), but if I want to know, the contact's name and phone? EXECUTESCALAR () is really better than DataReader, but it can only return a single value, this time it does not meet the requirements. We use stored procedure output parameters to solve this problem. Stored as follows: CREATE PROCEDURE GetInfo (@FID int, @ Fname varchar (8) output, @ Fphone varchar (12) output) ASSelect @Fname = Fname, @ Fphone = Fid = @FidGO above specified keyword output Fphone from friendwhere The parameter is the output parameter. Then we write the code: SqlConnection conn = new SqlConnection (connStr); SqlCommand cmd = conn.CreateCommand (); cmd.CommandText = "GetInfo"; cmd.CommandType = CommandType.StoredProcedure; above code conn new objects and objects cmd, and Specify the execution command of the CMD object as a stored procedure called GetInfo. Next we need to add a 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 noticed the above @fname and @fphone parameter added to specify the parameter as the output direction, this is in the stored procedure The parameter direction is consistent. Below we execute the command to obtain the corresponding value. Conn (); cmd.executenonquery (); string fname = cmd.Parameters ["@ fname"]. value.tostring (); string fPhone = cmd.Parameters ["@ fphone"]. Value.toString (); Conn.close (); 3, retrieving multiple unrelated result sets sometimes we need to have different tables (may also be the same table, but different inquiry content), such as I want to see all contacts , Then view the address of all contacts. Of course, this requires us to get a SQL statement, and do not need a so-called multiple record sets, but allow me to demonstrate the operation of multiple records in this need. Use between multiple query statements;
Specific code 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 in the past, but because we read the first recordset after reading the first record set? The answer is a nextResult () method. This method is a Bool type. If the next recordset is returned, otherwise it is false. Conn.open (); sqldataareader reader = cmd.executeRead (); int i = 1; do {console.writeline ("No. I.Tostring () " The record set is as follows: / n "); while Reader.read ()) {Console.WriteLine (Reader [0] .tostring () "/ t");} i ;} while (Reader.NextResult ()); Note: Due to DataReader itself, read-only characteristics You cannot compare the contents of multiple records, and you cannot move back and forth in multiple records. The result is a plurality of result sets, and the data reader is positioned on the first recordset, and the READ () method of the data reader is different (the method is before the recordset). In addition, this example simply demonstrates how many unrelated records, so don't pursue the actual significance of examples. In addition, when we need to retrieve relevant record information, multi-table connection queries are also a good choice, that is, using SQL JOIN queries. 4. When other related technologies retrieve binary data retrieval binary data, we must pass the CommandBehavior.SequentialAracse en enumeration value to the ExecuteReader method. Alternatively, pay attention to reading information from the recording set must be read in the order of your SQL statement. For example: SQL = "SELECT PUB_ID, PR_INFO, LOGO from Pub_info where pub_id = '0763'"; then you must get Pub_ID first, then PR_INFO, then logo, if you read Pr_info Want to read Pub_INFO, then you will get an exception. In addition, it is important to read a better way to read a large amount of binary data is to use the GetBytes method. The following code demonstrates if the LOGO binary data is read.
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)); Writer.Flush (); offset = BytesRead;} while (BytesRead == BufferSize); wherein the method parameters GetBytes more details, see msdn: ms-help: //MS.MSDNQTR.2003FEB.2052/cpref/html/frlrfsystemdatasqlclientsqldatareaderclassgetbytestopic.htm information retrieval mode if we Just want to get the pattern information of the database table, what should I do? 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 get traverse DataTable All modes 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 don't need to read the data set, but the program actually made this job. A viable solution is to pass the commandbehavior.schemaonly enumeration to the ExecuteRader method, and another way is to construct a special SQL command, such as: sql = "SELECT FID, FNAME, FPHONE FROM Friend where FID = 0" DataReader features Basic After the introduction, please check the MSDN if you need more detailed information. Next time we will discuss the simple function of DataSet.
