1.Adding NameSpaces
Imports System.Data.SqlClient
2.Declare Variables
Dim Result As String --string
Dim i As Integer -- Int
3.If Conditions
If (i Mod 2 = 0) Then
3.If Conditions
If (i Mod 2 = 0) Then
Result = odd
Else
Result = Even
End If
4.For Loop
For i = 0 To 5
MsgBox(i)
Next
4.For Loop
For i = 0 To 5
MsgBox(i)
Next
5.While Loop
While Reader.Read()
MsgBox(Reader("section"))
End While
6.Shuffle a String
Method 1
Private Function ShuffleMethod1(ByVal input As String) As String
Dim ran As New Random
Dim Result As String = ""
Dim i As Integer
While input.Length > 0
i = ran.Next(0, input.Length)
Result &= input.Substring(i, 1)
input = input.Remove(i, 1)
End While
Return Result
End Function
Method 2
Private Function ShuffleMethod2()
Dim Result As String = ""
Dim input As String = "Baskaran"
For i = 0 To input.Length - 1
If (i Mod 2 = 0) Then
Result += input(i + 1)
Else
Result += input(i - 1)
End If
Next
Result = StrConv(Result, VbStrConv.ProperCase)
Return Result
End Function
Method 3
Private Function ShuffleMethod3()
Dim Result As String = ""
Dim input As String = "Baskaran"
Dim Dicimalcount As Double
Dim Intcount As Integer
Dim Finalcount As Integer
Dicimalcount = input.Length / 6
Intcount = input.Length / 6
If (Dicimalcount <= Intcount) Then
Finalcount = Intcount
Else
Finalcount = Intcount + 1
End If
Dim StartLength As Integer = 0
For i = 0 To Finalcount
If (StartLength + 3 <= input.Length) Then
Result += input.Substring(StartLength, 3) + "|"
StartLength += 3
Else
Result += input.Substring(StartLength, input.Length - StartLength)
End If
Next
Return Result
End Function
7.Select a Record From DB Using SQL Query
Private Function SelectRecord()
Dim Con As SqlConnection
Dim cmd As SqlCommand
Dim Reader As SqlDataReader
Dim connetionString As String
Dim Query As String
Dim FieldCount As String
Dim Result As String
connetionString = "Data Source=Local;Initial Catalog=Test;User ID=sa;Password=welcome@123"
Query = "select * from Configuration where ID in (0,1)"
Con = New SqlConnection(connetionString)
cmd = New SqlCommand(Query, Con)
Con.Open()
Reader = cmd.ExecuteReader()
FieldCount = Reader.FieldCount ' Number of Colum Count
If (Reader.HasRows) Then ' Validate HasRows or Not
While Reader.Read()
MsgBox(Reader.Item(0) & " - " & Reader.Item(1) & " - " & Reader.Item(2))
MsgBox(Reader("section"))
End While
End If
Reader.Close()
cmd.Dispose()
Con.Close()
End Function
8.Insert a Record From DB Using SQL Query
Private Function InsertRecord()
Dim Con As SqlConnection
Dim cmd As SqlCommand
Dim connetionString As String
Dim Query As String
Dim AffectedCount As String
connetionString = "Data Source=Local;Initial Catalog=Test;User ID=sa;Password=welcome@123"
Query = "INSERT INTO [dbo].[Configuration]([ID],[Section],[Group],[FK_RegionID],[Value],[About],[IsScriptResource])
VALUES((SELECT MAX(ID)+1 FROM [Configuration]),'Test','Test',0,1,null,null)"
Con = New SqlConnection(connetionString)
cmd = New SqlCommand(Query, Con)
Try
Con.Open()
AffectedCount = cmd.ExecuteNonQuery()
If (AffectedCount > 0) Then ' Validate Insert or Not
MsgBox("Inserted Successfully")
End If
Catch ex As Exception
MsgBox("Can not open connection ! ")
End Try
cmd.Dispose()
Con.Close()
End Function
9.Update a Record From DB Using SQL Query
Private Function UpdateRecord()
Dim Con As SqlConnection
Dim cmd As SqlCommand
Dim connetionString As String
Dim Query As String
Dim AffectedCount As String
connetionString = "Data Source=Local;Initial Catalog=Test;User ID=sa;Password=welcome@123"
Query = "Update Configuration Set Section='hi', [Group]='123' where id= 1059"
Con = New SqlConnection(connetionString)
cmd = New SqlCommand(Query, Con)
Try
Con.Open()
AffectedCount = cmd.ExecuteNonQuery()
If (AffectedCount > 0) Then ' Validate Insert or Not
MsgBox("Updated Successfully")
End If
Catch ex As Exception
MsgBox("Can not open connection ! ")
End Try
cmd.Dispose()
Con.Close()
End Function
10.Delete a Record From DB Using SQL Query
Private Function DeleteRecord()
Dim Con As SqlConnection
Dim cmd As SqlCommand
Dim connetionString As String
Dim Query As String
Dim AffectedCount As String
connetionString = "Data Source=Local;Initial Catalog=Test;User ID=sa;Password=welcome@123"
Query = "Delete from Configuration where id= 1059"
Con = New SqlConnection(connetionString)
cmd = New SqlCommand(Query, Con)
Try
Con.Open()
AffectedCount = cmd.ExecuteNonQuery()
If (AffectedCount > 0) Then ' Validate Insert or Not
MsgBox("Deteted Successfully")
End If
Catch ex As Exception
MsgBox("Can not open connection ! ")
End Try
cmd.Dispose()
Con.Close()
End Function
11.Select a Record From DB Using SQL Stored Procedure
Private Function SelectRecordFromSP()
Dim Con As SqlConnection
Dim cmd As New SqlCommand
Dim ada As SqlDataAdapter
Dim ds As New DataSet
Dim connetionString As String
Dim i As Integer
connetionString = "Data Source=Local;Initial Catalog=Test;User ID=sa;Password=welcome@123"
Con = New SqlConnection(connetionString)
Con.Open()
cmd.Connection = Con
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "USP_Config"
ada = New SqlDataAdapter(cmd)
ada.Fill(ds)
For i = 0 To ds.Tables(0).Rows.Count - 1
MsgBox(ds.Tables(0).Rows(i).Item(0))
Next
Con.Close()
End Function
12.Select a Record From DB Using SQL Stored Procedure with Parameter
Private Function SelectRecordFromSPWithParam()
Dim Con As SqlConnection
Dim cmd As New SqlCommand
Dim Reader As SqlDataReader
Dim ada As SqlDataAdapter
Dim ds As New DataSet
Dim connetionString As String
Dim FieldCount As String
Dim i As Integer
connetionString = "Data Source=Local;Initial Catalog=Test;User ID=sa;Password=welcome@123"
Con = New SqlConnection(connetionString)
Con.Open()
cmd.Connection = Con
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "USP_Config"
cmd.Parameters.Add("@ID", SqlDbType.Int).Value = 1
'Using Data Reader
'Reader = cmd.ExecuteReader()
'FieldCount = Reader.FieldCount ' Number of Colum Count
'If (Reader.HasRows) Then ' Validate HasRows or Not
' While Reader.Read()
' MsgBox(Reader.Item(0) & " - " & Reader.Item(1) & " - " & Reader.Item(2))
' MsgBox(Reader("section"))
' End While
'End If
'Reader.Close()
'Using Data Set
ada = New SqlDataAdapter(cmd)
ada.Fill(ds)
If (ds.Tables(0).Rows.Count > 0) Then
For i = 0 To ds.Tables(0).Rows.Count - 1
MsgBox(ds.Tables(0).Rows(i).Item(0))
Next
Else
MessageBox.Show("No data Found")
End If
Con.Close()
End Function
13.Insert a Record From DB Using SQL Stored Procedure
Private Function InsertRecordFromSP()
Dim Con As SqlConnection
Dim cmd As New SqlCommand
Dim connetionString As String
Dim AffectedCount As String
connetionString = "Data Source=Local;Initial Catalog=Test;User ID=sa;Password=welcome@123"
Con = New SqlConnection(connetionString)
Using (Con)
cmd.Connection = Con
cmd.CommandText = "USP_Config"
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("Section", "123")
cmd.Parameters.AddWithValue("Group", "111")
Con.Open()
AffectedCount = cmd.ExecuteNonQuery()
If (AffectedCount > 0) Then ' Validate Insert or Not
MsgBox("Inserted Successfully")
End If
Con.Close()
End Using
End Function
14.Update a Record From DB Using SQL Stored Procedure
Private Function UpdateRecordFromSP()
Dim Con As SqlConnection
Dim cmd As New SqlCommand
Dim connetionString As String
Dim AffectedCount As String
connetionString = "Data Source=Local;Initial Catalog=Test;User ID=sa;Password=welcome@123"
Con = New SqlConnection(connetionString)
Using (Con)
cmd.Connection = Con
cmd.CommandText = "USP_Config"
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("ID", "1061")
cmd.Parameters.AddWithValue("Group", "1111111")
Con.Open()
AffectedCount = cmd.ExecuteNonQuery()
If (AffectedCount > 0) Then ' Validate Insert or Not
MsgBox("Updated Successfully")
End If
Con.Close()
End Using
End Function
15.Delete a Record From DB Using SQL Stored Procedure
Private Function DeleteRecordFromSP()
Dim Con As SqlConnection
Dim cmd As New SqlCommand
Dim connetionString As String
Dim AffectedCount As String
connetionString = "Data Source=Local;Initial Catalog=Test;User ID=sa;Password=welcome@123"
Con = New SqlConnection(connetionString)
Using (Con)
cmd.Connection = Con
cmd.CommandText = "USP_Config"
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@ID", SqlDbType.NVarChar, 50).Value = "1059"
Con.Open()
AffectedCount = cmd.ExecuteNonQuery()
If (AffectedCount > 0) Then ' Validate Insert or Not
MsgBox("Deleted Successfully")
End If
Con.Close()
End Using
End Function
