vb.net Accessing SQL Server Databases (SqlDataReader and datasets two ways)

Three basic steps for. NET access to a database:

A. Connect to the database:

To use the SqlConnection class in the SqlClient namespace;

Define a SqlConnection object: Dim Sqlcon as New SqlConnection

The settings to be made are:

1.sqlCon. connectionstring= "server= server ip;database= database name; integrated security=ture/false (login password); uid= User name

(default: SA);p WD: Login Password ' Set connection properties

2.sqlCon. Open ' Opening the database

Two. Edit Operation Command:

To use the SqlCommand class in the SqlClient namespace;

Define a SqlCommand object: Dim SQLCM as New SqlCommand

The settings to be made are:

1.sqlCM. CommandType = CommandType.Text ' indicates what type the string command belongs to (stored procedure name, indication, SQL statement)

2.sqlCM. CommandText = "SELECT * FROM table" ' query statement

Three. Read data (data read in two ways: DataSet and DataReader)

Method one. A DataSet (a table that reads data out, slows down the memory, can populate a DataTable with a dataset, and then bind the table to DataGridView to make it easier to see the data, and SqlDataAdapter

Object cooperation)

Steps:

Dim myDataSet as New DataSet ' defines a DataSet object

　　　　　　　Dim Sqlad as New SqlDataAdapter ' defines a SqlDataAdapter object

　　　　　　　　To set the SqlDataAdapter property:

Sqlad.selectcommand = SQLCM ' Adapter Command property configuration

SqlAd.SelectCommand.Connection = SQLCNN ' Adapter Connection property configuration

Sqlad.fill (myDataSet, "Stu") "populates data into mydataset according to table Stu format

　　　　　At this point, the data returned in the database is already in the table myDataSet, in order to make it easier to observe, add a DataGridView space and bind to the table myDataSet to display the data,

The steps are as follows:

Datagridview1.datamember = "Table1" Control name

Datagridview1.datasource = mydataset.table (0) ' data source binding

Now, the database query results (tables) have been displayed on the space DataGridView1.

Note:　　　　　　　　 regarding the relationship between SqlDataAdapter and DataSet, the Novice (i) may be a bit blindfolded, providing several references:

　　　　　　　　　　　　Http://zhidao.baidu.com/question/165308688.html

Http://www.cnblogs.com/caijun520/archive/2013/06/02/3114425.html

Http://www.cnblogs.com/eagle1986/archive/2010/01/24/1655327.html

　　　　Method Two. SqlDataReader (each time a row of results is read and The connection cannot be broken while reading, it is fast and is suitable for read-only operation)

First, the idea: Because the SqlDataReader object can only read one row of data at a time, we want to display all the data, you can first add each row of data to a table (through the number of elements and the table

array with the same number of columns), and then bind the table to the DataGridView control.

Steps:　　

Dim Myread as Sqlclient.sqldatareader = Nothing ' defines a SqlDataReader object and assigns an initial value of nothing (otherwise warns)

Dim mytable as New DataTable () ' Defines a table that is used to store the results of a query

Dim MyStr (3) as String ' defines an intermediate array, I have a table of 4 columns here　　　　　　

' Add column
Dim sno as DataColumn = New DataColumn ("Sno")
Dim sname as DataColumn = New DataColumn ("sname")
Dim ssex as DataColumn = New DataColumn ("Ssex")
Dim Sphone as DataColumn = New DataColumn ("Sphone")
' Add columns to the table
MyTable. Columns.Add (SNO)
MyTable. Columns.Add (sname)
MyTable. Columns.Add (Ssex)
MyTable. Columns.Add (Sphone)　　　　　　　　　　

Myread = mycommand. ExecuteReader ' perform read action

While (Myread. Read) ' is not empty then always reads

Myread. GetValues (MYSTR) ' Performs a read action, storing a row of data in an MYSTR array
MyTable. Rows.Add (mystr) ' adds array data as a row to the table
End while

' Bind a table to a real control
Datagridview1.datasource = MyTable

The database query results (tables) are now displayed on the control DataGridView1.

Note: SqlDataReader reference:

Http://www.cnblogs.com/caijun520/archive/2013/06/02/3114422.html

Http://blog.163.com/[email protected]/blog/static/418935632013214370722/

Here are two different ways to put the complete code (you have to have two buttons and a DataGridView control):

1 ImportsSYSTEM.DATA.SQL2 ImportsSystem.Data.SqlClient3 4 5  Public ClassForm16 7 8 9     'DataReaderTen     Private SubButton1_Click (Sender as ObjectE asEventArgs)HandlesButton1.Click One  A         DimMyconnect as Newsqlclient.sqlconnection -         DimMyCommand as NewSqlclient.sqlcommand -         DimMyread asSqlclient.sqldatareader = Nothing the         DimMyTable as NewDataTable () -         DimMyStr (3) as String -  -         'Connection Properties +Myconnect. ConnectionString ="server=192.168.0.100;database=temp;integrated security=true;uid=sa;pwd=" -Myconnect. Open ()'Open Database +  A         'set command Properties atmycommand. Connection =Myconnect -myCommand.CommandType =CommandType.Text -myCommand.CommandText ="select * from Stu" -  -         'Adding Columns -         DimSno asDataColumn =NewDataColumn ("Sno") in         DimSname asDataColumn =NewDataColumn ("sname") -         DimSsex asDataColumn =NewDataColumn ("Ssex") to         DimSphone asDataColumn =NewDataColumn ("Sphone") +         'add a column to a table - mytable. Columns.Add (SNO) the mytable. Columns.Add (sname) * mytable. Columns.Add (Ssex) $ mytable. Columns.Add (Sphone)Panax Notoginseng  -Myread = mycommand. ExecuteReader'perform read action the  +          while(Myread. Read)'not empty then read all the time A  theMyread. GetValues (MYSTR)'performs a read action, storing a row of data in the MYSTR array +MyTable. Rows.Add (MYSTR)'add array data as a row to the table -         End  while $  $         'bind a table to a real-world control -Datagridview1.datamember ="mytable" -Datagridview1.datasource =mytable the  -Myread. Close ()'Turn off ReadWuyiMyconnect. Close ()'Close Connection the  -     End Sub Wu  -  About  $     'DataSet -     Private SubButton2_Click (Sender as ObjectE asEventArgs)HandlesButton2.click -  -         DimMyconnect as Newsqlclient.sqlconnection A         DimMyCommand as NewSqlclient.sqlcommand +         DimMyadp as NewSqlclient.sqldataadapter the         DimmyDataSet as NewDataSet -  $Myconnect. ConnectionString ="server=192.168.0.100;database=temp;integrated security=true;uid=sa;pwd=" the Myconnect. Open () the  themycommand. Connection =Myconnect themyCommand.CommandType =CommandType.Text -myCommand.CommandText ="select * from Stu" in  theMyadp. SelectCommand =mycommand the MYADP. Fill (myDataSet) About  theDatagridview1.datasource = myDataSet. Tables (0) the  the Myconnect. Close () +  -     End Sub the End Class

　　　　　　

　　　　

　　　　

vb.net Accessing SQL Server Databases (SqlDataReader and datasets two ways)