Front End = Vb.net 2012
Database = MSSQL 2014
I have 2 tables OrderMaster, OrderDetail
OrderMaster Has Columns OrderId Int , Date (SmallDateTime), OrderNumber varchar(255)
OrderDetail Has OrderId int, Itemid Int, Qty Int
OrderMaster gets 1 Insert While OrderDetail gets Multiple Inserts (about 2000)
I am working on my security against SQL Injections.
I know sql injections can be stopped using Parametrized quries or stored procedures
i was searching for light solution where i could send a bulk of records to database for insertion or updates
Please guide me to any tutorial or any where i could get data
i need DAl type soloution for my project
My previous DAl is as follows
Option Explicit On
Option Strict On
Imports System.Data.SqlClient
Public Class CMDAL
Public Shared Function PB_Fn_Fetch_Records(l_Strquery As String) As SqlDataReader
Dim L_SqlConnection As New SqlConnection(PB_DC_Str_SqlConnection)
Dim l_SqlCommand As New SqlCommand(l_Strquery, L_SqlConnection)
Dim L_SqlReader As SqlDataReader
Try
L_SqlConnection.Open()
L_SqlReader = l_SqlCommand.ExecuteReader()
Return L_SqlReader
L_SqlReader.Close()
If L_SqlConnection IsNot Nothing Then
L_SqlConnection.Close()
End If
Catch ex As Exception
Return Nothing
End Try
End Function
Public Shared Function PB_Fn_Single_Save(l_Strquery As String) As Boolean
Dim L_SQLConnection As New SqlConnection(PB_DC_Str_SqlConnection)
L_SQLConnection.Open()
Dim l_SqlTran As SqlTransaction = L_SQLConnection.BeginTransaction()
Dim l_Sqlcommand As SqlCommand = L_SQLConnection.CreateCommand()
l_Sqlcommand.Transaction = l_SqlTran
Try
l_Sqlcommand.CommandText = l_Strquery
l_Sqlcommand.ExecuteNonQuery()
l_SqlTran.Commit()
PB_Fn_Single_Save = True
Catch ex As Exception
MsgBox("arooj")
Try
l_SqlTran.Rollback()
Catch exRollback As Exception
MsgBox(exRollback.Message)
End Try
PB_Fn_Single_Save = False
End Try
End Function
Public Shared Function PB_Fn_Single_Delete(l_Strquery As String) As Boolean
Dim L_SQLConnection As New SqlConnection(PB_DC_Str_SqlConnection)
L_SQLConnection.Open()
Dim l_SqlTran As SqlTransaction = L_SQLConnection.BeginTransaction()
Dim l_Sqlcommand As SqlCommand = L_SQLConnection.CreateCommand()
l_Sqlcommand.Transaction = l_SqlTran
Try
l_Sqlcommand.CommandText = l_Strquery
l_Sqlcommand.ExecuteNonQuery()
l_SqlTran.Commit()
PB_Fn_Single_Delete = True
Catch ex As Exception
MsgBox(ex.Message)
Try
l_SqlTran.Rollback()
Catch exRollback As Exception
MsgBox(exRollback.Message)
End Try
PB_Fn_Single_Delete = False
End Try
End Function
Public Shared Function PB_Fn_Multiple_Save(l_StrArrQuery() As String) As Boolean
Dim L_SQLConnection As New SqlConnection(PB_DC_Str_SqlConnection)
L_SQLConnection.Open()
Dim l_SqlTran As SqlTransaction = L_SQLConnection.BeginTransaction()
Dim i As Integer
Dim l_Sqlcommand As SqlCommand = L_SQLConnection.CreateCommand()
l_Sqlcommand.Transaction = l_SqlTran
Try
For i = 1 To l_StrArrQuery.Count - 1
If l_StrArrQuery(i) <> "" Then
l_Sqlcommand.CommandText = l_StrArrQuery(i)
l_Sqlcommand.ExecuteNonQuery()
End If
Next i
l_SqlTran.Commit()
PB_Fn_Multiple_Save = True
Catch ex As Exception
MsgBox(ex.Message)
Try
l_SqlTran.Rollback()
Catch exRollback As Exception
MsgBox(exRollback.Message)
End Try
PB_Fn_Multiple_Save = False
End Try
End Function
Public Shared Function PB_Fn_Multiple_Delete(l_StrArrQuery() As String) As Boolean
Dim L_SQLConnection As New SqlConnection(PB_DC_Str_SqlConnection)
L_SQLConnection.Open()
Dim l_SqlTran As SqlTransaction = L_SQLConnection.BeginTransaction()
Dim i As Integer
Dim l_Sqlcommand As SqlCommand = L_SQLConnection.CreateCommand()
l_Sqlcommand.Transaction = l_SqlTran
Try
For i = 1 To l_StrArrQuery.Count - 1
If l_StrArrQuery(i) <> "" Then
l_Sqlcommand.CommandText = l_StrArrQuery(i)
l_Sqlcommand.ExecuteNonQuery()
End If
Next i
l_SqlTran.Commit()
PB_Fn_Multiple_Delete = True
Catch ex As Exception
MsgBox(ex.Message)
Try
l_SqlTran.Rollback()
Catch exRollback As Exception
MsgBox(exRollback.Message)
End Try
PB_Fn_Multiple_Delete = False
End Try
End Function
Public Shared Function Pb_Fn_Insert_Update(l_Sqlcommand As SqlCommand) As Boolean
Dim L_SQLConnection As New SqlConnection
Dim l_SqlTran As SqlTransaction
L_SQLConnection.ConnectionString = PB_DC_Str_SqlConnection
L_SQLConnection.Open()
l_SqlTran = L_SQLConnection.BeginTransaction
With l_Sqlcommand
.Connection = L_SQLConnection
.CommandType = CommandType.Text
.Transaction = l_SqlTran
End With
Try
l_Sqlcommand.ExecuteNonQuery()
l_SqlTran.Commit()
Pb_Fn_Insert_Update = True
l_SqlTran.Dispose()
l_Sqlcommand.Dispose()
L_SQLConnection.Close()
L_SQLConnection.Dispose()
Catch ex As Exception
MsgBox(Err.Description)
Try
l_SqlTran.Rollback()
l_SqlTran.Dispose()
l_Sqlcommand.Dispose()
L_SQLConnection.Close()
L_SQLConnection.Dispose()
Catch exRollback As Exception
MsgBox(exRollback.Message)
l_SqlTran.Dispose()
l_Sqlcommand.Dispose()
L_SQLConnection.Close()
L_SQLConnection.Dispose()
End Try
Pb_Fn_Insert_Update = False
End Try
End Function
End Class
Thanks