Get Data From one SQL Server to Other SQL server (using C#)

Junaid Soomro

12y1.4kSQL

I want the code for copy data from one sql to another sql (Using C#)

When I click the Button

Copy my db2_connect Database data like:

select * from importdb where bhand = 3 and store = 14

To my another database which is: abdConnection

in abdConnection Table is importabd and filed is same (bhand)

Can someone write the complete code.

Thanking You

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jignesh Trivedi12y ago
hi,
I think if you have OLEDB connection so you must have OleDbDataReader.
try
OleDbDataReader readerr = command.ExecuteReader();
same as for odbc connection you must have OdbcDataReader...
hope this will help you.
Junaid Soomro12y ago
is this code okay? but showing error:

protected void Button1_Click(object sender, EventArgs e)
{
this.cn = new OdbcConnection();
this.Jda_Cmd = new OdbcCommand();
this.cn.ConnectionString = "DSN=Imports_Img;UserID=Oabd;Password=2323;Data Source=172.16.4.5;Transport Product=Client Access;SSL=DEFAULT;DEFAULT COLLECTION='BINDAWOOD'";
this.Jda_Cmd.Connection = this.cn;
//string str = "SELECT * FROM BDGDVPLIB.FAMSTPF WHERE FASTNO='" + Strings.Trim(this.TxtRefNo.Text.ToString()) + "'";
//this.Jda_Cmd.CommandText = str;
if (this.cn.State == ConnectionState.Closed)
{
this.cn.Open();

SqlCommand command = new SqlCommand("SELECT * FROM MM410LIB.INVBAL WHERE INUMBR = 79610 AND ISTORE=14");
// SqlDataReader reader = command.ExecuteReader();
SqlDataReader readerr = command.ExecuteReader();

SqlConnection connection2 = new SqlConnection ("JdaDB");
connection2.Open();

while (readerr.Read())
// while (reader.Read())
{
SqlCommand command2 = new SqlCommand ("insert into invbaldb ('sku') values ('" + readerr[0] + "')", connection2);
command2.ExecuteNonQuery();
}
connection2.Close();
}
}

error line: SqlDataReader readerr = command.ExecuteReader();
Jignesh Trivedi12y ago
hi,
I think SQL bulk copy feature can help you.
try..
//fill you data set to one server..
DataSet ds = FillData();
//Create connetion for other Server...
SqlConnection sCon = new SqlConnection(@"Other data base connection string");
System.Data.SqlClient.SqlBulkCopy bc = new SqlBulkCopy(sCon);
bc.DestinationTableName = "Destination table Name";
sCon.Open();
bc.WriteToServer(ds.Tables[0]);
bc.Close();
sCon.Close();
hope this will help you.