first function to delete from table
second function to insert into table
i want to use one transaction to begin trans ans commit trans between the tow fuctions
but i use sqlcommand in first function and another sqlcommand in second function
and so the error occur
What I have tried:
this is a delete method
public virtual Tuple<bool,SqlTransaction> DeleteFromTabel(SqlConnection cnn, string TabelName, bool useTruncate = false, bool useCommetTrans = false,
string WherCondtion = null,bool useContuniTrans =false,params object[] Paramter)
{
#region Variables
int Result;
SqlCommand DeleteCommand = new SqlCommand();
SqlTransaction DeleteTransAction=null;
#endregion
if (useCommetTrans | useContuniTrans)
{
DeleteTransAction = cnn.BeginTransaction();
DeleteCommand.Transaction = DeleteTransAction;
}
if (useTruncate)
{
DeleteCommand.CommandText = "truncate Table " + TabelName;
}
else
{
if (Paramter.Length!=0)
{
string[] ParameterName = new string[Paramter.Length];
int i = 0;
foreach (SqlParameter Par in Paramter)
{
ParameterName[i] = Par.ParameterName;
DeleteCommand.Parameters.AddWithValue(Par.ToString(), Par.Value);
i++;
}
string DeleteString = "Delete from " + TabelName + " where " + WherCondtion;
DeleteString = string.Format(CultureInfo.InvariantCulture, DeleteString, ParameterName);
DeleteCommand.CommandText = DeleteString;
}
else
{
DeleteCommand.CommandText = "Delete from " + TabelName;
}
}
DeleteCommand.Connection = cnn;
Result= DeleteCommand.ExecuteNonQuery();
#region Result
if (useCommetTrans)
{
}
if (Result == 0)
return new Tuple<bool, SqlTransaction>(false, DeleteTransAction);
else
return new Tuple<bool, SqlTransaction>(true, DeleteTransAction);
#endregion
}
This is a Insert Method
public virtual Tuple<bool, SqlTransaction> InsertIntoTable(SqlConnection cnn, string TabelName, string FieldsName, string FieldsValue, bool useCommetTrans = false,
string SelectFromAnotherTable = null, params object[] ParamterValues)
{
#region Variables
int Result;
SqlCommand InsertCommand = new SqlCommand();
SqlTransaction InsertTransAction = null;
#endregion
if (useCommetTrans )
{
InsertTransAction = cnn.BeginTransaction();
InsertCommand.Transaction = InsertTransAction;
}
if (ParamterValues.Length != 0 && FieldsName != null)
{
string[] ParameterName = new string[ParamterValues.Length];
int i = 0;
foreach (SqlParameter Par in ParamterValues)
{
ParameterName[i] = Par.ParameterName;
InsertCommand.Parameters.AddWithValue(Par.ToString(), Par.Value);
i++;
}
string InsertIntoTable = "Insert Into " + TabelName + "(" + FieldsName + ") Values (" + FieldsValue + ")";
InsertIntoTable = string.Format(CultureInfo.InvariantCulture, InsertIntoTable, ParameterName);
InsertCommand.CommandText = InsertIntoTable;
}
else
{
if (ParamterValues.Length != 0)
{
string[] ParameterName = new string[ParamterValues.Length];
int i = 0;
foreach (SqlParameter Par in ParamterValues)
{
ParameterName[i] = Par.ParameterName;
InsertCommand.Parameters.AddWithValue(Par.ToString(), Par.Value);
i++;
}
string InsertIntoTable = "Insert Into " + TabelName + " " + "Select " + SelectFromAnotherTable;
InsertIntoTable = string.Format(CultureInfo.InvariantCulture, InsertIntoTable, ParameterName);
InsertCommand.CommandText = InsertIntoTable;
}
else
{
string InsertIntoTable = "Insert Into " + TabelName + " " + "Select " + SelectFromAnotherTable;
InsertCommand.CommandText = InsertIntoTable;
}
}
InsertCommand.Connection = cnn;
Result = InsertCommand.ExecuteNonQuery();
#region Result
if (useCommetTrans)
{
InsertTransAction.Commit();
}
if (Result == 0)
return new Tuple<bool, SqlTransaction>(false, InsertTransAction);
else
return new Tuple<bool, SqlTransaction>(true, InsertTransAction);
#endregion
}