Basic SQL Server and ASP. NET operations, sqlasp.net

Basic SQL Server and ASP. NET operations, sqlasp.net

Ado.net provides a variety of database operations, which can be divided into three steps:

• First, use the SqlConnection object to connect to the database;
• Second, create a SqlCommand object to execute SQL statements and call stored procedures;
• Third, perform operations on the "Results" returned after SQL or stored procedures are executed.

There are two types of operations to return results:

• First, use SqlDataReader to read a dataset from one row;
• Second, DataSet and SqlDataAdapter are used to operate databases.

Comparison:

• SqlDataReader moment and remote database serverKeep connectedThe remote data is transmitted to the client in the form of a "stream", which is "read-only. Because the database is accessed directly, the efficiency is high, but it is not convenient to use.
• DataSet acquires data from the data source to the local database at one time and establishes a micro-database (including the relationship between tables, rows, columns, rules, and tables) locally ), during this period, you can disconnect from the server and useSqlDataAdapterThe object operation "local micro-Database" is completed and updated to the remote database server at one time through SqlDataAdapter. This method is easier to use. However, the performance is slightly worse than the first one. (In general, the performance of the two is negligible .)The connection is disconnected after obtaining data.

A well-known structure of ADO. NET:

2. Writing the connection string

String connectString = "Data Source =.; Initial Catalog = Student; Integrated Security = True ";
Connections are generally written in webconfig.

3. SqlConnection object

Namespace: System. Data. SqlClient. SqlConnection;

Returns the database connection object, a parameter string. Instantiate the "connection object" and open the connection

SqlConnection sqlCnt = new SqlConnection(connectString);sqlCnt.Open();

After use, you need to disable "connection object"

sqlCnt.Close();

4. SqlCommand object

Namespace: System. Data. SqlClient. SqlCommand;

The SqlCommand object is used to perform database operations. There are three operation methods:

• SQL statement: command. CommandType = CommandType. Text;
• Stored Procedure: command. CommandType = CommandType. StoredProcedure;
• Entire table: command. CommandType = CommandType. TableDirect;

Instantiate a SqlCommand object

SqlCommand command = new SqlCommand (); command. Connection = sqlCnt; // bind a SqlConnection object

Or directly create from SqlConnection

SqlCommand command = sqlCnt.CreateCommand();     

Common Methods:

• Command. ExecuteNonQuery (): Returns the affected functions, such as adding, deleting, and modifying functions;
• Command. ExecuteScalar (): Execute the query and return the results of the first column of the first line;
• Command. ExecuteReader (): Returns a data stream (SqlDataReader object ).

Common Operations ① Execute SQL

SqlCommand cmd = conn. createCommand (); // create the SqlCommand object cmd. commandType = CommandType. text; cmd. commandText = "select * from products = @ ID"; // SQL statement cmd. parameters. add ("@ ID", SqlDbType. int); cmd. parameters ["@ ID"]. value = 1; // assign a Value to the parameter of the parameter SQL statement

② Call the Stored Procedure

SqlCommand cmd = conn. CreateCommand (); cmd. CommandType = System. Data. CommandType. StoredProcedure; cmd. CommandText = "Stored Procedure name ";

③ Entire table

SqlCommand cmd = conn. CreateCommand (); cmd. CommandType = System. Data. CommandType. TableDirect; cmd. CommandText = "table name"

5. SqlDataReader object

Namespace: System. Data. SqlClient. SqlDataReader;

The SqlDataReader object provides the read-only one-way data function. One-way: only the next data can be read in sequence. Read-only: the data in DataReader is read-only and cannot be modified. The data in the DataSet object can be read and modified at will.

It has a very important method: Read (). The return value is a Boolean value, which is used to forward data to the next data record. It is executed when the Boolean value is true, it is a false bounce. For example

SqlCommand command = new SqlCommand (); command. connection = sqlCnt; command. commandType = CommandType. text; command. commandText = "Select * from Users"; SqlDataReader reader = command. executeReader (); // execute the SQL statement and return a "stream" while (reader. read () {Console. write (reader ["username"]); // print the username of each user}

6. DataSet object 6.1 SqlDataAdapter;

Namespace: System. Data. SqlClient. SqlDataAdapter;

SqlDataAdapter is a bridge between SqlCommand and DataSet. it instantiates the SqlDataAdapter object:

SqlConnection sqlCnt = new SqlConnection (connectString); sqlCnt. open (); // create SqlCommandSqlCommand mySqlCommand = new SqlCommand (); mySqlCommand. commandType = CommandType. text; mySqlCommand. commandText = "select * from product"; mySqlCommand. connection = sqlCnt; // create SqlDataAdapterSqlDataAdapter myDataAdapter = new SqlDataAdapter (); myDataAdapter. selectCommand = mySqlCommand; // bind the SqlCommand object to be executed to the SqlDataAdapter object

The preceding SQL statement can be simplified

SqlConnection sqlCnt = new SqlConnection (connectString); sqlCnt. open (); // hides the definition of the SqlCommand object, and hides the binding between the SqlCommand object and the SqlDataAdapter object SqlDataAdapter myDataAdapter = new SqlDataAdapter ("select * from product", sqlCnt );

Attributes and Methods

