Add a business layer for a website

The business layer is also called the middle layer. It is the essence of a website and controls applications. Program Business logic.

(The examples in this article only describe the problem. errors may be found and cannot be copied)

1. Connect to the database

1) Open the SQL connection

2) perform database operations and return data (golden rule: Open the database as soon as possible and close it as soon as possible)

3) Close the database connection

The connection class is sqlconnection, which specifies three important parameters: SQL Instance name, Database Access authorization information, and database name.

Example:

Sqlconnection connection = new sqlconnection connection. connectionstring = "sever =; user id = PSD = database =;" connection. open ();
 

2. Submit the command for executionStorageProcess

1) create a sqlcommand object:

The main function is to save the interactive information of the database. It stores the SQL statements to be executed andStorageThe name of the stored procedure.

 
Sqlcommand command = new sqlcommand (); Comand. Connection = connection; command. commandtext = "gegdd"; command. commandtype = commandtype. storeprocedure;
 

2) execute commands and close the connection

This is a proud time. After creating the connection and sqlcommand object, you can prepare to execute the command. Close the database connection immediately after execution.

Multiple execution methods (exccute) are introduced here: excutenonquery, excutescalar, and excutereader.

Excutenonquery is often used to execute SQL statements and stored procedures that do not return any records, such as update, insert, or delete operations. It returns an integer to indicate the number of affected rows.

 
Connectionopen (); command. excutenonquety (); command. Close ();
 

Excutescalar also returns a single value, but it does not return an affected number of rows, but the data read from the database. If multiple rows of data are obtained using the SELECT query, returns the data in the first column of the first row.

Executereader returns multiple records. It returns a sqldatareader object. A sqldatareader object reads and returns results one by one in the forward and read-only order. Its advantage is that it takes the fastest number of objects, and the disadvantage is that you need to open the connection. Before the connection is closed, you cannot use the same connection to perform any other database operations. In our solution, you can use sqldatareader to retrieve all records and store them in the datatable (which can store data without opening the connection), so that you can immediately close the database.

-----------------------------------------------

A datatable is classified into dataset. It is a very powerful object, such as a database in memory, which can store data tables, their data types, and the relationship between tables. Because of his line of responsibility, dataset will consume a lot of memory and avoid using him.

------------------------------------------------

ClassicCode:

 
Conn. open (); sqldatareader reader = comm. executereader (); // as mentioned above, excutereader returns a sqldatareader datatable table = new datatable (); table. load (Reader); reader. close (); Conn. closer ();