ExecuteNonQuery (), ExecuteScalar (), ExecuteReader usage

Below we will explain in detail how to add, delete, modify the database in Page_Load (), finally we summarize the usage of ExecuteNonQuery (), ExecuteScalar (), ExecuteReader.

--------------------------------------------------------------
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 (); ' Since a record is added, it returns 1
or Mycommand1.executereader (), add a record first, and then return an object of type System.Data.OleDb.OleDbDataReader, which is: EOF
or MyCommand1. ExecuteScalar (); Add a record first to return an object that is not actually listed
Myconnection.close ();
}


-------------------------------------------------------------------
2. Delete 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 (); ' Since N records were deleted, N is returned
or Mycommand1.executereader (), delete the N records first, and then return an object of type System.Data.OleDb.OleDbDataReader, which is: EOF
or MyCommand1. ExecuteScalar (); Delete n records to return an object that is not actually listed
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 (); ' Since 1 records have been modified, return n
or Mycommand1.executereader (), modify 1 records first, and then return an object of type System.Data.OleDb.OleDbDataReader, which is: EOF
or MyCommand1. ExecuteScalar (); 1 records were modified to return objects that were not actually listed
Myconnection.close ();
}


The difference between ExecuteNonQuery (), ExecuteScalar (), and the ExecuteReader method on mycommand:
1, ExecuteNonQuery (): Execute SQL, return an integer variable, if SQL is a record of the database operation, then the return operation affects the number of records, if it is sql= "CREATE TABLE lookupcodes (code_id smallint IDENTITY (PRIMARY KEY CLUSTERED, Code_desc varchar () not NULL) "Then the method returns-1 after the table is created successfully.
For example:
private void Page_Load (object sender, System.EventArgs e)
{
Myconnection.open (); ' Open Database
Mycommand1.commandtext = "CREATE TABLE lookupcodes (code_id smallint IDENTITY () PRIMARY KEY CLUSTERED, Code_desc varcha R (N) 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, the object is: EOF
or MyCommand1. ExecuteScalar (); First create a lookupcodes table that returns an object that is not actually listed
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) then returns an object that is not actually listed, because the object is not actually serialized, so the returned result cannot be ToString (), cannot equals (null), which means that the returned result has no effect

3. The ExecuteReader method executes SQL, (if SQL is a query select) returns a collection of query results, the type is System.Data.OleDb.OleDbDataReader, and you can get the data for the query through this result. if (if SQL is not a query select) then returns a collection of System.Data.OleDb.OleDbDataReader types without any data (EOF)


Iv. Summary:
Asp. NET in the operation of a lot of database, to achieve a unified goal of different people may take a different approach, as if some people in the ASP like to use Rs.addnew, some people like to use "Insert into", mainly to see the personal habits, Of course, in the performance of different methods may have a large difference, this can only rely on our ordinary learning in a bit of accumulation of experience. Also, 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 ()

