C # Database Access

ADO. NET is an object-oriented class library used to interact with data sources. Generally, the data source is a database, but it can also be a text file, an Excel table or an XML file.

Ado. Net supports two data access models: the connectionless model and the connection model. The connectionless model downloads data to the client and encapsulates the data to the memory on the client. Then, it can access the data in the memory like accessing the local relational database, such as dataset; the connection model depends on recorded access, which requires opening and keeping the link with the data source.

Connection class

To interact with a database, you must connect to it. Connection help specifies the database server, database name, user name, password, and other parameters required to connect to the database. The connection object will be used by the command object to know which data source to execute the command on.

The process of interacting with the database means that you must specify the operation you want to perform. This depends on the command object. You use the command object to send SQL statements to the database. The command object uses the connection object to indicate which data source to connect. You can use a command object to directly execute commands, or pass a reference to a command object to dataadapter, which saves a group of commands that can operate on the Data described below.

Command object

After the data connection is established, you can use the command object to execute commands such as query, modification, insertion, and deletion. commonly used methods of the command object include the executereader method, executescalar () method and executenonquery () method; you can use the executenonquery () method to insert data to execute the INSERT command.

Datareader class

Many data operations require that you only read a string of data. The datareader object allows you to obtain results from the SELECT statement of the command object. Considering performance, data returned from datareader is fast and only forward data streams. This means that you can only retrieve data from the data stream in a certain order. This is good for speed, but if you need to operate data, a better way is to use dataset.

DataSet object

Dataset objects are the representation of data in memory. It includes multiple able objects, and datatable contains columns and rows, just like a table in a common database. You can even define the relationship between tables to create a master-slave relationship (parent-child relationships ). Dataset is used in specific scenarios-it helps to manage data in memory and supports data disconnection. Dataset is the object used by all data providers, so it does not need a special prefix like data provider.

Dataadapter class

In some cases, the data you use is mainly read-only, and you rarely need to change it to the underlying data source. In the same situations, you need to cache data in the memory to reduce the number of database calls that do not change. Dataadapter disconnects the model to help you easily process the above situations. When a single batch of read/write operations on the database are continuously changed and returned to the database, the dataadapter fills in the (fill) DataSet object. Dataaadapter includes a reference to the connection object and automatically enable or disable the connection when reading or writing data to the database. In addition, dataadapter contains command object references for select, insert, update, and delete operations on data. You will define the dataaadapter for each table in the dataset. It will take care of all the connections to the database for you. All you will do is tell dataadapter when to load or write data to the database.

Datatable class

Datatable is a data grid control. It can be applied to VB and ASP. It can simply bind a database without code. It has a Microsoft-style user interface.

Summary

Ado. Net is a. NET technology that interacts with data sources. There are many data providers that allow communication with different data sources-depending on the protocol or database they use. However, no matter what data provider you use, you will use similar objects to interact with the data source. Sqlconnection object management and data source connection. The sqlcommand object allows you to communicate with the data source and send commands to it. To quickly read data only forward, use sqldatareader. To disconnect data, use dataset and implement the sqldataadapter that can read or write data sources.

**************************************** **************************************** **************************************** *********************

Note: SQL Server is used as an example in the following language description. My code is based on access.

1. connection object

You need to introduce the sqlconnection class in the namespace of system. Data. sqlclient

Connection objects include four types of database access object classes (data providers). Different namespaces are introduced based on different databases.

• SQL Server data provider, located in the namespace of system. Data. sqlclient
• ODBC data provider, located in system. Data. ODBC
• Ole db data provider, located in system. Data. oledb, which can be used for access.
• Oracle data provider, located in system. Data. oracleclient

Use the state attribute of the sqlconnection object to determine the connection status of the database. The attribute value is the enumerated value of connectionstate:

Broken: the connection is interrupted. This happens only after the connection is opened. You can close the connection and re-open it.

• Closed: Disabled
• Connecting: Connecting
• Executing: the connection object is executing the command
• Fetching: the connection object is retrieving data.
• Open: Open

Close connection:

Close (): close a connection. after closing, open () can be opened directly ()

Dispose (): close a connection and clear the resources occupied by the connection. After the connection is closed, you cannot directly open the connection using the open method. You must reinitialize the connection before opening it.

The following example shows how to enable and disable the ACCESS database:

