VB.NET Tutorial





Español Français 中文 Deutsch Portuguese Japanese nederlands
   
 
Visual Basic Tutorial
Introduction
Installation
Creating Project
Saving a Project
Compile, Run a Project
Open an Existing Project
VB IDE
Forms
VB Datatypes
Constants
Variables
User Defined Datatypes
VB Control Structure
VB Loops
Operators
VB Controls
Dialog Controls
Console Programming
VB Objects,Classes
VB Arrays
Math Functions
String Functions
Type Conversion Functions
Date Time Functions
Databases in VB.net
Enumeration
File Handling
Debugging
Debugging Tools
Exception Handling
Samples
Feedback
 

Database Connection In VB.NET

Tutorials Vbnet

Topic

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.







Other Links

web hosting