New Posts New Posts RSS Feed: Connection object or string in Entity Manager
  FAQ FAQ  Forum Search   Calendar   Register Register  Login Login

Connection object or string in Entity Manager
 Post Reply Post Reply
Author
  Share Topic Share Topic  Topic Search Topic Search  Topic Options Topic Options
tj62 View Drop Down
Groupie
Groupie
Avatar

Joined: 21-May-2009
Location: Iceland
Posts: 81 		Post Options Post Options   Quote tj62 Quote  Post ReplyReply Direct Link To This Post Topic: Connection object or string in Entity Manager
    Posted: 29-Jan-2010 at 6:33am

Hi,

Is there any way to access the Connection object directly from Entity Manager. I se by debugging that the Connection String is deep down in the class hiracy of EntityManager, but it is private there. Can I somehow get access to that string read only. I need this because I have a very complex business object where LINQ to Entities cant do what I need. Therefore I intend to access the database in this case with an old fashion DataReader (calling SQL queries or Stored Procedures) and using InvokeServerMethodAsync() to access it from the SL client. I'm using IBM Informix therefore I would like to do something like this on server side:

[AllowRpc]

public static SomeReturnStuff GetMenu(IPrincipal pPrincipal,EntityManager eMgr,params Object[] args){
   string dbConnectString = eMgr.DataSourceResolver.GetTheDatabaseConnectionString(); // This call does not exist
   DB2Connection con = new DB2Connection(dbConnectString );
   DB2Command cmd = new DB2Command();
   cmd.Connection = con;
   cmd.CommandType = System.Data.CommandType.Text;
   cmd.CommandText = "SELECT...";
   .....
   return something;
}
Back to Top
tj62 View Drop Down
Groupie
Groupie
Avatar

Joined: 21-May-2009
Location: Iceland
Posts: 81 		Post Options Post Options   Quote tj62 Quote  Post ReplyReply Direct Link To This Post Posted: 29-Jan-2010 at 6:51am
Well I cracked it...although realy ugly:

string dbConnectionString = ((IdeaBlade.EntityModel.Edm.EdmKey)eMgr.DataSourceResolver.DataSourceKeys.ElementAt(0)).DbConnectionString;

Is there no way geting access directly to the Connectoin object that I can use directly by DB2Command?
Back to Top
WardBell View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 31-Mar-2009
Location: Emeryville, CA,
Posts: 338 		Post Options Post Options   Quote WardBell Quote  Post ReplyReply Direct Link To This Post Posted: 29-Jan-2010 at 7:44pm
It's not supposed to be easy. One objective of frameworks like EF and DevForce is to insulate you from direct database access. If people start hacking away at the database directly, the app goes sideways fast.
 
We're also supporting n-tier scenarios which means that the connection strings shouldn't be knowable on the client. Your approach will fail misearably if run on the client in an n-tier deployment (e.g., Silverlight) ... as will mine and as should everyone's. If you could find the connection string from the client, that would be a really bad security violation!
 
Assuming you are running 2-tier or are performing your operation on the server, we know that you may have to get to the database directly. And so you can.
 
Take a look at the NumericIdGenerator code sample which uses raw ADO to talk to a table of counters for generating entity ids. After install on my Windows 7 machine it is located here
 
  C:\Users\Public\Documents\DevForce\Learning Resources\040_BusObjPersistence\AddingDeleting\Snippets\NumericIdGenerator
 
You'd go this route if you were creating entity ids old school with a NextId table in your database instead of using autoincrement columns or Guids.
 
Work your way quickly to the "AllocateMoreIds" method which takes a DateSourceKey. Inside it acquires an AdoHelper which is an easier way to talk to your database than raw ADO. Oh sure you can get the connection string from AdoHelper if you want. 
 
I'm not saying it's pretty. But it does the job as IdeaBlade thinks you should approach it :-)
 
======
 
As you read the code you see that it creates an AdoHelper via a DataSourceKey and that has to be an Ideablade.Ado.RdbKey. DevForce supplies that key to the IdGenerator. You're going to need to get your own.
 
RdbKeys don't fall off the tree. In fact, you have to construct yours from an EdmKey.
 
An EdmKey, "Entity Data Model Key", describes a data source that you've modeled. In theory that source does not have to be a database. Of course it usually is and for the rest of this example we will assume that it is.
 
How do you get an EdmKey? There are lots of ways. The way you did it with the DataSourceKeyResolver works. It just isn't obvious how it works.
 
I think many will find it more obvious to start with name of the key you want.
 
If you want to play along,  remember to reference the little-used IdeaBlade.Ado and IdeaBlade.Edm assemblies; they are in the GAC and in the IdeaBlade assemblies folder like all the others.
 
