none
How to do multiple inserts with ado.net RRS feed

  • Question

  • I know how to use a cmd object to execute a stored procedure with parameters, but do i have to do that over and over?  If i wanted to execute sql like below:

    insert into UserCompanyRoles(CompanyId, UserId, RoleID)

     

    Values(4,1,1)

     

    insert into UserCompanyRoles(CompanyId, UserId, RoleID)
    Values(4,1,2)

     

    insert into UserCompanyRoles(CompanyId, UserId, RoleID)
    Values(4,1,3)

     

    insert into UserCompanyRoles(CompanyId, UserId, RoleID)
    Values(4,1,4)


    McC
    Saturday, October 17, 2009 1:59 PM

All replies

  • It depends on what you need to achieve and what kind of database you are using. If it's SQL Server, you could collect all the items, format them as some sort of XML structure and sent that XML string to stored procedure where it could be handled as single batch without looping. If you cannot send items in one shot, then you would need to loop and call INSERT SQL statement from the client application one-by-one.
    Val Mazur (MVP) http://www.xporttools.net
    Monday, October 19, 2009 10:25 AM
    Moderator
  • Not sure I fully understand what you wish to do.

    You can either create the command for the stored procedure and then just change the parameter value and and execute in a loop.

    For example;

    create table DemoTable (companyId int, userId int)
    create procedure DemoProcedure (@cid int, @uid int) as begin
    	insert into DemoTable(companyId, userId) values (@cid, @uid)
    end
    -- drop procedure DemoProcedure
    -- drop table DemoTable
                using(SqlConnection con = new SqlConnection(cs))
                {
                    try
                    {
                        con.Open();
                        SqlCommand cmd = new SqlCommand("DemoProcedure", con);
                        cmd.CommandType = System.Data.CommandType.StoredProcedure;
    
                        SqlParameter p1 = cmd.CreateParameter();
                        p1.DbType = System.Data.DbType.Int32;
                        p1.ParameterName = "@cid";
    
                        SqlParameter p2 = cmd.CreateParameter();
                        p2.DbType = System.Data.DbType.Int32;
                        p2.ParameterName = "@uid";
    
                        cmd.Parameters.Add(p1);
                        cmd.Parameters.Add(p2);
    
                        for (int i = 5; i < 10; i++)
                        {
                            p1.Value = i;
                            p2.Value = i;
    
                            cmd.ExecuteNonQuery();
                        }
    
                        con.Close();
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine(ex);
                    }
                }
    (Not sure if this suits you in terms of performance)
    or you could, as VMazur suggests, send everything in a single batch using a table value parameter (assuming this is SQL Server), example of this here:

    "Table-Value parameter used in .Net. Simple sample"
    http://blogs.msdn.com/spike/archive/2009/03/20/table-value-parameter-used-in-net-simple-sample.aspx

    HTH
    //Michael

    This posting is provided "AS IS" with no warranties.
    Monday, October 19, 2009 12:58 PM
  • I like the Table value parameter, but the example in your link doesnt work, with the 1st comment as line one i get these errors:

     

    Msg 156, Level 15, State 1, Line 3
    Incorrect syntax near the keyword 'as'.
    Msg 102, Level 15, State 1, Procedure TVPAddUserRoles, Line 3
    Incorrect syntax near 'readonly'.
    Msg 1087, Level 15, State 2, Procedure TVPAddUserRoles, Line 6
    Must declare the table variable "@TVP".
    Msg 1087, Level 15, State 2, Line 8
    Must declare the table variable "@TVPTest".



    -- First we need a 'backing' table that we will insert data into

    create table TVPTable ( id int identity , fname nvarchar ( 20), lname nvarchar ( 20), age int , dateadded datetime )

    go

     

    -- Then we need to create our own type of type Table

    create type TVPTableType as table ( fname nvarchar ( 20), lname nvarchar ( 20), age int default ( 20))

    go

     

    -- And then a stored procedure that takes an table value parameter as argument

    create procedure TVPProcedure @TVP TVPTableType readonly

    as

                   insert into TVPTable ( fname, lname, age, dateadded)

                   select fname, lname, age, GETDATE () from @TVP

    go             

     

    -- and then just test it in T-SQL so that we know that it works

    declare @TVPTest as TVPTableType

    insert into @TVPTest( fname, lname) values ( 'Mike' , 'Spike' ) -- omitting Age to show that deafult can be used.

    exec TVPProcedure @TVPTest

     

    -- select from the backing table.

    select * from TVPTable

     

    -- drop table TVPTable

    -- drop type TVPTableType

    -- drop procedure TVPProcedure
    McC
    Tuesday, October 20, 2009 10:52 PM
  • Hi again John

    Apologies for the confusion. TVP is new in SQL Server 2008, you will get this error when running on a lower version of SQL Server.
    So if you are running, for example, SQL Server 2005, the unfortunately the TVP approach will not work.

    "Table-Valued Parameters (Database Engine)"
    http://msdn.microsoft.com/en-us/library/bb510489.aspx

    //Michael
    This posting is provided "AS IS" with no warranties.
    Wednesday, October 21, 2009 7:40 AM
  • You can create a datatable on the fly then save the data back to your database here is a sample

        Public dt As DataTable
        Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            'Create a DataTable
            dt = New DataTable
            'DataTable Schema
            dt.Columns.Add("CompanyID", GetType(System.Int32))
            dt.Columns.Add("UserID", GetType(System.Int32))
            dt.Columns.Add("RoleID", GetType(System.Int32))
            'Load Data in DataTable
            Dim dtRow As DataRow
            dtRow = dt.NewRow
            dtRow(0) = 4
            dtRow(1) = 1
            dtRow(2) = 1
            dt.Rows.Add(dtRow)
    
            dtRow = dt.NewRow
            dtRow(0) = 4
            dtRow(1) = 1
            dtRow(2) = 2
            dt.Rows.Add(dtRow)
    
            dtRow = dt.NewRow
            dtRow(0) = 4
            dtRow(1) = 1
            dtRow(2) = 3
            dt.Rows.Add(dtRow)
    
            dtRow = dt.NewRow
            dtRow(0) = 4
            dtRow(1) = 1
            dtRow(2) = 4
            dt.Rows.Add(dtRow)
    
        End Sub
    
        Private Sub Button_Save_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button_Save.Click
            'Save the Added Data only 
            Dim dtRowsAdded As DataTable = dt.GetChanges(DataRowState.Added)
    
            If Not dt.GetChanges() Is Nothing Then
                Using Conn As New SqlConnection(My.Settings.MyConnection)
                    Using Adapter As New SqlDataAdapter("spI_Values", Conn)
                        Adapter.InsertCommand.CommandType = CommandType.StoredProcedure
                        With Adapter.InsertCommand.Parameters
                            .Add("@CompanyID", SqlDbType.Int, 5, "CompanyID")
                            .Add("@UserID", SqlDbType.Int, 5, "UserID")
                            .Add("@RoleID", SqlDbType.Int, 5, "RoleID")
                        End With
                        Adapter.Update(dt)
                    End Using
    
                End Using
            End If
    
    
        End Sub

    You're Stored Procedure in the Database should look something like that:

    CREATE PROCEDURE spI_Values 
    	-- Add the parameters for the stored procedure here
    	@CompanyID int,
    	@UserID int,
    	@RoleID int,
    	
    AS
    BEGIN
    	SET NOCOUNT ON;
    	INSERT INTO dbo.LookupTable	
    		(CompanyID,
    		 UserID,
    		 RoleID)
        Values
    		(@CompanyID,@UserID, @RoleID)
    END
    GO
    




    John
    • Edited by Codernater Monday, November 2, 2009 4:05 PM stored procedure name
    • Proposed as answer by Codernater Monday, November 2, 2009 4:05 PM
    Monday, November 2, 2009 4:04 PM