.netenterprise-librarydaabalways-encrypted

Can we use dbCommand and AddInParameter while inserting always encrypted columns


Is there any way we can use dbCommand and AddInParameter while inserting always encrypted columns into table through .net?

db.AddInParameter(dbCommand, "@SSN", DbType.string, SSN);


Solution

  • Regarding how the .NET Framework Data Provider handles this - there is nothing you have to specifically do if the underlying object is a SqlParameter.

    More details on this: Develop using Always Encrypted with .NET Framework Data Provider

    The .NET Framework Data Provider for SQL Server automatically detects and encrypts parameters that target encrypted columns.

    I assume you are using the Enterprise Library. Looking at the AddInParameter method - it internally creates a DbParameter and SqlParamter inherits from DbParameter. Seems the same rules would apply in your case.

    That being said, you may want to reconsider using the Enterprise Library in favor of straight ADO.NET, Entity Framework, or a micro ORM like Dapper. To the best of my knowledge, the Enterprise Library is not an active project / being maintained.

    Edit

    Seems you can pass a SqlParameter to the dbCommand.

    Example (not tested)

                DatabaseProviderFactory factory = new DatabaseProviderFactory();
                Database db = factory.CreateDefault();
    
                var dbCommand = db.GetStoredProcCommand("spINSERT");
                SqlParameter p = new SqlParameter("xxxxxx", "yyyyyy") {SqlDbType = SqlDbType.VarChar};
                dbCommand.Parameters.Add(p);
                db.ExecuteNonQuery(dbCommand);