C # and database Access Technology Summary (18)

ADO Code Synthesis Example

OLE DB has been described earlier. NET and SQL Server.NET data providers can be used to connect to different data sources.

The following code not only provides a comprehensive demonstration of the general steps to access the database using the two data providers of ADO, but also describes the general steps of accessing the database using different types of ADO component collections.

You can learn more about the similarities and differences between the two types of data provider access through code.

Using OLE Db.net Provider

the data provider for OLE DB can access databases such as access and SQL, with the following code://Setting the connection string stringDbname=@"User.mdb"; stringdb=Server.MapPath (dbPath);stringConnectionString ="Provider=Microsoft.Jet.OLEDB.4.0;Data source="+db;//setting up SQL statements stringStrsql="Select UserId, UserName from User";//Create a Connection object based on the connection stringOleDbConnection conn=NewOleDbConnection (connectionString);//Create a DataAdapter object based on connection objects and SQL statementsOleDbDataAdapter da=NewOleDbDataAdapter (strSQL, conn);//Create OleDbCommandBuilder to work better with DataAdapterOleDbCommandBuilder cb=NewOleDbCommandBuilder (DA);

The code above demonstrates the general process of creating a connection object from a connection string, populating a DataSet with a Dataadpate object, and then submitting data updates using a DataSet object and binding to the DataGrid.

The code above also demonstrates the general process of accessing a database using connection, DataAdapter, and datasets.

Ado. NET accesses the database offline, so it is not necessary to maintain a connection to the database in the process of using the dataset to obtain data from the database, and to connect to the database until the database is submitted with the updated data. This approach is suitable for Yu Hai volume data update scenarios.

In the above code, because the data operation process is relatively simple, so until the end of data update submission is not disconnected, that is, there is no "offline" access to the database connection.

The reason for not taking the "offline" approach is that in database access operations, frequent connection and disconnection of data sources also requires a certain amount of system resources, and if the cost is greater than the cost of maintaining a short-term data connection, choose "always-on" connectivity.

Using SQL Server. NET Provider

The following code demonstrates how to use SQL Server's. Netprovider to connect and access data. //Connection Stringstringconnectionstring="server=local;database=northwind;user=sa;pwd=;";//Establish a connectionSqlconnection =NewSqlConnection (connectionString);//SQL statementsstringStrsql="Select UserID, UserName from User";//Create SqlCommand objects based on connection objects and SQL statementsSqlCommand cmd=NewSqlCommand (strSQL, conn); Conn. Open ();//To create a SqlDataReader object using the Command objectSqlDataReaderReader=cmd. ExecuteReader ();//populating a DataGrid control with a DataReader objectDatagrid_user.datasource=Reader;datagrid_user.databind ();//Close ConnectionConn.   Close (); The code above also demonstrates the general way to access a database using connection, command, and DataReader objects. 

A survey of database access

The previous way of accessing a connected database with two different data providers (Provider) is to use SQL Server's Provider if the data source is SQL Server, or if the data source is ODBC or another type of database, you can choose OLE The provider of the DB.

It also describes how to access databases using Connection+command+datareader objects and using connection+ dataadapter+dataset objects.

depending on the characteristics of DataReader, the usage scenarios for accessing the database in Connection+command+datareader mode are:

• Access data is used only for display, not modification.
• operate on only one data source, or on a single table.
• For data, you only want backward sequential access without repeating the traversal.
• The amount of data accessed is small and does not require a large amount of data to be stored in memory.
• The result set that needs to be accessed is too large to be put into memory all at once, and DataReader can be used for successive accesses.

The DataSet supports offline access and can be used in the following ways:

• The same business logic requires access to multiple data sources, such as requesting data from both SQL Server and the Oracle database.
• Because a dataset can contain more than one DataTable, if the data object you need to access comes from multiple tables, you can use the table of the dataset to store management separately.
• If the data volume of the access operation is large, using the offline access mechanism of the dataset can relieve the pressure on the database load.

DataGrid control and Database access technology

In real-world applications, it is often necessary to display the data information obtained with the ADO component in the interface for users to browse or modify.

Can be used by the. NET's DataGrid control implements this functionality.

DataGrid control and data binding

The main purpose of the DataGrid control is to implement data binding, which is to bind the data displayed on the DataGrid control together with the data in the background database and to change synchronously.

In addition, the DataGrid control displays the data result set that is queried as a table, the default access is read-only and cannot be modified, and can be modified and deleted by setting the record.

DataGrid code Example

The data in the database can be dynamically bound to the DataGrid object and displayed by the following steps.

(1) Create an access type database under D, named Student.mdb. Create a new Studentlnfo table in which the fields are shown in the table.

Field	Chinese description	Data type	Notes
< Span style= "FONT-SIZE:14PX;" >sid	< Span style= "FONT-SIZE:14PX;" > study number	text	primary key
< Span style= "FONT-SIZE:14PX;" >sname	< Span style= "FONT-SIZE:14PX;" > name	text	a
Sex	Gender	Text	One

Note: the "one" in this book indicates that there is no need to set relevant information.

When you are done, insert some records into it, such as (001,tom,male).

(2) After you open the Visual Studio environment, select File | New ' | ' New Web Site command, in the new Web site Pop-up dialog box, select ASP. NET project, enter the site name Testdatagrid and path Caroot~datagrid, and all code and configuration files for the login module are placed under this project.

(3) In the integrated development environment, in the Solution Explorer, select the project, right-click, select the Add New Item command from the popup shortcut menu, and create a new Web configuration file named Web. config. This profile is primarily used to manage some of the global data for the login module.

Where you set the connection properties of the database in the configuration file, use the data Provider of OLE DB to connect to the Access data source, and the code is as follows.

　　

<configuration>  <appsettings><add key="connstr" value= "  "></add>  </appsettings><connectionstrings/>

(4) In the integrated development environment in the Solution Explorer, select the project, right-click, in the Popup shortcut menu, select the Add New Item command, create a new Web form, named Showdatagrid.aspx.

(5) Open the Web form of the Toolbox, drag a DataGrid control onto the page, and from the DataGrid's property bar, you can see that the DataGrid object is called DataGrid1.

Select the DataGridView control, right-click, select Properties from the popup shortcut menu, click the "..." button in the columns of the property bar, and in the "Edit column" dialog box that pops up, add the "number", "name" to the DataGridView control by clicking the "Add" command. and gender 3 columns, and set the datapropertyname of each column to the database field corresponding to the column for data binding. The specific settings are shown in the table.

Data column Name	DataPropertyName
School Number	Sid
Name	SName
Gender	Sex

(6) Double-click the blank space of the form to enter the corresponding logic code file ShowDataGrid.aspx.cs.

At the top of the file, add the namespace statement for the database access reference:

  using System.Data.SqlClient;

and add a string definition of the connection database at the beginning of class:

Static string strconnect =system.configuration.configurationmanager.appsettings["connstr  "];

Because Dataprovider is used, the objects of the ADO component begin with OLE DB.

The main business logic for the above code is:

(1) Create a database connection object based on the connection string and create a OleDbCommand object based on the connection object.

(2) According to the SQL statement, query all the student's information, in the SID's/is ordered.

(3) Use the Oledbdataapapter object to populate the query results into a dataset. All datasets in the dataset table.

(4) Set the data source of the DataGrid to. DataSet data table for data binding.

This way, when this page is opened, the DataGrid will be able to bind to the Access database to display student information in the database.

C # and database Access Technology Summary (18)