Dailycode.info

Short solution for short problems

Unable to update the EntitySet '' because ...

Got this error using the entity framework and self-tranking entities:

Unable to update the EntitySet 'MyEntity' because it has a DefiningQuery and no <InsertFunction> element exists in the <ModificationFunctionMapping> element to support the current operation.

Couldn't find the problem at first, even google didn't really gave me any thoughts. Untill I noticed that I forgot to set a primary key in the database table. Problem solved. Or so I thought.

Apparently Visual studio created a key itself on the edmx model, off course the wrong one. After a update from database, the model added the correct key, but didn't removed the key which it had created itself. So I removed it myself, then did an updated the model again from the database. 

 


A good way to handle insert, update and delete using self-tracking entities and the entity framework.

The main thing I want to point out is that after an error occurred (for example the data is not correct or in case of delete there record is still used in a referenced table) you need to refresh your entity or in case of insert remove it from the context. Here’s some example code for update, insert and delete.

public GP_CMDB_Software GetSoftwareByID(string ID)

{

GP_CMDB_Software Software = ents.GP_CMDB_Software.Where(h => h.ID == ID).FirstOrDefault();

return Software;

}

public void SaveSoftware(GP_CMDB_Software software)

{

try

{

software.Modified = DateTime.Now;

software.AcceptChanges();

ents.SaveChanges();

}

catch (Exception)

{

ents.Refresh(RefreshMode.StoreWins, software);

throw new ArgumentException("Save failed, please check your data!");

}

}

public void InsertSoftware(GP_CMDB_Software software)

{

try

{

software.Created = DateTime.Now;

ents.GP_CMDB_Software.AddObject(software);

ents.SaveChanges();

}

catch (Exception)

{

ents.GP_CMDB_Software.DeleteObject(software);

throw new ArgumentException("Insert failed, please check your data!");

}

}

public void RemoveSoftware(string key)

{

GP_CMDB_Software software = GetSoftwareByID(key);

try

{

ents.GP_CMDB_Software.DeleteObject(software);

ents.SaveChanges();

}

catch (Exception)

{

ents.Refresh(RefreshMode.StoreWins, software);

throw new ArgumentException("Delete failed, please check if the record is not in use!");

}

}

 


Audit trail (History) using the entity framework and self-tracking entities (Part1)

This post will describe an automated way to audit all database changes of your entities. It took some search and programming to get a satisfying result. Reading several posts, and combining them. Still I needed to invent some tricks to get all information. On the code project I found the most information here! You can find all code needed in this post.

Improved insert auditing implemented in part 2: here 


The result audit table looks like this:


 

The implementation starts at the heart of the  entity frame work. The context. There you can catch the event:  SavingChanges. In my case this code is written in the process layer.

  

private MyEntities.Entities ents;
…
 

ents.SavingChanges += new EventHandler(ents_SavingChanges);

  

This is triggered each time a change is persisted to the database. So exactly what we need. Next we will get all changed entities and create a new record in the audit table. We will trace if its an update, insert or delete. We will log the user, time, key fields and values, original values and new values and last the columns that contained changes.

We will need to declare an enum and 2 variables:

  

privatestring UserName = "notloggedin";
  
List<GP_CMDB_DBAudit> auditTrailList = newList<GP_CMDB_DBAudit>();
  
public enum AuditActions
{
    I,
    U,
    D
}

 

The username should contain the logged in user, you can also get it somewhere else. The list of audit entities is just used over several functions and for easy put as private variable. Then the enum will be used to tell the database what happened, Insert, Update or Delete.

Now let’s look at the SavingChanges function:

 

 

void Ents_SavingChanges(object sender, EventArgs e)
{
    try
    {
        StaticEnts.DetectChanges();
        IEnumerable<ObjectStateEntry> changes = StaticEnts.ObjectStateManager.GetObjectStateEntries(EntityState.Deleted | EntityState.Modified);
        foreach (ObjectStateEntry stateEntryEntity in changes)
        {
            if (!stateEntryEntity.IsRelationship && stateEntryEntity.Entity != null && !(stateEntryEntity.Entity is GP_PROJ_DBAudit))
            {
                //is a normal entry, not a relationship
                GP_PROJ_DBAudit audit = this.AuditTrailFactory(stateEntryEntity, UserName);
                auditTrailList.Add(audit);
            }
        }

        if (auditTrailList.Count > 0)
        {
            foreach (var audit in auditTrailList)
            {//add all audits 
                StaticEnts.GP_PROJ_DBAudit.AddObject(audit);
            }
        }
        auditTrailList = new List<GP_PROJ_DBAudit>();
    }
    catch (Exception er)
    {
        //Audit falied, need to log it.
    }
}

 

Very important and took me some time to find, start with the DetectChanges method. If this is not called, it could happen that insert or delete is not detected.