Using system; using system. collections. generic; using system. componentmodel; using system. data; using system. drawing; using system. LINQ; using system. text; using system. windows. forms; using system. data. oledb; // oledb provider // using system. data. sqlclient ;//. sqlserver; provider namespace accesstest {public partial class form1: FORM {oledbconnection conn; // sqlconnection conn; private void button#click (Object sender, eventargs E) {try {// connection string // string connstr = "provider = Microsoft. jet. oledb.4.0; Data Source = D: \ accesstest \ surrounding rock stability prediction knowledge base. MDB; user id = admin; Password = "; string connstr =" provider = Microsoft. jet. oledb.4.0; Data Source = D: \ accesstest \ surrounding rock stability prediction knowledge base. MDB; "; // create a sqlconnection object. Note that different database types are accessed due to the introduction of namespaces. // conn = new sqlconnection (connstr ); // create oledbconnection object conn = new oledbconnection (connstr); Conn. open (); If (Conn. state = connectionstate. open) {MessageBox. show ("database connection enabled") ;}} catch {MessageBox. show ("database connection exception") ;}} private void button2_click (Object sender, eventargs e) {try {Conn. close (); // close if (Conn. state = connectionstate. closed) {MessageBox. show ("Database Connection closed");} Conn. open (); // re-open if (Conn. state = connectionstate. open) {MessageBox. show ("database connection has been re-opened");} catch (exception ex) {MessageBox. show (ex. message) ;}} public form1 () {initializecomponent ();}}}

2. Command object

Data command object, used to send SQL commands to the database, mainly in the following forms:

• Sqlcommand: sqlserver Database
• Oledbcommand: the database opened by ole db. Access and MySQL are all open databases of ole db.
• Odbccommand: used to send SQL statements to databases exposed to ODBC
• Oraclecommand: Oracle Database

The command object has three important attributes: connection, commandtext, and commandtype.

• The connection attribute is used to set the sqlconnection connection object used by the sqlcommand object.
• Commandtext is used to set the SQL statement or stored procedure to be executed on the data source.
• Commandtype is used to specify the commandtext type, which is the enumerated value of commandtype: storedprocedure stored procedure name; tabledirect table name; text-SQL text command

(1) executenonquery () method

Execute the SQL statement and return the number of affected items. It is usually used to add, delete, and modify commands.

(2) executereader () method

Run the SQL statement to return an instance of the sqldatareader object containing data. SELECT command

(3) executescalar ()

Execute the SQL statement and return the first column in the first row of the result set. If it is null, an empty reference is returned. Common Aggregate functions

• AVG average
• Count statistics
• Max
• Min minimum
• Sum and

Private void button3_click (Object sender, eventargs e) // command {try {If (Conn. state = connectionstate. open) {oledbcommand cmd = new oledbcommand (); cmd. connection = conn; cmd. commandtext = "select count (*) from physical and mechanical properties of Surrounding Rock of liyazhuang mine"; cmd. commandtype = commandtype. text; int I = convert. toint32 (CMD. executescalar (); // The first row and the first column of MessageBox. show ("Total" + I. tostring () + "data entries"); // executenonquery () method cmd. commandtext = "Update physical and mechanical properties of surrounding rock in liyazhuang mine set Ky = 12"; int K = convert. toint32 (CMD. executenonquery (); MessageBox. show (I. tostring () + "affected data entries"); // executereader method cmd. commandtext = "select * from physical and mechanical properties of surrounding rock in Li yazhuang mine"; oledbdatareader oddr = cmd. executereader (); string STR = ""; while (oddr. read () {STR + = oddr [2]. tostring (); // The number of column 2, starting with 0} MessageBox. show (STR); // executescalar method, see above} catch (exception ex) {MessageBox. show (ex. message );}}

3. datareader object

The data reader object that provides a read-only forward cursor. If the application only needs to read data forward and does not need to modify the data, you can use this object to read data.

There are four types of databases: sqldatareader, oledbdatareader, odbcdatareader, and oracledatareader.

(1) Whether the hasrows attribute exists

(2) read data, read method, and move the cursor down. Only one datareader can be opened for each connection.

Private void button4_click (Object sender, eventargs e) // datareader {try {oledbcommand cmd = new oledbcommand (); cmd. connection = conn; cmd. commandtext = "select count (*) from physical and mechanical properties of Surrounding Rock of liyazhuang mine"; cmd. commandtype = commandtype. text; oledbdatareader oddr = cmd. executereader (); If (oddr. hasrows) {MessageBox. show ("values in Data Tables");} string STR = ""; while (oddr. read () {STR + = oddr [2]. tostring (); // The number of column 2, starting with 0} MessageBox. show (STR);} catch (exception ex) {MessageBox. show (ex. message );}}

