Inserting in sql server from array

Question

@foreach(var id in myidlist)
{
    message = sql.insertId(userid).ToList();
}

I know I can do this, but I really don't want to insert anything if there is a problem while inserting any one data.

For eg.

int[] numbers = new int[] {1, 2, 3, a, 5};

There is a character so there should come exception but 1, 23 and 234 gets inserted and is not rolled back. I need to use something like transaction.

If its' useful

public int insertId(int id)
{
    open();

    SqlCommand cmd = new SqlCommand("IDU", con);
    cmd.CommandType = CommandType.StoredProcedure;

    cmd.Parameters.AddWithValue("@Action", "INSERT");
    cmd.Parameters.AddWithValue("@id", id);

    int res = 0;

    res = cmd.ExecuteNonQuery();

    return res;
    close();
}

Can't you just check value with IF and pass it or decline insert? — Veljko89
– Veljko89, Commented Dec 31, 2015 at 11:31
yes its possible but , what i'm trying to say is that, if there is any error then i dont want to insert anything. — prabin badyakar
– prabin badyakar, Commented Dec 31, 2015 at 11:34
Yes, I understood that ... but i just thought it would be better to go through that array first and if everything is fine then send it for insert ... but if you really wanna go with SQL doing that for you, check for Begin and ROLLBACK Transaction — Veljko89
– Veljko89, Commented Dec 31, 2015 at 11:36
Please add the DBMS you are using. In SQL Server for example you can pass a table parameter. Other ways may include somekind of "serialization" passing a bunch of values as a concatened string — jean
– jean, Commented Dec 31, 2015 at 11:39

Arunprasanth K V · Accepted Answer · 2016-01-01 05:38:21Z

If you are looping the array and pass each variables to an sp then if array contains many records then it will cause performance issues, because each time you need to contact the sqlserver.

I think the best way is make all of your id values to an xml and pass it to an sp as a parameter and there you can use xquery to process it and simply in one step you can update all values into your destination table, also you can use sql transactions for rollback because you are calling the sp only once and all insertions are happen in same time so if anything will happen wrong then sql server transaction will take care of it. Method 1) Example :

an example table for insertion

create table tablez1
    (
    id int
    )

sp for insertion

 IF EXISTS (
            SELECT *
            FROM sys.objects
            WHERE type = 'P'
                AND NAME = 'SpName'
            )
    BEGIN
        DROP PROCEDURE SpName
    END
    GO

    create procedure spname
    @YourXml nvarchar(4000)
    as 
    BEGIN TRANSACTION  

    BEGIN TRY  
    SET NOCOUNT ON
        SET ARITHABORT ON

        DECLARE @iinput XML;

    DECLARE @xmldata XML = @YourXml
       insert into tablez1    

          SELECT id = Adr.value('(id)[1]', 'nvarchar(max)')

    FROM @xmldata.nodes('/Root/idvalues') AS Adddress(Adr)

    COMMIT TRANSACTION  
    END TRY  

    BEGIN CATCH  
     SELECT ERROR_NUMBER() AS ErrorNumber  
      ,ERROR_SEVERITY() AS ErrorSeverity  
      ,ERROR_STATE() AS ErrorState  
      ,ERROR_PROCEDURE() AS ErrorProcedure  
      ,ERROR_LINE() AS ErrorLine  
      ,ERROR_MESSAGE() AS ErrorMessage  
    END CATCH  
    GO

sample xml as parameter to our sp

exec spname '<Root>
    <idvalues>
    <id>1</id>
    </idvalues>
      <idvalues>
    <id>2</id>
    </idvalues>
      <idvalues>
    <id>3</id>
    </idvalues>
      <idvalues>
    <id>a</id>
    </idvalues>
      <idvalues>
    <id>5</id>
    </idvalues>
      </Root>'

Method 2)else you can use server side transactions Method 3)use a table valued parameter

To know more about querying xml data using XQUERY- READ

etalon11 · Accepted Answer · 2015-12-31 11:33:54Z

0

You have to work with transactions. If an SQL within a transaction fails, none of the commands will be commited. You should read Into transactions on mysql.com

answered Dec 31, 2015 at 11:33

etalon11

9943 gold badges15 silver badges40 bronze badges

3 Comments

jean Over a year ago

OP don't stated the DBMS he/she is using

etalon11 Over a year ago

Oh yes thats right. But in all dbms there are transactions.

jean Over a year ago

Sure OP is in the right way to try to put that bulk operation in a single atomic transaction but it's not the problem. The problem is how to implement it and it depends if he/she is using Oracle/MS SQL/Postgree...

Collectives™ on Stack Overflow

Inserting in sql server from array

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related