MsSQL Bulk Copy With Transaction in C#.NET

private static SqlConnection _oConnection=null;

private static SqlTransaction _oTransaciton=null;

protected int ExecuteBulkCopy(string sTableName,DataTable dtData)

{

int nReturn = 0;

try

{

if (_oConnection == null)

{

_oConnection = new SqlConnection(CONNECTION_STRING);

}

if (_oConnection.State == ConnectionState.Closed)

{

_oConnection.Open();

}

try

{

_oTransaciton = _oConnection.BeginTransaction();

SqlBulkCopy bulkCopy = new SqlBulkCopy(_oConnection,

SqlBulkCopyOptions.TableLock,_oTransaciton);

bulkCopy.DestinationTableName = sTableName;

bulkCopy.WriteToServer(dtData);

_oTransaciton.Commit();

_oTransaciton.Dispose();

_oTransaciton = null;

nReturn = 1;

}

catch {

_oTransaciton.Rollback();

_oTransaciton = null;

nReturn = 0;

}

}

catch (SqlException oSQLException)

{

throw new Exception("SQL ERROR: " + oSQLException);

}

return nReturn;

}