Click here to Skip to main content
15,885,278 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I have two databases A and B.

I want to insert into both database tables at the same time.

I have a table tbl_std (column-userid) in database A and tbl_cust(column-userid) in database B.

I want to insert userid in both the database tables at the same time. How to do it?

Shall I write two insert statements? IF yes, then shall I have to give two connections in the web.config file? Since I'm using two databases.

I am a beginner. Any insight would be highly appreciated.
Posted
Updated 24-Jun-15 0:59am
v2

As already pointed out: when both databases share the same server instance, one connectionstring will do, you simply use the full qualified name of the table. I would recommend putting the two statements in a transaction block though, either both succeed or none. Something like this:

C#
using (IDbConnection connection = new SqlConnection(ConnectionString))
{
  connection.Open();
  var transaction = connection.BeginTransaction();
  try
  {
    string sSql1 = "INSERT INTO  [A].dbo.tbl_std  (userid) VALUES ( @USERID ) "
    string sSql2 = "INSERT INTO  [B].dbo.tbl_cust (userid) VALUES ( @USERID ) "
    var command1 = new SqlCommand(sSql1, connection);
    command1.Parameters.Add(new SqlParameter("@USERID", 101));
    var command2 = new SqlCommand(sSql2, connection);
    command2.Parameters.Add(new SqlParameter("@USERID", 101));
    int iRecordsAffected1 = command1.ExecuteNonQuery();
    int iRecordsAffected2 = command2.ExecuteNonQuery();
    //perhaps check that 1 record has been affected each
    transaction.Commit();
  }
  catch (Exception ex)
  {
    Logger.Error(ex);
    transaction.Rollback();
  }
}
 
Share this answer
 
v2
Comments
Jörgen Andersson 25-Jun-15 2:46am    
Principally correct, but not complete. You could add some information on linked servers.
CaThey Joy Galias 24-Mar-18 18:22pm    
This problem also i need solusyon.... How if those two table have different value to save... But less value for the second insert statement... I got error because of the same attribute they have... .any idea
It can done through single connection string.
SQL
insert into a.tbl_std values(101)
insert into b.tbl_cust values(101)
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900