c# - ExecuteNonQuery always inserts Null into Nullable column - Stack Overflow

admin管理员组

文章数量:1356273

I have the following code, that is inserting data into some tables. One of the columns (AbortComment) is a nullable nvarchar(512) column. I debug the code and the insert query has all the values. Upon inserting, it always inserts Null into this database column, although I provide a value.

Please see the DbInsert method:

public string SaveTestResults(string strRecipeName, TestResult TestResultDet, string AbortComment = "")
{
    string strTestName = "";

    // get the id of the recipe
    Decimal dReciID = GetRecipeID(strRecipeName);

    // save header details to database
    try
    {
        // start transaction with database
        m_DBInterface.DBBeginTransaction();

        Decimal dTestID = SaveTestResultsHeader(dReciID, strRecipeName, TestResultDet, ref strTestName, false, AbortComment);
                    
        // Insert into some other tables

        // commit transaction
        m_DBInterface.DBCommitTransaction();
    }
    catch(Exception Exc)
    {
        // delete the links of the saved test if partially saved
        m_DBInterface.DBRollBack();
        throw Exc;
    }

    return strTestName;
}
    
private Decimal SaveTestResultsHeader(Decimal dRecipeID, string strRecipeName,
                                      TestResult TestResultDet, ref string strTestName, bool bTransaction, string AbortComments = "")
{
    // Do some insert.
    ArrayList RetIDArray = m_DBInterface.DBInsert(DBTestDet.Count(), ref DBTestDet, false);
}
    
public ArrayList DBInsert(int nFieldCount, ref Queue[] qResultList, bool bTransaction)
{
    DateTime dtVal;
    String strTableName = "";
    String strFields = "";
    String strQuery = "";
    String strTemp = "";
    strTemp = qResultList[0].Peek().ToString();
    strTableName = strTemp.Substring(0, strTemp.IndexOf('.'));
    
    // Remove field names from the queues.
    for (int FieldIndex = 0; FieldIndex < nFieldCount; ++FieldIndex)
    {
        if (FieldIndex > 0)
            strFields += ",";

        strFields += qResultList[FieldIndex].Dequeue().ToString();
    }

    strFields += ",";
    strFields += "bDeleted"; // make the bDeleted to 0 by default

    // Create an Array for returning Primary Keys
    ArrayList PKArray = new ArrayList();
    int cntRecords = qResultList[0].Count;
    bool bRetry = false;
    
    do
    {
        try
        {
            if (true == bTransaction)
            {
                m_DBConnection.BeginTransaction();
            }
    
            for (int index = 0; index < cntRecords; ++index)
            {
                strQuery = "INSERT INTO ";
                strQuery += strTableName;
                strQuery += " ( ";
                strQuery += strFields;
                strQuery += " ) ";
                strQuery += " VALUES (";

                for (int FieldIndex = 0; FieldIndex < nFieldCount; ++FieldIndex)
                {
                    if (FieldIndex > 0)
                        strQuery += ",";

                    object objValue = qResultList[FieldIndex].Dequeue();

                    if (objValue == null)
                    {
                        strQuery += "null";
                    }
                    else
                    {
                        string strType = objValue.GetType().ToString();

                        if ("System.DateTime" == strType)
                        {
                            strQuery += "'";
                            dtVal = (DateTime)objValue;
                            string strDate = dtVal.ToString("MM/dd/yyyy HH:mm:ss", CultureInfo.InvariantCulture);
                            strQuery += strDate;
                        }
                        else if ("System.Single" == strType)
                        {
                            strQuery += "'";
                            // Modified by TimR to handle floating point values over 10,000,000
                            string strSingle = ((Single)objValue).ToString("0.###", CultureInfo.InvariantCulture);
                            strQuery += strSingle;
                        }
                        else if ("System.String" == strType)
                        {
                            strQuery += "N";
                            strQuery += "'";
                            strQuery += objValue.ToString();
                        }
                        else
                        {
                            strQuery += "'";
                            strQuery += objValue.ToString();
                        }

                        strQuery += "'";
                    }
                }

                strQuery += ",";
                strQuery += "0";  //bDeleted = 0;
                strQuery += ")";

                m_DBConnection.ExecuteNonQuery(strQuery);

                PKArray.Add(Convert.ToDecimal(m_DBConnection.GetValue("SELECT @@IDENTITY")));
            }
    
            if (true == bTransaction)
            {
                m_DBConnection.CommitTransaction();
            }
    
            bRetry = false;
        }
        catch (SqlDatabaseException e)
        {
            RetryDatabaseConnection(ref bRetry);
    
            if (bRetry == false)
                throw e;
        }
    } while (bRetry);
    
    return PKArray;
}

When debugging, m_DBConnection.ExecuteNonQuery(strQuery);, the strQuery is correctly populating the query. I get no exceptions.

Here is the query, please see the tblTestResults.AbortComment it has a value of 'Stopped'. But it always inserts Null into the column.

INSERT INTO tblTestResults ( tblTestResults.idRecipe, tblTestResults.bResult, tblTestResults.strSensorSerialNum, tblTestResults.nTestStatus, tblTestResults.strSensorModel, tblTestResults.strUserName, tblTestResults.strTestName, tblTestResults.dtTestResult, tblTestResults.nCheckSum, tblTestResults.fFlowRate, tblTestResults.fConcentrationLimit, tblTestResults.fViewVolume, tblTestResults.bDiscardFirstRun, tblTestResults.AbortComment, bDeleted)  
VALUES ('78', '0', N'Simulation', '65536', N'Simulation', N'test USER', N'ChP_2015_<25mL_Test_2025-03-28_15:13:23', '03/28/2025 15:13:23', '-19168396', '60', '18000', '100', '1', N'Stopped', 0)

This is the Insert:

本文标签： cExecuteNonQuery always inserts Null into Nullable columnStack Overflow