Click here to Skip to main content
15,881,089 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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) // Delete all table Data Without Condtion
           {
              DeleteCommand.CommandText = "truncate Table " + TabelName;

           }
           else //Delete data table By Condtion
           {
               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)

           {
              // DeleteTransAction.Commit();
           }

           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
        }
Posted
Comments
DerekT-P 12-Jun-19 14:53pm    
You don't seem to be passing the transaction you created in the Delete into the Insert methods... and even if you were, you're either always setting the transaction to null, or starting a fresh transaction (depending on useCommetTrans - we don't really know what that's meant to indicate). For us to assist you better, suggest you simplify both methods so you just leave the code that would be executed if you want to carry across the transaction from the delete to the insert, together with the calling code as well.
Also, be aware that if using Truncate, this command is not un-doable, i.e. does not participate in a transaction anyway; DDL (which isn't supported across all DBMS implementations) is a DDL command, not DML and shouldn't really be considered as an "alternative" to using Delete.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900