You must Sign In to post a response.
  • Category: .NET

    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
    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
    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
    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
    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
    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
    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


Sign In to post your comments