How to fill encrypted columns in database using SqlBulkCopy?

c# database encryption sqlbulkcopy sql-server

Question

I useSqlBulkCopy and System.Data.Datatable to add information to my database. I'm acting in the following manner:

var table = new DataTable();

// Creating table schema
var newRow = table.NewRow();
newRow["FirstName"] = Parser.Parse(values[0])
newRow["LastName"] = Parser.Parse(values[1])
...
newRos["SSN"] = //here I need to encrypt by db certificate value[5] before set it to this row value

simply copy to database code

using (var bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, transaction))
{
    bulkCopy.BatchSize = 100;
    bulkCopy.DestinationTableName = "dbo.Member";
    try
    {    
        bulkCopy.WriteToServer(table);
    }
    catch (Exception)
    {
        transaction.Rollback();
        connection.Close();
        throw;
    }
}

Naturally, I could set the encrypted column later using a database stored procedure, but I want to accomplish this in a single transaction. Can data be encrypted?string value utilizing my certificate in code retrieved from the database?

I appreciate it.

1
0
4/17/2016 12:20:41 PM

Accepted Answer

In my opinion, you may choose between the following choices, both of which ought to be simple to carry out:

Codify the encryption in C#.

Since you already have all the data there, you can use the appropriate.NET Framework methods to create the encryption there. You may test it to ensure that you have the same outcome.

Employ the AFTER INSERT trigger.

You'll alter and add the encrypted data after adding the value. It will be a component of the same transaction as the trigger. Simply use the Establish TRIGGER command to create a new trigger, check to see whether the value is NULL, and then use SQL procedures to insert the encrypted value.

For instance:

CREATE TRIGGER [dbo].[myTrigger]
   ON  [dbo].[Members] 
   AFTER INSERT
AS 
BEGIN
    SET NOCOUNT ON;
    UPDATE Members
    SET [EncryptedSSN] = EncryptByKey(Key_GUID('SSN_Key'), inserted.SSN)
    FROM inserted 
    INNER JOIN dbo.Members On inserted.id = Members.id
END 

Hope this was useful.

1
4/17/2016 2:45:05 PM


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow