如何同时插入两个数据库? [英] How do I insert into two databases at the same time?
本文介绍了如何同时插入两个数据库?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有两个数据库A和B.
我想同时插入两个数据库表。
我在数据库A中有一个表tbl_std(column-userid),在数据库B中有一个tbl_cust(column-userid)。
我想插入两个数据库表中的userid同时存在。怎么做?
我应该写两个插入语句吗?如果是,那么我必须在web.config文件中提供两个连接吗?因为我使用的是两个数据库。
我是初学者。任何见解都会受到高度赞赏。
解决方案
正如已经指出的那样:当两个数据库共享同一个服务器实例时,一个连接字符串就可以完成,你只需使用完整的限定名称的表。我建议将两个语句放在事务块中,要么成功要么都不成立。这样的事情:
使用(IDbConnection connection = new SqlConnection(ConnectionString))
{
connection.Open();
var transaction = connection.BeginTransaction();
尝试
{
字符串 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,连接);
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();
// 或许检查每条记录是否受到影响
transaction.Commit ();
}
catch (例外情况)
{
Logger.Error(ex);
transaction.Rollback();
}
}
它可以通过单个连接字符串完成。
插入 进入 a.tbl_std 值 ( 101 )
insert into b.tbl_cust 值( 101 )
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.
解决方案
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:
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(); } }
It can done through single connection string.
insert into a.tbl_std values(101) insert into b.tbl_cust values(101)
这篇关于如何同时插入两个数据库?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文