Database Operations in ASP. NET (1) -- add, delete, and modify

Note: This article does not explain how to call up and display database data for the moment, because it involves many things, so we will explain it in detail. This article focuses on adding, deleting, and modifying databases.

1. Define oledbcommand type variable: mycommand

To add, delete, and modify a database, we also need to define an oledbcommand or sqlcommand object based on the myconnectio type (Note that if myconnection is oledbconnection type, you can only use oledbcommand; if myconnection is of the sqlconnection type, you can only use sqlcommand. Assume that myconnection is an oledbconnection class ). (Method 1) You can drag and drop an oledbcommand like dragging and dropping myconnection and name it mycommand. (Method 2) in the (associated file). CS File protected system. Data. oledb. oledbconnection myconnection; Add the following manually:
Protected system. Data. oledb. oledbcommand mycommand;
In private void initializecomponent (), manually add the following line of this. myconnection = new system. Data. oledb. oledbconnection:
This. mycommand = new system. Data. oledb. oledbcommand ();
You can complete the definition of mycommand.
Note: mycommand is used to execute SQL commands.

2. Use the defined myconnectio and mycommand to add, delete, and modify databases.

First, we need to connect to and open a database (for database connection and open operations, please refer to our previous articles ). Open Database: myconnectio. open ();
Then, we need to specify the SQL command to be executed for mycommand: mycommand. commandtext = "delete from admin ";
Next we need to specify the data source for mycommand (execute the SQL command for that database): mycommand. connection = myconnection; then we can execute the mycommand command: mycommand. executenonquery (); if we still execute "delete from admin"; then we need to execute "insert into admin (admin_code, admin_pwd) values ('A', 'bb ')", you only need to specify mycommand again to specify the SQL command to be executed: mycommand. commandtext = "insert into admin (admin_code, admin_pwd) values ('A', 'bb')", and then execute mycommand. executenonquery. (Because the database is not closed, we do not need to or can't myconnectio again. open (); similarly, because the data source of mycommand is not changed, we do not need to specify mycommand again. connection = myconnection ;)

Next we will explain in detail how to add, delete, and modify databases in page_load (). Finally, we will summarize the usage of executenonquery (), executescalar (), and executereader.

--------------------------------------------------------------
1. Add new records
Private void page_load (Object sender, system. eventargs E)
{
Myconnection. open (); 'Open the database
Mycommand1.commandtext = "insert into admin values ('addq', 'as', 'ss ')";
Mycommand1.connection = myconnection;
Mycommand1.executenonquery (); 'returns 1 because a record is added.
// Or mycommand1.executereader (); First add a record, and then return an object of the system. Data. oledb. oledbdatareader type, which is EOF
// Or mycommand1. executescalar (); first, add a record to return objects that are not actually listed.
Myconnection. Close ();
}

-------------------------------------------------------------------
2. Delete existing data
Private void page_load (Object sender, system. eventargs E)
{
Myconnection. open (); 'Open the database
Mycommand1.commandtext = "delete * from admin ";
Mycommand1.connection = myconnection;
Mycommand1.executenonquery (); 'Because N records are deleted, n is returned.
// Or mycommand1.executereader (); Delete N records first, and then return an object of the system. Data. oledb. oledbdatareader type. The object is EOF
// Or mycommand1. executescalar (); Delete N records first and return objects not listed in real time.
Myconnection. Close ();
}

------------------------------------------------------------
3. Modify existing data
Private void page_load (Object sender, system. eventargs E)
{
Myconnection. open (); 'Open the database
Mycommand1.commandtext = "Update admin set admin_code = '000000', admin_pwd = '43 'Where admin_code = '23 '";
Mycommand1.connection = myconnection;
Mycommand1.executenonquery (); 'returns N because one record is modified.
// Or mycommand1.executereader (); First modify one record, and then return an object of the system. Data. oledb. oledbdatareader type, which is EOF
// Or mycommand1. executescalar (); first, 1 record is modified to return objects not listed.
Myconnection. Close ();
}

Iii. Differences in mycommand executenonquery (), executescalar (), and executereader methods:
1. executenonquery (): executes the SQL statement and returns an integer variable. If the SQL statement operates on database records, the number of records affected by the operation is returned, if SQL = "CREATE TABLE lookupcodes (code_id smallint identity () primary key clustered, code_desc varchar (50) not null)", return-1 after the table is created successfully.
For example:
Private void page_load (Object sender, system. eventargs E)
{
Myconnection. open (); 'Open the 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 creates a lookupcodes table and then returns-1
// Or mycommand1.executereader (); first create a lookupcodes table, and then return an object of the system. Data. oledb. oledbdatareader type, which is EOF
// Or mycommand1. executescalar (); first, create a lookupcodes table and return objects that are not actually listed.
Myconnection. Close ();
}

2. executescalar (): Execute the SQL statement (if the SQL statement is a query SELECT statement) to return the first column of the first row of the query result. If (if the SQL statement is not a query SELECT statement) the returned result cannot be tostring () or equals (null) because the objects are not actually columned. That is to say, the returned results have no effect.

3. Execute the SQL statement using the executereader method (if the SQL statement is a SELECT query) to return a set of query results. The type is system. data. oledb. oledbdatareader. You can use this result to obtain the queried data. If (If SQL is not a query select), a set of system. Data. oledb. oledbdatareader types (EOF) without any data is returned)

Iv. Summary:
ASP. net has many database operation methods. To achieve a unified goal, different people may adopt different methods, as if some people in ASP prefer to use Rs. addnew, some people like to use "insert into". It mainly depends on their habits. Of course, there may be great differences in performance between different methods, this can only rely on the accumulation of 1.1 pieces of experience in our ordinary learning. In addition, the ASP. NET page provides methods similar to the following:
Oledbcommand2.parameters ("au_id"). value = textbox1.text
Oledbcommand2.parameters ("au_lname"). value = textbox2.text
Oledbcommand2.parameters ("au_fname"). value = textbox3.text
Oledbcommand2.parameters ("phone"). value = textbox4.text
Oledbcommand2.parameters ("Address"). value = textbox5.text
Oledbcommand2.parameters ("city"). value = textbox6.text
Oledbcommand2.parameters ("St"). value = textbox7.text
Oledbcommand2.parameters ("Zip"). value = textbox8.text
Oledbcommand2.parameters ("contract"). value = checkbox1.checked
Cmdresults = oledbcommand2.executenonquery ()