I’ve griped about this before, not sure why everything has to be so complicated with EF at times, but holy hell executing just a simple SQL String “SELECT * FROM Table” is a major feat that takes more than just 1 line of code to do. Anyhow, if you are using EF 4.0 and not EF 4.1 it is slightly more difficult to execute sql strings right from EF. EF 4.1 has some nice methods that make a bit of a difference, but not very much, I still find myself needing the following code below.

I just want to execute my sql string and get a DataTable back for pete’s sake? Is that really too much to ask for? Apparently so…

So first thing’s first, we need to get a connection string. You would think this was easy… well it isn’t unfortunately.

Getting the Connection String in EF 4.0


If you are using EF 4.1 check out this link here to see how to do it in 4.1; I haven’t tried it so I can’t vouch for it. EF 4.1 offers a new property called “Database” which seems to help with some of this mundane code.

Getting a DataTable in EF 4.0


Please be aware that if you are going to return this DataTable over the pipe from a WCF service to a client application, you MUST name your DataTable or you will get a bogus WCF error that will only serve as unnecessary confusion (it will be going into my hall of shame soon). At all… Look here for a great question and answer about this.

Getting a DataSet in EF 4.0


I haven’t tested the DataSet code yet, but it should work since it is exactly the same as the DataTable code except that it is returning a DataSet instead of a DataTable. I tried making a generic version of this method, but it didn’t work. If you are going to return the DataSet across the pipe via WCF, you need to change it into a List<DataTable> so that it will be serializable. Just remember to name each table to avoid WCF bogus problems.

Sources
http://stackoverflow.com/questions/6587143/get-the-connection-string-for-entity-framework-using-the-poco-template/

http://stackoverflow.com/questions/10254272/execute-stored-procedure-in-entity-framework-return-listdatatable-or-dataset

http://stackoverflow.com/questions/915329/is-it-possible-to-run-native-sql-with-entity-framework

http://stackoverflow.com/questions/12702/net-returning-datatables-in-wcf

Leave a Reply

Your email address will not be published. Required fields are marked *