• MyDataAdapter. SelectCommand attribute: SqlCommand variable, which encapsulates the Select statement;
• MyDataAdapter. InsertCommand attribute: SqlCommand variable, which encapsulates the Insert statement;
• MyDataAdapter. UpdateCommand attribute: SqlCommand variable, which encapsulates the Update statement;
• MyDataAdapter. DeleteCommand attribute: SqlCommand variable, which encapsulates the Delete statement.
• MyDataAdapter. fill (): fills in the execution result to Dataset, and hides and opens SqlConnection and executes SQL operations.

6.2 SQL commandbuilder;

Namespace: System. Data. SqlClient. SqlCommandBuilder.

DataSet operations (change, add, or delete) are only performed locally. To submit data to a database, you must use the SqlCommandBuilder object. It is used to update the data after the client completes data editing. The usage is as follows:

SqlCommandBuilder mySqlCommandBuilder = new SqlCommandBuilder (myDataAdapter); // assign the SqlCommandBuilder function myDataAdapter to myDataAdapter. update (myDataSet, "table name"); // submit the changed DataSet to the database. The second parameter is the name of the storage table in DataSet, it is not the real table name in the database (the two are the same in most cases ).

6.3 DataSet

Namespace: System. Data. DataSet.

Datasets, a local micro-database, can store multiple tables.

The first step to use DataSet is to fill in the Dataset (table) returned by SqlDataAdapter to the DataSet object:

SqlDataAdapter myDataAdapter = new SqlDataAdapter ("select * from product", sqlCnt); DataSet myDataSet = new DataSet (); // create DataSetmyDataAdapter. fill (myDataSet, "product"); // enter the returned DataSet as a "table" in DataSet. The table name can be different from the actual table name in the database, it does not affect subsequent operations such as addition, deletion, and modification.

① Access data in DataSet

SqlDataAdapter myDataAdapter = new SqlDataAdapter ("select * from product", sqlCnt); DataSet myDataSet = new DataSet (); myDataAdapter. fill (myDataSet, "product"); DataTable myTable = myDataSet. tables ["product"]; foreach (DataRow myRow in myTable. rows) {foreach (DataColumn myColumn in myTable. columns) {Console. writeLine (myRow [myColumn]); // traverse each cell in the table }}

② Modify data in DataSet

SqlDataAdapter myDataAdapter = new SqlDataAdapter ("select * from product", sqlCnt); DataSet myDataSet = new DataSet (); myDataAdapter. fill (myDataSet, "product"); // modify DataSetDataTable myTable = myDataSet. tables ["product"]; foreach (DataRow myRow in myTable. rows) {myRow ["name"] = myRow ["name"] + "commodity ";} // submit the DataSet modification to "Database" SqlCommandBuilder mySqlCommandBuilder = new SqlCommandBuilder (myDataAdapter); myDataAdapter. update (myDataSet, "product ");

Note: in operations such as modification and deletion, the table product must define the primary key, and the select field must also contain the primary key. Otherwise, the system will prompt "SelectCommand does not return any key column information, dynamic SQL generation of UpdateCommand is not supported." Error

③ Add a row

SqlDataAdapter myDataAdapter = new SqlDataAdapter ("select * from product", sqlCnt); DataSet myDataSet = new DataSet (); myDataAdapter. fill (myDataSet, "product"); DataTable myTable = myDataSet. tables ["product"]; // Add a DataRow myRow = myTable. newRow (); myRow ["name"] = "ante"; myRow ["price"] = 13.2; // myRow ["id"] = 100; if the id is "auto-increment", you can leave it unspecified here, even if the setting is invalid. rows. add (myRow); // submit the DataSet modification to "Database" SqlCommandBuilder mySqlCommandBuilder = new SqlCommandBuilder (myDataAdapter); myDataAdapter. update (myDataSet, "product ");

④ Delete a row

SqlDataAdapter myDataAdapter = new SqlDataAdapter ("select * from product", sqlCnt); DataSet myDataSet = new DataSet (); myDataAdapter. fill (myDataSet, "product"); // Delete the first row of DataTable myTable = myDataSet. tables ["product"]; myTable. rows [0]. delete (); SqlCommandBuilder mySqlCommandBuilder = new SqlCommandBuilder (myDataAdapter); myDataAdapter. update (myDataSet, "product ");

Attribute

• Tables: gets the set of Tables contained in DataSet.
• Relations: gets a set of links used to link a table and allow browsing from the parent table to the child table.
• HasEroors: indicates whether the DataSet object value has been initialized.

Method

• Clear clears all data of all tables in the DataSet object.
• Clone and copy the structure of the DataSet object to another DataSet object. The copied content includes all structures, relationships, and constraints, but does not contain any data.
• Copy Copies the data and structure of the DataSet object to another DataSet object. The two DataSet objects are identical.
• CreateDataReader returns a DataTableReader with a result set for each able object. The order of display is the same as that of Tables in the Tables set.
• Dispose releases resources occupied by DataSet objects.
• Reset initializes the DataSet object.

7. release resources

After the resources are used up, the connection and release should be closed in time. The specific method is as follows:

MyDataSet. dispose (); // release the DataSet object myDataAdapter. dispose (); // release the SqlDataAdapter object myDataReader. dispose (); // release the SqlDataReader object sqlCnt. close (); // Close the database connection sqlCnt. dispose (); // release the database connection object


Reference http://lib.csdn.net/article/csharp/36098
You can also refer to the http://lib.csdn.net/article/csharp/8594