locked
How to update multiple Server tables using dblink in C# RRS feed

  • Question

  • Hi,

    I have requirement to update multiple data base which is in different server, i thought its straight like executenonquery using ado.net but its always updating 1st server, not all server.

    Query is like this : 

    Connection.open()

    Begin.Transaction();


    Update SchemaName.TableName@server1 set colum1 ='abc, where column2="vv";

    CommitTransaction();

    Begin.Transaction();

    Update SchemaName.TableName@server2 set colum1 ='abc, where column2="vv";

    CommitTransaction();

    Begin.Transaction();

    Update SchemaName.TableName@server3 set colum1 ='abc, where column2="vv";

    CommitTransaction();

    Connection.Close();

    Please help me what i dm doing wrong??


    Jumping

    Tuesday, August 18, 2020 1:21 AM

All replies

  • If using one connection object you need to change the connection string to correspond to the database you are working on then close the connection, change the connection string and do the next operation.  Does this make sense?

    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Tuesday, August 18, 2020 2:12 AM
  • I dont want to open multiple connection, dblink means if u open one connection, it can establish connection between server, so no need to open and close connection??

    like if i open SQl developer with one connection, i can run all my queries between different servers.

    Thanks


    Jumping

    Tuesday, August 18, 2020 2:19 AM
  • Is this Oracle database? Check if it works in case of stored procedures that update the linked servers. Also try without transactions.


    • Edited by Viorel_MVP Tuesday, August 18, 2020 5:13 AM
    Tuesday, August 18, 2020 5:02 AM
  • Thanks

    Yes this is Oracle Db link.

    I am trying to use with C# code, not from Store proc.

    Thanks


    Jumping

    Tuesday, August 18, 2020 12:46 PM
  • This is not valid C# code. Please use the Insert Code Block tool to insert the actual C# method you're calling rather than the subset you posted here. Most likely the issue isn't in subset of code you posted. The fact that you're using transactions for a single update statement indicates to me there is more going on here such that the problem is elsewhere. There is 0 benefit of using transactions for a single update statement.

    Also clarify what driver you are using. Note that .NET's OracleConnection support has been deprecated for years. When using Oracle you should be using IBM's ODP.NET drivers instead. They are official supported by IBM through their own dedicated forums. For questions related to using ODP.NET (which is mostly a drop in replacement) you should post in their forums.


    Michael Taylor http://www.michaeltaylorp3.net

    • Proposed as answer by Naomi N Tuesday, August 18, 2020 5:39 PM
    Tuesday, August 18, 2020 1:34 PM
  • Thanks,

    I gave just sudo code, not actual code, my requirement is this.

    1. from C# code want to use Oracle dblink, so i can update multiple tables which is in different servers.

    I dont open and close connection for each servers. i can do same thing using sql developer tool,

    like this update table@link1 set column='abc' where column2=bcd'

    update table@link2 set column='abc' where column2=bcd'

    and this working, i want to achieve same thing from C# code, with one server connection string.

    Thanks


    Jumping

    Tuesday, August 18, 2020 1:43 PM
  • So write the code then. Based upon your post it seems like it wasn't working for you but you're now saying you haven't tried. Why not? Using ODP.NET it is pretty much exactly like you wrote so there shouldn't be any issues. Just try it.

    using (var conn = new OracleConnection(connectionString))
    {   
       var cmd = conn.CreateCommand();
       cmd.CommandText = "BEGIN " +
                         "UPDATE ...;" +
                         "UPDATE ...;" + 
                         "END;";
    
       conn.Open();
       cmd.ExecuteNonQuery();
    };

    Completely making up the Oracle syntax here for running multiple SQL calls in a single batch. For parameters you'll want to use the Parameters collection to pass them in rather than using string concatenation to join them. Oracle prepends : for parameters so something like this.

    //Part of CommandText
    "UPDATE ... :p_myparam..."
    
    cmd.Parameters.Add(":p_myparam", OracleDbType.Varchar2, "someValue", ParameterDirection.Input);
    
    


    Michael Taylor http://www.michaeltaylorp3.net

    Tuesday, August 18, 2020 2:21 PM