Next we will get the entities that are changed from the objectstatemanager.  We start to loop over them and create an audit record using the AuditTrailFactory.

So that’s the next function we will take a look at:

 

 

private GP_PROJ_DBAudit AuditTrailFactory(ObjectStateEntry entry, string UserName)
{
    GP_PROJ_DBAudit audit = new GP_PROJ_DBAudit();
    audit.AuditId = Guid.NewGuid().ToString();
    audit.RevisionStamp = DateTime.Now;
    audit.TableName = entry.EntitySet.Name;
    audit.UserName = GetUserFromEntry(entry);

    if (entry.State == EntityState.Deleted)
    {//entry in deleted
        AddKeyFields(entry, audit);
        audit.OldData = GetEntryValueInString(entry, true, AuditActions.D);
        audit.Actions = AuditActions.D.ToString();
    }
    else
    {//entry is modified
        AddKeyFields(entry, audit);
        audit.OldData = GetEntryValueInString(entry, true, AuditActions.U);
        audit.NewData = GetEntryValueInString(entry, false, AuditActions.U);
        audit.Actions = AuditActions.U.ToString();

        IEnumerable<string> modifiedProperties = entry.GetModifiedProperties();
        //assing collection of mismatched Columns name as serialized string 
        audit.ChangedColumns = XMLSerializationHelper.XmlSerialize(modifiedProperties.ToArray());
    }

    return audit;
}

 

The AuditTrailFactory will instantiate an GP_CMDB_DBAudit entity.  Depending on the action we need to get different information.  First we fill in the properties we have like a GUID, a date time, the table name and the user.  Next we add the key fields and their values. This is done the AddKeyFields method:

  

private void AddKeyFields(ObjectStateEntry entry, GP_PROJ_DBAudit audit)
{
    var key = entry.EntityKey;
    var keyValues = entry.EntityKey.EntityKeyValues;
    StringBuilder sb = new StringBuilder(@"<?xml version=""1.0"" encoding=""utf-16""?><keyvalues>");

    foreach (EntityKeyMember member in keyValues)
    {
        if (member.Value.ToString() == "0" || member.Value.ToString() == "")
        {
            sb.AppendFormat("<{0}>new</{0}> ", member.Key);
        }
        sb.AppendFormat("<{0}>{1}</{0}> ", member.Key, member.Value);
    }
    sb.Append("</keyvalues>");
    audit.KeyValue = sb.ToString();
}

 

 

 

 

 

This is a very simple function that collect the key field and value and creates a xml structured record to save in the audit entity. Is structured like <ID>321546</ID>

Then we get the old data and the new data from the entry. In case of an insert,  we will insert all fields as xml in the newdata field, the old data will be empty. Also after I collect the data, I perform a accept changes because only then the primairy key fields are filled in, then I call the addkeyfields function. In case of delete,  we fill in all fields into the olddata field of the audit record. In case of an update, we only log the changed fields. The original values of these fields are logged in the olddata, the new values are logged in the newdata.

Here’s an example of the xml from the old data in case of an update:

 

<Remarks>dit is een test</Remarks>
<Modified>19/03/2012 11:26:55</Modified> 

Then the newdata will ook like this:

<Remarks>dit is een test van mark</Remarks>
<Modified>20/03/2012 16:56:53</Modified>

 

Now, let’s take a look at the function GetentryValueInString: (I added a function: FormatSetterValueToXML because some characters are illegal in xml, I'll explain more after this snippit)

 

 

private string GetEntryValueInString(ObjectStateEntry entry, bool isOrginal, AuditActions action)
{
    try
    {
        if (entry.Entity is IObjectWithChangeTracker)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(@"<?xml version=""1.0"" encoding=""utf-16""?><data>");
            object target = CloneEntity((IObjectWithChangeTracker)entry.Entity);


            if (action == AuditActions.I || action == AuditActions.D)
            {
                // Iterate over the members (i.e. properties (including complex properties), references, collections) of the entity type
                foreach (EdmMember member in (StaticEnts.ObjectStateManager.GetObjectStateEntry(entry.Entity).EntitySet.ElementType.Members).Where(t => t.TypeUsage.EdmType is PrimitiveType))
                {
                    object setterValue = null;
                    string propName = member.Name;

                    if (action == AuditActions.I)
                    {
                        setterValue = entry.CurrentValues[propName];
                    }
                    else
                    {
                        setterValue = entry.OriginalValues[propName];
                    }
                            
                    if (setterValue != DBNull.Value)
                    {
                        //PropertyInfo propInfo = target.GetType().GetProperty(propName);
                        //propInfo.SetValue(target, setterValue, null);
                        FormatSetterValueToXML(ref setterValue);
                        sb.AppendFormat("<{0}>{1}</{0}> ", propName, setterValue);
                    }
                }
            }
            else
            {

                foreach (string propName in entry.GetModifiedProperties())
                {
                    object setterValue = null;
                    if (isOrginal)
                    {
                        //Get orginal value 
                        setterValue = entry.OriginalValues[propName];
                    }
                    else
                    {
                        //Get orginal value 
                        setterValue = entry.CurrentValues[propName];
                    }
                    //Find property to update 
                    //PropertyInfo propInfo = target.GetType().GetProperty(propName);
                    //update property with orgibal value 
                    if (setterValue == DBNull.Value)
                    {//
                        setterValue = null;
                    }
                    //propInfo.SetValue(target, setterValue, null);
                    FormatSetterValueToXML(ref setterValue);
                    sb.AppendFormat("<{0}>{1}</{0}> ", propName, setterValue);
                }//end foreach

            }
            sb.Append("</data>");
            return sb.ToString();
        }
        return null;
    }
    catch (Exception er)
    {
        //Handle audit exception
        string test = er.Message;
        return null;
    }
}

