Vb.net-based database access-ADO. NET (1)

Vb.net-based database access-ADO. NET (1)

 

The name of ADO. NET originated from ADO (ActiveX Data Objects) and is a COM component library used to access

Ask data. When I first came into contact with ADO. NET, I felt that it had powerful functions when accessing the database. This blog post mainly introduces its structure, database access principles,

Specific implementation and learning experience.

 

I. Structure

 

 

Connection: you can connect to a data source or an SqlServer database.

 

Command: The Command sent to the database to perform database operations.

 

DataReader: reads data streams directly.

 

These three objects cannot be directly used in. NET applications. They must be accessed through the classes they inherit. For example

OleDbConnection, OleDbcommand, and OleDbDatareader Access the Access database through the data provided by ole db;

SqlConnection, SqlCommand, and SqlDataAdapter access the SQL Server database.

 

DataSet data is stored in the memory, which is expressed in XML format in the memory.

The DataAdapter object provides a bridge between the DataSet object and the data source. Use the Command object to execute SQL commands in the data source

Load the data to DataSet, and make the changes to the data in DataSet consistent with the data source.

 

 

2. Communication

 

 

 

Iii. Application During SQL Server access

 

Before writing code to access the SQL Server database, we reference the System. Data. SqlClient namespace. Used in its space

SqlConnection, SqlDataAdapter, SqlCommand, and SqlParameter.

 

SqlConnection: Contains the connection to the Database. The main parameters are Server (SQLServer name) and Database (Database

Name), User ID (User name used to connect to the database), Password (Password used to connect to the database)

For example, define the database connection object before accessing the database in the data room Charging System

Imports System. Data. SqlClient 'reference namespace 'defines the database connection object Dim conn As New SqlConnection ("server = WANGJU-PC; database = jfsf_net; user ID = sa; password = 123456 ")

 

SqlCommand class: Send the add, delete, modify, query command to the database to perform database operations. Its Attributes and methods are as follows:

Connection Properties

Before executing a query, you must set an attribute for SqlCommand, which is a SqlConnection object.

For example:

Dim cmd as New SqlCommand()cmd.Connection=conn

Commandtext attributes

Specifies the SQL statement or stored procedure to be executed.

Dim sql As String = "Select userId,password from T_User Where userId=@UserName And password=@PWD"Dim cmd As New SqlCommand(sql, conn)cmd.CommandText = sql

 

SqlParameters set

After learning about the CommandText attribute and the code above, you may want to ask @ UserName and @ PWD In the first line of code. What does it mean? No

In a hurry, let me give a small example of my life: I need to go to the restaurant for dinner on my birthday. I usually reserve a room one or two days before my birthday.

The room will not be scheduled to be the same time. Although people are not in the room, you will take up the room during this time period.

Yes. Similarly, the Placeholder "@ + variable" is provided in the SQL statement to occupy fields. You can insert a parameter to the placeholder.

Value. Let's see how it works.

For example:

 cmd.Parameters.Add(New SqlParameter("@UserName", User.UserId)) cmd.Parameters.Add(New SqlParameter("@PWD", User.Password))

ExecuteNonQuery Method

Executing an SQL statement and returning the number of rows affected by the query is also an effective way to check whether the command is executed.

The preceding attributes are processed accordingly. The following command is executed. Before execution, you need to open a database connection and call

ExecuteNonQuery method.

For example:

conn.Open()cmd.ExecuteReader()conn.Close()

 

 

Iii. Summary

 

Understanding and learning about ADO. NET database access technology greatly simplifies code writing in programming applications. Enhances code reusability, making programs more

Stable, easy to detect and handle errors. Here, I briefly summarized what I learned and understood it more clearly. Before learning this

A lot of reasons, after learning, there are many reasons for doing this and knowing something you don't know. The above is just one piece of content. In the next article, I

To sum up the bridge between the DataSet object and the data source-SqlDataAdapter.