connecting to 2 diffrent sql servers databases from single web application

i am having 2 sql servers one is web sql server server and other server is my local sql server. i am making web application through which i want to connect both sql servers at a time and by selecting data from web server i want to insert it into my local sql server both having same database on which i want to work , i want to know how it can be done

my idea as follows in web.config i specify 2 connection string and by selecting data from websql server database table i going to store it in dataset and then i return dataset to another function which connects to local sqlserver database.but database records are much large. can any body guide me .

i am going to place my web application on local server (C# asp.net 2.0) .

thank you,

i tried this code

c# file

public partialclass _Default : System.Web.UI.Page {protected void Page_Load(object sender, EventArgs e) { }protected void Button1_Click(object sender, EventArgs e) { Uupdatedata(); }public void Uupdatedata() {string constr1=ConfigurationSettings.AppSettings["abccon1"];string constr2 = ConfigurationSettings.AppSettings["abccon2"]; System.Data.SqlClient.SqlConnection dbcon1 =new System.Data.SqlClient.SqlConnection(constr1); dbcon1.Open(); System.Data.SqlClient.SqlConnection dbcon2 =new System.Data.SqlClient.SqlConnection(constr2); dbcon2.Open(); SqlDataReader dr; SqlCommand cmd =new SqlCommand("select * from Unit_Master",dbcon1); dr = cmd.ExecuteReader();while (dr.Read()) {string Sql ="insert into Table1(Unit_Id,Unit_Desc) values(" + dr.GetInt32(0).ToString() +"," + dr.GetString(1) +")"; SqlCommand cmd1=new SqlCommand (Sql,dbcon2);//cmd1.EndExecuteNonQuery (); }//dr.Close(); //dbcon2.Close(); }

web.config

<configuration><appSettings><add key="abccon1" value="Data Source=comp01;Initial Catalog=abc;User Id=sa ; Password=sa"/><add key="abccon2" value="Data Source=comp01;Initial Catalog=abctest;User Id=sa ; Password=sa"/></appSettings><connectionStrings/>

this 2 databases are from single my loacl server

it wont work it gives exception

please healp me.

What line is the exception thrown on?

|||

And also, I'm going to assume that your obfuscating your connection information, because your just using "comp01" for both, and you said that 1 of the DB's is on a remote machine, correct?

|||

i pasted code of testing application i making and exception is not thrown but when i debug at that time in second connection ie dbcon2 properties shows exception otherwisecode works fine but nothing inserted in the second table,please healp me.

|||

Hi,

i specify 2 connection string and by selecting data from websql server database table i going to store it in dataset and then i return dataset to another function which connects to local sqlserver database

From your code you provided, it seems that you did not follow the way you mentioned, instead, you are using datareader to retrieve those data and create each insert command and execute it for each row, right? After you have create the sqlcommand, why don't use call the ExecuteNonQuery() method to execute the command.

Besides, you mentioned that the records in database are much large. Then you'll loop many times while retrieving your data from datareader, right? Then in your code, the sqlcommand object instance would be created repeatly. It would damage the performance. So what I suggest is create the instance of sqlcommand once, and modify the commandtext of sqlcommand and execute it in each loop.

Thanks.