Sort database connections

Steps for connecting to a basic database:
 
1) Create and open a database link
Dim conpubs as sqlconnection
Conpubs = new sqlconnection ("server; uid; Pwd =; database = ;")
Conpubs. open ()

Create an example of the sqlconnection class named conpubs, and pass a connection string parameter through the sqlconnection class conpubs constructors to initialize the conpubs class. Finally, the link is actually opened by calling the open () method of the sqlconnection class. However, the connection time is only 15 seconds. You can add a piece of code in Initialization:
Conpubs = new sqlconnection ("server; uid; Pwd =; database =; connect_timeout = 90 ")

3) Select the read method command datareader or dataadapter and dateset
If you want to obtain some data and quickly display it on the web page, use the previous one. However, if you want to operate on the memory database records that are connected, use the latter one.
The data adapter must be connected to the database table, and the reader method must be called to retrieve each new record from the underlying database and record it To the memory, therefore, the same datareader cannot be used for multiple requests on multiple pages or across the same page.
Dateset provides memory-resident data representation. Therefore, you can perform operations on the database query results as a whole, such as sorting and filtering, but it does not need to connect to the database server, but it needs to occupy a large amount of memory.
Therefore, if datareader is used to retrieve different datasets on each request page, but if the same data is obtained on the request page, such as sorting, dateset is used.
4) display data

5) disconnect the database

Method 1: Command datareader
1. Steps to complete the SELECT statement:

Note: In the following example, The namespace is not referenced. When you connect to the database, you must write the following two lines of code:
Imports system. Data
Imports system. Data. sqlclint

1. When multiple statements are returned:
1) Create and open a database link
2) create a database command that represents the SQL SELECT statement to be executed
3) execute this command using the executereader () method and return a datareader
4) traverse datareader to display the query result (read method)
Example:
Dim conpubs as sqlconnection
Dim cmdselectauthors as sqlcommand
Dim dtrauthors as sqldatareader
Conpubs = new sqlconnection ("Server = localhost; uid = sa; Pwd =" "; database = pubs ")
Conpubs. open ()
Cmdselectauthor = new command ("select au_lname from authors", conpubs)
Dtrauthors = cmdselectauthors. executereader
While dtrauthors. Read ()
Response. Write ("<li> ")'*
Response. Write (dtrauthors ("au_lname "))'*
End wile
Conpubs. Close ()

You can also write loop statements as follows:
Txtbox1.text & = vbnewline
Dim intfield as integer
For intfield = 0 to dtrauthors. Field. Count =-1
Txtbox1.text & = dtrauthors (intfield). tosring (). padright (15)

Next

2. If you only need to return one statement, you only need to use executescalar

Example:
Dim conpubs as sqlconnection
Dim cmdselectcount as sqlcommand
Conpubs = new sqlconnection ("Server = localhost; uid = sa; Pwd =" "; database = pubs ")
Conpubs. open ()
Cmdselectcount = new command ("select count (*) from authors", conpubs)
Lblresult. Text = cmdselectcounts. executescalar
Conpubs. Close ()

2. Steps to complete the insert and delete update statements:
1) Create and open a database link
2) create an SQL statement to be executed
3) execute the command using the exectenonquery () method
Example:
Dim conpubs as sqlconnection
Dim cmdselectauthors as sqlcommand
Conpubs = new sqlconnection ("Server = localhost; uid = sa; Pwd =" "; database = pubs ")
Conpubs. open ()
Cmdselectauthor = new command ("insert authors (productname, unitprice) values ('milk', '12. 45')", conpubs)
Cmdselectauthors. executenonquery
Conpubs. Close ()

SQL statement:
Update authors set au_lname = 'Smith 'Where au_lname = 'benner'
Insert authors (au_lname) values ('Smith ')
Delete authors where au_lname = 'Smith'

You can input data in two ways:

Insert authors (productname) values (@ au_lname)
Cmdselectauthors. Parameters. Add (@ au_lname, txtauthorname. Text)

Or directly:
Insert authors (productname) values ('"+ txtauthorname. Text + "')

Category 2: dataadapter and dateset
The dataadapter class serves as a bridge between the dateset and the data source it represents. You can use dataadapter to fill in the dateset, or modify the dateset to update the existing data.
　　　　
Steps:
1) create a dateset
2) create a database connection
3) use SQL statements to connect to the database to create a dataadapter
4) Call the fill method of dataadapter to pass the dateset name and the new datetable name.
Dim D as DataGrid
Dim A as sqlconnection
A = new sqlconnection ()
Dim B as sqldataadapter
Dim C as Dataset
C = new dataset
B = ("select",)
A. open ()
B. Fill (C, "au_lname ")
D. datasource = C
D. databind ()

Note:
SQL uses single quotes to indicate the start and end of a string.
Http://www.connectionstrings.com/can find the connection string you want