C # ADO. NET access to SQL Server databases < go >

Ado. NET objects mainly include connection, Command, DataReader, DataAdapter, Dataset,ado. NET access to the database consists of two steps: establishing a database connection, reading or updating data.

First, establish a database connection

The connection object is responsible for establishing and controlling the connection between the user application and the database . All database connections use the connection string, which is a semicolon-delimited number of information that changes depending on the type of database and the content of the access.

Format of the connection string: "server= server name or server IP address; database= database name; User id= username; pwd= Password "

The following methods are used to connect a SQL Server database using the Connection object:

Using System.Data.SqlClient; Reference namespace

......

SqlConnection con=new SqlConnection (connection string);

Con. Open ();

......

Database-related Operations

......

Con. Close ();

1. Configure a string to connect to a database in the Web. config file

<configuration>

<connectionStrings>
<add name= "Connection string name" connectionstring= "Data source= server name or server IP address;

Initial catalog= database name; Persist Security info=true; User id= username; password= Password "

Providername= "System.Data.SqlClient"/>

<add name= "ConnectionString" connectionstring= "server=127.0.0.1;database=fxt_xpx;uid=sa;pwd=zdwpzdwp123456" Providername= "System.Data.SqlClient"/>

</connectionStrings>

</configuration>

2. Get the database connection string from the Web. config file in C #

Connection string =configurationmanager.connectionstrings["connection string name"]. ToString ()

Second, read the data

When an application is connected to a database, it is possible to read data from a table in a database, usually in two ways: one is to use command and DataReader objects, and the other is to use DataAdapter and DataSet objects.

1. Reading data using command and DataReader objects

DataReader provides forward-only read-only pointers to data retrieved from a SQL database. Because the DataReader class is abstract and cannot be instantiated directly, you need to create a command object if you want to use the DataReader object. The ExecuteReader method of the Command object creates a DataReader object that reads the read-only, forward-only data stream returned by the Select command from the database, and reads only one piece of data at a time.

SqlCommand com=new SqlCommand (Cmdstr,con);//Create SqlCommand objects with the specified SQL command and connection object

SqlDataReader dr=com. ExecuteReader ();//Execute SQL statement, return SqlDataReader object

while (Dr. Read ())//loop read, one record at a time

{

Loop in-body statement, for example: string s=dr["column_name"]. ToString ();

}

Dr. Close ();

When data is read in this way, there is only one line of content in memory, so it not only improves the performance of the application, but also helps reduce the overhead of the system. The way in which data is read using the DataReader object is appropriate for the following scenarios:

• No need to cache data;
• The results to be processed are too large to fit in memory;
• Requires fast access to data in a forward-only, read-only manner;

The limitations of the DataReader object include the following 3 points:

• Data can only be read forward;
• Can only read the data, cannot modify the data;
• Only one table of data can be processed;

Note: When DataReader is in use, connection is used exclusively. That is, when reading data with DataReader, the connection object associated with the DataReader object can no longer be used by other objects. Therefore, when you are finished using DataReader, you should explicitly call the close () method of DataReader and the Connection Association.

2. Reading data using DataAdapter and DataSet objects

DataAdapter is the communication medium between the dataset and the database, DataAdapter opens a connection and executes the specified SQL command, populating the captured data into a dataset. You can also update the data in the dataset to the data source. Common methods for DataAdapter objects are: 1. Fill () Gets the data from the data source to populate the dataset;2. Update () updates the data in the dataset to the data source.

A dataset is a cache of data in memory, equivalent to a small relational database in memory that is disconnected from the data source. The structure of a dataset is similar to a relational database, with properties such as tables, rows, and columns. It is primarily used to store data in memory and can read the contents of the entire data table at once.

The DataSet object can hold data obtained after the DataAdapter object executes the SQL command. The dataset is also a collection object, a DataSet object that includes a set of DataTable objects and DataRelation objects that the application can pass through the DataTable object and the DataColumn object within the DataTable object, The operation of the DataRow object reads data.

SqlDataAdapter Da=new SqlDataAdapter (Cmdstr,con);//Create SqlDataAdapter objects with the specified SQL command and connection object

DataSet ds=new DataSet ();//Create DataSet object

Da. Fill (ds, "table_name");//Fill the dataset with the Fill method of SqlDataAdapter and create a DataTable object named "table_name" where the data is stored

The process by which the dataset reads data is as follows:

1. Create a connection

2. Create a DataAdapter Object

3. Create a DataSet object

4. Execute the Fill () method of the DataAdapter object

5. Bind a table in a dataset to a data control

The process of reading the data DataReader is as follows:

1. Create a connection

2. Open the connection

3. Create a Command object.

4. The ExecuteReader () method of executing the command

5. Binding DataReader to a data control

6. Close DataReader

7. Close the connection

The Fill method of the DataAdapter automatically checks to see if the database connection is open, and if it is not open, the open () method is automatically called before the connection is opened, and then the fill operation is done, and the close () method is called automatically to close the database connection after the data is populated. Therefore, you do not need to add the open () and close () methods in your code.

When you use the command object, you need to add the open () method manually to open the connection to the database, and finally add the close () method to close the connection.

Third, update the data

Data update refers to the operation of adding, deleting, and modifying data. There are two ways to update data: 1, using the DataAdapter object, 2, using the Command object.

1. Updating data using the DataAdapter object

After the data is populated into a dataset with the DataAdapter object, the data in the dataset can be modified, added, and deleted, and then the data in the dataset is updated back to the database.

SqlDataAdapter Da=new SqlDataAdapter (Cmdstr,con);

DataSet ds=new DataSet ();

Da. Fill (DS, "table_name");

sqlcommandbuilder cb=new SqlCommandBuilder (DA); //Bind SqlDataAdapter object, automatically generate SQL command to update SQL Server from DataSet

Update the data for the DataSet object, for example:

Ds. tables["table_name"]. rows[0]["column_name"]=abc;//modifying data

Add data

Delete data

da. Update (DS, "table_name"); //Update data for DataSet back to SQL Server database

Note: Data in a dataset must have at least one primary key column or a unique column. If there are no primary or unique columns, calling update () will result in an invalidoperation exception and will not generate an INSERT, update, or delete command that automatically updates the database.

2. Updating data with the Command object

Iv. using Command object to implement transaction processing of database

A transaction is a group of related tasks in which the tasks are either all successful or all fail. The result of the final execution of a transaction is only two states, that is, commit or terminate.

During a transaction execution, if a step fails, the data changes involved in the transaction scope need to be restored to a specific point set before the transaction execution, which is called a rollback . For example, if a user wants to insert 10 records into a table, and during execution, an error occurs while inserting to 5th, the transaction rollback operation is performed and the 4 records that have been inserted are removed from the data table.

The following code is an example of an ASP. NET application implementation transaction, where the code adds records to the database and uses the Try...catch statement to catch the exception and, when an exception occurs, performs a transaction rollback operation.
Public Void Insert (Int ID)
{

Con. Open ();

String cmdstr= "INSERT INTO table_name (ID) VALUES (" +id+ ")";

sqltransaction St=con. BeginTransaction (); Call the BeginTransaction () method of the SqlConnection object to create a SqlTransaction object

SqlCommand cmd=new SqlCommand (Cmdstr,con);

Cmd. transaction=st;

Try

{

Cmd. ExecuteNonQuery ();

st.commit (); //Commit a transaction

Con. Close ();

}

Catch

{

Response.Write ("<script>alert (' Insert failed, perform transaction rollback ') </script>");

St. Rollback (); //Perform a transaction rollback operation

}
}

C # ADO. NET access to SQL Server databases < go >