The WriteToServer method will Copy all rows in the supplied DataTable to a destination table specified by the DestinationTableName property of the SqlBulkCopy object.
SqlBulkCopyOptions.CheckConstraints will Check constraints while data is being inserted. By default, constraints are not checked.
Once the bulkcopy is success then the data from the source is deleted and then commit both the transactions in source and target else rollback.
//move all rows to the Central, if insert is success delete all rows from the source else rollback.
SqlConnection Sconnection = new SqlConnection("Data Source=SYS2;User ID=sa;Password=sa123;initial catalog=TestData;");
SqlConnection Tconnection = new SqlConnection("Data Source=SYS-Central;User ID=sa;Password=sa123;initial catalog=TestData;");
SqlCommand command = Sconnection.CreateCommand();
SqlTransaction Stransaction = null;
SqlTransaction Ttransaction = null;
try
{
DataTable aSourceData = new DataTable();
command.CommantText="Delete from T_Emp_Details";
SqlDataAdapter dataAdapter = new SqlDataAdapter(SQLCommand, Sconnection)
Sconnection.Open();
dataAdapter.Fill(aSourceData);
Sconnection.Close();
Tconnection.Open();
Ttransaction = Tconnection.BeginTransaction();
SqlBulkCopy BulkCopy = new SqlBulkCopy(Tconnection,SqlBulkCopyOptions.CheckConstraints ,Ttransaction);
BulkCopy.DestinationTableName = T_Emp_Details;
BulkCopy.WriteToServer(aSourceData);
// have to set the source connection
Sconnection.Open();
Stransaction = Sconnection.BeginTransaction();
command.Transaction = Stransaction;
command.CommandText = "Delete from T_Emp_Details";
command.ExecuteNonQuery();
Ttransaction.Commit();
Stransaction.Commit();
}
catch(Exception e)
{
Ttransaction.Rollback();
Stransaction.Rollback();
}
finally
{
Tconnection.Close();
Sconnection.Close();
command.Dispose();
Stransaction.Dispose();
Ttransaction.Dispose();
Sconnection.Dispose();
Tconnection.Dispose();
}
|
No responses found. Be the first to respond and make money from revenue sharing program.
|