WriteEdit record lock MS SQL Database

nfite

I have an editor .NET Version 1.6.3, recently updated.

I was using PreEdit prior to do some calculations on certain fields in a table and defaulting some specific values.

I noticed the definition of WriteEdit and determined it would be the proper route in order to perform my calculations.

Now it would appear that when a Row is being edited by calling sub routines performing calculations I am running into a record lock issue in my MSSQL database.

Below is Write edit and the sub routines being called
```
//Get variables for Product Cubic Feet and pass them to CalcProductCubicFeet procedure
decimal PHeight = Convert.ToDecimal(itemDtObject("ProductODHeight", itemid, z));
decimal PWidth = Convert.ToDecimal(itemDtObject("ProductODWidth", itemid, z));
decimal PLength = Convert.ToDecimal(itemDtObject("ProductODLength", itemid, z));

                CalcProductCubicFeet(PHeight, PWidth, PLength, z);

Below is the function itemDtObject

    private string itemDtObject(string strFieldName, string itemID, overallEA e)
    {

        object objVariable;
        string strReturn = "";
        Dictionary<string, object> dictItem = (Dictionary<string, object>)e.Values["Item"];
        dictItem.TryGetValue(strFieldName, out objVariable);
        string strFieldCon = "Item.";
        string streFieldConFin = strFieldCon + strFieldName;
        //Try to get value from Dictionary Item
        if (objVariable != null)
        {
            if (objVariable.ToString().Trim() == "")
            {
                strReturn = Convert.ToString(0);
               //e.Editor.Field(streFieldConFin).SetValue(strReturn);
               setvaluesDirect(streFieldConFin, strReturn, e.Id.ToString(), "string");
            }
            else
            {
                strReturn = Convert.ToString(objVariable);
            }
        }
        else
        {
            //Get item from database
            var varVariable = getvalues(strFieldName, itemID);
            if (varVariable != "")
            {
                strReturn = varVariable;
            }
            else
            {
                strReturn = Convert.ToString(0);
                //e.Editor.Field(streFieldConFin).SetValue(strReturn);
                setvaluesDirect(streFieldConFin, strReturn, e.Id.ToString(), "string");
            }
        }

        return strReturn;
    }

Below is sub routine CalcProductCubicFeet

    private void CalcProductCubicFeet(decimal decHeight, decimal decWidth, decimal decLength, overallEA myargs)
    {
        decimal decProductCubicFeet = 0;
        decimal decCalc = 1728;
        decProductCubicFeet = ((decHeight * decWidth * decLength) / decCalc);



        //myargs.Editor.Field("Item.ProductCubicFeet").SetValue(decProductCubicFeet);
        setvaluesDirect("Item.ProductCubicFeet", decProductCubicFeet.ToString(), myargs.Id.ToString(), "numeric");

    }

Below is setvaluesDirect sub routine

    private string setvaluesDirect(string fieldname, string invalue, string itemid, string mytype = "string")
    {
        var settings = Properties.Settings.Default;

        string rtnval = "";

        using (var db = new Database(settings.DbType, settings.DbConnection))
        {
            string strSQL = "";
            var U = new tools();

            switch (mytype)
            {
                case "string":
                    strSQL = "Update item set " + fieldname + " = '" + invalue + "' where itemid = " + itemid + "; select 'temp';";
                    break;
                case "numeric":
                    strSQL = "Update item set " + fieldname + " = " + invalue + " where itemid = " + itemid + "; select 'temp';";
                    break;
                case "":
                    var d = "null";
                    strSQL = "Update item set " + fieldname + " = " + d + " where itemid = " + itemid + "; select 'temp';";
                    break;

                default:
                    strSQL = "Update item set " + fieldname + " = " + invalue + " where itemid = " + itemid + "; select 'temp';";
                    break;

            }
            rtnval = U.GetScalar(strSQL);
        }
        return rtnval;
    }

Below is GetScalar Function which exectues the SQL update statement

    public string GetScalar(string strSQL)
    {
        var sqlCon = new System.Data.SqlClient.SqlConnection(GetSQLConnection());

        var strReturn = "";

        try
        {
            sqlCon.Open();
            var sqlComm = new System.Data.SqlClient.SqlCommand(strSQL, sqlCon);
            strReturn = sqlComm.ExecuteScalar().ToString();
        }

        catch
        {
            strReturn = "";
        }
        finally
        {
            sqlCon.Close();
        }

        return strReturn;
    }

```

Upon the call of GetScalar I am seeing a record lock in my MS sql database which is not allowing CalcProductCubic Feet to update the Item.ProductCubicFeet field.

Any insight or help would be very appreciated

allan

I'm fairly certain that this will be because Editor is doing its write in a transaction, and you are attempting to use a different SQL connection resource, thus it will be locked out.

Two options:

1. Add .Transaction( false ) to the Editor chain before the Process() method is called to disable the transaction.
2. Reuse the Db instance that Editor is using and its classes - which are fully documented here.

Allan

nfite

.Transaction(false) did work thank you Alan, not 100% sure about option 2 and how to reuse the db instance editor uses....