Use DataReader to do the following in your application:
I. No cache data is required;
II. Data that is too large and cannot be stored;
III. It is necessary to access data once in only, read-only and fast way.
G. Benefits using a custom powerful DataSet type
By creating a child object inherited in DataSet, you can perform type checks and declarations during operation. When you have a certain plan or have a related structure for your Dataset, you can create a DataSet that represents an object with rows and columns. For example, you express a consumer object's name property to replace the names of the table of the table. For more information about this section, please refer to Microsoft Site: Working with a typed dataset
H. Process invalid data in custom DataSet
Check your DataSet by XSD language to make sure your DataSet handles invalid references. NULLVALUE Note Allows you to replace BBNULL into other characters, string.empty; or reserve invalid references, throw an error message, the prompt will depend on the context of your application, the default is to reference invalid characters.
I. Refreshing data in DataSet
If you want to refresh your DataSet from the database, use DataAdapter.Fill, if your DataTable has a primary key, DataAdapter.Fill will match the new row according to the primary key, and use the value of the existing row from the database. Unless the row is modified before refresh, its ROWSTATE will be set to unchanged. Note that if the DataTable does not set the primary key, your DataSet may have a repeated value. If you want to refresh a table from the database and keep the changes in the bank in any table, then you should first fill a new table, then use preserveChanges equal to TRUE to merge the DataTable to your DataSet.
J. Search data in DataSet
Use the index query will increase your query performance when you query a special standard line in a DataSet. When you give a DataTable design primary key, the index is also created. When you create DataView for a DataTable, the index is also created at the same time. The following is some of the cases using an index query:
I. Use DataTable.Rows.Find instead of DataTable.Rows.Find if the query is instead with the column order of the primary key in the DataTable.
II. If the query includes columns that do not have the main keys, you can use DataView to improve performance for multiple queries of data. When you use sort in DataView, you will create an index while you are in the query. DataView uses the Find and Findrows methods to query the data in DataTable;
IV. If you don't need a list of table, you can also create an index query with DataView for DataTable. Note that this only has an advantage only when you perform multiple queries. If you just perform a simple query, use this method will reduce your query efficiency.
K. DataView structure
As mentioned earlier, the potential also creates an index while creating DataView and sort, rowfilter, or rowstatefilter properties. When you create a DataView object, if sort, rowfilter, and rowstatefilter properties are also specified, the index will only create it; if you create an empty DataView, then the index is at least twice.
L. Page scheduling
ADO.NET allows you to clearly control what data returns from your database and how much data is stored to a DataSet. There is no single introduction to a query result, but when you design your application, you should take into account the following: i. Avoid overflow on StartRecord and MaxRecords values when using DataAdapter.fill.
II. The way to solve such a problem is to use the WHERE statement, the Order By statement, and TOP assertions.
III. There is also a solution to use TOP assertions and nested SELECT statements. Such as the following code:
SELECT TOP 10 * from
(SELECT TOP 30 * from customer, one id) as table1 order by id desc
IV. If your date is not often changed, you can use DataSet's storage capabilities to improve execution performance, such as you can store the data of the Page 10 pages to your DataSet, and then check the database when users have access to FirstPage and LastPage in the storage area. Get new data.
M. Planned DataSet
When using data fill DataSet, the DataAdapter.Fill method uses the DataSet existing plan and the data returned by SELECTCOMMAND to populate DataSet. If there is no corresponding table in the DataSet, Fill creates a table. By default, Fill only defines the types and columns. You can overload the default Fill method by setting DataAdapter's MissingsChemaAction property. For example, to make a Fill method to create a table, always contain primary key information, unique constraints, column properties, whether to allow null values, maximum lengths, read-only columns, and automatic increment columns, specify DataAdapter.MissingsChemaAction for missingschertion.addwithKey. As a choice, you can also call DataAdPater.Fillschema before calling DataAdapter.Fill to ensure that you plan to populate DataSet. Calling FillSchema will add additional burden to the database to output Schema information, so the best suggestion is to specify the DataSet plan, or set the MissingsChemaAction of DataAPter before calling Fill.
N. Use CommandBuilder
CommandBuilder automatically generates dataCommand, UpdateCommand, and deleteCommand properties based on DataAdapter's DataApter's selectCommand. Provide SelectCommand to perform a simple Select, the following information describes the best processing using CommandBuilder.
I. Do not use CommandBuilder during the design phase, and whether the process that produces a DataAdapter Command property will be interfered. If you know your Update, INSERT, and DELETE declaration, you should be clearly specified. One of the best designs is to create a stored procedure for your Update, INSERT, and DELETE, and set and use them in the Command property of the DataApterapter.
II. CommandBuilder uses SelectCommand to determine the value of the other Command properties. If DataAdapter's SelectCommand itself changes, you should use refreshschema to refresh the properties of Command.
III. As long as the DataAdapter's Command property is emmand, CommandBuilder only creates a Command, even if you explicitly specify the property value of Command, CommandBuilder will not rewrite, so if you want to create a command and keep the previous property settings, then take Command. The property is set to NULL. O. SQL batch declaration and processing
Many databases support use integrated query or batch or multiple subcommands in a command. For example, in SQL Server uses ";". Using integrated multi-command in a command can effectively reduce the number of times interacting with the database and increase efficiency in your application. For example, use batch to complete all delete tasks in your application.
The use of batch has indeed improved efficiency, but it also adds complexity when managing updated DataSet data in your application. To make complexity simplify, you have to create a DataAPter for each DataTable in your DataSet.
P. Fill a DataSet with multiple tables
If you use batch to return data from multiple tables and populate these data into a DataSet, the Fill method will use the first table named the first table, and the list will be used in the first one. The table name is based on an incremental number. For example, the following code will gradually explain the working principle of the Fill method:
'Visual Basic
Dim da as sqldataadapter = new sqldataadapter ("Select * from customer;", myconnection)
DIM DS AS DATASET = New Dataset ()
Da.fill (DS, "Customers")
'C #
SqldataAdapter Da = New SqldataAdapter ("Select * from Orders;", MyConnection;
DataSet DS = New DataSet ();
Da.fill (DS, "Customers");
As shown above, the Customers table data will be stored in a DataTable named Customers, while the ORDERS table data will be placed in a DataTable named Customers1. Of course, you can also easily modify the Customers1 table properties (TableName) for ORDERS after the end of the data fill. However, when the data is filled in the future, it will only affect the data in the Customers table, and the Orders table will ignore and create a new table named Customers1. To resolve this issue, you have to create a DataTableMapping mapping between Customers1 and ORDERS. This is also the case. for example:
'Visual Basic
Dim da as sqldataadapter = new sqldataadapter ("Select * from customer;", myconnection)
Da.TableMappings.Add ("Customers1", "Orders")
DIM DS AS DATASET = New Dataset ()
Da.fill (DS, "Customers")
'C #
Sqldataadapter Da = New SqldataAdapter ("Select * from Orders;", MyConnection; DA.TABLEMAPPINGS.ADD ("Customers1", "Orders");
DataSet DS = New DataSet ();
Da.fill (DS, "Customers");
Q. Use DataReader
Here is some techniques and answers to some questions:
I. DataReader must be turned off before using the associated Command to access any output parameters;
II. DataReader should be turned off after reading the data. If your Connection is just used to return DataReader, you should immediately close it immediately after shutting down the DataReader. Another way to close the Connection is to pass the CommandBehavior.closeConnection to the ExecuteReader method. This method is very useful when you return to DataReader from a method and use it when you don't turn off control or associated connections.
III. DataReader is a connected data access design;
IV. Return special data type data using getString, getInt32 or the like;
V. A connection is only allowed to use a DataReader. In ADO, if you only create a connection and use two RecordSets, a read-only cursor and a only time, but in fact, ADO has created an implicit connection for you and implicitly Close it. Whether in ADO.NET, you must create a Connection for each DataReader, which is also to give you more control information when using Connection.
VI. By default, DataRead is load all the data in the row to memory each time you read. And allow you to randomly access the data in the current line. If the access is not necessary (there is no need to load all the data to memory), and want to improve the execution efficiency, send CommandBehavior.sequentialAracseReader method to the ExecuteReader method, which change the default action of the DataReader to only load the request data to memory. Note that this method requires you to deploy the column data sequentially, once you skip a column, you will never read the data of the column.
VII. If you still have a lot of unrecognized data after you complete the data from a DataReader, you have to call the CANCEL command before calling the DataReader's close command. Calling the DataReader's Close command will cause the unwanted data to be loaded in and clear the data before shutting down the cursor. This part of the data will be discarded when calling the Cancel command, so that DataReader will not read them before turning. If you are returning from your command, call the ca ZANCEL command or you will discard them. If you need to read any output parameters, you don't use Cancel, and use Close directly.
R. BLOBS object
When you use DataReader to read binary data, you should pass Commandbehavior.sequentialAndbehavior.SequentialAccess to the ExecuteReader method. Because DataReader's default is to store all the data in each line each time you read data, the binary data is very large, and the result will make a large amount of memory space occupied by a single BLOB. SequentialAccess makes your DataReader default behavior to read only the required data. Then you can use GetBytes or getChars to determine how much data is read. Remember, after using SequentialAracse, you cannot access different fields in DataReader in order. That is to say, if your query returns three columns, the third column is the BLOB data type, if you want to access the third column data, then you must first visit the first column, then the second column, then the first Three columns of BLOB data. This is because the data returned at this time is ordered, and once you skip a column, it is not possible to read this column again.
S. Use command
ADO.NET provides several different methods for executing commands, as well as several different parameters for optimizing execution commands. The following will be introduced is to select the skills of the best execution command and improve the performance of an executable command.
I. OLEDBCommand Best Practice
The execution command standard between various data providers in the .NET framework is almost the same. But there is also a different, the following is some techniques to perform OLEDBCommand:
1 Call the stored procedure using CommandType.Text, generate using CommandType.StoredProcedure;
2 Determine the type, size (if required) and accuracy (if you are a number or decimal), pay attention to if you don't explicitly set the OLEDBParameter, OLEDBCommand will regenerate the OLDBParameter for your execution command.
II. SQLCommand Best Practice
Quickly perform stored procedures using SQLCommand: If you want to call a stored procedure, specify the CommandType of SQLCommand as the CommandType of the stored procedure. This will be submitted to the stored procedure when executing the command, thereby achieving quick execution.
III. Using the prepared method
Command.prepare method Optimizes your parameterization execution command. The prepare structure is the most optimized specified command for multiple calls. To use prepare, you first understand how your database is called corresponding to Prepare. In SQL Server 2000, Command has been implicitly optimized and prepare is not required; using prepare is valid in SQL Server 7.0 or other databases.
IV. Clearly specify planning and metadata
A lot of objects in ADO.NET must infer metadata information, as long as the user does not specify it, is as follows:
1 If there is no existence in the DataSet, the DataAdapter.Fill method creates a table and column information;
2CommandBuilder generates a DataAdPater command parameter for a SELECT command for a stand-alone table;
3commandbuilder.deriveParameters assembles the parameter information of a command object;
If you use the methods mentioned above, you may reduce the performance performance. It is recommended to use in the design phase and advertising applications. It is possible to specify planning and metadata. These include specifying tables and columns of DataSet, specifying the Command property of DataAdapter and the parameter information specified for Command.
V. ExecuteScalar and ExecutenonQuery
If you want to return only a simple value, such as count (*), sum (price) or avg (quantity), you can use ExecuteScalar, which helps you get the value you want step by step, avoid using DataReader two-step calculations (ExecuteReader getValue); When you don't want to return to row information, such as modify data (INSERT, UPDATE, DELETE), or only need to output parameters or return values, using ExecuteNonQuery, it removes unnecessary processing to create an empty DataReader.
VI. Null
If a null value is allowed in your table, you can use the WHERE statement to check for null values, which will be given below:
Select * from customers where (Lastname = @ lastname) or (Lastname is null and @lastname is null))
The above statement checks if the column is empty and the parameter is empty.
VII. Pass NULL parameter value
When you pass the NULL parameter value to the database in the command, you can't use Null (Nothing in VB), you should use dbnull.value. Example:
'VB
Dim param as sqlparameter = new sqlparameter ("@ name", sqldbtype.nvarchar, 20)
Param.Value = dbnull.value
'C #
Sqlparameter param = new Sqlparameter ("@ name", sqldbtype.nvarchar, 20);
Param.Value = dbnull.value;
Viii. Use transaction processing
The transaction processing model in ADO.NET has changed, in ADO, once the StartTransaction is called, the updates under any transaction are considered part of the transaction. However, in ADO.NET, when Connection.BeginTransaction is called, return a command-associated transaction object (transaction property is specified by the transaction property of the command). This ensures that you have multiple transactions in a Connection. If the command of the command is inconsistent with the starting transaction, the command will not complete and throw an error.
IX. Use Connections
High-efficiency applications should use the least amount of time to establish a connection with the database, such as using Connection Pool, etc. Here's how to use ADO.NET to build some database of high-efficiency applications.
1Connection Pooling
In the Data Provider in SQL Server, OLE DB, and .NET Framework, implicit connection pool connection support is provided. You can specify different parameter values to control the behavior of the connection pool in Connectionstring. For example, the following example makes the OLE DB connection pool and automatically perform transaction processing:
Provider = SQLOLEDB; OLE DB SERVICES = -4; Data Source = localhost; integrated security = sspi;
The following parameters are available in SQL Server.Net Data Provider: Connection LiftTime, Connection Reset, Enlist, Max Pool Size, Min Pool Size, and Pooling.
2 use DataAdapter optimization connection
The appropriate connection is automatically opened when using the DataAdpater's Fill and Update methods. If Fill or Update opens a connection, it will turn off this connection after it is complete. The best execution is to establish a connection when you need it. At the same time, reduce the number of times the connection is turned on and closed when multiple operations. It is recommended that you allow Fill or Update methods to implicate and close connections when just performing a Fill or Update; if you want to perform multiple Fill or Update, it is recommended that you explicitly establish a connection, execute the Fill or Update action and then explicitly Close the connection.
Additionally, when we perform a transaction, you should explicitly establish the connection before starting the transaction, and explicitly shut down the connection after the end of the transaction. Example:
'Visual Basic
Public Sub RunsqlTractions (Da As Sqldataadapter, MyConnection As SqlConnection, DS AS Dataset)
MyConnection.Open ()
Dim myTrans as sqltransaction = myconnection.begintransaction ()
mycommand.transaction = myTrans
Try
Da.UPDATE (DS)
MyTrans.commit ()
Console.writeline ("Update Successful.")
Catch e as exception
Try
MyTrans.rollback ()
Catch ex as sqlexception
IF not myTrans.connection is nothing then
Console.writeline ("an Exception of Type" & ex.gettype (). Tostring () &_
"Was Encountered While Attempting to Roll Back The Transaction.")
END IF
END TRY
Console.writeline ("An Exception of Type" & E.GETTYPE (). Tostring () & "WAS Encountered.")
Console.writeLine ("Update Failed.")
END TRY
MyConnection.Close ()
End Sub
'C #
Public void Runsqltransaction (SqlDataAdapter Da, SQLCONNECTION MyConnection, DataSet DS)
{
MyConnection.open ();
Sqltransaction myTrans = myconnection.begintransaction ();
MyCommand.Transaction = MyTrans;
Try
{
Da.UPDATE (DS);
Mycommand.transaction.commit ();
Console.writeline ("Update Successful.");
}
Catch (Exception E)
{
Try
{
MyTrans.rollback ();
}
Catch (SQLException EX)
{
IF (MyTrans.Connection! = NULL)
{
Console.writeLine ("An Exception of Type" EX.GETTYPE ()
"WAS Encountered While Attempting to Roll Back The Transaction.");
}
Console.writeLine (E.TOString ());
Console.writeline ("Update Failed.");
}
MyConnection.Close ();
}
X. Always close Connections and DataReaders
After you have used the connection or DataReader object, you should clear them clearly. The fragmentation of the system is only organized when the final needs, and some resource-consuming connections have to be released by yourself. At the same time, if you don't clearly turn off the connection, this connection may not return the connection pool unless the Max Pool Size connected to the pool has been reached and this connection is still valid.
Note: Do not use Close or Dispose to use a Connection or a DataReader or any managed object if you are using a CLOSE or DAPOSE. In a Finalizer, just release the unaffected resources that you have directly owned by your class. If your class does not have any unrecognized resources, you don't use the Finalize method in your class.
Xi. Use using declarations in C #
In C #, a very convenient guarantee that Close the way you have used the Connection and DataReader objects is to use the USING declaration. When the object exceeds its range, the USING declaration will automatically release the object. Example:
'C #
String connString = "Data Source = localhost; integrated security = sspi; initial catalog = northwind;"
Using (SqlConnection Conn = New SqlConnection (ConnString))
{
Sqlcommand cmd = conn.createCommand ();
cmd.comMandtext = "Select Customerid, CompanyName from Customers";
Cn.open ();
Using (SqlDataReader DR = cmd.executeReader ())
{
While (Dr.Read ())
Console.writeline ("{0} / t {1}", Dr.getstring (0), Dr.getstring (1));
}
}
USING declaration is not available in Visual Basic.NET.
Xii. Avoid accessing the OLEDBConnection.State property
If you need to check the State property frequently, you'd better listen to the StateChange event on the OLEDBConnection. The following code demonstrates a message to the console when oleDbconnection.state changes, using stateChange:
'Visual Basic
AddHandler Nwindconn.StateChange, New StateChangeEventHandler (Addressof OnStateChange)
Protected Shared Sub onStateChange (Sender As Object, Args as StateChangeeventargs)
Console.writeline ("The Current Connection State Has Changed from {0} to {1}.", _
Args.originalState, args.currentstate
End Sub
'C #
Nwindconn.StateChange = new statechangeeeventhandler (onStateChange); protected static void onstatechange (Object Sender, StateChangeeventargs Args)
{
Console.writeline ("The Current Connection State Has Changed from {0} to {1}.",
Args.originalState, args.currentstate;
}
ADO.NET Best Practices (below)
http://www.9cbs.net/develop/read_article.asp?id=22664
