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)