Secure connection strings in app.config for a desktop application

The goal is to get away as little as possible of the standard.

In this case the standard is : Encrypting Configuration Information Using Protected Configuration

In short: use a ProtectedConfigurationProvider. The .Net Framework provides two of them:

• DpapiProtectedConfigurationProvider: uses the Windows Data Protection API (DPAPI) to encrypt and decrypt data.
• RsaProtectedConfigurationProvider: uses the RSA encryption algorithm to encrypt and decrypt data.

The main inconvenience of those providers comes from the fact that they have been designed for web applications: that are applications in which the securisable is the application server. That is one web.config in one machine

With a desktop application you have a plurality of app.config

Said providers are machine based, i.e. there is one configuration file per machine. In other words, the configuration file of one machine can't be used by another because of different encryption keys.

Fortunately the .Net Framework provides an abstract class that can be overridden to built a custom provider.

Here is a sample

The DLL:

using System;
using System.Configuration;
using System.Xml;

namespace MBT.ProtectedConfigurationProviders {

    public class WeakProtectedConfigurationProvider : ProtectedConfigurationProvider {        
        public override void Initialize(string name, 
            System.Collections.Specialized.NameValueCollection config) {
            
            base.Initialize(name, config);
        }

        public override System.Xml.XmlNode Decrypt(System.Xml.XmlNode encryptedNode) {
            string decryptedData =
                DecryptString(encryptedNode.InnerText);

            XmlDocument xmlDoc = new XmlDocument();
            xmlDoc.PreserveWhitespace = true;
            xmlDoc.LoadXml(decryptedData);

            return xmlDoc.DocumentElement;
        }

        public override System.Xml.XmlNode Encrypt(System.Xml.XmlNode node) {
            string encryptedData = EncryptString(node.OuterXml);

            XmlDocument xmlDoc = new XmlDocument();
            xmlDoc.PreserveWhitespace = true;
            xmlDoc.LoadXml("" +
                encryptedData + "");

            return xmlDoc.DocumentElement;
        }

        public static String EncryptString(String st) {
            return Convert.ToBase64String(WeakProtectedConfigurationProvider.GetBytes(st));
        }

        public static String DecryptString(String st) {
            return WeakProtectedConfigurationProvider.GetString(Convert.FromBase64String(st));
        }

        public static byte[] GetBytes(string str) {
            byte[] bytes = new byte[str.Length * sizeof(char)];
            System.Buffer.BlockCopy(str.ToCharArray(), 0, bytes, 0, bytes.Length);
            return bytes;
        }

        public static string GetString(byte[] bytes) {
            char[] chars = new char[bytes.Length / sizeof(char)];
            System.Buffer.BlockCopy(bytes, 0, chars, 0, bytes.Length);
            return new string(chars);
        }
    }

}

Here the WEAK word is deliberately chosen. Base 64 encoding is not at all a cypher algorithm.

Then the Program:

using System;
using System.Configuration;

namespace bas {
    class Program {
        static void Main(string[] args) {
            //String cs = @"";
            //Console.WriteLine(Convert.ToBase64String(WeakProtectedConfigurationProvider.GetBytes(cs)));
            Console.WriteLine(ConfigurationManager.ConnectionStrings["SomeName"].ConnectionString);
        }
    }
}

And finally the app.config

<?xml version="1.0" encoding="utf-8" ?>
<configuration >
  <!-- xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0"-->
   
  <configProtectedData defaultProvider="WeakProtectedConfigurationProvider">
    <providers>
      <clear />
      <add name ="WeakProtectedConfigurationProvider" type="MBT.ProtectedConfigurationProviders.WeakProtectedConfigurationProvider, WeakProtectedConfigurationProvider"/>
    </providers>
  </configProtectedData>

  <connectionStrings configProtectionProvider="WeakProtectedConfigurationProvider">
    <EncryptedData>
      <CipherData>
        <CipherValue>PABjAG8AbgBuAGUAYwB0AGkAbwBuAFMAdAByAGkAbgBnAHMAPgA8AGEAZABkACAAbgBhAG0AZQA9ACIAUwBvAG0AZQBOAGEAbQBlACIAIABjAG8AbgBuAGUAYwB0AGkAbwBuAFMAdAByAGkAbgBnAD0AIgBEAGEAdABhACAAUwBvAHUAcgBjAGUAPQBTAG8AbQBlAFMAZQByAHYAZQByADsASQBuAGkAdABpAGEAbAAgAEMAYQB0AGEAbABvAGcAPQBTAG8AbQBlAEQAYQB0AGEAQgBhAHMAZQA7AEkAbgB0AGUAZwByAGEAdABlAGQAIABTAGUAYwB1AHIAaQB0AHkAPQBUAHIAdQBlADsAQwBvAG4AbgBlAGMAdAAgAFQAaQBtAGUAbwB1AHQAPQAxADgAMAA7AE0AdQBsAHQAaQBwAGwAZQBBAGMAdABpAHYAZQBSAGUAcwB1AGwAdABTAGUAdABzAD0AVAByAHUAZQAiACAAcAByAG8AdgBpAGQAZQByAE4AYQBtAGUAPQAiAFMAeQBzAHQAZQBtAC4ARABhAHQAYQAuAFMAcQBsAEMAbABpAGUAbgB0ACIAIAAvAD4APAAvAGMAbwBuAG4AZQBjAHQAaQBvAG4AUwB0AHIAaQBuAGcAcwA+AA==</CipherValue>
      </CipherData>
    </EncryptedData>
  </connectionStrings>
  
