(I'm in a hurry) Problems with TransactionScope and MS DTC
Hello, I need help with this topic...
I'm working with Visual Studio 2005 and SQL Server 2005 DB within a Simple Local Network. I receive a Message:
"The asociated transaction administrator has disabled its support of remote or network transactions" (it's in spanish, so I did translate it to what it means to me in English)
The Service (MSDTC) is enabled, allowing inbound and outbound, and the "no authentication required" option is selected in both PC's, but it keeps throwing this message.
both PC's running Windows XP SP2 has the Firewalls (windows one included) disabled. but not seems to work anyway, even connecting them directly with a crossover cable.
I'm getting frustrated at full, so if anyone can reply me with a code in C# and the correct configuration of the MS DTC, It will make me very happy.
Here are some notes about what I'm doing:
- Connection String on the app.config file.
- Updates are made using TableAdapters.
- The Transaction will be executed within the code section of the DataSet (not in the WinForm). 'cause I have to update several tables at once (obvioulsy :-P)
- The Computers are connected using a Hub with the same IP range.
- The version of SQL Server 2005 is standard.
A lot of thanks.
JSR.
Never thought to answer an own question posted in a forum, but anyway here I go...
Notice that I don't know so much English so I will be as clear as possible...
There are some issues when using TransactionScope with TableAdapters, so if you want to use several TableAdapters in order to execute your queries, you should notice that, for each TableAdapter executed, a Connection to the DataBase will be opened up, 'cause of that process is promoted or moved from a simple transaction (better known as LightWeightTransaction) to a distributed transaction and for some reasons, as far as I now, it simply won't work, even with a full network access settings in the MS DTC.
Fortunately you won't need to replace the use of the TableAdapter, with a little help I found on the Web, I found something that works but I can assure you this is the best way to do, that's why I'm replying to my own question, in that way anyone that knows a better way or correction will be gladfully acepted.
Here is my complete code in the DataSet (notice that it was first coded in spanish).
using System;
using System.Windows.Forms;
//*** Required Namespaces ***//
using System.Data;
using System.Data.SqlClient;
using System.Transactions;
namespace PruebaControles.DSTablaTableAdapters {
public partial class RecibosTableAdapter {
// Apertura de la conexión (Opening the connection)
public SqlConnection OpenConnection() {
// Se le pasa la cadena de connexión (The ConnectionString is passed to)
// _connection es la propiedad de conexión del TableAdapter (_connection is TableAdapter's connection property)
_connection = new SqlConnection(Properties.Settings.Default.MyConnectionString);
if (_connection.State != ConnectionState.Open) {
_connection.Open(); }
return _connection;
}
// Cierre de la conexión (Closing the connection)
public void CloseConnection() {
_connection.Close();
}
public void Grabar(DSTabla DataSetTabla, DSTabla.BancosDataTable Bancos) {
ReciboDetalleTableAdapter ReciboDetalleAdapter = new ReciboDetalleTableAdapter();
BancosTableAdapter BancosAdapter = new BancosTableAdapter();
bool Hacer = true;
string mensaje = "";
// Inicio de la transacción (Beginnig the transaction)
using (TransactionScope Transaccion = new TransactionScope()) {
try {
this.OpenConnection(); // Apertura de la conexión (opening connection)
// Se agregan todos los querys (The query methods are called)
this.Update(DataSetTabla.Recibos);
ReciboDetalleAdapter.Update(DataSetTabla.ReciboDetalle);
BancosAdapter.Update(Bancos);
this.CloseConnection(); // Cierre de la conexión (closing connection)
}
catch (Exception any) {
mensaje = any.Message;
Hacer = false;
}
finally {
// Si no hubo ninguna Excepción se hace el Commit (if there were not Exceptions the Transaction is commited)
if (Hacer) {
Transaccion.Complete();
}
} // end_try
} // end_using
if (Hacer) {
MessageBox.Show("Finalizó el proceso de grabar con xito", "PruebaControles", MessageBoxButtons.OK, MessageBoxIcon.Information); }
else {
MessageBox.Show("No se grabaron los datos.\nError:\n" + mensaje, "PruebaControles", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
} // end_Grabar()
} // end_Class
} // end_Namespace
This is the way it worked for me. I hope this helps anyone who might need it. All the credit for this goes to Sahil Malik (thanks again) and his blogspot.
http://blah.winsmarts.com/2006/06/18/the-definitive-tableadapters--transactions-blog-post.aspx
Be seeing ya !!!
