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 conexion (Opening the connection)
public SqlConnection OpenConnection() {
// Se le pasa la cadena de connexion (The ConnectionString is passed to)
// _connection es la propiedad de conexion 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 conexion (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 transaccion (Beginnig the transaction)
using (TransactionScope Transaccion = new TransactionScope()) {
try {
this.OpenConnection(); // Apertura de la conexion (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 conexion (closing connection)
}
catch (Exception any) {
mensaje = any.Message;
Hacer = false;
}
finally {
// Si no hubo ninguna Excepcion 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("Finalizo el proceso de grabar con exito", "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 !!!