</configuration>

At least two points are significant here. First:

<add name ="WeakProtectedConfigurationProvider"
    type="MBT.ProtectedConfigurationProviders.WeakProtectedConfigurationProvider,  
          WeakProtectedConfigurationProvider"/>

Here to avoid a GAC registration, on use the syntax "type, assembly"

Second:

<configuration xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0">

One may want to use a namespace in configuration tag of app.config to avoid Visual Studio complaint. But, in my case this also leads to side effect: "side by side configuration error" which prevents application from starting. So, for now, I do without, and it runs well.

Thank you to StackOverflow for the StringToBytesToString.

Managing default values for entities in EF II: the just on time way

You probably still know how to create an entity with default values. This is the 'from birth' way.
Well, there is another way to set default values: a 'just on time way'.
The principle is to override the SaveChanges method to explore entities in the tracker to find the one having unset values, just like in the 'from birth way'. Here it is how it can be done.

        //the default value for datetime properties
        public DateTime DefaultDate { get; set; }

        public override int SaveChanges() {
            Boolean defaultSet = false;

            //seeking only pertinent entities
            foreach (DbEntityEntry dbee in ChangeTracker.Entries().
                     Where(x => x.State != EntityState.Deleted && x.State != EntityState.Detached ) ) {
                defaultSet = false;
                Object o = dbee.Entity;
                Type t = o.GetType();
                foreach ( MemberInfo m in t.GetProperties() ) {
                    PropertyInfo p = t.GetProperty(m.Name);
                    switch (Type.GetTypeCode(p.PropertyType)) {
                        case TypeCode.String:
                            if (p.GetValue(o, null) == null) {
                                p.SetValue(o, String.Empty, null);
                                defaultSet = true;
                            }
                            break;
                        case TypeCode.DateTime:
                            if ((DateTime)p.GetValue(o, null) == DateTime.MinValue) {
                                p.SetValue(o, DefaultDate, null);
                                defaultSet = true;
                            }
                            break;
                    }
                    
                    //update the state if needed
                    if ( defaultSet ) {
                        switch ( dbee.State ) {
                            case EntityState.Unchanged :
                                dbee.State = EntityState.Modified;
                                break;
                        }
                    }
                }
            }

            return base.SaveChanges();
        }

This could be used to implement business rules, such as LastModificationDate.
Before EF6, you may have to look for the tracker in the underlying ObjectContext
A full piece of code:

using System;
using System.Linq;
using System.Data.Entity;
using System.Collections.Generic;
using System.Data.Entity.ModelConfiguration;
using System.Reflection;
using System.Data.Entity.Infrastructure;

namespace testef {
    public class Order {
        public Int32 Id { get; set; }
        public String Title { get; set; }

        public virtual ICollection Details { get; set; }

        public DateTime OrderDate { get; set; }

        public virtual OrderType Type { get; set; }
    }

    public class OrderType {
        public String Code { get; set; }
        public String Description { get; set; }
    }

    public class OrderDetail {
        public virtual Order Order { get; set; }
        public Int32 Id { get; set; }
        public String D { get; set; }
        public Boolean IsActive { get; set; }
    }

    public class OrderDetailConfiguration : EntityTypeConfiguration {
        public OrderDetailConfiguration()
            : base() {
            HasRequired(d => d.Order).WithMany(o => o.Details);
        }
    }

    public class OrderConfiguration : EntityTypeConfiguration {
        public OrderConfiguration()
            : base() {
            HasRequired(d => d.Type).WithMany();
        }
    }

    public class OrderTypeConfiguration : EntityTypeConfiguration {
        public OrderTypeConfiguration()
            : base() {
            Property(x => x.Code).HasColumnType("varchar").HasMaxLength(10);
            HasKey(x => x.Code);
        }
    }

