Asp. NET operation of database operations _ self-study Process

One, define OleDbCommand type variable: mycommand
　　
To add, delete, and modify the database we also need to define a OleDbCommand or SqlCommand object based on the type of myconnectio (note that if MyConnection is a OleDbConnection type, Then only use OleDbCommand, if MyConnection is SqlConnection type, then only use SqlCommand. This assumes that MyConnection is a OleDbConnection class).
　　
Method One
You can drag and drop a OleDbCommand like drag and drop myconnection and name it mycommand.
Method Two
In (associated file). CS file in protected System.Data.OleDb.OleDbConnection myconnection; manually add below:
protected System.Data.OleDb.OleDbCommand mycommand;
In private void InitializeComponent ()
This. MyConnection =newsystem.data.oledb.oledbconnection () is manually added below the next line:
This. mycommand = new System.Data.OleDb.OleDbCommand () to complete the definition of mycommand
Description: The role of mycommand is to execute SQL commands
　　
Ii. adding, deleting and modifying the database using the defined Myconnectio and mycommand
　　
First we need to connect and open a database (see our previous article for connection and open operations on the database).
To open a database:
Myconnectio.open (); Then we need to specify the SQL command to execute for mycommand:
myCommand.CommandText = "Delete from admin"; then we need to specify the data source for mycommand (Execute SQL command on that database):
Mycommand.connection = myconnection; then we execute the mycommand command:
mycommand. ExecuteNonQuery (); If we're in the execution, there's
"Delete from admin";
Insert into admin (admin_code,admin_pwd) VALUES (' AA ', ' BB '), then we just specify mycommand to specify the SQL command to execute:
myCommand.CommandText = "INSERT into admin (admin_code,admin_pwd) VALUES (' AA ', ' BB ')" and then execute mycommand. ExecuteNonQuery (); (because the database is not closed, so we do not need and can not myconnectio.open () again, the same reason because there is no change in the mycommand data source, so we do not need to specify Mycommand.connection = MyConnection ;）
　　
Below we will explain in detail how to add, delete, modify the database in Page_Load (), finally we summarize ExecuteNonQuery (), ExecuteScalar (), ExecuteReader usage
　　
1. Add new records
private void Page_Load (object sender, System.EventArgs e)
{
Myconnection.open (); ' Open Database
Mycommand1.commandtext = "INSERT into admin values (' aaddq ', ' as ', ' SS ')";
Mycommand1.connection = myconnection;
Mycommand1.executenonquery (); ' Because a record is added, it returns 1
or Mycommand1.executereader (), add a record first, and then return an object of System.Data.OleDb.OleDbDataReader type, which is: EOF
or MyCommand1. ExecuteScalar (); First add a record to return the object that is not materialized
Myconnection.close ();
}2, deleting existing data
private void Page_Load (object sender, System.EventArgs e)
{
Myconnection.open (); ' Open Database
Mycommand1.commandtext = "Delete * from admin";
Mycommand1.connection = myconnection;
Mycommand1.executenonquery (); ' Returns n because of the deletion of N records
or Mycommand1.executereader (), delete N records first, and then return an System.Data.OleDb.OleDbDataReader type object: EOF
or MyCommand1. ExecuteScalar (); Delete n records first, return the objects that are not materialized
Myconnection.close ();
}
3. Modify 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= ' where admin_code= ' 23 '";
Mycommand1.connection = myconnection;
Mycommand1.executenonquery (); ' Because 1 records have been modified, return n
or Mycommand1.executereader (), first modify 1 records, and then return an object of type System.Data.OleDb.OleDbDataReader, which is: EOF
or MyCommand1. ExecuteScalar (); 1 records were first modified to return the objects that were not materialized
Myconnection.close ();
}
　　
Iii. the difference between the ExecuteNonQuery (), ExecuteScalar () and the ExecuteReader method of mycommand:
　　
1, ExecuteNonQuery (): Execute SQL, return an integer variable, if the SQL is the record of the database operation, then return the effect of the operation of the number of records, if it is
Sql= "CREATE table Lookupcodes (code_id smallint IDENTITY (1,1) PRIMARY KEY CLUSTERED, Code_desc varchar () not NULL)" Then in the table-creating The method returns 1 after it has been successfully built.
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 varcha R () not NULL) "; mycommand1.connection = myconnection;
Mycommand1.executenonquery (); ' First create a lookupcodes table, and then return-1
or Mycommand1.executereader (), first create a lookupcodes table, and then return an object of type System.Data.OleDb.OleDbDataReader, which is: EOF
or MyCommand1. ExecuteScalar (); First create a lookupcodes table that returns the objects that are not materialized
Myconnection.close ();
}
2. ExecuteScalar (): Execute SQL, (if SQL is query Select) returns the first column of the first row of the query result, if (if SQL is not a query select), returns the object not materialized because the object is not materialized, so the return result cannot be ToString (), cannot be equals (null), which means that the return result has no effect
　　
3, the ExecuteReader method executes SQL, (if SQL is query Select) returns a collection of query results, the type is System.Data.OleDb.OleDbDataReader, you can get the data of the query by this result. if (if SQL is not a query select) returns a collection of System.Data.OleDb.OleDbDataReader types without any data (EOF)
　　
Four, Summary:
　　
Asp. NET in the operation of the database a lot of, to achieve a unified goal of different people may adopt different methods, as if in the ASP some people like to use rs.addnew, some people like to use "Insert into", mainly to see the habits of individuals, Of course, in the performance of different methods may be a big difference, this can only rely on us in the ordinary study of the accumulation of experience. And by the way, ASP.net page provides an action method 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 ()