none
Accessing database connections used by LINQ RRS feed

  • Question

  • I am trying to decrypt some data using LINQ.  Here is what I have in Query Analyzer:

    open

     

    symmetric key Person_Phone_Key
    decryption by certificate Certificate_Name

    select

     

    *,
    convert (varchar, DecryptByKey(PhoneE)) as 'PhoneD'
    from dbo.Person

    I can write a View for the select statement and then in VS create a dataconext class.  The problem of course is that  this does not open the encryption key.  I cannot use a stored procedure because my application is based on a foundation of Views.  Using LINQ and Views how can I open the encryption key, run the view, and then  close the key.

    Jay

    Friday, August 21, 2009 6:17 PM

Answers

  •  
    I think I figured it out.  Below is the code. Green is the extra code I needed to open the encryption key, etc.  Normally, LINQ opens the connection to the database behind he scenes but evidently it allows me to explicitly open the connection and then it uses that open connection. VPersons is my View that contains the actually decryption which is in my original  post.

    If you look at the connection state using var state = db.Connection.State it is always closed without the additional code in green.  LINQ opens the connection and closes it internally.  I could never see where it did the actual opening and closing of the connection.  But as I said it allows me to explicitly do an open and then use it for other purposes like opening an encrytion key.
     
    Jay
     

     

    protected void Page_Load(object sender, EventArgs e)

    {

     

    DataClassesDataContext db = new DataClassesDataContext();

    db.Connection.Open();

    db.ExecuteCommand(

    "open symmetric key Person_Phone_Key decryption by certificate Certificate_Name");

     

    var query = db.VPersons;

    gridData.DataSource = query;

    gridData.DataBind();

    db.ExecuteCommand(

    "close symmetric key Person_Phone_Key");

    db.Connection.Close();

    }

    Saturday, August 22, 2009 3:40 AM