Getting the EdmKey name
 
You're golden if you know the name brute force. The default name from our Object Mapper is "Default" so you could write:
 
  var edmKeyName = "Default";
 
If you aren't sure of the name but you know there is only one in your application try
 
  var edmKeyName = IdeaBlade.Core.IdeaBladeConfig.Instance.EdmKeys[0].Name;
 
That fishes the name from your in-memory IdeaBlade application configuration which is most likely derived from the XML in your app.config (or web.config in n-tier).
 
If you have multiple EDM models (really? I want to meet you!) requiring multiple keys, then you have to know which EdmKey to use.  If you happen to know an entity type that is mapped to the database you want, you can get the name from the type.
 
  var edmKeyName = IdeaBlade.EntityModel.EntityMetadata.GetDataSourceKeyName(typeof(myEntityType));
 
If you are using DataSourceKeyExtensions ... stop where you are and tell me. You have to toss in a wrinkle at this point in the recipe ... and I don't want to lose any more readers.
 
Get the EdmKey
 
Now that you know the edmKeyName, you can get an EdmKey.
 
  var edmKey = IdeaBlade.EntityModel.Edm.EdmKey.LocateEdmKey(edmKeyName);
 
You're almost there!
 
You need an RdbKey ("Relational Database Key"). Remember that an EdmKey doesn't necessarily point to a database. You're going to have to ask it to make an RdbKey for you if it can. It will if it refers to a database; it won't if it can't.
 
Ok ... if all you want is the database connection string, you can stop right now and get it from the edmKey. You found this out already. The phrase is "edmKey.DbConnectionString".
 
But I want you to consider using AdoHelper. So we will proceed to manufacture an RdbKey like so:
 
  var rdbKey = edmKey.RdbKey as IdeaBlade.Ado.RdbKey;
 
Now you can write:
 
  var adoHelper = new IdeaBlade.Ado.AdoHelper(rdbKey);
 
===
Why is this "really ugly" as you say?
 
I half explained at the top of this post but it bears repeating. The point of using infrastructure like DevForce and EntityFramework (and all of the others) is to decouple your application from the database.
 
We make it possible for you to have one object model that is built from multiple datasources, some of them databases and some of them not. We make it possible for you to specify and dynamically swap database instances for different environments (dev / test / stage / production) or for multiple tenant applications or because one instance of your application will run against SQL Server and another against DB2 or Oracle ...
 
... and you don't have to change your code.
 
IF you start programming in a database-specific way, all our attempts at decoupling are for naught.
 
When you have to reach the database directly, you certainly can. It just takes some effort to defeat the decoupling that usually works in your favor.
 
I hope that both answers your question and explains why it seems so baroque.
Back to Top
mikewishart View Drop Down
Groupie
Groupie
Avatar

Joined: 26-Feb-2010
Location: Reno, NV
Posts: 49 		Post Options Post Options   Quote mikewishart Quote  Post ReplyReply Direct Link To This Post Posted: 25-Mar-2010 at 6:10pm
Thanks Ward!  Just used this info to help with writing a RPC.
Back to Top
tj62 View Drop Down
Groupie
Groupie
Avatar

Joined: 21-May-2009
Location: Iceland
Posts: 81 		Post Options Post Options   Quote tj62 Quote  Post ReplyReply Direct Link To This Post Posted: 26-Mar-2010 at 3:17am
Hi Ward,
I think you are overseeing on4 important thing here.  The code in the original message is an RPC method on the server, not on the client. So I'm retrieveing the connection string server side, not client side. So there should be no risk involved, is it??!!

So the code is Server only and this method is accessed from the client by a InvokeServerMethodAsync(). The database connecton string is cracked server side only and never returned to the client. So I don't se there is any security issue here.

Regards
  TJ
Back to Top
WardBell View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 31-Mar-2009
Location: Emeryville, CA,
Posts: 338 		Post Options Post Options   Quote WardBell Quote  Post ReplyReply Direct Link To This Post Posted: 26-Mar-2010 at 9:54am
No issue. You should be fine getting the connection string on the server and doing what you need with it. Maybe I was being a bit TOO controlling.
 
In fact, in our DevForce 2010 release, AdoHelper will "disappear".
 
You DO want to ensure that you're getting the same connection string that DevForce is using; that's a reason to go through the IdeaBladeConfig mechanism to retrieve it.
 
Once you have the connection string, do what you what seem most appropriate: raw ADO, direct to Entity Framework, whatever.
 
The key (pun intended) is to ensure that the connection string is accessible only on the server AND that your database interactions are consistent with application business logic. You don't want to bypass important business rules or actions  that are inscribed in your code (e.g., auditing client queries).
 
