Asp.net learning journey 4

Today, let's talk about the database-related classes. This can be a lot. When I first looked at it, I didn't know which class to use...

Connection object. to connect to the database, you must first construct a connection string. Here there is a class that can easily generate a connection string, then sqlconnectionstringbuilder
Sqlconnectionstringbuilder connstrbuilder = new sqlconnectionstringbuilder ();
Connstrbuilder. datasource = "127.0.0.1 ";
Connstrbuilder. userid = "sa"
Connstrbuilder. Password = "123456"

Sqlcommand object, used for database operations
Sqlconnection connection = new sqlconnection ("connstr ");
Sqlcommand command = new sqlcommand ("select * from table ");
Connection. open ();
Int COUNT = int. parse (command. executescaler (). tostring );
Connection. close;
Enable the connection before executing the SQL statement, and disable it after executing the SQL statement. optimization is to enable the connection as soon as possible and close it as early as possible.

Executernonquery (); execute add, update, and delete operations. The number of affected rows is returned.
Executerrreader (); returns the result of executing a query SQL statement | datareader object
Executescaler (); retrieves a single value from the database and returns the first column of the First row. For example: Select count (*) from Tab

Datareader is a read-only stream that reads rows. It can only read forward, but it is very efficient ., the instance cannot be directly instantiated and must be created through the command object. this object will be connected to the database all the time. Therefore, the data connection should be closed immediately after the datareader object is used up.

The first type is read in the order of field columns. If the field type is different, an exception occurs (the highest efficiency)
Sqldatareader reader;
Reader. getint (0)
Reader. getstring (1)

Second, read by column Index
Reader (1), which returns the object

The third type is to read data by column names (which is less efficient but flexible)
Reader ["column name"]


The dataadapter object is mainly used for data filling. The dataadapter object uses connection to connect to the data source, Uses command to operate the database, and sends the Retrieved Data to dataset. This object is automatically connected when operating the database. it is automatically disabled after use.

Datatable is the core object of ADO. net, just like a common database. It also has rows and columns, including datarow and datacolum, which is equivalent to a memory table.

The DataSet object is equivalent to a memory database. dataset provides an offline data source, which reduces the burden on the database and network. During program design, dataset objects can be used as data sources.


Paging query SQL
Select top3 * from Table order by userid number 123
Select top3 from table where userid not in (select top3 userid from Table order by userid) query no. 456
Page = (M % n) = 0? (M/N) :( M/N + 1)

Today we are here... this is a clear piece of data operations .......