This article describes how to fill the data into DataSet. To do that we use DataAdapter to retrieve the data from the database and place the data into DataSet. To fill the data into the DataSet use Fill method.
Creating connection object
To create a connection we pass the connection string as a parameter in connection object.
Dim str As String = "Data Source=.;uid=sa;pwd=123;database=master"
Dim con As New SqlConnection(str)
The above string defines the connection string which is used to connect the database with the application.
DataAdapter
The DataAdapter is used to retrieve the data from the database and place that data in DataSet.
Dim str1 As String = "SELECT * FROM logn"
Dim cmd As New SqlCommand(str1, con)
Dim da As New SqlDataAdapter(cmd)
Dim ds As New DataSet()
da.Fill(ds, "logn")
DataColumn
The Datacolumn represents a schema of the column in data table and ColumnName property is used to find the name of the column.
Dim dc As DataColumn
For Each dc In ds.Tables(0).Columns
Console.Write("{0,12}", dc.ColumnName)
Next
Console.WriteLine("")
DataRow
DataRow represents a row of data in a data table.
Dim dr As DataRow
For Each dr In ds.Tables(0).Rows
Dim i As Integer
For i = 1 To ds.Tables(0).Columns.Count
Console.Write("{0,12}", dr(i - 1))
Next i
Console.WriteLine("")
Now we create a database table and insert some values in this table. Table looks like this.
create table logn
(
username varchar(50),
password varchar(40)
)
go
insert into logn values('monu','mohan')
go
insert into logn values('Rohatash','rohit')
go
insert into logn values('Manoj','singh')
go
select * from logn;
The table looks like this.
OUTPUT
For example
Imports System.Data.SqlClient
Module Module1
Sub Main()
Dim str As String = "Data Source=.;uid=sa;pwd=123;database=master"
Dim con As New SqlConnection(str)
Dim str1 As String = "SELECT * FROM logn"
Dim cmd As New SqlCommand(str1, con)
Dim da As New SqlDataAdapter(cmd)
Dim ds As New DataSet()
da.Fill(ds, "logn")
Dim dc As DataColumn
For Each dc In ds.Tables(0).Columns
Console.Write("{0,12}", dc.ColumnName)
Next
Console.WriteLine("")
Dim dr As DataRow
For Each dr In ds.Tables(0).Rows
Dim i As Integer
For i = 1 To ds.Tables(0).Columns.Count
Console.Write("{0,12}", dr(i - 1))
Next i
Console.WriteLine("")
Next
End Sub
End Module
OUTPUT