How to access data in Visual Basic. Net (zz)

Http://info.shangdu.com/new/2003-5-8/200358113624.htm

Visual Basic. NET is an upgraded version of Microsoft's Visual Basic language. It is a fully object-oriented programming language. Microsoft has made great improvements. ADO is no longer directly supported in Visual Basic. net. There are new changes in access data, which is much more complex than before. We are familiar with ADO, and ADO is simple, convenient, and applicable. The fixed person uses ADO to access the most widely used databases on the market. Now I am working with Visual Basic. Net's powerful class development function. I have compiled the ADO data access method into components and completely encapsulated them for your convenience. The method is as follows:

Start Visual Studio. NET. Select a visual basic project in the new project, select a class library in the template, and enter the class library name in the name, for example, dataaccess. OK. Then enter the class library development environment and change class1 to adoaccess.

Add reference to the project menu, select the com page, and find Microsoft actionx Data Object 20 library or a later version. Com is set by Microsoft to distinguish between current. NET and be compatible with previous development methods. components before. Net can be found on the com page. (Microsoft actionx Data Object 20 library is an ADO component provided by Microsoft. It greatly facilitates database access and is one of the most practical tools for developing database-related software)

Write imports ADODB on the class name. connection imports ADODB. recordset, imports ADODB. cursorlocationenum, imports system. dbnull contains four reference statements, which reference macro definitions of ADO and the source of null functions.

Define a local connection object variable in the class. Private mcnndb as new ADODB. Connection ()

Then define the process of connecting to the access database. The ACCESS database is a local database developed by Microsoft and specified by aduseclient. It uses Microsoft. jet. oledb data access method, Microsoft. jet. oledb has multiple versions, of which 4.0 is the highest version and can access Access 2000. Therefore, the data provider here is specified as Microsoft. jet. oledb.4.0. After the local database and provider are specified, you can open a database and implement it using the open method. The complete code is as follows:

'Role: connect to the Access Database

'Parameter: dbname Database Name

Public sub connaccess (byval dbname as string)

Dim strdb as string

Mcnndb. cursorlocation = aduseclient

Mcnndb. provider = "Microsoft. Jet. oledb.4.0"

Mcnndb. Open (dbname, "admin ")

End sub

ODBC is a widely used method for connecting to a variety of databases. It has the power of a master key, but it needs to first establish a DNS through ODBC, which is not described in detail here. with DNS, you can access the connected database. When you access ODBC, specify the provider. The provider can only name msdasql Microsoft. Then, specify the data source, user name, and password through the connection string, specify it in the connectionstring below, and open it with open. Since ODBC is divided into user name and user name, we must implement them separately. With the function name overload function of the class, we compile two processes with the same name. The complete code is as follows:

'Purpose: connect to the ODBC database (users and passwords are not required)

'Parameter: dsnname is ODBC name

Public sub connodbc (byval dsnname as string)

Mcnndb. provider = "msdasql"

Mcnndb. connectionstring = "Data Source = '" & dsnname &"'"

Mcnndb. open ()

End sub

'Purpose: connect to the ODBC database (user and password must be specified)

'Parameter: dsnname ODBC name, userid username, userpwd userpassword

Public sub connodbc (byval dsnname as string, byval userid as string, byval userpwd as string) mcnndb. provider = "msdasql"

Mcnndb. connectionstring = "Data Source = '" & dsnname & "'user id ='" & userid &"';"&_

"Password = '" & userpwd &"

Mcnndb. open ()

End sub

SQL Server database is a widely used background database developed by Microsoft. Access SQL Server can be achieved by specifying the ODBC driver as SQL Server, that is, there must be driver = {SQL Server} in the connection string, because it is a background database, therefore, you must specify the name of the computer where the SQL server is located, usually called the server. The servername below describes this and then specifies the database to which the SQL Server is connected. Others are similar to ODBC above. SQL Server users are divided into Win NT and authorized users. Win NT users are super users who do not need to specify the user name and password. Otherwise, the user name and password must be specified, which is determined by the SQL Server database administrator, the complete code is as follows:

