Insert bulk Records In Master/Detail Tables Using Stored Procedure Or Parameterized Quries

Question

0.00/5 (No votes)

See more:

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

VB

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()

        ' Enlist a command in the current transaction.
        Dim l_Sqlcommand As SqlCommand = L_SQLConnection.CreateCommand()
        l_Sqlcommand.Transaction = l_SqlTran

        Try
            ' Execute two separate commands.

            l_Sqlcommand.CommandText = l_Strquery
            l_Sqlcommand.ExecuteNonQuery()
            ' Commit the transaction
            l_SqlTran.Commit()
            PB_Fn_Single_Save = True

        Catch ex As Exception
            ' Handle the exception if the transaction fails to commit.
            MsgBox("arooj")

            Try
                ' Attempt to roll back the transaction.
                l_SqlTran.Rollback()

            Catch exRollback As Exception
                ' Throws an InvalidOperationException if the connection
                ' is closed or the transaction has already been rolled
                ' back on the server.
                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()

        ' Enlist a command in the current transaction.
        Dim l_Sqlcommand As SqlCommand = L_SQLConnection.CreateCommand()
        l_Sqlcommand.Transaction = l_SqlTran

        Try
            ' Execute two separate commands.
            l_Sqlcommand.CommandText = l_Strquery
            l_Sqlcommand.ExecuteNonQuery()
            ' Commit the transaction
            l_SqlTran.Commit()
            PB_Fn_Single_Delete = True

        Catch ex As Exception
            ' Handle the exception if the transaction fails to commit.
            MsgBox(ex.Message)

            Try
                ' Attempt to roll back the transaction.
                l_SqlTran.Rollback()

            Catch exRollback As Exception
                ' Throws an InvalidOperationException if the connection
                ' is closed or the transaction has already been rolled
                ' back on the server.
                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
        ' Enlist a command in the current transaction.
        Dim l_Sqlcommand As SqlCommand = L_SQLConnection.CreateCommand()
        l_Sqlcommand.Transaction = l_SqlTran

        Try
            For i = 1 To l_StrArrQuery.Count - 1
                ' Execute two separate commands.
                If l_StrArrQuery(i) <> "" Then
                    l_Sqlcommand.CommandText = l_StrArrQuery(i)
                    l_Sqlcommand.ExecuteNonQuery()
                End If
            Next i

            ' Commit the transaction
            l_SqlTran.Commit()
            PB_Fn_Multiple_Save = True


        Catch ex As Exception
            ' Handle the exception if the transaction fails to commit.
            MsgBox(ex.Message)

            Try
                ' Attempt to roll back the transaction.
                l_SqlTran.Rollback()

            Catch exRollback As Exception
                ' Throws an InvalidOperationException if the connection
                ' is closed or the transaction has already been rolled
                ' back on the server.
                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
        ' Enlist a command in the current transaction.
        Dim l_Sqlcommand As SqlCommand = L_SQLConnection.CreateCommand()
        l_Sqlcommand.Transaction = l_SqlTran

        Try
            For i = 1 To l_StrArrQuery.Count - 1
                ' Execute two separate commands.
                If l_StrArrQuery(i) <> "" Then
                    l_Sqlcommand.CommandText = l_StrArrQuery(i)
                    l_Sqlcommand.ExecuteNonQuery()
                End If
            Next i

            ' Commit the transaction
            l_SqlTran.Commit()
            PB_Fn_Multiple_Delete = True

        Catch ex As Exception
            ' Handle the exception if the transaction fails to commit.
            MsgBox(ex.Message)

            Try
                ' Attempt to roll back the transaction.
                l_SqlTran.Rollback()

            Catch exRollback As Exception
                ' Throws an InvalidOperationException if the connection
                ' is closed or the transaction has already been rolled
                ' back on the server.
                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

        'Assign Connection String to Connection
        L_SQLConnection.ConnectionString = PB_DC_Str_SqlConnection
        L_SQLConnection.Open()


        l_SqlTran = L_SQLConnection.BeginTransaction

        'Begin Transaction From Connection

        With l_Sqlcommand
            .Connection = L_SQLConnection
            .CommandType = CommandType.Text
            .Transaction = l_SqlTran
        End With


        Try
            l_Sqlcommand.ExecuteNonQuery()
            ' Commit the transaction
            l_SqlTran.Commit()
            Pb_Fn_Insert_Update = True

            l_SqlTran.Dispose()
            l_Sqlcommand.Dispose()
            L_SQLConnection.Close()
            L_SQLConnection.Dispose()

        Catch ex As Exception
            ' Handle the exception if the transaction fails to commit.
            MsgBox(Err.Description)

            Try
                ' Attempt to roll back the transaction.
                l_SqlTran.Rollback()

                l_SqlTran.Dispose()
                l_Sqlcommand.Dispose()
                L_SQLConnection.Close()
                L_SQLConnection.Dispose()


            Catch exRollback As Exception
                ' Throws an InvalidOperationException if the connection
                ' is closed or the transaction has already been rolled
                ' back on the server.
                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

Posted 15-Feb-15 7:19am

Member 2351001

Updated 15-Feb-15 7:37am

v4

Add a Solution

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

_Maxxx_ · Accepted Answer · 2015-02-15T15:56:00

Solution 1

You can pass a datatable as a parameter to a stored procedure

There's an article about it at Passing a datatable to a Stored Procedure in SQL Server 2008[^]

So populate your datatable with the detail values, and call the SP with the table value parameter as described.

Posted 15-Feb-15 15:56pm

_Maxxx_

Comments

Member 2351001 16-Feb-15 2:05am

Thanks for your reply Maxxx

i have no experience using data set
but as far as i know data set is a heavy approach it takes alot of resouces on network. Especially when working with tables having atleast 1000k rows

my insertion or updations will be max of 2k rows

can u please elaborate i may be wrong about data set

and 1 last thing can we use this approach in DAL (Generice Purpose DAL)

_Maxxx_ 16-Feb-15 6:03am

Using a DataTable carries an overhead over, say, passing the raw data - but it does allow you quite simply to pass in a single transaction all your data to the database. Generally in my experience it is better to pass a large amount of data in one transaction than a slightly smaller amount in many transactions.

The major overheads in a DataTable (my understanding of it, anyway) are in storing 'versions' of the data and defining indexes etc. - but for our purposes, you don't need to do that.

A system I worked on used datatables solely for passing to stored procedures where a collection was required - so the DataTable was built up from the collection of objects in code, then passed as a single parameter, and disposed of immediately.

Very convenient and easily maintainable.

The table-creation was done non-generically (i.e. there was a DataTable defined for every DataTable parameter type in the system - but there were less than a handful) because it is necessary to create a table type in the database that matches.

Insert bulk Records In Master/Detail Tables Using Stored Procedure Or Parameterized Quries

1 solution

Solution 1

Add your solution here

Preview 0

Insert bulk Records In Master/Detail Tables Using Stored Procedure Or Parameterized Quries

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us