    public class TestEFContext : DbContext {
        public DbSet Orders { get; set; }
        public DbSet Details { get; set; }

        public TestEFContext(String cs)
            : base(cs) {
            Database.SetInitializer(new DropCreateDatabaseAlways());
            //Database.SetInitializer(null);
            //Database.SetInitializer(new CreateDatabaseIfNotExists());
            //Database.SetInitializer(new CustomDataBaseInitializer());

        }

        protected override void OnModelCreating(DbModelBuilder modelBuilder) {
        }

        public DateTime DefaultDate { get; set; }

        public override int SaveChanges() {

            Boolean defaultSet = false;
            foreach (DbEntityEntry dbee in ChangeTracker.Entries().
                     Where(x => x.State != EntityState.Deleted && x.State != EntityState.Detached ) ) {
                defaultSet = false;
                Object o = dbee.Entity;
                Type t = o.GetType();
                foreach ( MemberInfo m in t.GetProperties() ) {
                    PropertyInfo p = t.GetProperty(m.Name);
                    switch (Type.GetTypeCode(p.PropertyType)) {
                        case TypeCode.String:
                            if (p.GetValue(o, null) == null) {
                                p.SetValue(o, String.Empty, null);
                                defaultSet = true;
                            }
                            break;
                        case TypeCode.DateTime:
                            if ((DateTime)p.GetValue(o, null) == DateTime.MinValue) {
                                p.SetValue(o, DefaultDate, null);
                                defaultSet = true;
                            }
                            break;
                    }
                    if ( defaultSet ) {
                        switch ( dbee.State ) {
                            case EntityState.Unchanged :
                                dbee.State = EntityState.Modified;
                                break;
                        }
                    }
                }
            }

            return base.SaveChanges();
        }
    }

    public class CustomDataBaseInitializer : CreateDatabaseIfNotExists {
        public CustomDataBaseInitializer()
            : base() {
        }
    }
        
    class Program {
        static void Main(string[] args) {
            String cs = @"Data Source=ALIASTVALK;Initial Catalog=TestEF;Integrated Security=True; MultipleActiveResultSets=True";
            using (TestEFContext ctx = new TestEFContext(cs)) {
                ctx.DefaultDate = new DateTime(2000, 1, 1);

                OrderType t = new OrderType { 
                    Code = "T1",
                    Description = "type One"
                };
                ctx.Orders.Add(new Order { 
                    Title = "first order",
                    Type = t
                });

                ctx.SaveChanges();
            }

            using (TestEFContext ctx = new TestEFContext(cs)) {
                Order o = ctx.Orders.First();
                Console.WriteLine(o.OrderDate);
            }
        }
    }
}

Managing default values for entities in EF: the from birth way

I recently face the problem of setting default value for entities when using entity framework.

After some walk around, here is a part of my actual solution.

This is not really the default values of the properties, but the default values of the types of the properties.

Please consider the following class:

using System;
using System.Linq;
using System.Reflection;


namespace AppXYZ.Entities {
    public static class EntityTools {
        public static readonly DateTime dtDef = new DateTime(1900, 1, 1, 0, 0, 0);

        public static void SetDefaults (object o) {
            Type T = o.GetType();
            foreach ( MemberInfo m in T.GetProperties() ) {
                PropertyInfo P = T.GetProperty(m.Name);
                switch ( Type.GetTypeCode(P.PropertyType) ) {
                    case TypeCode.String :
                        if ( P.GetValue(o, null) == null ) 
                            P.SetValue(o, String.Empty, null); 
                        break;
                    case TypeCode.DateTime :
                        if ( (DateTime)P.GetValue(o, null) == DateTime.MinValue )
                            P.SetValue(o, EntityTools.dtDef, null); 
                        break;
                }
            }
        }

        //T must have a constructor with 0 argument
        public static T GetNewEntity<T> () {
            T e;
            try {
                e = Activator.CreateInstance<T>();
            } catch {
                e = default(T);
            }
            SetDefaults(e);


            return e;
        }
    }
}

Since, I discover at least another way: the just on time way

Entity Framework: Code-First, Foreign Key One-To-Many

The object of this article is to (try to) illustrate two ways for configuring a one to many relation by using entity framework 4.1. For the need of the sample I use a very small and anecdotic part of the data model of Cegid Business Applications. That is I use code first on an existing database !!! :)

To help understanding differences, I comment code from one version to the other.

Of course you will need a connecion string:

Please note:

• the activation of MARS

First: by using the Data Annotations

