Secure database connection and easy transaction handling in .Net Applications

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

Related Posts by Categories