Hi,

I have two questions related to following code. We have bought license and this is critical for the encryption functionality we are planning to implement.

1. How to use DbFucntionCall with FetchEntityCollection function below?
2. How to apply DbFucntionCall to PrefetchPath?

   public IList FindAll(IEntity2 obj, IRelationPredicateBucket filter, List<IPrefetchPathElement2> prefetchPathElement2, bool isEventBind)
        {
            var entityType = (EntityType)obj.LLBLGenProEntityTypeValue;
            
            using (var adapter = new DataAccessAdapter())
            {
                var entityCollection = new EntityCollection(obj.GetEntityFactory());
                adapter.FetchEntityCollection(entityCollection, filter, SetPrefetchPath(entityType, prefetchPathElement2));
                if (isEventBind)
                {
                    BindEvents(entityCollection);
                }
                return entityCollection.GetList();
            }
        }

I have tried this with FetchTypedList function, but want to achieve this with FetchEntityCollection.

 public void TestDecryptForSelect()
        {
            ResultsetFields fields = new ResultsetFields(2);
            fields.DefineField(TblSysSettingFields.SettingName, 0);
            fields.DefineField(TblSysSettingFields.SettingTopicCode, 1);
            fields[1].ExpressionToApply = new DbFunctionCall("dbo.Encrypt",
                                                             new object[] { TblSysSettingFields.SettingTopicCode });

            DataTable results = new DataTable();
            DataAccessAdapter dataAdapter = new DataAccessAdapter();
            dataAdapter.FetchTypedList(fields, results, null);
            Assert.IsTrue(results.DefaultView.Count == 8);
        }

I think that the easy way is to extend your TblSysSettingEntityFactory.

Create a partial class file for your TblSysSettingEntityFactory (this is in your DBGeneric generated project):

using System;
using System.Collections.Generic;
using <yourRootNamespace>.EntityClasses;
using <yourRootNamespace>.HelperClasses;
using <yourRootNamespace>.RelationClasses;
using SD.LLBLGen.Pro.ORMSupportClasses;

namespace <yourRootNamespace>.FactoryClasses
{   
    public partial class TblSysSettingEntityFactory
    {
        public override IEntityFields2 CreateFields()
        {
            var toReturn =  base.CreateFields();
            toReturn[(int) TblSysSettingFieldIndex.SettingTopicCode.CompanyName].ExpressionToApply =
               new DbFunctionCall("dbo", "Encrypt", new object[] { TblSysSettingFields.SettingTopicCode});

            return toReturn;
        }
    }
}

... there you go. Now every time you fetch an entity or an entity collection, it will be applied the Encrypt function on the query.

I just realized one caveat: this expression in your field will also affect the way the entities are saved back to DB. So, in this case, if you change something in your collection and save it back, the generated code will look something like:

UPDATE TblSysSetting
SET SettingTopicCode = Encrypt(SettingTopicCode )
...
WHERE
...

So I think it's better to have a special factory for those fetches, and use the normal factory when you want to update info. So change the code I put above with this:

using System;
using System.Collections.Generic;
using <yourRootNamespace>.EntityClasses;
using <yourRootNamespace>.HelperClasses;
using <yourRootNamespace>.RelationClasses;
using SD.LLBLGen.Pro.ORMSupportClasses;

namespace <yourRootNamespace>.FactoryClasses
{   
    public partial class TblSysSettingEncryptEntityFactory : TblSysSettingEntityFactory 
    {
        public override IEntityFields2 CreateFields()
        {
            var toReturn = base.CreateFields();
            toReturn[(int) TblSysSettingFieldIndex.SettingTopicCode.CompanyName].ExpressionToApply =
             new DbFunctionCall("dbo", "Encrypt", new object[] { TblSysSettingFields.SettingTopicCode});

            return toReturn;
        }
    }
}

Then when you fetch a collection you specify your special factory:

var collectionToFetch = new EntityCollection<TblSysSettingEntity>(new TblSysSettingEncryptEntityFactory());
adapter.FetchEntityCollection(collectionToFetch, null);

Another option is to do your Encrypt in-memory using a TypeConverter. But I don't know if this is feasible for you (i.e. you have the Encrypt in DB for some special reason).

Hi,

Thanks a lot for your feedback. I have two parts here. While fetch, I want to apply **Decrypt **function and while saving back, it need to be Encrypt.

1. What is your suggestion for this scenario?
2. Also, when you apply this, what about for related prefetch elements? Do I need to create factory classes for them as well?
3. The following overridden function is not working while inserting new entity? do I need to do anything differently for insert functionality?

My code snippet as you give for fetch is below:

 public partial class TblSysSettingEntityFactory 
    {
        public override IEntityFields2 CreateFields()
        {
            var toReturn = base.CreateFields();
            toReturn[(int)TblSysSettingFieldIndex.SettingDefaultValue].ExpressionToApply =
             new DbFunctionCall("dbo", "Encrypt", new object[] { TblSysSettingFields.SettingDefaultValue });
            return toReturn;
        }

    }

