Category: .NET

12 Aug 2016 13:07

Joined:	02/06/2016
Level:	Bronze
Points:	6

Points:	5

Update multiple rows at once using c#

Hi,

I want to update multiple rows with different values at once (avoid multiple calls to db)using c#(just console app)

static void Main(string[] args)
{

String connectionString = "";
int EID = 0;
string EmailAddress = "";

try
{
using (SqlConnection connection = new SqlConnection(connectionString))
using (SqlCommand command = new SqlCommand("select * from table", connection))
{
connection.Open();

using (SqlDataReader reader = command.ExecuteReader())
{

while (reader.Read())
{

EID =int.Parse( reader["Rec_ID"].ToString());
EmailAddress = reader["FirstlName"].ToString();
//take this EID and update values where EID = EID
here i want to update each row with different values based on 'EID '
and update whole thing at once instead of updating each row
}
Console.ReadLine();
}
}
}
catch(Exception ex)
{

Console.WriteLine(ex.Message);
Console.ReadLine();
}
}

thanks
madhu

#767541

12 Aug 2016 22:13

Joined:	29/05/2009
Level:	Gold
Points:	10380

Points:	3

Hi

same time we canot do update multiple row. Because update field we must different data so

so you can try this Query


update tbl_employee set [Employee Name]='a-T20' where [Employee ID] IN (1,3)

This Query Reflecting who is emoloyee id 1,3 only updated but employeename same both records

thats drawback for this situation Query different id in same Query Execution.

Name : Dotnet Developer-2015
Email Id : kumaraspcode2009@gmail.com

'Not by might nor by power, but by my Spirit,' says the LORD Almighty.

#767563

17 Aug 2016 05:02

Joined:	13/08/2015
Level:	Silver
Points:	102

Points:	3

hi Madhu,
First of all list out which of the fields you want to update.
set new value of your fields.
just add your List out fields in where condition.
update tbl_employee set [Employee Name]='a-T20' ,email ='value',mobile='value' , city='value',....n where [Employee ID] IN (1,3) and email='xxx@gmail.com' and mobile='12345' and ........ n
N number of condition you can add iinside the Where.

hope this will help you.
Paul.S

#767575

17 Aug 2016 22:18

Joined:	03/05/2013
Level:	Gold
Points:	15822

Points:	2

Hi,

As per your request you want to stop multiple calls to database in that case store the data into CSV file and update the CSV file records into database at single shot.

Refer below link here they were explain how to insert bulk insertion into database.

--------------------------------------------------------------------------------
Give respect to your work, Instead of trying to impress your boss.

N@veen
Blog : http://naveens-dotnet.blogspot.in/

#767577

17 Aug 2016 22:25

Joined:	13/06/2007
Level:	Gold
Points:	21327

Points:	3

if you have multiple rows and each row has its unique ID then you need to loop on each row, there is not shortcut or alternative for it.
as update is accepting unique values in WHERE clause, otherwise it has update with same ID, but in your case you have to update it with different ID, so I think you need to loop on each row

Thanks
Koolprasd2003
Editor, DotNetSpider MVM
Microsoft MVP 2014 [ASP.NET/IIS]

#767579

18 Aug 2016 00:00

Joined:	15/12/2015
Level:	Gold
Points:	1308

Points:	4

Hi,
Just create update script inside while loop and after end of while loop, execute it.
Remember one thing that you must separate out all update statements with ';' i.e. semicolon. In this way you can execute multiple sql statements at a single glance on DB.


            string szQuery="";
            while (reader.Read())
             {
                 EID =int.Parse( reader["Rec_ID"].ToString());
                 EmailAddress = reader["FirstlName"].ToString();
                 szQuery = szQuery+"Update table set comumn1 = YourValue Where EID= '"+EID+"';";
             }

            SqlCommand command = new SqlCommand(szQuery, con);
            command.ExecuteNonQuery();

#767727

31 Aug 2016 23:01

Joined:	28/04/2006
Level:	Gold
Points:	29773

Points:	4

Hai Madhu,
If you don't want to call multiple times the database or don't want to hit database multiple times, you can use single Update statement by passing all the values at once as table.
From your code, pass the values like below in the single Update statement:


UPDATE YourTable  SET Comumn1 = 'value1'   WHERE Rec_ID = '1'
(
  SELECT Rec_ID   FROM YourTable  
            WHERE Rec_ID = '1'
)

So here you will update and select at the same time.
If you want to update bulk records at once, you need to create the stored procedure which will take the whole table as the parameter and then inside the stored procedure, you can loop for each records and do the update.
Hope it will be helpful to you.

Regards,
Pawan Awasthi(DNS MVM)
+91 8123489140 (whatsApp), +60 14365 1476(Malaysia)
pawansoftit@gmail.com

Update multiple rows at once using c#

About dotnetspider.com

Quick Links

General

Services