Database operations for C # INSERT, select, Delete, update

Please see the following interface definition:

The code is as follows

Copy Code

<summary> Database Operation interface </summary> public interface Hidatabase { <summary> Find data </summary> <param name= "SQL"/> SQL statements that need to be executed <returns> find the data set </returns> System.Data.DataSet find (String sql); <summary> Update data </summary> <param name= "SQL"/> SQL statements that need to be executed <returns> the number of data bars affected </returns> int update (String sql); <summary> Add data </summary> <param SQL statement executed by name= "SQL"/> <returns> id</returns> of current records int add (String sql); }

It was preceded by the SQL Action statement: INSERT, SELECT, Delete, update four of these, in the previous Java version, the students suggested that the interface of the data to define such a three method on the line, Because a closer look at delete and update is actually a property operation: Execute the SQL statement, and get the number of rows affected. Of course, this type of writing will throw away some of the advanced usage of SQL, like things, but it can be implemented in subclasses. This interface can be oriented to all storage modes:
1. Database server level storage;
2. File level storage;
3. Memory level storage.

The following is an example of familiarity with updating a database:

First use visual Studio2005 to make the following interface:

After the interface is done, it is equivalent to making an empty shell. The next step is to add events to the inside. We would also like to borrow the Conndb class in the previous article to add a method in the class: Update () to update the database, which has a parameter string sql.

The code is as follows

Copy Code

public class Conndb {OleDbConnection conn = null;//object to connect to the database The following is a constructor connection database Public Conndb () {if (conn==null)//To determine whether the connection is empty {conn = new OleDbConnection (); Conn. Connectionstring= "Provider=SQLOLEDB.1;Data source=.; Initial Catalog=capucivar;user id=sa;pwd= ";//connection to database string} IF (Conn. state = = connectionstate.closed) {Conn. Open ();//Opening database connection } } The following method is a way to find data from a database Public DataSet query (String sql) {DataSet ds = new DataSet ();//dataset is a collection of tables OleDbDataAdapter da = new OleDbDataAdapter (sql,conn);//Query from database Bah Fill (DS);//populate DataSet with data Connclose ()//close connection Return ds;//returns results } The following method is to update the database public int update (String sql) {OleDbCommand oc = new OleDbCommand ()//represents the SQL statement or stored procedure to be executed on the data source Oc.commandtext = sql;//To set the text of the command Oc.commandtype = commandtype.text;//Set the type of command Oc. Connection = conn;//connection to set command int X=oc. ExecuteNonQuery ()//Execute SQL statement Connclose ()//close connection return x; Returns a number of affected rows } The following Connclose () method closes the database connection public void Connclose () {if (conn). state = = ConnectionState.Open) {//To determine the connection state of the database, turn it off if the state is open Conn.    Close (); }   }  }

The Operation class for the database is written. And then to implement and delete the function:

To sort out the idea, first add a user, how to write code: 1, get the customer fill in the data (username and password); 2, write the INSERT statement, add the user information to the database through the CONNDB () class, 3, and return an affected number of rows to inform the customer whether the execution was successful. The code is as follows:

The code is as follows

Copy Code

private void Add_but_click (object sender, EventArgs e) {//button click event The user name and password are filled in 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);//Spell SQL statement inserts the user information into the database int x = new Db.conndb (). Update (SQL),//Execute SQL statement through the update () method of the Conndb () object and return an affected number of rows if (x > 0) {//If the number of rows affected is greater than 0, the insert succeeds or the insertion fails. MessageBox.Show ("Add success!") ”); } else { MessageBox.Show ("Add failed!") ”); } }

After you add a user, it appears in the ListBox on the right:

The code is as follows	Copy Code
public void Refurbish () {String sql = ' SELECT * from users ';//sql statement query Data www.111cn.net DataSet ds = new Db.conndb (). query (SQL);//Queries return a dataset This.listBox1.DisplayMember = "username"; columns to be displayed in//listbox This.listbox1.datasource=ds. Data source for the tables[0];//listbox }

The results of the implementation are as follows:

When the customer selects an option in the right listbox, it can be deleted or modified accordingly. The code to delete is as follows:

The code is as follows

Copy Code

private void Del_but_click (object sender, EventArgs e) {String uname = this.listbox1.text;//Gets the value selected in the ListBox String sql = string. Format ("Delete from users where Username= ' {0} '", uname);/spell SQL statement Delete user int x = new Db.conndb (). update (SQL);//Call Update () method returns the number of rows affected if (x > 0) {//To determine whether the deletion was successful based on the number of rows returned MessageBox.Show ("Delete succeeded!") ”); } else{ MessageBox.Show (delete failed!)   ”); }  }

The results after the deletion are as follows:

After clicking on the "Update" button, a window should be displayed to display the customer's selected user information for the customer to update. The updated code is as follows:

The code is as follows

Copy Code

private void Upa_but_click (object sender, EventArgs e) {String uname = this.listbox1.text;//Gets the user information selected in the ListBox New Upd (uname). ShowDialog ()//Pop-up to update window Upd.cs} The code for Upd.cs is as follows: public partial class Upd:form {public upd ()//parameterless constructor {InitializeComponent (); } Public upd (String uname)//argument constructor {InitializeComponent (); This.uname_text. Text = uname;//The user name to a text box String sql = string. Format ("SELECT * from Users where username= ' {0} '", uname);//spelling SQL statement to find the user's information by user name DataSet ds = new db.conndb (). query (SQL); The information in the result set is listed below in the corresponding text box, respectively This.uid_text. Text = ds. Tables[0]. Rows[0][0].            ToString (); This.upass_text. Text = ds. Tables[0]. ROWS[0][2]. ToString (); } private void Button1_Click (object sender, EventArgs e)//Click on the "Confirm modify" button to respond to the event {int uid = Convert.ToInt32 (this.uid_text. Text);//Get UID String uname = This.uname_text. text;//Get user name Www.111cn.Net String upass = This.upass_text. text;//Get user Password String sql = string. Format (update users set Username= ' {0} ', userpass= ' {1} ' where uid={2} ', uname,upass,uid);//spell a modify SQL statement int x = new Db.conndb (). update (SQL);//Returns the number of affected rows if (x > 0) {//To determine whether the modification was successful based on the number of rows affected MessageBox.Show ("Modify success!") ”); This. Visible = false;//Hide the page } else { MessageBox.Show ("Modification failed!") ”); Return } } private void Button2_Click (object sender, EventArgs e) {//Click the Cancel button to respond to the event This. Visible = false;//Hide the page }  }}

The results of the modifications are as follows:

After each modification to the database, the data in the listbox to the right of the interface is updated once, so the refurbish () method should be called after each database operation. A simple code that uses C # to make the database more censored is finished. The code is very simple to write, just complete the simplest additions and deletions to check the function, you can make the above code more perfect.