[VB.NET] CRUD using ADO.NET

Deskripsi
    Create, Update , Read / Retrieval , Insert on VB.NET (Different from VB 6) , before go to the next step we must know conceptual ADO.NET. here's the picture (taken from MSDN).

How To

Concept :
1. Import OleDb
2. Create Connection String , if you don't know how to create read this article
3. Specify the type of statement :
    - Create , Update , Insert is execute type (required open connection) , you can use adapter (not use open connection)  it's not recommended but it's depends on user habits (manipulation data should use open connection).
    - Read / Retrieval is safe without required open connection , it's just get the data without manipulation.

i only give raw code vb.net , you should create your own class in order to look better code and manageable, ok let's start :)

SQL Type Execute (Create , Update , Insert), create 1 button on form and double click :
// import collection
Imports System.Data.OleDb
Private Sub Command1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdshutdown.Click
// Declare variable
Dim dbcmd
Dim strsql As String
Dim dbconn = New OleDbConnection
// create connection string , and open the connection
dbconn.ConnectionString = "Provider=SQLOLEDB.1;Password=xxxxx;Persist Security Info=True;User ID=sa;Initial Catalog=mytable;Data Source=192.168.0.xx"
dbconn.Open()
//create your sql statement
strsql = "insert test (id,name) values ('1','yeah')"
//add your sql statement to oledb command
dbcmd = New OleDbCommand(strsql, dbconn)
 dbcmd.CommandType = CommandType.Text
//executed the oledbcommand 
dbcmd.ExecuteReader()
End Sub


SQL Type Retrieval(select), create 1 button and 1 datagridview on form and double click the button:
// import collection
Imports System.Data.OleDb
Private Sub Command1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdshutdown.Click
// Declare variable
Dim dbcmd,adaptor, datacontent
Dim strsql As String
Dim dbconn = New OleDbConnection
// create connection string , and open the connection
dbconn.ConnectionString = "Provider=SQLOLEDB.1;Password=xxxxx;Persist Security Info=True;User ID=sa;Initial Catalog=mytable;Data Source=192.168.0.xx"
//dbconn.Open()
//create your sql statement
strsql = "select * from table where xxx=yyy"
//add your sql statement to oledb command
dbcmd = New OleDbCommand(strsql, dbconn)
dbcmd.CommandType = CommandType.Text
//running sql command via adapter (look the conceptual)
adaptor = New OleDbDataAdapter(dbcmd)
datacontent= New DataTable
adaptor.Fill(datacontent)
datagridview1.DataSource = datacontent
End Sub     

note : to fill data on gridview , datagridview require an object datatable that filled by adaptor , look my red mark.



Single Retrieval
Dim adaptor
Dim strsql As String
Dim i As Double
Dim dbcmd As OleDbCommand
Dim datacontent As DataTable
Dim dbconn = New OleDbConnection
Dim reader As OleDbDataReader
Dim fetchReader As OleDbDataReader

dbconn.ConnectionString = "FILE NAME=" & My.Application.Info.DirectoryPath & "\SQLServer.udl"
strsql = "select USER_ID,USERNAME from MS_USER_LOGIN"

 'add your sql statement to oledb command
dbcmd = New OleDbCommand(strsql, dbconn)
dbcmd.CommandType = CommandType.Text
dbconn.Open()

reader = dbcmd.ExecuteReader
datacontent = New DataTable
datacontent.Load(reader)

'item(1) is index row number 2 , the first row always at index 0.
'ItemArray(1) refers index column, total index based on your query and always start at index 0
MsgBox(datacontent.Rows.Item(1).ItemArray(1).ToString())
MsgBox(datacontent.Rows(0).Field(Of String)("USERNAME").ToString)

'this for fetching all row
For Each row As DataRow In datacontent.Rows
      MsgBox(row.Field(Of String)("USERNAME").ToString)
Next

reader.Close()