Check out the full code for database connections, also learn steps on how to read an ini file from vb net, asp net applications where you can store your database settings like datasource, username, password etc .
This is very easy but very essential and convenient way of connecting the VB .net application to the SQL server database and handling the transactions.
Also simple execute and returning dataset functions are very helpful for any application development.
Required is the
DBConfig.ini file where you can store your database information and keep it safe.
[DBConnection]
DataSource= 10.10.10.10
DBaseName= Data
UserID= sa
Password= sa
Please check out the following functions:
Public Const strDBConfFile = "C:\DBconfig.ini"
Imports System.Data.SqlClient
Public Class clsDBObj
Dim conn As SqlConnection
Dim cmd As SqlCommand
Dim isTran As Boolean
Private trans As SqlTransaction
Dim connStr As String
' Just connect
Public Function
Connect() As Boolean
Dim bln As Boolean
Try
If conn Is Nothing Then
bln = ReadDatabseConfig(strDBConfFile)
If bln = True Then
conn = New SqlConnection(connStr)
Else
MessageBox.Show("There is no Configuration file or Error in Config File!! Please Check")
End If
End If
If conn.State = ConnectionState.Closed Then
conn.Open()
End If
Exit Function
Catch ex As Exception
MessageBox.Show("Cannot connect to the database")
End Try
End Function
Public Sub
BeginTransaction()
If isTran Then Return
If conn.State = ConnectionState.Closed Then
conn.Open()
End If
trans = conn.BeginTransaction()
isTran = True
End Sub
Public Sub
CommitTransaction()
If Not isTran Then Return
trans.Commit()
conn.close()
trans = Nothing
isTran = False
End Sub
Public Sub
RollBackTransaction()
If Not isTran Then Return
trans.Rollback()
conn.close()
trans = Nothing
isTran = False
End Sub
Public Sub
CloseConn()If Not conn Is Nothing Then
If Not conn.State = ConnectionState.Closed Then
conn.Close()
End If
End If
End Sub
Public Function
ExecuteQuery(ByVal strCmdTxt As String) As Boolean
Dim intRows As Integer
Try
If conn.State.Closed = ConnectionState.Closed Then
Connect()
End If
cmd = New SqlCommand
cmd.Connection = conn
cmd.CommandText = strCmdTxt
cmd.CommandType = CommandType.Text
If Not isTran Then
intRows = cmd.ExecuteNonQuery()
conn.close()
Else
cmd.Transaction = trans
intRows = cmd.ExecuteNonQuery()
End If
If intRows> 0 Then
ExecuteQuery = True
Else
ExecuteQuery = False
End If
Catch ex As Exception
Throw ex
End Try
End Function
Public Function
ExecuteAndGetID(ByVal strCmdTxt As String) As String
Try
If conn.State.Closed = ConnectionState.Closed Then
Connect()
End If
strCmdTxt = strCmdTxt & " ; select scope_Identity();"
cmd = New SqlCommand
cmd.Connection = conn
cmd.CommandText = strCmdTxt
cmd.CommandType = CommandType.Text
If Not isTran Then
ExecuteAndGetID = CStr(cmd.ExecuteScalar())
conn.close()
Else
cmd.Transaction = trans
ExecuteAndGetID = CStr(cmd.ExecuteScalar())
End If
Catch ex As Exception
Throw ex
End Try
End Function
Public Function
getDataset(ByVal strCmdTxt As String) As DataSet
Dim da As SqlDataAdapter = New SqlDataAdapter
Dim ds As DataSet = New DataSet
Try
If conn.State = ConnectionState.Closed Then
Connect()
End If
cmd = New SqlCommand
cmd.Connection = conn
cmd.CommandText = strCmdTxt
cmd.CommandType = CommandType.Text
If isTran Then
cmd.Transaction = trans
End If
da.SelectCommand = cmd
da.Fill(ds)
If Not isTran Then
conn.Close()
End If
Return ds
Catch ex As Exception
Throw ex
End Try
End Function
Public Sub
New()
If Not Connect() Then
Exit Sub
End If
End Sub
Public Function
ReadDatabseConfig(ByVal filename As String) As Boolean
Dim result As Boolean
Dim strDataSource As String
Dim strInitialCatalog As String
Dim strUserID As String
Dim strPassword As String
Dim str
Try
Dim sr As New IO.StreamReader(filename)
Dim ln As String = sr.ReadLine
While IsNothing(ln) = False
If ln.StartsWith("DataSource") = True Then
strDataSource = getConfigValue(ln)
ElseIf ln.StartsWith("DBaseName") = True Then
strInitialCatalog = getConfigValue(ln)
ElseIf ln.StartsWith("UserID") Then
strUserID = getConfigValue(ln)
ElseIf ln.StartsWith("Password") = True Then
strPassword = getConfigValue(ln)
End If
ln = sr.ReadLine
End While
sr.Close()
connStr = "Data Source=" & strDataSource & ";" & "Initial Catalog=" & strInitialCatalog & _
";User ID=" & strUserID & ";Password=" & strPassword
result = True
ReadDatabseConfig = result
Catch ex As Exception
result = False
End Try
Return result
End Function
Public Function
getConfigValue(ByVal line As String) As String
Dim values() As String = Split(line, "=")
Dim reply As String = values(1).Trim
'txtactionlog.text = WriteLine("Value = {0}", reply)
Return reply
End Function
End Class
Read more!
Posts
