Objects in the. NET database connection

Zookeeper

When learning VB. NET videos, several of them talk about the database design and connection of. NET. We are no stranger to database connection. We have been in touch with the redbooks and data center charging systems. However, in my impression, these knowledge about database connections is vague. It is even more informative about database connection objects.

I went back to several examples in the Redbook and talked about connecting to the database using the ADO control. It involves seven database connection objects (connection, command, recordset, Field, property, parameter, error)

So what objects are involved in database connections in. net learning today?

In fact, it is not difficult to find out whether it is the previous ADO or the present. NET, there are two permanent objects in the database connection and command, so in. net, how are they used?

 1. Connection

Database connection object, responsible for connecting to the database

Specific usage: different data providers have different namespaces, but they are basically the same. Take SQLserver as an example:

<Span style = "font-family: SimSun; font-size: 18px;"> Imports ss = System. data. sqlclient' declares the namespace Dim sConn as string, dbConn as ss. sqlConnectionsConn = "Server = localhost; Database = Dbname; integrated Security = SSPI" 'connection string dbConn = new ss. sqlConnection (sConn) 'is used to instantiate the connection object dbConn. open () 'Now, you can connect to the database to operate the data in the database. </span>

2. Command

DATABASE Command objects are mainly used to execute Operation commands, such as adding, deleting, modifying, and querying. They can also be used to execute pre-stored programs.

The command objects can be divided into SqlCommand, OledbCommand, OdbcCommand, and OracleCommand according to the database provider type.

You only need to instantiate it for use.

<Span style = "font-family: SimSun; font-size: 18px;"> Dim ssCmd as ss. sqlCommandssCmd = new ss. sqlCommand (strSql, sConn) 'strsql is a command statement such as select, and sConn is a connection character. </span>

Of course, in addition, the command object has three common methods:

ExecuteNonQuery (): returns the number of affected rows.

ExecteScalar (): Retrieves a single value from the database, the first column of the first row of the returned result

ExecuteReader (): executes an SQL statement and returns a DataReader object.

What does this mean when talking about DataReader objects? Let's introduce other objects in. net.

3. DataReader

A simple dataset is used to retrieve a read-only dataset from a data source. When retrieving data, this object only allows read-only and forward Data Reading and cannot return data, this object can be created by retrieving data from the data source using the ExecuteReader () method in the command object.

Its read () method is used to read the next record. If data is read, TRUE is returned; otherwise, FALSE is returned.

Based on the above two, let's take a look at its specific use:

<Span style = "font-family: SimSun; font-size: 18px;"> Dim ssReader as ss. sqlDataReader and the command above are the same principle: strSql = "select * from MERs order by lastName ASC, firstName ASC;" ssReader = ExecuteReader () 'execute SQL directive 'to start reading records, the following firstName and lastName are two column names, which constitute a record. Each cycle is performed, and a record do while ssReader is returned. read () Fn = System. convert. toString (ssReader ("firstName") In = System. convert. toString (ssReader ("lastName") me. listbox1.Items. add (In, + Fn) loop </span>

In addition. design. to operate the database offline, the Recordset object in the ADO data connection is replaced with DataSet to perform offline database operations

In ADO, the Recordset becomes the DataSet object, so the DataSet object in. NET is the DataSet!

4. DataSet

In layman's terms, it is actually an offline Recordset for offline data processing, because DataSet provides an offline data source (usually displayed as a table) to ease the network burden.

In the process of using DataSet, you still need to use the DataAdapter object. What is the role of DataAdapter?

In fact, if we analyze it carefully, if DataSet wants to perform offline processing on the client, although it is said to act as the data source, if it is not connected to the real data source, in the end, offline processing will fail. Therefore, we must build a bridge between the real data source and DataSet to enable communication between them. The bridge is the DataAdapter, which is obviously inseparable. Let's take a look at the DataAdapter object.

5. DataAdapter

The adapter object in. NET serves as a bridge between the data source and DataSet to retrieve and save data. So how does he implement communication between the two?

First, it connects to the data source through the database connection object (connection). Second, it uses the database command object (command) to retrieve data from the data source using the specified operation and send it to DataSet, or send data from DataSet to the data source.

Through the above introduction, I have a certain understanding of the specific functions of DataSet and DataAdapter. How do they work together? Let's look at a small example:

Perform operations based on the preceding connection and command code:

<Span style = "font-family: SimSun; font-size: 18px;"> Dim adapter as ss. sqlDataAdapterDim ds as System. data. dataSet uses the following code to transmit data from the data source to DataSet SQL = "select * from Products;" adapter = new ss. sqlDataAdapter (ssCmd) 'command to be transmitted is the command ds = new System in ssCmd. data. dataSet () 'instantiate datasetssConn. open () 'Here the open and close methods can be omitted, dataset will automatically check whether the connection is successful Adapter. fill (ds) ssConn. close () 'the following code can be used to extract data from dataset to Ds in listbox. tables ("Table "). tableName = "Products" Dim row as System. data. dataRow, name as stringFor each row in ds. table ("Products "). rows Name = convert. toString (row. item ("ProductName") Me. listBox1.Items. add (name) Next row </span>

The above are the objects commonly used in the connection to the VisualStudio.net database mentioned in the. net video. By understanding the basic functions and usage of these objects, we can take a closer look at. net database connections.

 

 

Zookeeper