How to do multiple inserts with ado.net

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

Answer 1

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

Answer 2

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.

Answer 3

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

Answer 4

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.

Answer 5

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