Asp. NET operation of database in the initial----add, delete, modify

asp.net| Data | database

Note: This article temporarily does not explain the data of the database pull up and display, because he involves more things, so we will explain in detail. This paper is mainly about the increase, deletion and modification of database.

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 a myconnection, and name it mycommand. (Method II) in (associated file). CS file in protected System.Data.OleDb.OleDbConnection myconnection; manually add below:
protected System.Data.OleDb.OleDbCommand mycommand;
This in private void InitializeComponent (). myconnection = new System.Data.OleDb.OleDbConnection () is manually added below the next line:
This. mycommand = new System.Data.OleDb.OleDbCommand ();
You can 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). Open database: Myconnectio.open ();
Then we need to specify the SQL command to execute for mycommand: myCommand.CommandText = "delete from admin";
Then we need to give mycommand the data source (Execute SQL command on that database): Mycommand.connection = myconnection; then we execute the mycommand command: mycommand. ExecuteNonQuery (); If we have "delete from admin" in execution, then we need to execute "INSERT into admin (admin_code,admin_pwd) VALUES (' AA ', ' BB ')", Then we just specify mycommand again specifies the SQL command to execute: myCommand.CommandText = "INSERT into admin (admin_code,admin_pwd) VALUES (' AA ', ' BB ')", Then execute the 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, adding a new record
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 (); ' Because a record is added, returns 1
//or Mycommand1.executereader (), adds a record, and then returns an System.Data.OleDb.OleDbDataReader type Object that is: EOF
//or MYCOMMAND1. ExecuteScalar (); Adds a record first, returns the object
Myconnection.close () that is not materialized

-------------------------------------------------------------------
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, returns n
//or Mycommand1.executereader (), deletes N records, and then returns an System.Data.OleDb.OleDbDataReader type Object that is: EOF
//or MYCOMMAND1. ExecuteScalar (); deletes n records first, returns the object
Myconnection.close () that is not materialized

------------------------------------------------------------
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= ' where admin_code= '";
Mycommand1.connection = myconnection;
Mycommand1.executenonquery (); ' Because 1 records have been modified, returns n
//or Mycommand1.executereader (), modifies 1 records, and returns an object of type System.Data.OleDb.OleDbDataReader, which is: EOF
//or MYCOMMAND1. ExecuteScalar (); 1 records were first modified to return the object
Myconnection.close () that is not materialized

Iii. the difference between the ExecuteNonQuery (), ExecuteScalar () and the ExecuteReader method of mycommand:
1, ExecuteNonQuery (): Execute SQL, return an integer variable, if SQL is the record of the database operation, then return 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 () not NULL) "The method returns –1 after the table was 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 (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 ()