使用存储过程或参数化Quries在主/明细表中插入批量记录 [英] Insert bulk Records In Master/Detail Tables Using Stored Procedure Or Parameterized Quries

问题描述

前端= Vb.net 2012

数据库= MSSQL 2014



我有2张桌子OrderMaster，OrderDetail

OrderMaster有列OrderId Int，Date（SmallDateTime），OrderNumber varchar（255）



OrderDetail具有OrderId int，Itemid Int，Qty Int



OrderMaster获得1次插入，而OrderDetail获得多次插入（约2000）



我正在努力防范SQL注入。



我知道使用参数化的quries或存储过程可以停止sql注入

i正在搜索轻量级解决方案，我可以发送大量记录到数据库插入或更新



请指导我任何教程或任何我可以获得数据的地方



i需要我的项目的DAl类型解决方案



我以前的DAl如下

 选项 明确 开启 
 选项 严格 开 
 
  Imports  System.Data.SqlClient 
 
 公开 类 CMDAL 
 
 
 公开 共享 功能 PB_Fn_Fetch_Records（l_Strquery 作为 字符串 ） As  SqlDataReader 
  Dim  L_SqlConnection  As  新 SqlConnection（PB_DC_Str_SqlConnection）
  Dim  l_SqlCommand 作为 新 SqlCommand（l_Strquery，L_SqlConnection）
  Dim  L_SqlReader  As  SqlDataReader 
 
 
 
 尝试 
 L_SqlConnection。 Open（）
 L_SqlReader = l_SqlCommand.ExecuteReader（）
 
 返回 L_SqlReader 
 
 L_SqlReader.Close（ ）
 
 如果 L_SqlConnection  IsNot   Nothing  然后 
 L_SqlConnection.Close（）
 结束 如果 
  Catch  ex  As 例外
 返回 无 
 结束 尝试 
 
 结束 功能 
 
 
 公开 共享 功能 PB_Fn_Single_Save（l_Strquery  As   String ） As  布尔 
  Dim  L_SQLConnection  As  新 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 
 
 尝试 
  执行两个单独的命令。 
 
 l_Sqlcommand.CommandText = l_Strquery 
 l_Sqlcommand .ExecuteNonQuery（）
 ' 提交事务 
 l_SqlTran.Commit（）
 PB_Fn_Single_Save =  True  
 
  Catch  ex  As 异常
 ' 如果事务无法提交，则处理异常。 
 MsgBox（  arooj）
 
 < span class =code-keyword>尝试 
 ' 尝试回滚事务。  
 l_SqlTran.Rollback（）
 
  Catch  exRollback  As 例外
 ' 如果连接 
 ' 已关闭或交易已经滚动 
 ' 返回服务器。 
 MsgBox（exRollback.Message）
 
 结束 尝试 
 PB_Fn_Single_Save =  False  
 
 
 
 结束 尝试 
 
 结束 功能 
 
 公开 共享 函数 PB_Fn_Single_Delete（l_Strquery  As  字符串）作为 布尔 
  Dim  L_SQLConnection  As  新 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 
 
 尝试 
 ' 执行两个单独的命令。 
 l_Sqlcommand.CommandText = l_Strquery 
 l_Sqlcommand.ExecuteNonQuery（）
 ' 提交交易 
 l_SqlTran.Commit（）
 PB_Fn_Single_Delete =  True  
 
  Catch  ex  As 异常
 ' 句柄如果事务未能提交，则为异常。 
 MsgBox（ex.Message）
 
 尝试 
 ' 尝试回滚事务。 
 l_SqlTran.Rollback（）
 
  Catch  exRollback  As 异常
 ' 如果连接 
 ' 已关闭或交易已经滚动 
 ' 返回在服务器上。 
 MsgBox（exRollback.Message）
 
 结束 尝试 
 PB_Fn_Single_Delete = 错误 
 
 结束 尝试 
 结束 功能 
 
 公开 共享 功能 PB_Fn_Multiple_Save（l_StrArrQuery（） As   String ） As   Boolean  
  Dim  L_SQLConnection 作为 新 SqlConnection（PB_DC_Str_SqlConnection）
 L_SQLConnection.Open （）
 
  Dim  l_SqlTran  As  SqlTransaction = L_SQLConnection.BeginTransaction（）
  Dim  i 作为 整数 
 ' 在当前交易中登记一个命令。 
  Dim  l_Sqlcommand  As  SqlCommand = L_SQLConnection.CreateCommand（）
 l_Sqlcommand.Transaction = l_SqlTran 
 
 尝试 
 对于 i =  1    l_StrArrQuery.Count -   1  
 ' 执行两个单独的命令。  
 如果 l_StrArrQuery（i）<>   然后 
 l_Sqlcommand .CommandText = l_StrArrQuery（i）
 l_Sqlcommand.ExecuteNonQuery（）
 结束 如果 
 下一步 i 
 
 ' 提交事务 
 l_SqlTran.Commit（）
 PB_Fn_Multiple_Save =  True  
 
 
  Catch  ex  As 异常
 ' 如果事务无法提交，则处理异常。 
 MsgBox（ex.Message）
 
 尝试 
 ' 尝试回滚事务。 
 l_SqlTran.Rollback（）
 
  Catch  exRollback  As 异常
 ' 如果连接 
 ' 已关闭或交易已经滚动 
 ' 返回服务器。 
 MsgBox（exRollback.Message）
 
 结束 尝试 
 PB_Fn_Multiple_Save =  False  
 
 结束 尝试 
 结束 功能 
 
 公共 共享 功能 PB_Fn_Multiple_Delete（l_StrArrQuery（） As  字符串）作为 布尔 
  Dim  L_SQLConnection 作为 新 SqlConnection（PB_DC_Str_SqlConnection） ）
 L_SQLConnection.Open（）
 
  Dim  l_SqlTran  As  SqlTransaction = L_SQLConnection.BeginTransaction（）
  Dim  i  As  整数 
 ' 登记当前事务中的命令。 
  Dim  l_Sqlcommand  As  SqlCommand = L_SQLConnection。 CreateCommand（）
 l_Sqlcommand.Transaction = l_SqlTran 
 
 尝试 
  For  i =  1    l_StrArrQuery.Count  -   1  
 ' 执行两个单独的命令。 
 如果 l_StrArrQuery（i）<>   然后 
 l_Sqlcommand .CommandText = l_StrArrQuery（i）
 l_Sqlcommand.ExecuteNonQuery（）
 结束 如果 
 下一步 i 
 
 ' 提交事务 
 l_SqlTran.Commit（）
 PB_Fn_Multiple_Delete =  True  
 
  Catch  ex 作为例外
 ' < span class =code-comment>如果事务无法提交，则处理异常。 
 MsgBox（ex.Message）
 
 尝试 
 ' 尝试回滚事务。 
 l_SqlTran.Rollback（）
 
  Catch  exRollback  As 异常
 ' 如果连接 
 ' 已关闭或交易已经滚动 
 ' 返回服务器。 
 MsgBox（exRollback.Message）
 
 结束 尝试 
 PB_Fn_Multiple_Delete =  False  
 
 结束 尝试 
 结束 功能 
 
 公共 共享 功能 Pb_Fn_Insert_Update（l_Sqlcommand  As  SqlCommand ） As   Boolean  
 
  Dim  L_SQLConnection 作为 新 SqlConnection 
  Dim  l_SqlTran  As  SqlTransaction 
 
 ' 将连接字符串分配给连接 
 L_SQLConnection.ConnectionString = PB_DC_Str_SqlConnection 
 L_SQLConnection.Open（）
 
 
 l_SqlTran = L_SQLConnection.BeginTransaction 
 
 ' 从连接开始交易 
 
 使用 l_Sqlcommand 
 .Connection = L_SQLConnection 
 .CommandType = CommandType.Text 
 .Transaction = l_SqlTran 
  End  使用 
 
 
 尝试 
 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 异常
 ' 如果事务未能提交，则处理异常。 
 MsgBox（Err.Description）
 
 尝试 
 ' 尝试回滚事务。 
 l_SqlTran.Rollback（）
 
 l_SqlTran.Dispose（）
 l_Sqlcommand.Dispose（）
 L_SQLConnection.Close （）
 L_SQLConnection.Dispose（）
 
 
  Catch  exRollback  As 异常
 ' 如果连接 
 ' 已关闭或交易已经滚动 
 ' 返回服务器。 
 MsgBox（exRollback.Message） 
 l_SqlTran.Dispose（）
 l_Sqlcommand.Dispose（）
 L_SQLConnection.Close（）
 L_SQLConnection.Dispose（）
 
 结束 尝试 
 Pb_Fn_Insert_Update = 错误 
 < span class =code-keyword>结束 尝试 
 结束 函数 
 
 结束 类 

谢谢

解决方案

您可以将数据表作为参数传递给存储过程



有一篇关于它的文章在将数据表传递给SQL中的存储过程Server 2008 [ ^ ]



因此使用详细信息值填充数据表，并使用表值参数调用SP，如上所述

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

        ' 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

解决方案

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.

这篇关于使用存储过程或参数化Quries在主/明细表中插入批量记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！