I want to use an adaptor to fetch a specific record if its exists. If not I want to be able to insert a new one. I would have thought this would be real easy, but I was wrong.

The following code is still a work in progress.

I am using a two field unique constraint.

public string GetNextCaseIdentifier(string CaseType) { if (!databaseOnline) throw new Exception("Database is offline");

        int year = DateTime.Now.Year;


        CaseIdentifierEntity newCaseIdentity = new CaseIdentifierEntity();
        newCaseIdentity.CaseType = CaseType;
        newCaseIdentity.CaseYear = year;
        DataAccessAdapter adaptor = new DataAccessAdapter(databaseConnectionString,true);
        try
        {
            adaptor.FetchEntityUsingUniqueConstraint(newCaseIdentity,
                                                 newCaseIdentity.ConstructFilterForUCCaseTypeCaseYear());
        }
        catch (ORMEntityOutOfSyncException ex)
        {
            MessageBox.Show(ex.Message);
            return ex.Message;
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
            return ex.Message;
        }

        string caseIdentifier = String.Format("{0}{1}{2}",(year % 100).ToString("00"),CaseType,newCaseIdentity.CaseSequence.ToString("00000"));

        return caseIdentifier;
    }

If the record is there it works great. If the FetchEntityUsingUniqueConstraint fails I want to be able to populate the entity and then save it.