Your help is appreciated.

Regards,

Praveen.V.Nair

In general, there are many ways to solve this.

1- In Decrypt and Encrypt at the application level. While Data is saved encrypted in the database. 2- Use Dataase triggers to perform such functionality. 3- Use a TypedList to fetch decryptedData and project the result to an EntityCollection. And use a DBFunctionCall, to encrypt upon saving. 4- Use "Transparent Data Encryption" (SQL Server 2008 +)

Hi,

The solution given earlier is working fine except for two things.

1. while insertion (insert query)
2. the factory pattern given is working fine. But, I want to use encrypt/decrypt functions based on fetch/save.

If I use solution 1 and 2 together, I think, I can achieve this. But, want to find that there is a better way or not.

I want to use clr function for encrypt/decrypt because, the same encrypted tables are used outside my application.

regards,

Praveen.V.Nair

And this is not for SQL Server 2008 or above?

Yes. This is for sql server 2008 R2.

Best solution is to use SQL Server Transparent Data Encryption: http://msdn.microsoft.com/en-us/library/bb934049(v=sql.105).aspx

Hi,

I would prefer to use this with DAL layer of LLBLGen. Can you please give me some sample for your solution 3?

Regards,

Praveen.V.Nair

Hi,

I found this article http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=15502 which says LLBLGen doesn't support DBFunction calls on Inserts, at the moment. But, the post was very old back in 2009.

Is this case same as of now?

This solution is very important because, my client want to handle things using clr functions..

Waiting for your reply..

This would be too erronous to work. Either have the implementation in the database side, or have it in code (client side). In the first case you should use TDE, and everything will work transparently from the code. Or have the Encrypt/Decrypt functions in code, and take the database out of the picture.

Hi,

From the first two examples given by daelmo, dbfunctions are working fine with fetch and update query. Not seems to be working for insert query. Doesnt it still not support for insert query?

My customer is very particular to have them in LLBLGen layer than db or at the application level.

Regards,

Praveen.V.Nair

Praveen wrote:

From the first two examples given by daelmo, dbfunctions are working fine with fetch and update query. Not seems to be working for insert query. Doesnt it still not support for insert query?

The expression won't work on inserts. This is considered for future releases, but it's not possible right now.

If you want to go on the Factories option, you should insert and then update with your custom encrypt expression.

I second Walaa in the sense of doing this complete on the DB or complete on Client side, with a custom crypto assembly or even with a TypeConverter which will do this transparently.

I know I recommended modifying factory classes, but now that I look back, it seems that you will have additional concerns using it, so if possible, go with DB encryption or client encryption.

Hi,

Thanks a lot and your excellent ontime reponses so far. I have done the following:

1. Used the partial factory class for encryption

 public partial class TblSysEntityFactory
    {
        public override IEntityFields2 CreateFields()
        {
            var toReturn = base.CreateFields();
            toReturn[(int)TblSysFieldIndex.MaxAgency].ExpressionToApply =
                new DbFunctionCall("dbo", "Decrypt", new object[] { TblSysFields.MaxAgency });
            return toReturn;
        }
    }

1. used Bal repository for encryption while insert

  var tblEntity = (TblEntity)entity2;
                    tblEntity.PatContactNameLast = EncryptDecrypt.EncryptCode(tblEntity.PatContactNameLast);
                    tblEntity.PatContactNameFirst = EncryptDecrypt.EncryptCode(tblEntity.PatContactNameFirst);

1. used db function during update

 var tblEntity = (TblEntity)entity2;
                    tblEntity.Fields[(int)TblFieldIndex.username].ExpressionToApply = tblEntity.Fields[(int)TblFieldIndex.username].IsChanged
                        ? new DbFunctionCall("dbo.Encrypt", new object[] { tblEntity.username }) : null;
                    entity2 = tblEntity;

This way, though, it looks little complicated, I can keep them at the dal and bal level. Please do let us know, when you enable db function for insert which will help me remove these additional steps.

Is there a way for changing the factory class of a entity directly? While fetching collection or when fetching new entity, I could change the factory class. But, when I use a already created entity object, can I change the factory to use?

Regards,

Praveen.V.Nair

The collections have an EntityFactoryToUse property.

Hi,

Yes. I know.

But, while saving a entity, I want to change the factory to be used by the entity. Is that possible?

Regards,

Praveen.V.Nair

Not possible. The factory of the entity is used when creating it. And it has no role to play while saving.

Hi,

Thanks a lot for your prompt response on this thread.

I am doing the Decrypt using factory and doing some additional coding at BAL level for encryption.

Lack of support for db functions while insert really put me off during the development. Please do let us know, when you have this feature. I will change my BAL code accordingly at that point of time.

Regards,

Praveen.V.Nair

It's planned but we don't know when it will be implemented.

One thing that's making it not that important is that expressions / functions needed during insert are often better implemented as a default in the column definition in the table. I'm not sure if you have looked into that, would that make things less problematic?