I want to implement transaction within three database insert methods.

void Method1(parameters,connection,transaction)
{
cmd.Connection = connection;
cmd.Transaction = transaction;
...
}

void Method2(parameters,connection,transaction)
{
cmd.Connection = connection;
cmd.Transaction = transaction;
...
}
void Method3(parameters,connection,transaction)
{
cmd.Connection = connection;
cmd.Transaction = transaction;
...
}

Inside Main method I create the connection object and SqlTransaction object.

con.Open();
transaction = con.BeginTransaction();

Method1(con,transaction);//along with other parameters
Method2(con,transaction);
Method2(con,transaction);

After calling these methods I got these....

"Timeout
expired. The timeout period elapsed prior to completion
of the operation or the server is not responding."

Removing the transaction works OK but I want operations to be transacted..

Any ideas?

"Dont you ever give up!"

__________________
\"Dont you ever give up!\"

hi

use same SQLCommand object for all the procedures.

Ashu

doesn't fix that..I still have problems.

I created a SqlCommand object and passed it to all the methods.Timeout problem is still thrown.......

"Dont you ever give up!"

can u post or mail the source code ?

Ashu

Here's how it look like:

//Here is the main method
public void SubmitSection(string xml)
{
SqlConnection con = new SqlConnection(conStr);
SqlTransaction tran = null;
SqlCommand cmd = new SqlCommand();
try{
con.Open();
tran = con.BeginTransaction();
//XML processing codes here
//first call
InsertValues(tmpSqlString,section["Fields"],fieldsTable,cmd);
cmd.Parameters.Clear();
UpdateLastSectionSubmitted(m_sectionId,con);
cmd.Parameters.Clear();
UpdateEnrolmentStatus((uint)subjectId,1,cmd);
//commit transaction
tran.Commit();
}catch(Exception){
//rollback the transaction
tran.RollBack();
}finally{
    con.Close();
}



private void InsertValues(string sql,SqlCommand cmd)
{
StringBuilder mySqlString = new StringBuilder();
    SqlCommand cmd = new SqlCommand();
    //build the sql string
    mySqlString.Append(sql);
    cmd.CommandText = mySqlString.ToString();
    cmd.CommandType = CommandType.Text;
    try
    {
        cmd.ExecuteNonQuery();
    }catch(SqlException) {
         throw;
    }
}

//same thing happens in other two methods
//setting command parameters
//executing the command


"Dont you ever give up!"

hi

Dont make a new SQLCommand object in the Insert procedure and pass the SQLCommand object ByRef.

Also initialize the Connection and Transaction properties for the SQLCommand object.

Ashu

i have initialize the cmd transaction and Connection parameters on the SubmitSection method,sorry i had removed it...il try passing the cmd object by reference. thanks

"Dont you ever give up!"