Using ADO to access SQL Server databases

in the previous blog we gave you a brief introduction of some of the vb.net language, as for the theoretical knowledge of learning we can use the knowledge system of VB as the basis, and then the object-oriented programming language knowledge fusion can be programmed in combat.

What if we need to access an enterprise relational database (such as SQL Server or Oracle) and need to include data from tables that are composed of complex relationships? We can use the ADO built-in feature to extract and manipulate data just like inserting, updating, and deleting data in SQL Server.

Let's start with a brief introduction to ADO. The so-called ADO is Activexdata Objects, after years of development into today's ADO. Ado. NET architecture is designed to be a disconnected schema, that is, the connection time of an application to a database is simply to ensure that data is retrieved or updated and then disconnected. If you are retrieving data, you can store the data in one of the data objects of ADO, such as a dataset or DataView. This processing mechanism has one of the biggest advantages is that the database can support more user operations, improve the efficiency of database access.

When we talk about ADO, we are actually talking about the System.Data and System.oledb namespaces. Many of the classes in both namespaces can be used to access data from almost any type of data source, and this time we look at only the sections related to SQL Server, and here are a few classes related to database access.

OleDbConnection, OleDbDataAdapter, and OleDbCommand are derived from System.data.oledb,dataset and DataView and are derived from the System.Data namespace. Before you use these classes, you must import the namespaces to which they belong.

OleDbConnection class

This class provides a connection to a data source whose constructor accepts an optional parameter and is a connection string. The connection string consists of a number of parameters whose general parameters are listed in the following table:

Once you have defined and initialized a connection object, you can invoke any method of the OleDbConnection object, such as open and close.

OleDbDataAdapter class

This class can read and write data in all OLE DB data sources, and can be set to include the SQL statement or stored procedure name to be executed. OleDbDataAdapter does not really store any data, but serves as a bridge between the DataSet class and the Data Warehouse.

The property SelectCommand property of the OleDbDataAdapter class is derived from the OleDbCommand class to specify which data to select and how to select the brush data. When you specify the SelectCommand property, you must set additional properties to let the OleDbDataAdapter object know how to retrieve the data.

Its common properties are connection: Set the OleDbConnection object used to access the database, CommandText: Sets the SQL statement or stored procedure name used to pick the data, CommandType: Sets the value that determines how the CommandText property is interpreted.

Once the SelectCommand property is set correctly, the next step is to execute the specified SQL statement or stored procedure by calling the ExecuteNonQuery method and return the number of rows affected by the SQL string stored procedure. For a SELECT statement, the return value is always-1, and for statements such as Insert,update and delete, the return value is the number of rows affected by the command.

The Fill method is then called to populate the DataSet object with the syntax format: Fill (dataset,string), the dataset parameter specifies a valid dataset object, and the string parameter specifies the name of the table to be manipulated.

DataSet class

This class is used to store data retrieved from the database and saved to the client's memory. Its object contains a collection of tables, relationships, and constraints that are consistent with the data read from the database.

DataView class

This class is typically used to sort, filter, find, edit, and navigate data from a dataset, and DataView is bindable, which means that he can bind to a control like a dataset. The memory data used by the dataset and the DataView object is a DataTable object, and the DataView class is a custom view of the DataTable object that inherits from the DataSet.

OleDbCommand class

This class represents a query to a database that can be selected, inserted, updated, or deleted, and can be represented as a SQL string or stored procedure. The query that executes can contain parameters, or it can not contain. The OleDbCommand class has many constructors, but the simplest method is to initialize a OleDbCommand object without parameters so that once the object is initialized, you can set the desired properties to = Perform the task at hand. The specific property description of the people to find out their own information, confined to the length of the details.

Light said no practice fake bashi, let's do a small example, to show you how to access the SQL Server database: First create a Windows Forms application, place a DataGridView control on the form, and then write the following code in the Code Editor:

Imports system.dataimports System.Data.OleDbPublic Class Form1 Inherits System.Windows.Forms.Form Dim objconnection As OleDbConnection = New OleDbConnection ("Provider=sqloledb;data source=localhost;initial Catalog=charge_sys; User Id=sa;    password=123456; ") Dim objdataadapter As New OleDbDataAdapter () Dim objdataset As DataSet = New DataSet () Dim objreader As Oledbdatarea Der Private Sub Form1_Load (sender as Object, e as EventArgs) Handles mybase.load objconnection.open () obj Dataadapter.selectcommand = New OleDbCommand () objDataAdapter.SelectCommand.Connection = Objconnection Objda TaAdapter.SelectCommand.CommandText = "Select Studentno,cardno,studentname from Student_info" Objdataadapter.selec Tcommand.commandtype = CommandType.Text objDataAdapter.SelectCommand.ExecuteNonQuery () Objdataadapter.fill (o        Bjdataset, "Charge_sys") objconnection.close () objDataAdapter = Nothing objconnection = Nothing DaTagridview1.datasource = Objdataset Datagridview1.datamember = "Charge_sys" End Sub ' Protected Overloads over Rides Sub Dispose (ByVal disposing as Boolean) ' objdataset = Nothing ' End subend Class

The author uses VisualStudio2012, the database used is SQL Server2008, the following picture illustrates the data content we want to access:

The results of the operation are as follows:

This is just a way to connect to the database, other ways everyone is interested to try. Software development is inseparable from the database, so mastering a variety of languages and databases interactive an unavoidable technology, VB. NET introduces a lot of things, I just think that the most important part of the present to summarize the study, for the subsequent reconstruction of the system to prepare.