Update multiple rows at once : The Official Microsoft ASP.NET Forums

Reply

Re: Update multiple rows at once

Jan 16, 2012 08:13 AM|LINK

Hi Sophia,

Actually, if you use XML, you can write code in such a way that, your code will hit the database only once.

you have to create an XML file, then, you have to write the stored procedure at the backend. database hit will be only once.

Rajani

Reply

Re: Update multiple rows at once

Jan 16, 2012 08:17 AM|LINK

Public Shared Function InsertAmazonSettlementData(ByVal objDt As DataTable) As Boolean

        Dim objDa As DataAccess = Nothing
        Dim param(17) As SqlParameter
        'Dim intRetValue As Integer
        'Dim blnResult As Boolean = True
        Dim blnResult As Boolean = False
        param(0) = New SqlParameter("@idMarketplace", SqlDbType.Int)
        param(0).SourceColumn = "idMarketplace"
        param(1) = New SqlParameter("@prevSettlementDate", SqlDbType.SmallDateTime)
        param(1).SourceColumn = "prevSettlementDate"
        param(2) = New SqlParameter("@settlementDate", SqlDbType.SmallDateTime)
        param(2).SourceColumn = "settlementDate"

        param(3) = New SqlParameter("@transactionsPeriod", SqlDbType.VarChar, 255)
        param(3).SourceColumn = "transactionsPeriod"
        param(4) = New SqlParameter("@Orders_ProductCharges", SqlDbType.Money)
        param(4).SourceColumn = "Orders_ProductCharges"
        param(5) = New SqlParameter("@Orders_PromoRebates", SqlDbType.Money)
        param(5).SourceColumn = "Orders_PromoRebates"
        param(6) = New SqlParameter("@Orders_AmazonFees", SqlDbType.Money)
        param(6).SourceColumn = "Orders_AmazonFees"
        param(7) = New SqlParameter("@Orders_OtherCharges", SqlDbType.Money)
        param(7).SourceColumn = "Orders_OtherCharges"
        param(8) = New SqlParameter("@Orders_TotalBalance", SqlDbType.Money)
        param(8).SourceColumn = "Orders_TotalBalance"
        param(9) = New SqlParameter("@Refunds_ProductCharges", SqlDbType.Money)
        param(9).SourceColumn = "Refunds_ProductCharges"
        param(10) = New SqlParameter("@Refunds_PromoRebates", SqlDbType.Money)
        param(10).SourceColumn = "Refunds_PromoRebates"
        param(11) = New SqlParameter("@Refunds_AmazonFees", SqlDbType.Money)
        param(11).SourceColumn = "Refunds_AmazonFees"
        param(12) = New SqlParameter("@Refunds_OtherCharges", SqlDbType.Money)
        param(12).SourceColumn = "Refunds_OtherCharges"
        param(13) = New SqlParameter("@Refunds_TotalBalance", SqlDbType.Money)
        param(13).SourceColumn = "Refunds_TotalBalance"
        param(14) = New SqlParameter("@Other_TransactionCharges", SqlDbType.Money)
        param(14).SourceColumn = "Other_TransactionCharges"

        param(15) = New SqlParameter("@closingBalance", SqlDbType.Money)
        param(15).SourceColumn = "closingBalance"
        param(16) = New SqlParameter("@strText", SqlDbType.VarChar, 6000)
        param(16).SourceColumn = "strText"
        param(17) = New SqlParameter("@status", SqlDbType.VarChar, 50)
        param(17).SourceColumn = "status"
        'param(5) = New SqlParameter("@intRetValue", SqlDbType.Int)
        'param(5).Direction = ParameterDirection.Output

        Try
            objDa = New DataAccess("Amazon_Settlement_Insert", CommandType.StoredProcedure, param)
            objDa.runAdapater_Update(objDt, DataAccess.daCommandType.InsertCommand)
            'intRetValue = param(5).Value
            blnResult = True
        Catch ex As Exception
            blnResult = False
            'intRetValue = -1
        Finally

            If Not IsNothing(objDa) Then
                objDa.Dispose()
            End If
            objDa = Nothing

            If Not IsNothing(objDt) Then
                objDt.Dispose()
                objDt = Nothing
            End If

        End Try
        'If intRetValue = -1 Then
        '    blnResult = True
        'ElseIf intRetValue = 1 Then
        '    blnResult = False
        'End If

        Return blnResult
    End Function

MARK AS ANSWER IF THIS WORKS

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparameter.sourcecolumn.aspxHello Sophia,

you can pass datatable....and can call only ones...as following :

pass solurcecolumn instead of value in sql parameter...

Mark Answered if it helps - Good luck!
Cheers!
- Kaushik Patel

Reply

Re: Update multiple rows at once

Jan 16, 2012 08:36 AM|LINK

HI Kaushik,

Thanks for code, but can you please tell me how and where to call it and how to pass few no. of records that are to be updated

as parameter.

Many thanks....

Edited by sophia_asp on Jan 16, 2012 08:37 AM

Reply

Re: Update multiple rows at once

Jan 16, 2012 08:53 AM|LINK

Hello shopia...whever you want to call you save function.....that time you can pass the whole Datatable to you SQL call..... and still you want to see than please refere my blg....i will provide whole solution...

http://kaushikpatels.blogspot.com/2012/01/pass-datatable-to-store.html

MARK AS ANSWER IF THIS WORKS....

Mark Answered if it helps - Good luck!
Cheers!
- Kaushik Patel

