Database Operations in VB.NET

===========Connected model====================

Imports System.Data.SqlClient
Public Class Frm_add_user
    Dim con As SqlConnection
    Dim cmd As SqlCommand
    Dim dr As SqlDataReader
    Dim obj As conclass
   Under Load event()
        obj = New conclass
        con = New SqlConnection(obj.connect)
OR
// Con=New SqlConnection("Data Source=.;Initial Catalog=ICMSDB;User ID=sa;Password=123")
        con.Open()

Public Class conclass
    Dim constr As String = ("Data Source=.;Initial Catalog=ICMSDB;User ID=sa;Password=123")
    Public ReadOnly Property connect()
        Get
            Return constr
        End Get

Under SaveButton_Click()
cmd = New SqlCommand
        cmd.CommandType = CommandType.Text
        cmd.CommandText = "insert into Login values(@user_name,@password)"
        cmd.Parameters.AddWithValue("@user_name", txtusername.Text.ToUpper)
        cmd.Parameters.AddWithValue("@password", txtpassword.Text.ToUpper)
        If con.State = ConnectionState.Closed Then
            con.Open()
        End If
        cmd.Connection = con
  Try
cmd.ExecuteNonQuery()
 MsgBox("Record has saved Successfully...!!", MsgBoxStyle.Information, "NEW LOGIN")
Catch ex As Exception
            MsgBox(ex.Message)
End Try

updateButtonClick_event()
cmd = New SqlCommand
cmd.CommandType = CommandType.Text
cmd.CommandText = "update expenditure set Emp_id=@Emp_id,Emp_name=@Emp_name where Purchase_id=@Purchase_id"
            cmd.Parameters.AddWithValue("@Purchase_id", cmbpid.SelectedItem)
            cmd.Parameters.AddWithValue("@Emp_id", txteid.Text)
            cmd.Parameters.AddWithValue("@Emp_name", txtfname.Text.ToUpper)
If con.State = ConnectionState.Closed Then
                con.Open()
            End If
cmd.Connection = con
 Try
cmd.ExecuteNonQuery() Then
MsgBox("Record Has Updated...!!", MsgBoxStyle.Information, "UPDATE EXPENDITURE")
Catch ex As Exception
   MsgBox(ex.Message)
 End Try

=========Select Or SelectedIndexChanged======================================

Private Sub cmbpid_SelectedIndexChanged()
        cmd = New SqlCommand
        cmd.CommandType = CommandType.Text
        cmd.CommandText = "select * from Expenditure where Purchase_id=@Purchase_id "
        cmd.Parameters.AddWithValue("@Purchase_id", cmbpid.SelectedItem)
        If con.State = ConnectionState.Closed Then
            con.Open()
        End If
        cmd.Connection = con
        dr = cmd.ExecuteReader
        While dr.Read
            txteid.Text = dr(1)
            txtfname.Text = dr(2)
        End While
        dr.Close()
        con.Close()

Delete Record

Under DeleteButton_Click()
cmd = New SqlCommand
cmd.CommandText = "delete from expenditure where Purchase_id=@Purchase_id"
cmd.Parameters.AddWithValue("@Purchase_id", cmbpid.SelectedItem)
If con.State = ConnectionState.Closed Then
        con.Open()
End If
cmd.Connection = con
  Try
  cmd.ExecuteNonQuery()
 MsgBox("Record has Deleted.!!", MsgBoxStyle.Information, "UPDATE EXPENDITURE")
Catch ex As Exception
   MsgBox(ex.Message)
 End Try
  con.Close()

Validating Values

Private Sub txtfname_Validated()
If txtfname.Text <> "" Then
 If Not Char.IsLetter(txtfname.Text) Then
    MsgBox("Enter Olnly Letter")
      txtfname.Clear()
      txtfname.Focus()
End If
End If

Insert or Update Stored Procedure

CREATE PROCEDURE Ins_Upd_Machine_Info(@MacNo Int,@MacName Varchar(50),@Price Money,@Purchase_Date smalldatetime)
As
Begin
If Not Exists (Select MacNo From Machine_Info where MacNo=@MacNo
Insert Into Machine_Info(MacNo,MacName,Price,Purchase_Date,Warr_Year,Maint_Period)Values(@MacNo,@MacName,@Price,@Purchase_Date,@Warr_Year,@Maint_Period)
Else
Update Machine_Info Set MacName=@MacName, Price=@Price, Purchase_Date=@Purchase_Date, Warr_Year=@Warr_Year, Maint_Period=@Maint_Period where MacNo=@MacNO

===============Stored Procedure used in application===================

cmd.Parameters.Clear()
cmd.CommandText = "Ins_Upd_Machine_Info"
cmd.Parameters.AddWithValue("@MacNO", TxtMacNo.Text)

……..rest of same as above mentioned.

==========Increase value(ID) by 1===============
//normal
cmd.CommandText = "select isnull(max(Regno),0)+ 1 from TB_REG"
OR
cmd.CommandType = CommandType.StoredProcedure
        sqlstr = "Select_Reg_max_id"  //procedure name
======stored procedure===========
CREATE PROCEDURE Select_Reg_max_id
as
begin
select isnull(max(Regno),0)+ 1 from TB_REG

 =======Disconnected Model============================

Imports System.Data.SqlClient
Public Class Form1
    Dim con As SqlConnection
    Dim obj As Conclass
    Dim ds As DataSet
    Dim cmd As SqlCommand
    Dim da As SqlDataAdapter
    Dim cmbr As SqlCommandBuilder
    Dim dr As DataRow

    Under Form1_Load()
        'con = New SqlConnection("Data Source=.; Initial Catalog=empdb; User            ID=sa; Password=nda*007*")
        obj = New Conclass()
        con = New SqlConnection(obj.connect)
        con.Open()
        MsgBox("Connection Successfully......!!!!")
        da = New SqlDataAdapter("Select eid, ename, salary from tbemp", con)
        cmbr = New SqlCommandBuilder(da)
        ds = New DataSet()
        da.Fill(ds, "tbemp")
        DataGridView1.DataSource = ds.Tables(0)

Under Save

Dim dr As DataRow
        dr = ds.Tables(0).NewRow()
        dr(0) = TextBox1.Text
        dr(1) = TextBox2.Text
        dr(2) = TextBox3.Text
        ds.Tables(0).Rows.Add(dr)
        da.Update(ds, "tbemp")

Under Select
Dim dtrow() As DataRow
        dtrow = ds.Tables(0).Select("eid=" + TextBox1.Text)
        If dtrow.Length <> 0 Then
            dr = dtrow(0)
            TextBox1.Text = dr(0)
            TextBox2.Text = dr(1)
            TextBox3.Text = dr(2)
        Else
            MsgBox("Invalid student")

Under Update
ds.Tables(0).Rows(0)(0) = TextBox1.Text
        ds.Tables(0).Rows(0)(1) = TextBox2.Text
        ds.Tables(0).Rows(0)(2) = TextBox3.Text
        da.Update(ds, "tbemp")

Under Delete

'Dim dr As DataRow
        'dr.Delete()
        'da.Update(ds, "tbemp")

====================================================