Database Connection In VB.NET
How to connect Database in VB.NET?
Explanation
Connecting Databases in VB.net 2008.
In ADO.net the databases are connected first, then a copy of the database is stored in the memory immediatley the connection to the database is disconnected. Database is connected only if any changes made to the copy of the database need to be updated to the datatbase itself.ADO.net classes are contained in the 'System.Date' namespace. Following are some of the important classes used in ADO.net.
- Connection
- Command
- DataAdapter
- DataReader
- DataSet
Connection:
This class is used to provide connection to the database. SqlConnection class is used for MS-SQL server database. OleDBConnection is used for databases like Oracle and MS-Access.
Command:
This class is used to provide the command to be executed on an database. The sql commands include select, update, insert and delete queries.Three major method used by this classes are ExecuteNonQuery execute commands that dont return values,ExecuteScalar executes commands that returns a single value,ExecuteReader executes commands that returns a result set.
DataAdapter:
This class acts as bridge between the databases and the copy of the database in the memory.Fill method is used to populate the datatset or datatable with data from the database.Update method is used to update the database after the dataset is manipulated.
DataReader
This class acts as a read only or forward only action to fetch data from a database.ExecuteReader method is used to returns the object of this class.
DataSet
This class stores the copy of the database retreived from the original.
Following example, is used to display the records from an access database and enables to Insert, Update, Delete records. Also has buttons to traverse the database between Next, Previous, First and Last record.
Example:
Imports System.Data.OleDb
Imports System.Console
Public Class Form1
Inherits System.Windows.Forms.Form
Dim cn As OleDbConnection
Dim cmd As OleDbCommand
Dim dr As OleDbDataReader
Dim icount As Integer
Dim str As String
Public head As New System.Data.DataTable
Public No As Integer = 0
Public headadaptor As New OleDbDataAdapter
Public headcommand As New OleDbCommandBuilder
Private Sub Insert1_Click(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles Insert1.Click
cn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=C:student.mdb;")
cn.Open()
str = "insert into student values(" & CInt(TextBox1.Text) & ",
'" & TextBox2.Text & "','" & _TextBox3.Text & "'," &
TextBox4.Text & ")"
cmd = New OleDbCommand(str, cn)
cmd.ExecuteNonQuery()
MessageBox.Show("Data Inserted!...")
cn.Close()
initial()
showRecords()
End Sub
Private Sub Update2_Click(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles Update2.Click
cn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=C:student.mdb;")
cn.Open()
str = "update student set name='" & TextBox2.Text & "',
sex='" & TextBox3.Text & "',age=" & _TextBox4.Text &"
where id=" & TextBox1.Text
cmd = New OleDbCommand(str, cn)
cmd.ExecuteNonQuery()
MessageBox.Show("Data Updated!...")
initial()
showRecords()
cn.Close()
End Sub
Private Sub Delete3_Click(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles Delete3.Click
cn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=C:student.mdb;")
cn.Open()
initial()
str = "delete from student where id=" & TextBox1.Text
cmd = New OleDbCommand(str, cn)
cmd.ExecuteNonQuery()
MessageBox.Show("Data Deleted!...")
head = New DataTable
headadaptor.Fill(head)
No = No - 1
showRecords()
initial()
cn.Close()
End Sub
Private Sub First4_Click(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles Button4.Click
No = 0
display()
End Sub
Sub display()
If (No <= head.Rows.Count - 1) Then
TextBox1.Text = head.Rows(No)("ID").ToString
TextBox2.Text = head.Rows(No)("Name").ToString
TextBox3.Text = head.Rows(No)("Sex").ToString
TextBox4.Text = head.Rows(No)("Age").ToString
End If
End Sub
Sub showRecords()
cn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=C:student.mdb;")
cn.Open()
cmd = New OleDbCommand("select * from student", cn)
dr = cmd.ExecuteReader
While dr.Read()
TextBox1.Text = dr(0)
TextBox2.Text = dr(1)
TextBox3.Text = dr(2)
TextBox4.Text = dr(3)
End While
dr.Close()
cn.Close()
End Sub
Private Sub Form1_Load(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles MyBase.Load
cn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=C:student.mdb;")
cn.Open()
initial()
End Sub
Sub initial()
headadaptor = New OleDbDataAdapter("select * from student",
cn)
headcommand = New OleDbCommandBuilder(headadaptor)
headadaptor.Fill(head)
display()
End Sub
Private Sub Next6_Click(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles Next6.Click
If (No = head.Rows.Count - 1) Then
MsgBox("this is last")
Else
No = No + 1
display()
End If
End Sub
Private Sub Last8_Click(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles Last8.Click
No = head.Rows.Count - 1
display()
End Sub
Private Sub Previous7_Click(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles Previous7.Click
If (No = 0) Then
MsgBox("this is first")
Else
No = No - 1
display()
End If
End Sub
Private Sub Clear9_Click(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles Clear9.Click
TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
TextBox4.Text = ""
End Sub
End Class
In the above Database Connection example the access database named 'student.mdb' is used with the 'ID' as the primary key. So the 'OleDbConnection','OleDbCommand' classes are used. A function named 'initial()' is used to populate the datatset using the 'Fill' method. And a display() method is used to display records.
