C # connect to and update a database

Database operations can be divided into two types: Query (select) and update (insert, delete, update ). Why? Let's take a closer look at the differences between the two types. select only uses the data from the database, and the other three modify the physical data of the database. Capucivar has elaborated on database query operations in the previous article. This article describes how to update data.

To update database information, first connect to the database. This capucivar has been introduced in C # database query. To update a database, you need an object: OleDbCommand. This object indicates the SQL statement or stored procedure to be executed on the data source.

This object has three attributes: 1. CommandText indicates the text of the command to be set; 2. Connection indicates the Connection of the command to be set; 3. CommandType indicates the type of the command to be set, the default is an SQL statement (but if it is not an SQL statement, you must specify the command type ). After the OleDbCommand object is set, you should execute the SQL statement. The ExecuteNonQuery () method is to execute an SQL statement. If you cannot remember this method, we will teach you a simple way: divide the word "ExecuteNonQuery" into three parts: "execute not to query", that is, update data.

The following is an example to familiarize yourself with database updates:

Use Visual Studio2005 to make the following interface:

 

After the interface is completed, an empty shell is created. The next step is to add events to it. We also need to use the ConnDb class in the previous article to add a method in the class: update () to update the database, which contains a string SQL parameter.

Public class ConnDb

{OleDbConnection conn = null; // The object connecting to the database

// The following is how the constructor connects to the database.

Public ConnDb ()

{If (conn = null) // determines whether the connection is null.

{Conn = new OleDbConnection ();

Conn. ConnectionString = "provider = sqloledb.1; data source = ..; initial catalog = capucivar; user id = sa; pwd ="; // database connection string}

If (conn. State = ConnectionState. Closed)

{Conn. Open (); // Open the database connection

}}

// The following method is used to find data from the database.

Public DataSet query (string SQL)

{DataSet ds = new DataSet (); // DataSet is a set of tables.

OleDbDataAdapter da = new OleDbDataAdapter (SQL, conn); // query data from the database

Da. Fill (ds); // Fill data in DataSet

ConnClose (); // close the connection

Return ds; // return results

}

// The following method is to update the database.

Public int update (string SQL)

{OleDbCommand oc = new OleDbCommand (); // indicates the SQL statement or stored procedure to be executed on the data source

Oc. CommandText = SQL; // set the command text

Oc. CommandType = CommandType. Text; // you can specify the command type.

Oc. Connection = conn; // set the command Connection

Int x = oc. ExecuteNonQuery (); // execute the SQL statement

ConnClose (); // close the connection

Return x; // return an affected number of rows.

}

// The following connClose () method is to close the database connection.

Public void connClose ()

{If (conn. State = ConnectionState. Open)

{// Judge the connection status of the database. If the connection status is enabled, close it.

Conn. Close ();}}}

Write down the operation class of the database. Then we can implement the add, delete, and modify functions:

Let's take a look at the ideas. First, add a user and write the code: 1. Get the data (user name and password) filled by the customer; 2. Write the insert statement to pass the user information through ConnDb () class added to the database; 3. Return an affected number of rows to notify the customer of successful execution. The Code is as follows:

Private void add_but_Click (object sender, EventArgs e)

{// Click the event

// Obtain the username and password entered by the user

String uname = this. uname_text.Text; string upass = this. upass_text.Text;

String SQL = string. format ("insert into users values ({0}, {1})", uname, upass); // spelling SQL statement inserts the user information into the database

Int x = new Db. ConnDb (). update (SQL); // execute the SQL statement using the update () method of the ConnDb () object and return an affected number of rows

If (x> 0)

{// If the number of affected rows is greater than 0, the insertion is successful. Otherwise, the insertion fails.

MessageBox. Show ("added successfully! ");

} Else {

MessageBox. Show ("failed to add! ");

}}

After adding a user, it is displayed in the listBox on the right:

Public void refurbish ()

{String SQL = "select * from users"; // query data using SQL statements

DataSet ds = new Db. ConnDb (). query (SQL); // a DataSet is returned for the query.

This. listBox1.DisplayMember = "username"; // columns to be displayed in listBox

This. listBox1.DataSource = ds. Tables [0]; // the data source of listBox

}

The execution result is as follows:
Below:

 

When you select an option in the listBox on the right, you can delete or modify it accordingly. The deletion code is as follows:

Private void del_but_Click (object sender, EventArgs e)

{String uname = this. listBox1.Text; // obtain the value selected in listBox.

String SQL = string. Format ("delete from users where username = {0}", uname); // delete a user using a spelling SQL statement

Int x = new Db. ConnDb (). update (SQL); // call the update () method to return the number of affected rows

If (x> 0)

{// Determine whether the deletion is successful based on the number of affected rows returned

MessageBox. Show ("deleted successfully! ");

} Else {

MessageBox. Show ("deletion failed! ");}}

 

The deleted result is as follows:
Below:

After clicking the "Update" button, a window should pop up to display the information of the user selected by the customer for the customer to update. The updated code is as follows:

Private void upa_but_Click (object sende