namespace ef {
    class Program {
        static void Main (string[] args) {
             CegidContext cc = new CegidContext();
            foreach ( gThirdParty gtp in (from t in cc.gThirdParties where t.GBusinesses.Count() > 0 select t) ) {
                 Console.WriteLine("{0,-17} : {1}", gtp.T_TIERS, gtp.GBusinesses.Count());
            }
        }
    }

    [Table("AFFAIRE")]
    public class gBusiness {
        [Key]
         public string AFF_AFFAIRE {get; set;}
         public string AFF_LIBELLE { get; set; }
         public string AFF_TIERS { get; set; }
 
        [ForeignKey("AFF_TIERS")]
         public virtual gThirdParty GThirdParty { get; set; }
    }

    [Table("TIERS")]
    public class gThirdParty {
         public gThirdParty () { 
            GBusinesses = new List<gBusiness>();
        }

        [Key]
         public string T_TIERS { get; set; }
         public string T_LIBELLE { get; set; }
         public virtual ICollection<gBusiness> GBusinesses { get; set; }
    }


    /*public class gBusinessConfiguration
        : EntityTypeConfiguration<gBusiness> {
        public gBusinessConfiguration ()
            : base() {
            HasKey(p => p.AFF_AFFAIRE);
            HasRequired(p => p.GThirdParty).WithMany(t => t.GBusinesses).Map(x => x.MapKey("AFF_TIERS")); 
        }
    }

    public class gThirdPartyConfiguration
        : EntityTypeConfiguration<gThirdParty> {
        public gThirdPartyConfiguration ()
            : base() {
            HasKey(p => p.T_TIERS);
        }
    }*/    
    
    public class CegidContext : DbContext {
        public DbSet<gBusiness> gBussinesses { get; set; }
        public DbSet<gThirdParty> gThirdParties { get; set; }

        /*protected override void OnModelCreating (DbModelBuilder modelBuilder) {
            modelBuilder.Configurations.Add(new gBusinessConfiguration());
            modelBuilder.Configurations.Add(new gThirdPartyConfiguration());
            base.OnModelCreating(modelBuilder);
        }*/
    }
}

Second: by using the fluent API

namespace ef { 
    class Program { 
        static void Main (string[] args) {
            CegidContext cc = new CegidContext();
            foreach ( gThirdParty gtp in (from t in cc.gThirdParties where t.GBusinesses.Count() > 0 select t) ) {
                 Console.WriteLine("{0,-17} : {1}", gtp.T_TIERS, gtp.GBusinesses.Count());
            }
        }
    }

    [Table("AFFAIRE")]
    public class gBusiness { 
        //[Key] 
         public string AFF_AFFAIRE {get; set;} 
         public string AFF_LIBELLE { get; set; } 
         //public string AFF_TIERS { get; set; } 
              
        //[ForeignKey("AFF_TIERS")] 
         public virtual gThirdParty GThirdParty { get; set; } 
    }

    [Table("TIERS")] 
     public class gThirdParty { 
         public gThirdParty () { 
            GBusinesses = new List<gBusiness>(); 
        }

        //[Key] 
         public string T_TIERS { get; set; } 
         public string T_LIBELLE { get; set; } 
         public virtual ICollection<gBusiness> GBusinesses { get; set; } 
    }

    public class gBusinessConfiguration 
       : EntityTypeConfiguration<gBusiness> { 
        public gBusinessConfiguration () 
           : base() { 
           HasKey(p => p.AFF_AFFAIRE); 
           HasRequired(p => p.GThirdParty).
               WithMany(t => t.GBusinesses).
               Map(x => x.MapKey("AFF_TIERS")); 
       } 
    }

    public class gThirdPartyConfiguration 
        : EntityTypeConfiguration<gThirdParty> { 
         public gThirdPartyConfiguration () 
            : base() { 
            HasKey(p => p.T_TIERS); 
        } 
    }

    public class CegidContext : DbContext { 
         public DbSet<gBusiness> gBussinesses { get; set; } 
         public DbSet<gThirdParty> gThirdParties { get; set; }
 
         protected override void OnModelCreating (DbModelBuilder modelBuilder) { 
            modelBuilder.Configurations.Add(new gBusinessConfiguration()); 
            modelBuilder.Configurations.Add(new gThirdPartyConfiguration()); 
            base.OnModelCreating(modelBuilder); 
        } 
    } 
}

Enjoy

Pending question:

• How to avoid lazy loading and force explicit loading as Linq to SQL allows with DataLoadOption in the DataContext.

---

Edit -----

---

About not lazy, here is the way: cc.gThirdParties.Include("GBusinesses")