Note: This article does not explain the data transfer and display of the database, because he involved is more, so we will further explain in detail. This article mainly wants to increase, delete, and modify the database.
1. Define oledbcommand type variables: MyCommand
To increase, delete, modify the database. We also need to define an OLEDBCommand or SQLCOMMAND object according to the type of MyConnectio (please note that if MyConnection is the OLEDBConnection type, then you can only use oleDbcommand; if MyConnection is the SqlConnection type, then then you can only use Sqlcommand. It is assumed that myConnection is an OLDBConnection class). (Method 1) You can drag and drop an OLEDBCommand like drag and drop myConnection and name MyCommand. (Method II) protected System.Data.OleDb.OleDbConnection MyConnection in (association file) .CS file; manually add the following: protected System.Data.OleDb.OleDbCommand MyCommand; the private void InitializeComponent () in this.MyConnection = new System .Data.oledb.oledbconnection (); next to the next line to add: this.mycommand = new system.data.oledb.oledbcommand (); you can complete the definition of MyCommand: MyCommand's role is used to execute SQL commands
Second, use the defined MyConnectio and MyCommand to increase, delete, modify the database
First we need to connect and open a database (for the connection and open operations of the database, please see our previous article). Open the database: MyConnectio.Open (); then we need to specify the SQL command to be executed to myCommand: mycommand.commandtext = "delete from admin"; then we need to specify a data source to myCommand (execute SQL commands to that database): MyCommand. Connection = myconnection; then we execute myCommand command: mycommand. ExecutenononQuery (); if we do "delete from admin"; need to be executed after "Insert Intmin_code, admin_pwd) Values ('aa', ' BB '), then we only specify myCommand again specify the SQL command to be executed: myCommand.commandtext = "Insert Intmin (Admin_code, Admin_PWD) VALUES (' AA ',' BB ')", then execute MyCommand. ExecutenonQuery () . (Because the database is not closed, we don't need MYCONNECTIO.Open () ();
Below we will explain in detail how to increase, delete, modify the database in page_load (), and finally we will summarize the usage of ExecuteNonQuery (), ExecuteScalar (), ExecuteReader.
-------------------------------------------------- ------------ 1, add new record private void page_load (object sender, system.eventargs e) {myconnection.open (); 'Open Database MyCommand1.commandtext = "Insert Intmin Values 'Aaddq', 'AS', 'SS' "; MyCommand1.connection = MyConnection; MyCommand1.executeNonQuery (); 'Due to add a record, return 1 // or mycommand1.executeReader (); first add a record, Then return a system.data.oledb.oledbdataReader type object, which is: eof // or mycommand1. ExecuteScalar (); first add a record, return unprotected object myconnection.close ();} --- -------------------------------------------------- -------------- 2, remove existing data private void points_load (object sender, system.eventargs e) {myconnection.open (); 'Open Database MyCommand1.comMandtext = "delete * from "; mycommand1.connection = myConnection; mycommand1.executenonury ();" Due to the deletion of N records, return n // or mycommand1.executeReader (); first delete N records, then return a system.data.OleDb. OLEDBDataReader type object, this object is: eof // or mycommand1. ExecuteScalar (); first delete N records, return unprotected objects myconnection.Close ();
-------------------------------------------------- ---------- 3, modify the existing data private void page_load (object sender, system.eventargs e) {myConnection.Open (); 'Open Database myCommand1.commandtext = "Update Admin SET Admin_code =' 212 ', Admin_pwd = '23' "; myCommand1.connection = myconnection; mycommand1.executenononne (); 'Due to modifying 1 record, return n // or mycommand1.executeReader (); first modified 1 record, then return a system.data.oledb.oledbdataReader type object, which is: eof // or mycommand1. ExecuteScalar (); first modify 1 record, return unprotected object MyConnection.Close ( }
Third, about myCumenononQuery (), ExecuteScalar (), ExecuteReader method: 1. ExecutenonQuery (): Execute SQL, return a integer variable, if SQL is a record of the database, then the return operation affects records Number, if it is SQL = "CREATE TABLE LOOKUPCODES (Code_ID Smallint Identity (1, 1) Primary Key Clustered, Code_Desc VARCHAR (50) Not NULL" The method returns -1 after the table is created. For example: private void Page_Load (object sender, System.EventArgs e) {MyConnection.Open (); 'Open Database MyCommand1.CommandText = "CREATE TABLE LookupCodes (code_id smallint IDENTITY (1,1) PRIMARY KEY CLUSTERED, code_desc varchar (50) NOT NULL) "; myCommand1.connection = myConnection; myCommand1.executenonQuery (); first build a lookupCodes table, then return -1 // or mycommand1.executeReader (); first build a lookupcodes table, then return a system.data. OLEDB.OLEDBDATAREADER type object, this object is: eof // or mycommand1. ExecuteScalar (); first build a lookupCodes table, return unprotected object myconnection.close ();} 2, executescalar (): Execute SQL, (If SQL is Query SELECT) Returns the first line of the query results, if (if SQL is not querying SELECT), returning unprinted objects, because the object is not substantial, so the return result cannot be TOSTRING (), Can't equals (null), that is, there is no effect of returning results.
3, ExecuteReader method Execute SQL, (if SQL is a query select) Returns the collection of query results, the type is system.data.oledb.oledBDataReader, you can get the query data by this result. If (if SQL is not query select), return a collection of system.data.oledb.oledbdataReader type without any data (EOF)
