How to insert multiple rows at a time from vb.net or c# into SQL Server?

Question

I have list object of type my class(Employee) and it has around 3 items.

Let's say for example:

empObj.Id = 3, empObj.Name = abc, emp.Value = Yes
empObj.Id = 4, empObj.Name = xyz, emp.Value = No
empObj.Id = 5, empObj.Name = pqr, emp.Value = Yes

Now, I want to insert all the items as individual rows into my DB using a stored procedure.

I don't want to hit the DB for each item.

My input parameter for SaveMethod in my data layer will be the above list object and my approach is like following:

Public Overridable Function SaveMethod(businessEntity As List(Of Employee)) As Boolean
    Using scope As System.Transactions.TransactionScope = New System.Transactions.TransactionScope()
        'DECLARE CONNECTION VARIABLE
        Dim objSqlConn As SqlConnection = Nothing
        'DECLARE SQL PARAMS VARIABLE
        Dim objSqlParams As SqlParameter() = Nothing
        'DECLARE BOOLEAN VARIABLE
        Dim bolReturnValue As Boolean = False
        'SET THE CONNECTION
        objSqlConn = GetCSSConnection()
        'SET THE PARAMETERS TO THE STORED PROCEDURE
        objSqlParams = New SqlParameter(3) {}
        For i As Integer = 0 To businessEntity.Count - 1
            objSqlParams(0) = New SqlParameter("@myParam1", SqlDbType.Int)
            objSqlParams(0).Value = 2 ' businessEntity(i).Id

            objSqlParams(1) = New SqlParameter("@myParam2", SqlDbType.Int)
            objSqlParams(1).Value = businessEntity(i).Name

            objSqlParams(2) = New SqlParameter("@myParam3", SqlDbType.Bit)
            objSqlParams(2).Value = businessEntity(i).value
        Next

        'BUILD NEW SQL CONNECTION AND EXECUTE THE STORED PROCEDURE
        'ASSIGN THE RESULT TO BOOLEAN VARIABLE
        bolReturnValue = (Microsoft.VisualBasic.IIf(ExecuteNonQuery(objSqlConn, CommandType.StoredProcedure, "myStoredProcedure", objSqlParams) > 0, True, False))

        CloseConnection(objSqlConn)
        Return bolReturnValue 'RETURNS BOOLEAN VALUE
    End Using
End Function

It's not throwing any error, it is returning True as well. But it's not saved to the database.

What is missing here? Is this approach correct? Is there any better way?

@SamAxe with out reading question, please don't edit tags. I need answer either in c# or vb.net. So I tagged c# as well. — Santosh, May 16 '18 at 03:16
Since your code is in VB, C#-only guys probably won't like it. Adding a [.net] tag will probably be better. Also, please properly indent your code. — 41686d6564 stands w. Palestine, May 16 '18 at 03:21
Regarding your problem: 1) Have you checked the answer in the link that SamAxe referred to? 2) Note that your code overwrites the parameter values in each iteration within your for loop. — 41686d6564 stands w. Palestine, May 16 '18 at 03:28
Populate a `DataTable` with the appropriate data and then save the lot with a single call to the `Update` method of a `SqlDataAdapter`. You would need to set the `InsertCommand` and/or `UpdateCommand` as appropriate. — jmcilhinney, May 16 '18 at 04:49

score 0 · Answer 1 · answered May 16 '18 at 03:16

0

Why don't you go with table-valued parameter, which available with SQL server 2008.

answered May 16 '18 at 03:16

Vikas Chaturvedi

76
5

How do I do that from C#? Any example? – Santosh May 16 '18 at 03:17
Here I find example for you https://www.google.co.in/amp/s/www.red-gate.com/simple-talk/dotnet/net-framework/using-table-valued-parameters-with-vb-net/amp/ – Vikas Chaturvedi May 16 '18 at 03:19

score 0 · Answer 2 · answered May 16 '18 at 10:05

0

Before End Using you need to complete your transaction to apply changes:

scope.Complete()

answered May 16 '18 at 10:05

Amc

88
7

How to insert multiple rows at a time from vb.net or c# into SQL Server?

2 Answers2