Usage of ExecuteNonQuery(),ExecuteScalar(),ExecuteReader

Below we will explain in detail how to add, delete, and modify the database 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('aaddq','as ','ss')";
MyCommand1.Connection = MyConnection;
MyCommand1.ExecuteNonQuery();'Since a record is added, 1 is returned
//or MyCommand1.ExecuteReader(); first add a record and then return one System.Data.OleDb.OleDbDataReader type object, the object is: EOF
//or MyCommand1. ExecuteScalar(); first add a record and return the unimplemented object
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(); 'Since n records have been deleted, n will be returned
//Or MyCommand1.ExecuteReader(); delete n records first record, and then returns an object of type System.Data.OleDb.OleDbDataReader, which is: EOF
//or MyCommand1. ExecuteScalar(); first delete n records and return an unimplemented object
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='212',Admin_pwd='43' where admin_code='23'";
MyCommand1.Connection = MyConnection;
MyCommand1.ExecuteNonQuery();'Because 1 record has been modified, so return n
//Or MyCommand1.ExecuteReader(); first modified a record, and then returned an object of System.Data.OleDb.OleDbDataReader type, which is: EOF
//Or MyCommand1. ExecuteScalar(); first modified 1 record, returning an unimplemented object
MyConnection.Close();
}


3. Differences between MyCommand’s ExecuteNonQuery(), ExecuteScalar(), and ExecuteReader methods:
1. ExecuteNonQuery(): Execute SQL , returns an integer variable. If SQL operates on database records, then returns the number of records affected by the operation. If it is SQL="CREATE TABLE LookupCodes (code_id smallint IDENTITY(1,1) PRIMARY KEY CLUSTERED, code_desc varchar( 50) NOT NULL)" then this method returns -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 create a LookupCodes table, and then return -1
//Or MyCommand1.ExecuteReader(); First create a LookupCodes table, Then return an object of type System.Data.OleDb.OleDbDataReader, which is: EOF
//or MyCommand1. ExecuteScalar(); First create a LookupCodes table and return the unimplemented object
MyConnection.Close();
}


2. ExecuteScalar(): Execute SQL, (if SQL is a query Select) return the first row and first column of the query result, if (if SQL is not a query Select) then return an unmaterialized object, because the object It is not columnarized, so the returned result cannot be ToString() or Equals(null), which means that the returned result has no effect.

3. The executeReader method executes SQL, (if the SQL is a query Select) and returns a collection of query results, type It is System.Data.OleDb.OleDbDataReader. You can get the queried data through this result. If (if the SQL is not a query Select) then return a collection of System.Data.OleDb.OleDbDataReader type without any data (EOF)


4. Summary:
There are many methods of operating the database in ASP.Net, and it is necessary to achieve unification People with different goals may adopt different methods. Just like in ASP, some people like to use RS.ADDNEW, and some people like to use "Insert Into". It mainly depends on personal habits. Of course, there are different methods in terms of performance. There may be big differences. This can only be achieved by accumulating experience bit by bit in our daily study. By the way, the ASP.Net page provides an operation 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()