loosing transaction with sqltransaction and sqlcommandbuilder

[silenius]

I got trouble using transaction and sqlCommandbuilder, it work 99% of time
and from time to time it crash telling that the command doesn't have a
transaction for the selected connection. here is what i do if anybody got an
idea.

protected SqlDataAdapter[] m_sqlAdapters = null;
static SqlConnection[] s_sqlConnections=null;
static SqlTransaction[] s_sqlTransactions=null;

public virtual SqlConnection[] Connections
{
get
{
return s_sqlConnections;
}
set
{
s_sqlConnections=value;
}
}

public virtual SqlTransaction[] Transactions
{
get
{
return s_sqlTransactions;
}
set
{
s_sqlTransactions=value;
}
}

[...]

for(int i=0;i<this.NbConnections;i++)
{
this.m_sqlAdapters=new SqlDataAdapter("select * from
"+TableName,this.Connections);
m_standardBuilder=new SqlCommandBuilder(this.m_sqlAdapters);
}

[...]

//GetConnection give the id of a usable connection, oppen a transaction and
assign it in the transaction array.
this.GetConnection(ref idConnection);
if
((this.Transactions[idConnection]!=null)&&(this.Transactions[idConnection].C
onnection!=null))
{
//I am sure it did this even before a crash, (tested in debug mode)

this.m_sqlAdapters[idConnection].SelectCommand.Transaction=Transactions[idCo
nnection];
}
else
{
this.m_sqlAdapters[idConnection].SelectCommand.Transaction=null;
}
this.m_sqlAdapters[idConnection].Update(tempArray);

 

[Miha Markic [MVP C#]]

Hi,

Each command used by dataadapter requires a transaction (InsertCommand,
UpdateCommand, DeleteCommand) - the only one that is not used within Update
method is SelectCommand
Try assigning to each command the transaction instance.