ASP.net Connecting to two databases at once?
Is it possible to connect to two SQL databases at the same time? I mean, from one database, I read records and compare them against some data like an email address and based on a decision on whether that email address exists in the database or not. I am saving a new record to another database.
Is this double action possible?
I am connecting to databases using SqlConnection and SqlCommand statements from C # .net
Thank.
source to share
Yes it is possible.
You can either return the value to your asp.net app and then connect to another database like:
cmdEmailExists SqlCommand = new SqlCommand("SQL HERE...", Conn1);
if (((int)cmdEmailExists.ExecuteScalar())>0){
cmdInsert SqlCommand = new SqlCommand("SQL INSERT HERE...", Conn2)
cmdInsert.ExecuteNonQuery();
}
Where Conn1
and Conn2
are 2 different SqlConnection
, connecting to 2 different databases.
Or it can be done at the end of the SQL like:
IF EXISTS(SELECT Email FROM [Database1].dbo.tbl)
BEGIN
INSERT INTO [Database2].dbo.tbl ..........
END
source to share
Maybe this will help you or send ur code
SqlConnection con1 = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["con1"].ConnectionString);
SqlConnection con2 = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["con2"].ConnectionString);
SqlCommand cmd = new SqlCommand("select * from table1", con1);
SqlDataReader dr;
con1.Open();
dr = cmd.ExecuteReader(CommandBehavior.SingleResult);
while (dr.Read())
{
// ....
}dr.Close()
//your condition then fire insert commnd with connection con2
SqlCommand insertcmd = new SqlCommand("insert into table2", con2);
SqlDataAdapter dap = new SqlDataAdapter(insertcmd);
// ...
source to share
This is a good solution, but not the right answer! It is not necessary to use 2 connection strings to use 2 different databases on the same server (assuming the user has the correct permission). We specify the home directory at connection time to determine where we want to run the query, but if the user has sufficient permissions to execute the query in other databases, he can do so using two Db..table points
This code will work using one connection string!
SqlConnection con = new SqlConnection(@"Use-connection-string-here");
SqlCommand cmd = new SqlCommand(
"select * from Table1 t1 left join Database2..Table2 t2 on t1.id = t2.id", con
);
source to share