SA Connection Authentication with Entity Framework 4.1

[Former Member]

Hello,

I am developing an application that uses the entity framework to access a SQL Anywhere database. The application will be deployed with an OEM version of SA. So I have to authenticate every connection that is opened to have full access to the database (authentication is done sending the "SET TEMPORARY OPTION connection_authentication ..." statement).

How would I tell entity framework to do that for every database connection it is opening?

Greetings and thanks in advance for your answers.

[MCMartin · Accepted solution]

You can use code like this:

 Assembly lAssembly = Assembly.GetExecutingAssembly();
 System.Data.Metadata.Edm.MetadataWorkspace workspace = new System.Data.Metadata.Edm.MetadataWorkspace(new string[] { "res://*/" }, new Assembly[] { lAssembly });
 iAnywhere.Data.SQLAnywhere.SAConnection myConn=new iAnywhere.Data.SQLAnywhere.SAConnection("dsn=mydatabase");
 // here you can issue the Set Temporary Option command on the just created connection
 // and now provide the connection to your entities:

 Entities lEntities = new Entities(new EntityConnection(workspace, myConn));

[Former Member · Accepted solution]

Hi and thanks for the quick response. I assume this example is for using ObjectContext (EF 4.0)? DbContext (EF 4.1) has different CTor overloads (it doesn't take an EntityConnection but a DbConnection, so maybe I could put the SAConnection directly into the DbContext).

But the real question here is: Will the externally created SAConnection be used for the whole lifetime of the ObjectContext/DbContext? As far as I know DbConnections are opened and closed on demand in the context for each query/command that is issued to the database, and the connection authentication has to be done after every Open, right?

[MCMartin · Accepted solution]

Yes you can use SAConnection for a DbConnection. For a reopen of a connection you could connect to the DbConnection.StateChange Event to react to the change of state to open. Anyway I doubt, that DBContext will close the connection as it is also said not to dispose the connection which you provide to the constructor.

[VolkerBarth · Accepted solution]

As a different approach, you might also use the InitString SAConnection property or the connection parameter with the same name.

This would be helpful when a framework does create its own connections - cf. the last paragraphs on this doc page.

[Former Member · Accepted solution]

Thanks a lot for your answers.