In Vs2008 I am getting an unhandled exception in my CaseIdentifierEntity.cs class here

    /// <summary> The CaseSequence property of the Entity CaseIdentifier<br/><br/>
    /// </summary>
    /// <remarks>Mapped on  table field: "CaseIdentifier"."CaseSequence"<br/>
    /// Table field type characteristics (type, precision, scale, length): Int, 10, 0, 0<br/>
    /// Table field behavior characteristics (is nullable, is PK, is identity): false, false, false</remarks>
    public virtual System.Int32 CaseSequence
    {           **get { return (System.Int32)GetValue((int)CaseIdentifierFieldIndex.CaseSequence, true); }**            

{set { SetValue((int)CaseIdentifierFieldIndex.CaseSequence, value); } }

Its not bubbling the exception up so I can trap it.

It says its not being handled ORMEntityOutOfSyncException was unhandled Ok I would like to handle it but I can't.

At the risk of being labelled a cretin, can you please help me (no please don't pass me the gun)

The probloem is that you try to read the newCaseIdentity.CaseSequence in all cases, while you should only try to read it if an entity was fetched from the database.

I suggest your code should look like:

public string GetNextCaseIdentifier(string CaseType)
        {
            if (!databaseOnline)
                throw new Exception("Database is offline");

            int year = DateTime.Now.Year;
            string caseIdentifier = String.Empty;

            CaseIdentifierEntity newCaseIdentity = new CaseIdentifierEntity();
            newCaseIdentity.CaseType = CaseType;
            newCaseIdentity.CaseYear = year;
            DataAccessAdapter adaptor = new DataAccessAdapter(databaseConnectionString,true);
            try
            {
                adaptor.FetchEntityUsingUniqueConstraint(newCaseIdentity,
                                                     newCaseIdentity.ConstructFilterForUCCaseTypeCaseYear());
                if(newCaseIdentity.Fields.State == EntityState.Fetched)
                {
                       caseIdentifier = String.Format("{0}{1}{2}",(year % 100).ToString("00"),CaseType,newCaseIdentity.CaseSequence.ToString("00000"));
                }
            }
            catch (ORMEntityOutOfSyncException ex)
            {
                MessageBox.Show(ex.Message);
                return ex.Message;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return ex.Message;
            }


            return caseIdentifier;
        }

Great that fixed that problem.

If I do get a record back, I want to update casesequence field and save it back to the database.

This should be easy or so I thought.

My code now looks like this (its not pretty but it was a quick change)

     try
        {
            adaptor.FetchEntityUsingUniqueConstraint(newCaseIdentity,
                                                 newCaseIdentity.ConstructFilterForUCCaseTypeCaseYear());
            if (newCaseIdentity.Fields.State == EntityState.Fetched)
            {
                caseIdentifier = String.Format("{0}{1}{2}", (year % 100).ToString("00"), CaseType, newCaseIdentity.CaseSequence.ToString("00000"));
            }
        }
        catch (ORMEntityOutOfSyncException ex)
        {
            MessageBox.Show(ex.Message);
            return ex.Message;
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
            return ex.Message;
        }
        if (caseIdentifier.Length > 0)
        {
            newCaseIdentity.CaseSequence += 1;
            **adaptor.SaveEntity(newCaseIdentity);**
        }

I get an exception saying that

SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException was unhandled Message="An exception was caught during the execution of an action query: Invalid column name 'CaseIdentiferSPKey'.\r\nInvalid column name [quote][color value="FF0000"]CaseIdentiferSPKey[/color][/quote]. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception." Source="SD.LLBLGen.Pro.ORMSupportClasses.NET20" RuntimeBuild="09112008"

I don't have a column called CaseIdentiferSPKey. A long time back I did in the database, but I fixed that. I have regenerated the entities if I do a entire solution whide search I get

Find all "CaseIdentiferSPKey", Subfolders, Keep modified files open, Find Results 1, "Entire Solution" Matching lines: 0 Matching files: 0 Total files searched: 36

The field should be caseIdentiferPkey which exists ok.

Find all "caseIdentiferPkey", Subfolders, Keep modified files open, Find Results 1, "Entire Solution" C:\Projects\WardenII\Trunk\Source\Services\CaseIndentifierDataAccess\DatabaseGeneric\ConstantsEnums.cs(20): ///<summary>CaseIdentiferPkey. </summary> C:\Projects\WardenII\Trunk\Source\Services\CaseIndentifierDataAccess\DatabaseGeneric\ConstantsEnums.cs(21): CaseIdentiferPkey, C:\Projects\WardenII\Trunk\Source\Services\CaseIndentifierDataAccess\DatabaseGeneric\EntityClasses\CaseIdentifierEntity.cs(95): /// <param name="caseIdentiferPkey">PK value for CaseIdentifier which data should be fetched into this CaseIdentifier object</param> C:\Projects\WardenII\Trunk\Source\Services\CaseIndentifierDataAccess\DatabaseGeneric\EntityClasses\CaseIdentifierEntity.cs(97): public CaseIdentifierEntity(System.Int32 caseIdentiferPkey):base("CaseIdentifierEntity") C:\Projects\WardenII\Trunk\Source\Services\CaseIndentifierDataAccess\DatabaseGeneric\EntityClasses\CaseIdentifierEntity.cs(100): this.CaseIdentiferPkey = caseIdentiferPkey; C:\Projects\WardenII\Trunk\Source\Services\CaseIndentifierDataAccess\DatabaseGeneric\EntityClasses\CaseIdentifierEntity.cs(104): /// <param name="caseIdentiferPkey">PK value for CaseIdentifier which data should be fetched into this CaseIdentifier object</param> C:\Projects\WardenII\Trunk\Source\Services\CaseIndentifierDataAccess\DatabaseGeneric\EntityClasses\CaseIdentifierEntity.cs(107): public CaseIdentifierEntity(System.Int32 caseIdentiferPkey, IValidator validator):base("CaseIdentifierEntity") C:\Projects\WardenII\Trunk\Source\Services\CaseIndentifierDataAccess\DatabaseGeneric\EntityClasses\CaseIdentifierEntity.cs(110): this.CaseIdentiferPkey = caseIdentiferPkey; C:\Projects\WardenII\Trunk\Source\Services\CaseIndentifierDataAccess\DatabaseGeneric\EntityClasses\CaseIdentifierEntity.cs(453): _fieldsCustomProperties.Add("CaseIdentiferPkey", fieldHashtable); C:\Projects\WardenII\Trunk\Source\Services\CaseIndentifierDataAccess\DatabaseGeneric\EntityClasses\CaseIdentifierEntity.cs(550): /// <summary> The CaseIdentiferPkey property of the Entity CaseIdentifier<br/><br/> C:\Projects\WardenII\Trunk\Source\Services\CaseIndentifierDataAccess\DatabaseGeneric\EntityClasses\CaseIdentifierEntity.cs(552): /// <remarks>Mapped on table field: "CaseIdentifier"."CaseIdentiferPKey"<br/> C:\Projects\WardenII\Trunk\Source\Services\CaseIndentifierDataAccess\DatabaseGeneric\EntityClasses\CaseIdentifierEntity.cs(555): public virtual System.Int32 CaseIdentiferPkey C:\Projects\WardenII\Trunk\Source\Services\CaseIndentifierDataAccess\DatabaseGeneric\EntityClasses\CaseIdentifierEntity.cs(557): get { return (System.Int32)GetValue((int)CaseIdentifierFieldIndex.CaseIdentiferPkey, true); } C:\Projects\WardenII\Trunk\Source\Services\CaseIndentifierDataAccess\DatabaseGeneric\EntityClasses\CaseIdentifierEntity.cs(55 : set { SetValue((int)CaseIdentifierFieldIndex.CaseIdentiferPkey, value); } C:\Projects\WardenII\Trunk\Source\Services\CaseIndentifierDataAccess\DatabaseGeneric\HelperClasses\FieldCreationClasses.cs(20): /// <summary>Creates a new CaseIdentifierEntity.CaseIdentiferPkey field instance</summary> C:\Projects\WardenII\Trunk\Source\Services\CaseIndentifierDataAccess\DatabaseGeneric\HelperClasses\FieldCreationClasses.cs(21): public static EntityField2 CaseIdentiferPkey C:\Projects\WardenII\Trunk\Source\Services\CaseIndentifierDataAccess\DatabaseGeneric\HelperClasses\FieldCreationClasses.cs(23): get { return (EntityField2)EntityFieldFactory.Create(CaseIdentifierFieldIndex.CaseIdentiferPkey);} C:\Projects\WardenII\Trunk\Source\Services\CaseIndentifierDataAccess\DatabaseGeneric\HelperClasses\FieldInfoProvider.cs(69): base.AddElementFieldInfo("CaseIdentifierEntity", "CaseIdentiferPkey", typeof(System.Int32), true, false, true, false, (int)CaseIdentifierFieldIndex.CaseIdentiferPkey, 0, 0, 10); C:\Projects\WardenII\Trunk\Source\Services\CaseIndentifierDataAccess\DatabaseSpecific\PersistenceInfoProvider.cs(6 : base.AddElementFieldMapping( "CaseIdentifierEntity", "CaseIdentiferPkey", "CaseIdentiferPKey", false, (int)SqlDbType.Int, 0, 0, 10, true, "SCOPE_IDENTITY()", null, typeof(System.Int32), 0 ); Matching lines: 19 Matching files: 5 Total files searched: 36

Any idea of how to track this down?

Sorry please forget this one.

Its a stupid trigger that I failed to fix when I changed the column name a while back.

Duh! I feel like a right cretin now.

UPDATE Global.CaseIdentifier SET CaseSequence = CaseSequence + 1 WHERE CaseIdentiferPKey = 8

Msg 207, Level 16, State 1, Procedure CaseIdentifierTrigger, Line 19 Invalid column name 'CaseIdentiferSPKey'. Msg 207, Level 16, State 1, Procedure CaseIdentifierTrigger, Line 19 Invalid column name 'CaseIdentiferSPKey'.