Reply

harish.t

Member

73 Points

30 Posts

Re: Update multiple rows at once

Jan 17, 2012 11:37 AM|LINK

use stored procedures...

http://msdn.microsoft.com/en-us/library/aa174792(v=sql.80).aspx

http://www.go4expert.com/forums/showthread.php?t=9129

http://www.sql-server-performance.com/2003/stored-procedures-basics/

please "mark as answer" if my post helps u....

thanks & regards,
Harish T

Edited by harish.t on Jan 17, 2012 11:37 AM
Edited by harish.t on Jan 17, 2012 11:38 AM
Edited by harish.t on Jan 17, 2012 11:38 AM
Edited by harish.t on Jan 17, 2012 11:38 AM
Edited by harish.t on Jan 17, 2012 11:40 AM
Edited by harish.t on Jan 17, 2012 11:40 AM
Edited by harish.t on Jan 17, 2012 11:40 AM

Reply

Sudhir Murth...

Member

190 Points

47 Posts

Re: Update multiple rows at once

Jan 17, 2012 11:59 AM|LINK

You can use SQLTransaction class to achieve it.

The SQLTransaction makes all the batch updates an ATOMIC process,

i.e, it will either do a succesful update on all or fail.

for instance, you can use all the inserts / updates in one SQLTransaction.

Mark as answer, if found useful.

Cheers,

Sudhir

Reply

nadellas

Participant

1218 Points

299 Posts

Re: Update multiple rows at once

Jan 17, 2012 12:26 PM|LINK

I think the best option would be if you are implementing it using Entities I have some code to do that.

Basically U have to use Lists to do that.

Ex:

Create an Entity as below

namespace BusinessLayer

Class Student

{

public long StudentID {get; set; }

public string FirstName {get; set; }

public string LastName {get; set; }

public int Age {get; set; }

}

In Your Database Layer or Code for Adding Data to the Database

int AddData(iList<Entity> lst)

{

OracleTransaction trans = null;

StringBuilder Query = new StringBuilder();

int result;

bool commit = false;

string[] studentID = new string[lst.Count];

string[] firstName = new string[lst.Count];

string[] lastName = new string[lst.Count];

for (int i=0;i<lst.Count;i++)

{

studentID[i] = lst[i].studentID.ToString();

firstName[i] = lst[i].firstName.ToString();

lastName[i] = lst[i].lastName.ToString();

}

query.Append("INSERT Into Student(studentID,firstName,LastName) values (:StudentID,:FirstName,:LastName)");

using(OracleCommand command = database.GetSqlStringCommand(query.ToString()))

{

command.Parameters.Add(new OracleParameter(":StudentID", oracleDbType.Varchar2, studentId, ParameterDirection.Input));

command.Parameters.Add(new OracleParameter(":FirstName", oracleDbType.Varchar2, firstName, ParameterDirection.Input));

command.Parameters.Add(new OracleParameter(":LastName", oracleDbType.Varchar2, lastName, ParameterDirection.Input));

command.ArrayBindCount = lst.Count;

}

using (OracleConnection connection = new OracleConnection(_connectionString))

{

connection.Open();

trans = connection.BeginTransaction();

command.Connection = connection;

result = command.ExecuteNonQuery();

if (result > 0)

{

commit = true;

}

In your Aspx Page

iList<Entity> entities = new List<Entity>();

Entity ent = null;

foreach (GridView gd in <gridViewObject>.rows)

{

ent = new Entity();

<control> cntrl = (<control>) gd.FindControl("<ctrlID>');

ent.studentID = cntrl.Text;

//similarly add the other two values to the entity

ent.FirstName = cntrlFirstName.Text;

ent.LastName = cnntrlLastName.Text;

entities.Add(ent);

}

call Your Database Method and passparameter the entities object.

AddData(entities);

I belive this would be the best approach to update / insert the bunch of Data at once.

Thanks

Please Mark this thread as Answered if your problem is solved.

Marke Answer if find helpful -Srinivasa Nadella

Reply

sophia_asp

Member

521 Points

469 Posts

Re: Update multiple rows at once

Jan 17, 2012 04:01 PM|LINK

It is really becoming frustrating for me,

Can somebody give an example with a makup page where few controls are and the values are entered in those controls by user.

Then these values are collected in code behind and then call a method to update the multiple records with these new values that were entered by

user.

Many thanks...

Reply

sophia_asp

Member

521 Points

469 Posts

Re: Update multiple rows at once

Jan 18, 2012 06:33 PM|LINK

Kindly reply me....

Reply

rajanireddy

Member

362 Points

161 Posts

Re: Update multiple rows at once

Jan 19, 2012 03:22 AM|LINK

Are these rows in a grid or Dataset?

Rajani

Update multiple rows at once RSS

44 replies

rajanireddy

Re: Update multiple rows at once

kaushik_tatv...

Re: Update multiple rows at once

sophia_asp

Re: Update multiple rows at once

kaushik_tatv...

Re: Update multiple rows at once

harish.t

Re: Update multiple rows at once

Sudhir Murth...

Re: Update multiple rows at once

nadellas

Re: Update multiple rows at once

sophia_asp

Re: Update multiple rows at once

sophia_asp

Re: Update multiple rows at once

rajanireddy

Re: Update multiple rows at once

This site is managed for Microsoft by Neudesic, LLC. | © 2013 Microsoft. All rights reserved.

Follow Us On:

Microsoft