. NET connection to SQL Server

Knocking at the computer room, VB. Net do the front desk of the computer room for operation, use SQL Server to do backstage to save the data, the foreground and backstage contact, use to ADO. It is a set of object-oriented class libraries for interacting with data sources. Typically, a data source is a database, but it can also be a text file, an Excel table, or an XML file. Ado. NET allows interaction with different types of data sources and databases. Simple point is that the database implementation of the increase, delete, change, check

Ado. The two main components for accessing and managing data in net are. NET Framework data providers and datasets.

A.. NET framework, there are 4 categories: Connection,command,datareader,dataadapter.

Understand the connections, differences, and features between them

1.Connection Class (Database connection Class)

A) used to connect, open, and close the database connection.

b) in each database-related operation, we want to operate on the database so we must first connect and open, and then close the connection after the operation is complete.

2.Command Class (Database command Class)

A) issue a command to the database operation. Divided into two kinds

1. Query: Select

2. Non-query: Update,insert,delete

b) Premise: After you need to connection the connection and open the database.

3.DataAdapter Class (Data adaptation Class)

A) similar to command, operate on the database.

b) Differences: As shown, the main application is in DataSet dataset, the bridge between the database and the dataset, transfer data. It is a two-way transmission.

4.DataReader Class (Data read Class)

A) function: Store the query results. With the command class, store the query results.

b) reading from this class of data queried from the database can only be read in order, and only one piece of data can be read at a time.

two. DataSet class (Data set Class)

A) the same as the DataReader class, for storing data. But there are differences.

b) Different:
When data is stored in a dataset, data in the dataset can still be used when the database is disconnected.

Data stored in a dataset can be read arbitrarily without having to read sequentially.

three. Actual Operation

Using the. NET Framework Data Provider and dataset requires a reference to import system.data.Sqlclient

1. Database connection strings, different databases are not the same, take SQL Server as an example.

Dim connstring As String = " Server=fxq_pc;database=jifang_sys;user id=sa;password=123456 "

Database Connection object: Dim conn as new Sqlconnection (connstring)

To open and close a database connection

Conn.Open ()

Conn. Close (): Release database connection

2.Command objects, manipulating SQL Server

Creates a Comman object based on the creation of the connection object.

Two parameters: 1. The name of the database connection object. 2. SQL statement of the operational database to be executed

Command object instantiation: Dim cmd as New SqlCommand (Sql,conn)

3. Execute the Command object's SQL statement

Querying SQL statements to form a Command object

In the SQL statement parameter of the operations database, the keyword is select

Executes the ExecuteReader method of the Command object, returning the DataReader type data.

Dim Dr as new Sqldatareader=cmd. ExecuteReader ()

Stores the query data to the database read object.

if (Dr.read ()) ' Determine if query data exists in DR

{

Stringstr = dr [0].tostring (); ' Fetch the data from the 1th row 1th column in the query results.

}

Non-query SQL statement to form another command object

The keyword in the SQL statement is Insert,update,delete,

The object executes the ExecuteNonQuery method. Returns an integer type data.

The return value is-1, which indicates that the data table operation failed.

The return value is 0 and has no effect on the data in the database table;

The return value is an integer that is the number of data rows updated in the database.

Dim I As Integer

I=cmd. ExecuteNonQuery ()

Both 4.DataSet and DataAdapter are used when querying a data table.

Creating DataAdapter is similar to command. Requires two parameters.

1. Execute the SQL statement, which is the execution of the query statement, the keyword is select.

2. Database Connection object;

Dim Ada as New SqlDataAdapter (Select-sql,conn);

Create a DataSet object and populate the DataSet with DataAdapter

Dim DS as New DataSet

Ada.fill (DS) ' Populate the DataSet object;

Four. Summarythrough practical operation to understand the theoretical knowledge of ADO, in practice, each operation must first connect and open the database, and then execute the command, the order "increase, delete, change" into a category, "check" into another category. "Increase, delete, change" Return is the shaping variable, is the number of rows in the database, "check" the contents of the database. When operations are required, SQL statements and database connection objects are executed as parameters. After these clear, every time the operation, will be used, in the actual operation, it is necessary to encapsulate this part, and then repeat the call on it, save a lot of code, but also effective to avoid errors, improve the code quality.

. NET connection to SQL Server