Tech Off Thread

5 posts

Forum Read Only

This forum has been made read only by the site admins. No new threads or comments can be added.

Batch INSERT (T-SQL92)

Back to Forum: Tech Off
  • User profile image
    W3bbo

    'lo

    I've got a class that inherits CollectionBase, and I need to get the contained classes into SQL Server.

    The only way I can think of isn't exactly elegant, but it goes like this:

    -------------------------------------------------------------

    PROCEDURE dbo.AddFoo (

    @Foo char(3),
    @Bar char(3),
    @Baz char(3)

    )

    AS

    INSERT INTO Blargh (Foo, Bar, Baz) VALUES (@Foo, @Bar, @Baz)

    -------------------------------------------------------------

    Dim C As New SqlCommand("AddFoo", Connection)
    C.CommandType = CommandType.StoredProcedure

    For i As Integer = 0 To Collection.Count - 1
       
        C.Parameters.Add("@Foo", "Bar")
        C.Parameters.Add("@Bar", "Baz")
        C.Parameters.Add("@Baz", "Foo")
       
        C.ExecuteNonQuery()
       
    Next

    -------------------------------------------------------------

    Is this the only way to move a collection into a database or is there a better way?

  • User profile image
    Minh

    This change will make things go twice as fast!!!


    PROCEDURE dbo.AddFoo (

    @Foo1 char(3), @Bar1 char(3), @Baz1 char(3),
    @Foo2 char(3), @Bar2 char(3), @Baz2 char(3)

    )

    AS

    INSERT INTO Blargh (Foo, Bar, Baz) VALUES (@Foo1, @Bar1, @Baz1)
    INSERT INTO Blargh (Foo, Bar, Baz) VALUES (@Foo2, @Bar2, @Baz2)






    Hahahaha Big Smile




  • User profile image
    kriskdf

    Another less than elegant way is to generate an XML string and have your SP take a single parameter.  You're SP can then shred the xml and insert it that way.  The subject of this post makes me think you may not want to use any SqlServer specific stuff though.

    I think there is a bulk insert feature in .NET 2.0 but I don't know for sure.

    In the end it really depends on how much stuff you need to insert.  If it is < 10 or so (measure to find the right number) you could probably do it the way you have it and be fine.

    I'm kind of curious what you find to be the most performant way.  Post if you try some of these things out and find a clear winner. 

  • User profile image
    blowdart

    kriskdf wrote:

    I think there is a bulk insert feature in .NET 2.0 but I don't know for sure.


    There certainly is,

    adapater.UpdateBatchSize

  • User profile image
    Simo

    Another way I've seen it done some times is to concatonate up the EXEC Stored Proc commands and fire the sql at the DB in one go.

    ie

    EXEC dbo.AddFoo 'a', 'b', 'c';EXEC dbo.AddFoo 'd', 'e', 'f'; .... etc


    Doing this in ADO classic or ADO.NET, you're circumventing the parameter object model, losing some typing and also, I suppose,  opens you up a little more to SQL injection attack.

    Another way would be to 'blob it' and treat the DB as nothing more than an object store. But I hate that.

Conversation locked

This conversation has been locked by the site admins. No new comments can be made.