'Role: connect to the SQL Server database

'Parameter: servername server name, dbname Database Name

Public sub connsqlserver (byval servername as string, byval dbname as string)

With mcnndb

. Connectionstring = "uid =; Pwd =; driver = {SQL Server };"&_

"Server =" & servername &_

"; Database =" & dbname

. Open ()

End

End sub

'Role: connect to the SQL Server database

'Parameter: servername server name, dbname Database Name, userid username, userpwd User Password

Public sub connsqlserver (byval servername as string, byval dbname as string, byval userid as string, byval userpwd as string)

With mcnndb

. Connectionstring = "uid = '" & userid & "'; Pwd = '" & userpwd & "'; driver = {SQL Server };"&_

"Server =" & servername &_

"; Database =" & dbname

. Open ()

End

End sub

Oracle Database is currently the most influential background database. When accessing Oracle, specify its provider msdaora. Oracle is different from SQL Server because it is not managed through a database, so it does not need to specify a database, but it must specify a user when connecting, even a Super User, this is one of the reasons that its security performance is higher than SQL Server, so we only need to write a process. Others are similar. The complete code is as follows:

Role: connect to the Oracle database

'Parameter: servername server name, dbname Database Name, userid username, userpwd User Password

Public sub connoracle (byval servername as string, byval userid as string, byval userpwd as string)

With mcnndb

. Provider = "msdaora"

. Connectionstring = "User ID = '" & userid &"';"&_

"Password = '" & userpwd &"';"&_

"Data Source = '" & servername &"'"

. Open ()

End

End sub

With the above method of connecting to the database, we can directly read and write data. The following functions use ADO to expand data reading and writing.

When accessing a table, ADO must specify its cursor type and lock type, and specify different permissions. The permissions are divided into two types: read and write, here we write a general function with read and write permissions, So we specify the cursor cursortype as adopenkeyset and the lock as the unlock adlockoptimistic ,. net needs to specify its source, which is why the "imports ADODB. the reason for the cursorlocationenum statement. With this, you can execute a query statement to open a table. After opening the table, we can determine whether the table is empty. If not, move the record to the end and then reproduce it to the record header (this is to allow access to each of the records, in particular, when the recordcount is used to calculate the number of records, the key of-1 is not sometimes returned.) Finally, a record set is returned. The complete code is as follows:

'Purpose: connect to the table

'Parameter: tablename table name

'Return: record set

Public Function OpenTable (byval tablename) as ADODB. recordset

Dim strsql as string

Dim rec as ADODB. recordset

Rec = new ADODB. recordset ()

Rec. cursortype = ADODB. cursortypeenum. adopenkeyset

Rec. locktype = ADODB. locktypeenum. adlockoptimistic

Strsql = "select * from" & tablename

Rec. Open (strsql, mcnndb) 'Open record set

If not Rec. EOF then

Rec. movelast ()

Rec. movefirst ()

End if

OpenTable = rec

End Function

The following describes how to expand the functions of the above function to access a single table based on conditions.

Public overloads function OpenTable (byval tablename as string, byval strwhere as string) as ADODB. recordset

Dim strsql as string

Dim rec as ADODB. recordset

Rec = new ADODB. recordset ()

Rec. cursortype = ADODB. cursortypeenum. adopenkeyset

Rec. locktype = ADODB. locktypeenum. adlockoptimistic

Strsql = "select * from" & tablename & "where" & strwhere

Rec. Open (strsql, mcnndb) 'Open record set

If not Rec. EOF then

Rec. movelast ()

Rec. movefirst ()

End if

Return rec

End Function

We continue to expand the table access function. Sometimes you need to open multiple tables and read and write data in them. You can create a query view. The complete code is similar to the above OpenTable:

'Role: connect multiple tables

'Parameter: strsql

'Return: record set

Public Function executesql (byval strsql as string) as ADODB. recordset

Dim rec as new ADODB. recordset ()

Rec. cursortype = ADODB. cursortypeenum. adopenkeyset

Rec. locktype = ADODB. locktypeenum. adlockoptimistic

