ADO.NET technology

Microsoft. NET include a new data access technology called ADO.NET. Using this new technology to various reasons, including: the disconnection of the internal data support, as well as comprehensive support for the XML technology with the whole. NET framework for seamless integration. This paper focused on what features of the technology include, and how to make full use of this technology to use the database. We first introduced connected to the basic elements of the database, and then discuss how to read and write database. The contents of ADO.NET extensive operation against each provided a lot of ways. In this paper, we will use the DataSet object to read and write data, and try to use DataReader and the Command object manually read and write data. Dynamic SQL at the same time will also explore the use and storage process. Read this article, you will be able to use ADO.NET to create, read, update and delete data. Most Visual Basic? . NET project types are automatically invoked ADO.NET, including Windows? Applications, ASP.NET Web applications and class library project. In addition, these types of projects to a particular level, will automatically into System.Data namespace. You can project Property (attributes) that the overall situation in the dialog boxes List Import statement. System.Data namespace for application to provide basic data to support focused mainly on the use of data rather than the data access itself. This means that, in the default situation, we can visit DataSet object and its related sub-object, you can also access definition of how to proceed with the basic data access interface. However, to interact with the database before, we need to identify the data used to provide procedures. Microsoft. NET includes two off-the-shelf data provider. SQL Server provides procedures for Microsoft SQL Server? 7.0 or higher version of the optimization of the visit. OleDb provide procedures to provide a procedure to provide all the OleDb database visit. Provide procedures in SQL Server System.Data.SqlClient namespace. Provide procedures for the visit of SQL Server were optimized, and the use of ADO through OLE DB compared, it can provide better performance. In addition, the program also has provided an intelligent link buffer pool mechanism, and therefore its predecessor OLE DB or ODBC compared to the buffer can provide faster connections to the database. OleDb procedures provided in the System.Data.OleDb namespace. Provide procedures for the use of this, we can OLE DB database and interactive. Contain any provision of the existing OLE DB database procedures can be adopted. NET in the OleDb provide procedures for a visit. There is also a Odbc provider, you can download the program from MSDN. It is ODBC packaging program that lets you with any ODBC driver with an interactive database. Connected to the database Database connectivity by providing procedures for database processing. All offers procedures will provide a connection object. SQL procedures include providing SqlConnection object, and provide procedures include OleDbConnection OleDb objects. Linking these two objects are connected using string and other information (such as security evidence) to define the location of the database. OleDbConnection use ordinary OLE DB connection string, it is not surprising that, because it is only passed to the foundation string OLE DB Provider. Below are the Access database for the OleDbConnection string Example: Provider = Microsoft.Jet.OLEDB.4.0; Password = ""; User ID = Admin; Data Source = grocertogo.mdb SqlConnection use similar connection string, but it does not need database provider, but only on how to find needed information and database security credentials. Below are the Pubs database SQL Server examples: Data source = localhost; Initial catalog = pubs; User id = sa; Password = In many cases, even when the object manually create links, we also have to worry about how to create your own connection string. Visual Studio? . NET IDE contains the functions that we do not need to type any code can be easily added and configured connections. In the toolbox, you will find Data (data) tab, including common data objects, such as DataSet, OleDbConnection and SqlConnection object. You can drag and drop these objects to the Windows Forms, Web Form or components of the design. These non-object graphics entity, it will not directly displayed on the window, but on display in the design of icons at the bottom of the column to facilitate user access. These entities are in many ways similar to the controls, so when these entities click, you can use an ordinary Properties (attributes) window to change their attributes and settings. For example, we create a name ADO Windows applications, and drag and drop一个SqlConnection object in the window. The object icon will appear in control column. Then, you can use Properties (attributes) window will be its name changed to a more descriptive name, such as cnPubs. Wizard is a more useful tool, it can help us connect string structure. In Properties (attributes) window click ConnectionString field, and then click the field revealed that the next arrow. Controls which will set out the definition of all connections and a <New Connection...> (<link ...>) new entry. If you click <New Connection...> (<link ...>) new entries, Visual Studio. NET will display a dialog to define data sources. Click OleDbConnection object will show standard OLE DB connection dialog box, you can choose from OLE DB data provider, and the designation of the procedures necessary to provide full information. Click SqlConnection object will show a dialog box, you can choose from SQL Server, security settings and choose a definition of the server database. Here, we set up a local computer at the Pubs database connectivity, in the back of examples will use the link. CnPubs settings stored in the Windows Forms Form Design in the generated code for the region, by default, these settings are hidden. This and other Forms of control is no different. You can also choose the manual configuration connecting objects. To this end, need to use code similar to the following: Dim cnPubs As New SqlConnection () CnPubs.ConnectionString = _ "Data source = localhost; Initial catalog = pubs; Integrated security = SSPI " With these basic code for the design of the basic code we create the same. Open and close the connection Please note that regardless of the design or use manual code, only definition of the connection, and did not open the link. We open and close the connection when it has complete control over the application procedure can be carried out in the most effective operation. Remember, SqlClient will buffer database connectivity, therefore, as a general principle, it is better to open the links for system design, the use of connections, and immediately after the completion of operations and close the connection. When using OleDb client, a database for your specific OLE DB Provider, you need to follow certain best practices and experiences. Use correct error handling to ensure that the database connectivity always been closed (even when there is a mistake), this point is very important. The use of database connections, recommend the following structure: Try CnPubs.Open () Try 'Data visit here Finally CnPubs.Close () End Try Catch 'Wrong here handle data access End Try The outer Try .. Catch block allows you to deal with any possible data access errors, including not break connection, as well as in the read or update data at any mistakes that may occur. Try .. Finally the inner block can ensure that, even in the read or update data has indeed wrong circumstances, the connections are still closed. Finally replacement structure will not be wrong, all wrong Catch all the outer structure will be caught. However, whether or not an error occurs, will run Finally block to ensure correct and close the connection. In fact, in many cases you do not need this structure, because we are about to use the other ADO.NET objects are automatically open or close the connection. Generally speaking, you prepare for the substantial additional data access code, ADO.NET is likely to be targeted or through other methods provide a more simple solution. The use of data We already know how the definition of open and closed Database Connection, Next, we will read and write through the use of data to the database. Interactive database is ultimately passed Command object to complete, although these objects more likely to be hidden in other abstract objects behind. Command object contains information on how to read or update data in the database directives. These directives may be dynamic SQL statements, also may be called for information storage process. Read data usually have two methods. One is the DataSet object, which we can use to provide a, abstract and disconnected copy of the data. DataSet filled by the DataAdapter object, DataAdapter object with basic Command and Connection objects interactive. DataAdapter using a DataReader objects called from the database access to the actual data. Then, these data will be used to fill DataSet object. The second way is to read data directly use DataReader, thereby bypassing the entire DataSet and DataAdapter technology. This approach in the creation of Web pages or XML Web Services, is particularly useful, because we usually only extract data from the database, then simply copy the pages of its XML output or outcome. Data will be copied to the DataSet, and then copy the page or its XML results need to spend extra time. Although this manual methods are sometimes quite fast, but compared to the use of DataAdapter and DataSet, which we need to do more. DataSet and DataAdapter object also provided the mechanism for updating data. DataAdapter not only includes the Command object retrieval of data, but also contains insert, update and delete data Command object. You simply have to change the DataSet object transmitted to the DataAdapter, the latter will automatically call the Command object corresponding change in the update to the database. By default, this update process with the use of optimized processing, that is, before the update will examine basic data. If the user changes the data, it is not applied update, and will notify the code in the conflict. You can disable this behavior, so as to avoid blindly into the database will be updated, regardless of the changes made by other users. In addition, you can also choose through direct call Command Objects (which includes instructions for updating the database) manually update the database. In such circumstances, we need to address any issue with. Use DataSet read and update data DataSet is stored in memory for you to use the disconnection copy of the data. Data provided from the proceedings filled DataSet, we need to use the DataAdapter object, the object will use the Command object Generation DataReader, then data can be read from the DataReader will DataSet. The creation and configuration DataSet many ways, the simplest way is to use Visual Studio. NET. You can also manually configuration DataSet, but like dealing with the same object connect you with the code prepared by the Visual Studio. NET code automatically generated the same effect. Interestingly, the adoption of Visual Studio. NET DataSet is actually the creation of such a process, namely the creation of DataAdapter object, then let DataAdapter object automatic generation DataSet. From the toolbox will SqlDataAdapter OleDb tab drag and drop objects in the window. This will activate the guide. The first panel shows, and then guide the next panel will be asked to use the connection object. We have only one connection, it is easy to choose. Please note that you can also connect from the creation of a panel, then use the DataAdapter started the whole process, so that we can through this wizard guides you through the whole process.   A panel depends on the choice of options. If you choose to use SQL statements or create new storage process, you can use input SELECT statements or inquiries generator to build a storage process. Executive insert, update and delete operations will be based on three other orders automatically constructed SELECT statements. If you choose the existing storage process, a panel will allow you to choose four storage process orders with four associated. In this example, we use contains the SQL SELECT statements, as follows: SELECT Au_id, Au_lname, Au_fname FROM Authors The last panel guide only confirm our designated operation. Click Finish (completed), the code for the DataAdapter configuration will be saved to Form or other hidden code designed for the region. In Properties (attributes) window, will change the name of DataAdapter object to daAuthors, because it is configured for use together with the authors table. You can right-click the column controls icon DataAdapter, and then choose Configure Data Adapter (configuration data adapter) option, which of these options to change. You can also right-click control and select Generate Dataset (generating data sets), the representative of the DataAdapter automatically create data DataSet object. This configuration DataSet object is the most simple method, we will use this method. This will display a dialog box, from which you can be with existing DataSet DataAdapter linked to, or create a new DataSet. In this example, we will create a new DataSet called dsPubs. Click OK (determine), can create DataSet. A new document called dsPubs1.xsd will be added to the project, a new name dsPubs1 Controls Controls icon will be added to the column. Xsd XML document is a framework document for the definition of control maintained by the data structure. The framework is based DataAdapter object of SELECT statements or stored procedures automatically generated. Click dsPubs1 control, and then Properties (attributes) window will change its name to dsPubs. The control is our DataSet, it is now ready to programming or data through the use of bundled data in the DataAdapter. Choose DataGrid, and then use Properties (attributes) of its DataSource attribute window set to dsPubs.Authors. This will mesh bundled with DataSet, and will immediately display grid are correct name. The project has added xsd framework document, and therefore all the data are available. The remaining work just add some code, the use of DataAdapter to load the DataSet. In Refresh data (data set) buttons behind adding this code: Private Sub btnRefresh_Click (ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles btnRefresh.Click DaAuthors.Fill (DsPubs.authors) End Sub Please note that here we do not have open or close the connection. Because we DataAdapter can be done automatically for this operation. It can open connectivity, data acquisition and close connections, and no need for us to prepare any additional code. Similarly, we can Save Changes (preservation changes) button inserted following code to update the data: Private Sub btnSave_Click (ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles btnSave.Click DaAuthors.Update (DsPubs.authors) End Sub As DataAdapter contain enforceable insert, update and delete operations Command object, it only called Automatic Update method will be able to complete all the tasks. DataSet itself will be tracking data for each trip was changed, inserted or deleted, this technology will be changed to update the data in the database. As grid bundled with DataSet, when we run the application and click Refresh (refresh) button, the data will show, and can edit the data. Just click Save Changes (preservation changes) button, the changes will be saved to the database. This shows that the DataAdapter DataSet object and the power, but also shows that we right. NET data binding in the control. Furthermore, we should also be aware that the use of the DataSet here is very simple. In fact, DataSet can simultaneously support multiple forms, and can know the relationship between these tables, which help to strengthen relations between the rules and navigation data. DataSet is completely disconnected connected, that is, through the network can be transmitted to the remote client, and remote client can be returned to the server (which can be used to change the DataAdapter updates to the database). Manually read and update data Although DataSet and DataAdapter very simple and very useful, but they are not the best choice at all times. Sometimes, we need to quickly read data, and does not require storage of data in memory copy. Other times, we have to update the data in the database may not come from the DataSet, or to update the data in the database, will be deemed unsuitable for use DataSet data on memory. Read data, we could only find data and send it to other locations, such as Web pages, XML files or text files. Under these circumstances, the direct use DataReader object will be more effective. To explain its methods of work, we replicate data to text file. Add in another Forms button, and was named btnCopyToFile. Then add the following code: Private Sub btnCopyToFile_Click (ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles btnCopyToFile.Click Dim cn As New SqlConnection () Dim cm As New SqlCommand () Cn.ConnectionString = _ "Data source = localhost; Initial catalog = pubs; Integrated security = SSPI " Try Cn.Open () Try Cm.Connection = cn Cm.CommandText = "SELECT au_id, au_fname, au_lname FROM authors" 'Data processing here Finally Cn.Close () End Try Catch ex As Exception MsgBox (ex.ToString) End Try End Sub This is what we discussed in front of the code structure, it would open the Connection object and the closure of the operation included in the nested Try block to ensure that no matter what mistakes can close the connection. Please note that our allocation of a Command object to the use of SQL statements from the authors table in the choice of data. Here, we use Command object directly, rather than be embedded in the DataAdapter.