VB. net access to the sqlserver Database

You can use VB6.0 to write a billing system. You can use ADODB (Active Data Objects Data Base) + recordset, while sqldataadapter, sqldatareadr, dataset, and datatable in VB.net, when I first wrote the charging system for the second time, these concepts were vague, which could also reflect the current impetuousness and lack of new knowledge acceptance ability. The following describes common methods and attributes.

Sqldataadapter

If you only need to execute SQL statements, you do not need to use dataadapter. You can simply use the SQL command execute series methods. Sqldataadapter serves as a bridge between dataset and database.

First, we will introduce common structures, methods, and attributes (system. Data. sqlclient must be referenced first)

Common Methods

Fill

The fill method is used to populate the data in the database with dataset. For example:

Dim ds as new dataset defines a dataset dim da as new sqldataadapter defines a sqldataadapter 'to fill in dataset da. Fill (DS, "user_info ")

Update

The update method updates the data in dataset to the database. For example:

Dim ds as new dataset defines a dataset dim da as new sqldataadapter defines a sqldataadapter 'to update dataset da. Update (DS, "user_info ")

Dataset

Dataset can be regarded as a database in memory, and dataset is an independent data set independent of the database. The so-called independence means that, even if the data link is disconnected or the database is closed, dataset is still available, and dataset is composed of a group of able objects.

Dim ds as new dataset defines a dataset Ds. Tables. Add (DT) 'to add datatabele to dataset.

Datatable

It is a virtual grid table that temporarily stores data and can be used to store data. For example, drag and drop a dview control on the form and write code in button1:

Dim dT as new datatable () 'defines a datatabele DT. columns. add ("card number") 'Add the field "card number" DT. columns. add ("name") 'add field "name" DT. columns. add ("student ID") 'add the field "student ID" DT. columns. add ("instructor") 'Add the field "instructor" DT. rows. add ("10001", "Jain", "0905024", "mi") 'write data rows to datatabele

Note that datatabele needs to add columns before adding rows. In my understanding, it is equivalent to adding fields in the database table before entering data. The effect is as follows:

Sqldatareader

Sqldatareader is a method for obtaining database data. The obtained data is read-only. And it needs to be used with sqlcommand, the most common use method:

Dim connstring as string = _ "Data Source = 192.168.24.158; initial catalog = cr_charge_sys; persist Security info = true; user id = sa; password = 123456 "dim sqltxt as string =" select * From user_info "'defines the SQL statement dim sqlconn as new sqlconnection (connstring)' and defines the connection dim sqlcmd as new sqlcommand (sqltxt, sqlconn) 'define sqlcommand dim userreader as sqldatareader 'define sqldatareader try sqlconn. open () 'Open the connection userreader = sqlcmd. executereader 'generates sqldatareader userreader. read () 'read data msgbox (userreader. item (3) 'displays the fourth read data userreader. close () 'close read catch ex as exception msgbox (ex. message) Finally sqlcmd. dispose () 'release sqlcommand end try

In this way, the simplest Data Reading operation can be achieved. Note that to use sqldatareader, you must first open the database connection to generate sqldatareader, and you must. Read () before obtaining data.

The following uses obtaining data from a table in the entire database as an example to use sqldatareader, sqldataadapter, datatabele, and dataset at the same time. Code:

Private sub button2_click (byval sender as system. object, byval e as system. eventargs) handles button2.click dim connstring as string = _ "Data Source = 192.168.24.158; initial catalog = cr_charge_sys; persist Security info = true; user id = sa; password = 123456 "dim sqltxt as string =" select * From user_info "'defines the SQL statement dim sqlconn as new sqlconnection (connstring)' and defines the connection dim sqlcmd as new sqlcommand (sqltxt, sqlconn) 'define sqlcommand dim da as new sqldataadapter (sqlcmd) 'define sqldataadapter and connect dim ds as new dataset dim userreader as sqldatareader' to define sqldatareader try sqlconn. open () 'Open the connection userreader = sqlcmd. executereader 'generates sqldatareader userreader. read () 'read data msgbox (userreader. item (3) 'displays the fourth read data userreader. close () 'Close Reading da. fill (DS, "userinfo") maid = Ds. tables ("userinfo") catch ex as exception msgbox (ex. message) Finally sqlcmd. dispose () 'release sqlcommand end try end sub

Display result: