Hi!

One of my "old problems" with LLBLGen is that sometimes I simply need to use a SQL query to retrieve info from my db. If this happens whilst I am deep into a LLBLGen transaction then either my table is locked or a new sql connection is created. None of those scenarios are desirable. So I wonder if there is any way I can attach the "database transaction" that is part of the LLBLGen transaction to my own SQL query using System.Data.SqlClient?

I tried to work this out a few times before over the years with no luck. If it is possible I would really like to know how to do it.

Grateful for help!

!Rob

Oops, this thread really did not land in the right place, sorry. I have no idea how I move it myself.

Adapter or SelfServicing?

(Edit)

try searching the forums for: Raw SQL Or: Adhoc SQL

Hi

Selfservicing, I have understood that makes me a lower life-form. But Being 250 tables and 6 years into the project I am disinclined to change to adapter unless somebody gives me a very good reason too.

I tried searching the forums. It came up with MANY threads but I found none that discussed using the HelperClasses.Transaction in SqlClient.

Am I vague in what it is I am after?

The below would roughly describe what I would like to achieve:

        public static DataTable getDataTableFromSQLWithLLBLGenTxn(string sSQL, sevenG.BusinessLogic.HelperClasses.Transaction dbtxn)
        {
            DataTable dt = new DataTable();
            try
            {
                SqlDataAdapter da = new SqlDataAdapter(sSQL, new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["Main.ConnectionString"].ToString()));
                da.SelectCommand.Transaction = dbtxn.PhysicalTransaction;
                da.SelectCommand.CommandTimeout = 600;
                da.Fill(dt);
                da = null;
                return dt;
            }
            catch
            {
                return null;
            }
        }

The line:

da.SelectCommand.Transaction = dbtxn.PhysicalTransaction;

Obviously doesn't work but I put it there to make it clear what my aim is.

regards,

!Rob

Ok, I have come up with this:

public static DataTable getDataTableFromSQLWithLLBLGenTxn(string sSQL, Transaction dbtxn, out Exception ex)
{
    try
    {
        RetrievalQuery query = new RetrievalQuery(new SqlCommand(sSQL));
        query.Connection = dbtxn.PhysicalTransaction.Connection;
        query.WireTransaction(dbtxn.PhysicalTransaction);
        SqlDataReader dr = (SqlDataReader)query.Execute(CommandBehavior.Default);
        DataTable dt = new DataTable();
        dt.Load(dr);
        dr.Close();
        dr = null;
        ex = null;
        return dt;
    }
    catch (Exception exTemp)
    {
        ex = exTemp;
        return null;
    }
}

I would appreciate a comment on whether this will accomplish my objective. That is, will the query execute in the context of my HelperClasses.Transaction?

Regards,

!R

The code looks fine, and it will be executed within the dbtxn transaction.

Thanks Walaa

I spruced it up a bit in case somebody else have use for it.

Regards,

!Rob

public static DataTable getDataTableFromSQL(string sSQL, HelperClasses.Transaction txn, out Exception ex)
{
    DataTable dt = new DataTable();
    try
    {
        if (txn == null)
        {
            SqlDataAdapter da = new SqlDataAdapter(sSQL, new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["Main.ConnectionString"].ToString()));
            da.Fill(dt);
            da = null;
        }
        else
        {
            RetrievalQuery query = new RetrievalQuery(new SqlCommand(sSQL));
            query.Connection = txn.PhysicalTransaction.Connection;
            query.WireTransaction(txn.PhysicalTransaction);
            SqlDataReader dr = (SqlDataReader)query.Execute(CommandBehavior.Default);
            dt.Load(dr);
            dr.Close();
            dr = null;
        }
        ex = null;
        return dt;
    }
    catch(Exception exTemp)
    {
        ex = exTemp;
        return null;
    }
}

Thanks for the feedback.