You want to preserve both Security and Data Integrity.
 
But, as you observe, the client won't have access to your server side connection string.
Back to Top
tj62 View Drop Down
Groupie
Groupie
Avatar

Joined: 21-May-2009
Location: Iceland
Posts: 81 		Post Options Post Options   Quote tj62 Quote  Post ReplyReply Direct Link To This Post Posted: 26-Mar-2010 at 10:08am
Ward,
I would very much like to avoid doing things like this. The sad fact is just that the LINQ to Entiyt Framwork is not powerfull enoug to perform many of my complex queries. Secondly although some of them I can do via LINQ it has revealed to be 10-100 times slower than going via RPC using old style DataReaders at the server side.

Anothe thing is also that quite often we need to perform quite many queryes on the server side before geting the end result valid for the UI. In that case it is more easy to do it all in one round trip to the server than doing all the LINQ-queries client side causing many round trips to the server. Maybe I'm wrong here, but then please tell me about a usable alternative.
Back to Top
WardBell View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 31-Mar-2009
Location: Emeryville, CA,
Posts: 338 		Post Options Post Options   Quote WardBell Quote  Post ReplyReply Direct Link To This Post Posted: 05-Jun-2010 at 10:18am
Sorry I haven't checked in on this thread in awhile. I had a feeling it was closed in the sense that you were satisfied with my answer about how to get the connection string and were sharing your regret that such things are necessary from time to time.
 
Upon reflection, I'm not sure if I answered your question ... or if you thought there was something more we could or should do. If I hear from you, I'll know :-)
Back to Top
WardBell View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 31-Mar-2009
Location: Emeryville, CA,
Posts: 338 		Post Options Post Options   Quote WardBell Quote  Post ReplyReply Direct Link To This Post Posted: 14-Sep-2010 at 8:14pm
Yes ... you can get at the connection string. It will be the Entity Framework connection string (see the Web.Config or the App.config in the EF model project)  but you can figure out how to parse that and strip out the portion of it that you need to access the db directly.
 
Please note that the string would only be accessible to server-side code. You wouldn't want your client application looking at the database connection string ... that's a security violation.
 
Side-note: when you run 2-tier, your "client-side" and "server-side" are executing in the same process so of course you can access the string there. I'm talking about accessibility in n-tier deployments as would be required, for example, in a Silverlight application.
 
Are you ready to learn how to get that string?
 
You'll have to wait for tomorrow :-)   I've run out of time this evening. But, trust me, you can get that string on the server.
Back to Top
WardBell View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 31-Mar-2009
Location: Emeryville, CA,
Posts: 338 		Post Options Post Options   Quote WardBell Quote  Post ReplyReply Direct Link To This Post Posted: 14-Sep-2010 at 8:23pm
Wait a minute, @teresap989! I read your request too fast.
 
You wrote: "I see by debugging that the Connection String is deep down in the class heirarchy of EntityManager"
 
No it is not. You cannot get to the connection string from the EntityManager.
 
I'll give you a big prize if you can find such a string from the EntityManager ... by reflection, debugging, or any other means ... because that would be a huge security hole. The connection string is not accessible from any client DevForce code.
 
You will find the DataSourceKey. That is just a name ... a name for requesting access to a server-side data source with that key. The key does not itself contain any information that would be useful in accessing the database directly.
 
Fortunately, I see that you are actually talking about writing server-side method (a "Remote Server Method") that makes use of ADO. That is ok.
 
As I said ... tomorrow I'll show you how you can get the string in DevForce 2010 from server-side code.
Back to Top
Vonzkie View Drop Down
Senior Member
Senior Member
Avatar

Joined: 01-Aug-2011
Location: PH
Posts: 133 		Post Options Post Options   Quote Vonzkie Quote  Post ReplyReply Direct Link To This Post Posted: 05-Aug-2012 at 11:49pm
Hi Guys,

Any update on how to get the connection string in the SERVER side?
I need to know the current connection string used by the EntityManager without checking on the EdmKeys cause I used DataSourceKeyResolver to compose it dynamically..
I'm about to run a native sql server commands on the server side..

Thanks,
Von
Back to Top
WardBell View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 31-Mar-2009
Location: Emeryville, CA,
Posts: 338 		Post Options Post Options   Quote WardBell Quote  Post ReplyReply Direct Link To This Post Posted: 06-Aug-2012 at 3:23pm
If you wrote your own DataSourceKeyResolver which delivers the string to DevForce, why can't you call it yourself to get the string you need? I'm just trying to sidestep anything too fancy. What am I missing?
Back to Top
 Post Reply Post Reply

Forum Jump Forum Permissions View Drop Down