WriteEdit Record Lock with My MSSQL 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 sql database which is not allowing CalcProductCubic Feet to update the Item.ProductCubicFeet field.

nfite

My apologies its an MS SQL database I edited the above comment to reference