private void FormatSetterValueToXML(ref object setterValue)
{
    if (setterValue.GetType() == typeof(string))
    {
        setterValue = setterValue.ToString().Replace("&", "_");
        setterValue = setterValue.ToString().Replace("-", "_");
        setterValue = setterValue.ToString().Replace('"', '_');
        setterValue = setterValue.ToString().Replace("'", "_");
    }
}

 

  

As you can see I created the function FormatSetterValueToXML. Because nvarchars can hold any character, XML doesn't. All the audit data is stored in xml columns, so we will need to format the following signs if they appear in the string:& - " ' You can choose yourself in what king of string or character you want to replace it to. For now I just replace them by _

 

After the clone you can see the function is divided into 2 parts. Insert and Delete will follow a different procedure than the update.

The insert and delete will simply loop over all (simple) properties and transfer them to a xml structured string. To loop over the properties of the ObjectStateEntity we call the GetObjectStateEntry method and restrict the properties return to primitive, because I do not want to loop over the linked entities of this object and only the properties.

 

.Where(t => t.TypeUsage.EdmType is PrimitiveType)

 

 

For the modified entities we simply loop over the modified properties that you can get using the GetModifiedProperties method on the entry.

I must say that the first test are really positive. The auditing implementation is really simple and the result is very satisfying. It will audit all changes on the database and helps you to track when, what and who.  That’s exactly what the auditors like ;-)


 

DBAuditLarge.png (214.34 kb)

Here you have the source code that could help you to implemnt it. It also includes the sql for the audit table.

ENT_DBAudit.zip (23.75 kb)


Free Copy directries program and free directory structure generator program

Some time ago, on request of a friend, I created a directory structure generator. This tool will generate the structure of a directory with or without subdirectories. You can specify what information you want to see in your overview.

 

Then the second program is a directory copy program, where you can set a date. Then this program will only copy files that where modified after this date. This can be handy when youare archiving.



 

You can download the executables here: 

CopyFilesDirStruct.zip (102.88 kb)


Sometimes RV_PRICE_PRINT_HEAD return no values

We are creating Adobe forms to eventually use with PI for E-invoicing. The adobe form works fine if we use it as a printer output type. But when PI calls it with the special function, the RV_PRICE_PRINT_HEAD return nu values. Very strange and very hard to test. At the end we came across this post that help us solve the problem: http://scn.sap.com/thread/1450764

 

So before you call the function RV_PRICE_PRINT_HEAD, call another function to correctly refresh the header:

  CALL FUNCTION 'RV_PRICE_PRINT_REFRESH'
    
TABLES
      tkomv 
tkomv.


  
CALL FUNCTION 'RV_PRICE_PRINT_HEAD'
    
EXPORTING
      comm_head_i 
komk
      
language    nast-spras
    
IMPORTING
      comm_head_e 
komk
      comm_mwskz  
print_mwskz
    
TABLES
      tkomv       
tkomv

       tkomvd      hkomvd. 


Devexpress master-detail ASPxGridView: selected records in the detail grid.

 

I had following scenario:

An aspx page with some devexpress grids. The goal was to use a master detail grid, where I could select a record in the master grid, then select a record in the detail grid. Then I press an install button where I get the selected records and save them to the database.

Showing the grids was no problem, getting the selected master grid line was also very easy. But then the challenge came, getting the selected record in the detail grid. I couldn’t find it working in server side code.  There were a lot of examples on how to fill up a detail grid or how to collapse or expand records in a detail grid, but no real examples on how to get the selected records in a detail grid.

At the end of my frustration, I decided to use 3 separate grids and let the 3rd function as a detail grid. Now it’s working fine. The devexpress way of showing a detail grid inside a master grid, was a very nice feature, but not working for this case. So the way I solved it now is to handle the row-focused event in the master grid, check some data and show the detail grid if necessary.