Rec. Open (strsql, mcnndb) 'Open record set

Executesql = rec

End Function

The following describes how to fill the axmsflexgrid grid with a record set. Here, the function recordcount is a self-compiled function that records data in the record set. Here we cannot directly use ADO's recordcount. If the record set is empty, the process is exited. Otherwise, the number of records and field data of the record set are obtained to determine the row and column data of the axmsflexgrid mesh, and then the data of the record set is read and directly filled into the axmsflexgrid mesh. It should be noted that when reading the data of the record set, you must first determine whether it is null, implemented by the function isdbnull (function isdbnull comes from system. dbnull ). the final record set should be returned to the first record; otherwise, the original record set will be affected. The complete code is as follows:

'Purpose: Fill the grid with record set data

'Parameter: msgrid object, REC record set object

Public sub fillmsgrid (byval msgrid as axmsflexgridlib. axmsflexgrid, byval rec as ADODB. recordset)

Dim I, j, recordnum as integer

If rec. EOF then exit sub

Recordnum = recordcount (REC)

Msgrid. rows = recordnum + 1

Msgrid. Cols = Rec. Fields. Count + 1

For I = 0 to recordnum-1

For J = 0 to rec. Fields. Count-1

If isdbnull (Rec (j). Value) then

Msgrid. set_textmatrix (I + 1, J + 1 ,"")

Else

Msgrid. set_textmatrix (I + 1, J + 1, Rec (j). value)

End if

Next

Msgrid. set_textmatrix (I + 1, 0, I)

Rec. movenext ()

Next

Rec. movefrist ()

End sub

'Purpose: obtain the number of records in the record set.

'Parameter: REC record set object

'Return: number of records in the record set

Public Function recordcount (byval rec as ADODB. recordset) as integer

Dim I as integer

If rec. EOF then

Recordcount = 0

Exit Function

End if

With rec

. Movefirst ()

Do while not. EOF

I + = 1

. Movenext ()

Loop

. Movefirst ()

End

Recordcount = I

End Function

The code above is compiled and placed in the defined class. The ellipsis below represents the above functions and processes. You can directly generate DLL components. In the. NET editing environment, select generate and press generate to generate the DLL file. Then, you can directly call this component.

Imports ADODB. Connection

Imports ADODB. recordset

Imports ADODB. cursorlocationenum

Imports system. dbnull' function isdbnull Source

Class adoaccess

Private mcnndb as new ADODB. Connection ()

...

End Class

Call the component generated above as follows: Create a project in Visual Basic. net, add an axmsflexgrid mesh in the form1 window and name it msgrid1, and then reference the created DLL. You can select Add reference from the project menu, select the project page, and click browse to find its DLL file. The reference is complete. Define an adoaccess object in form1, write the call code in the load event, and test its functions respectively. The Code is as follows:

Public class form1

Inherits system. Windows. Forms. Form

Dim dB as new adoaccess ()

Private sub form1_load (byval sender as system. Object, byval e as system. eventargs) handles mybase. Load

Dim rec as new ADODB. recordset ()

'Db. connaccess ("E: \ test. mdb ")

'Db. connsqlserver ("wj-1058", "test ")

'Db. connodbc ("testodbc ")

'Db. connoracle ("wj-1059", "system", "manager ")

'Rec = dB. OpenTable ("demo. Customer ")

Rec = dB. OpenTable ("doc_file ")

DB. fillmsgrid (msgrid1, REC)

End sub

Note:

1. you must have an Access database file (E: \ test. MDB), MS Server database has database test, doc_file for its test table, Oracle database has table demo. customer.

　

2. The wj-1058 is the MS Server server name, The wj-1059 is the Oracle server name, "system", and "manager" are the user name and password respectively.

3. Only one type of database is connected at a time. The above program passes debugging in Visual Studio. NET.

Summary: The preceding method introduces the Visual Basic that was just launched.. net. net Applications, connect to several of the most practical databases, and introduce the method of writing components. This knowledge greatly facilitates software developers to access data and improves development efficiency.