4. dataadapter data adapter

It serves as a bridge between dataset and data sources. The four attributes enable interconnection with data sources.

• Selectcommand: Send a query statement to the database
• Deletecommand: Delete
• Insertcommand: insert
• Updatecommand: Update

(1) The fill method is used to fill the dataset and use the SELECT statement to retrieve data from the data source. Note: The connection object must be valid, but you do not need to open it because the dataadapter object will automatically open and close the database.

Public int fill (Dataset dataset, string srctable );

Dataset: dataset to be filled in

Srctable: name of the source table used for table ing

(2) update the database by updating the data modified in dataset to the database.

Before calling the update method, you need to instantiate a commandbuilder class that can automatically judge other insertcommand, updatecommand, and deletecommand Based on the SQL statement specified by the selectcommand attribute of dataadapter, in this way, you do not need to set these three attributes. Instead, you can directly update the dataset, able, and datarow Arrays Using the update method of dataadapter.

Dataset Ds; // create a dataset oledbdataadapter Odda; // adapter private void button5_click (Object sender, eventargs e) // dataadapter {try {oledbcommand cmd = new oledbcommand (); cmd. connection = conn; cmd. commandtext = "select * from physical and mechanical properties of surrounding rock in Li yazhuang mine"; cmd. commandtype = commandtype. text; // another constructor for building command objects // oledbcommand cmd = new oledbcommand ("select * from physical and mechanical properties of Surrounding Rock of liyazhuang mine", Conn ); DS = new dataset (); // create a dataset o DDA = new oledbdataadapter (); // create a data adapter Odda. selectcommand = cmd; Odda. fill (DS, "Physical and Mechanical Properties of surrounding rock in liyazhuang mine"); datagridview1.datasource = Ds. tables [0];} catch (exception ex) {MessageBox. show (ex. message) ;}} private void datagridviewinclucellclick (Object sender, datagridviewcelleventargs e) {// click a piece of data to display its details. // Note that the following learning method is used: Try {textbox5.text = maid [maid. currentcell. rowindex]. cells [1]. value. tostring (); // The primary key textbox1.text = maid [maid. currentcell. rowindex]. cells [4]. value. tostring (); // The column number. The index starts from 0. textbox2.text = maid [maid. rowindex]. cells [5]. value. tostring (); textbox3.text = maid [maid. currentcell. rowindex]. cells [6]. value. tostring (); textbox4.text = maid [maid. currentcell. rowindex]. cells [7]. value. tostring ();} catch (exception ex) {MessageBox. show (ex. message) ;}} private void button6_click (Object sender, eventargs e) // update {try {datatable dt = Ds. tables ["Physical and Mechanical Properties of surrounding rock in liyazhuang mine"]; Odda. fillschema (DT, schematype. mapped); // load the table structure to the datarow DR = DT in the "Physical and Mechanical Properties of Surrounding Rock of liyazhuang mine" table. rows. find (textbox5.text); // create a datarow using DT. rows. when using the find method, you must set the primary key // set the datarow value Dr ["Ky"] = textbox1.text. trim (); Dr ["Kl"] = textbox2.text. trim (); Dr ["E"] = textbox3.text. trim (); Dr ["U"] = textbox4.text. trim (); // instantiate a commandbuilder oledbcommandbuilder odcb = new oledbcommandbuilder (Odda); Odda. update (DT);} catch (exception ex) {MessageBox. show (ex. message );}}

5. DataSet object

The DataSet object is like a small database in the memory, which can contain data tables, data columns, data rows, views, constraints, and relationships.

(1) Merge merge

Public void Merge (Dataset dataset, bool preservechanges, missingschemaaction missingscheaction );

• Dataset: the DataSet object to be merged.
• Preservechanges: whether to retain changes to the current dataset. Optional values: true and false.
• Missingschemaaction: enumeration Value
• Add: add necessary columns to complete the schema
• Addwithkey
• Error: if the specified column ing is missing, invalidoperationexception is generated.
• Ignore: Ignore extra columns

(2) Copy

Dataset ds2 = ds1.copy ();