Here you have the screen example of how I solved it now:


 

If a record is selected that has detail records, then I show the detail grid. In this grid, its possible to select a record and perform actions, like uninstall it.


To get the focused row working on server side, you need to configure some things on the grid. The  most important settings are:

	    ClientIDMode="AutoID" OnFocusedRowChanged="grdMaster_FocusedRowChanged"
            SettingsBehavior-AllowFocusedRow="true" SettingsBehavior-AllowMultiSelection="false"
            SettingsBehavior-ProcessFocusedRowChangedOnServer="true" EnableRowsCache="false" EnableCallBacks="false" SettingsBehavior-ProcessSelectionChangedOnServer="true"

Then this is the code to use if you want to get the selected records from the grids: (The first 2 lines get the selected record from the master grid, the second 2 lines get the selected record from the detail grid (3rd grid))

 

string[] sfield = { "ID" };

string softID = Convert.ToString(grdSoftwareOverview.GetRowValues(grdSoftwareOverview.FocusedRowIndex, sfield));


string[] sfieldLic = { "SoftwareLicenseID" };

string selectedItemLic = Convert.ToString(grdLicenseOverview.GetRowValues(grdLicenseOverview.FocusedRowIndex, sfieldLic));

 


Adoba Forms in SAP: use javascript to manipulate text in textfield

I was looking for the javascript to replace any occurences of a text in a string.

If you put this code on the initialize event of a textfield, you can do it!

 

var b64 = this.rawValue;

b64 = b64.replace(/mderaeve.com/gi,'markderaeve.be');

this.rawValue = b64;

The /gi makes sure to do a global case-insensative replace. Be aware that you can lose formatting, such as enters. So for long texts this could not be the best solution.


How to read an email adress (mail box) from an Exchange 2007 or 2010 server and download email attachments?

For this I created a very simple WPF program (Source code attached at the bottom of this post) that will read a certain email address and process some items that have a certain subject. It will then download all the attachments of the items to the local disk. In the beginning I had some trouble finding the attachments of the items, but this was because I didn’t force a Load on the items. This will load all attachment information. (Lazy Loading I guess)

ExchangeService service = new ExchangeService(ExchangeVersion.Exchange2010_SP1);

 

    //You can pass credentials of the creadetials running the program have no access to the mail box

    //service.Credentials = new NetworkCredential( "{Active Directory ID}", "{Password}", "{Domain Name}" );

 

    //Provide the mail box to read

    service.AutodiscoverUrl("mark@dailycode.info");

 

    //Create a filter for the mails returned, in this case only the mails with SendFilesID= in the subject will be returned

    SearchFilter searchFilter = new SearchFilter.ContainsSubstring(ItemSchema.Subject, "SendFilesID=");

 

    //Return the first 10 items

    FindItemsResults<Item> findResults = service.FindItems(WellKnownFolderName.Inbox,searchFilter,new ItemView(10));

           

    try

    {

        //Loop over the items

        foreach (Item item in findResults.Items)

        {

            //Show result

            txtResult.Text += item.Subject +Environment.NewLine;

            //This load is needed to fill all the properties of the item, if you do not use this, attachment information will be empty

            item.Load();

            //Check if the item has attachments

            if (item.HasAttachments)

            {

                //Loop over the attachments

                foreach (Attachment attachment in item.Attachments)

                {

                    //Check if the attachemnts is a file

                    if (attachment is FileAttachment)

                    {

                        if (!Directory.Exists(("C:\\TEMP\\Attachments")))

                        {

                            Directory.CreateDirectory("C:\\TEMP\\Attachments");

                        }

                        FileAttachment fileAttachment = attachment as FileAttachment;

                        // Load the file attachment into memory

                        fileAttachment.Load();

                        Console.WriteLine("Attachment name: " + fileAttachment.Name);

                        // Stream attachment contents into a file.

                        Stream theStream = File.Create("C:\\TEMP\\Attachments\\" + fileAttachment.Name); //new FileStream("D:\\Downloads\\Attachments\\" + fileAttachment.Name, FileMode.OpenOrCreate, FileAccess.ReadWrite);

                        fileAttachment.Load(theStream);

                        theStream.Close();

                        theStream.Dispose();

                    }

                    else // Attachment is an item attachment.

                    {

                        // Load attachment into memory and write out the subject.

                        ItemAttachment itemAttachment = attachment as ItemAttachment;

                        itemAttachment.Load();

                        Console.WriteLine("Subject: " + itemAttachment.Item.Subject);

                    }

                }

            }

        }

    }

    catch (Exception er)

    {

        MessageBox.Show(er.Message);

    }

}

 

 

TestReadEmail.zip (17.67 kb)