Asked by:
Get Datatable from WCF Data Service

Question
-
User-1651183775 posted
Hello
I have created WCF Data Service using WF6. my question is how can i get data in datatable from EF. I know it does not return datatable straight a way. i am looking for which shows how to acheive it.i came across below link but i am not sure how to use it.
https://stackoverflow.com/questions/10254272/execute-stored-procedure-in-entity-framework-return-listdatatable-or-dataset
IEnumerable<SqlParameter> parameters = new List<SqlParameter>() { 1, 0, 0 }; using (var ctx = new RContext()) { ExecuteStoredProcedure(context, Constants.GetApplicationPrimaryUrl, parameters); } public static DataTable ExecuteStoredProcedure(ObjectContext db, string storedProcedureName, IEnumerable<SqlParameter> parameters) { var connectionString = ((EntityConnection)db.Connection).StoreConnection.ConnectionString; var ds = new DataTable(); using (var conn = new SqlConnection(connectionString)) { using (var cmd = conn.CreateCommand()) { cmd.CommandText = storedProcedureName; cmd.CommandType = CommandType.StoredProcedure; foreach (var parameter in parameters) { cmd.Parameters.Add(parameter); } using (var adapter = new SqlDataAdapter(cmd)) { adapter.Fill(ds); } } } return ds; }
Thursday, November 2, 2017 10:32 AM
All replies
-
User475983607 posted
Returning a data table is considered a bad practise as data tables are specific to the .NET framework. This will cause problems for clients that are not using .NET. Simply return a list of type using EF6.
If you really need a data table, which is unlikely, then you can convert a generic list to a data table.
https://stackoverflow.com/questions/564366/convert-generic-list-enumerable-to-datatable
Thursday, November 2, 2017 10:59 AM -
User-1651183775 posted
There are various sql queries used in my several stored procedure hence i am willing to use DataTable. Could you please give me link of article which shows how to use stored procedure in EF6 which return generic list. This is what i know so far:
context.Database.ExecuteSqlCommand("usp_CreateBook @BookName, @ISBN, @BookId OUT", new SqlParameter("@BookName", "Book"), new SqlParameter("@ISBN", "ISBN"), bookIdParameter);
,
Thursday, November 2, 2017 2:22 PM -
User475983607 posted
See the Entity Framework reference documentation.
Thursday, November 2, 2017 2:30 PM -
User-1651183775 posted
anything better article than this i am from webforms background
Thursday, November 2, 2017 3:05 PM -
User475983607 posted
anything better article than this i am from webforms background
The reference doc explicitly explains the EF syntax. It does not get any better than that...
// Commenting out original code to show how to use a raw SQL query. //Department department = await db.Departments.FindAsync(id); // Create and execute raw SQL query. string query = "SELECT * FROM Department WHERE DepartmentID = @p0"; Department department = await db.Departments.SqlQuery(query, id).SingleOrDefaultAsync();
In this explain db is just your context.
You can always visit other tutorials on this site from the Learn link above.
Thursday, November 2, 2017 5:28 PM -
User1120430333 posted
Your problem here is you choose the wrong technology, the WCF Data Service, using EF with the WCF Data Service not being flexible in working with EF and a Stored Procedures. What you should be using is a ASP.NET WebAPI or the ASP.NET WCF Web service, which would allow you to use the Repository pattern to deal with the EF model objects being returned to the client or DTO(s) retuned from a Stored Procedure with the DTO(s) acting as a model in their own rights.
It's unfortunate, that you have painted yourself into this corner thinking that a datatable is your way out of this.
Friday, November 3, 2017 4:13 PM -
User475983607 posted
Your problem here is you choose the wrong technology, the WCF Data Service, using EF with the WCF Data Service not being flexible in working with EF and a Stored Procedures.Really? How so?
@kodnil
https://docs.microsoft.com/en-us/dotnet/framework/data/wcf/defining-wcf-data-services
Friday, November 3, 2017 9:30 PM -
User1120430333 posted
DA924
Your problem here is you choose the wrong technology, the WCF Data Service, using EF with the WCF Data Service not being flexible in working with EF and a Stored Procedures.Really? How so?
@kodnil
https://docs.microsoft.com/en-us/dotnet/framework/data/wcf/defining-wcf-data-services
Myself I would have just used an ASP.NET WebAPI using a the Repository pattern so that I could use EF and the Sprocs no muss and nu fuss. :)
https://code.tutsplus.com/tutorials/the-repository-design-pattern--net-35804
<copied>
The Repository Design Pattern, defined by Eric Evens in his Domain Driven Design book, is one of the most useful and most widely applicable design patterns ever invented. Any application has to work with persistence and with some kind of list of items. These can be users, products, networks, disks, or whatever your application is about. If you have a blog for example, you have to deal with lists of blog posts and lists of comments. The problem that all of these list management logics have in common is how to connect business logic, factories and persistence.
<end>
One can even make EF use its backdoor to call the sprocs, using traditional ADO.NET and SQL Command objects. that would use the datareader, the DTO and the List<T> as was shown to you over in the MSDN forms, which you have ignored with you insisting upon trying to send a datatable through the service.
https://blogs.msdn.microsoft.com/alexj/2009/03/25/tips-and-tricks/
You do know when using EF in a Linq query, then it's using the EF entities, which are custom objects, loaded into a LIst<T>, a generic collection, right?
That's all that is being shown to you that you are running from is how to use the generic collection with the DTO being the custom object that can be sent through the WCF DATA Service when you use the Sprocs.
https://dzone.com/articles/reasons-move-datatables
<copied>
ORMs are becoming increasingly popular, and they use generic collections for all data operations.
<end>
You can use the Repository pattern behind that WCF Data Service. One Repository to use EF, and one to use the Sprocs using the DTO(s) being sent back in the generic List<T>.
http://www.eidias.com/blog/2011/10/14/abstract-repository-for-direct-ef-wcf-and-wcf-data
But you show anywhere where a datatable the Ford Model T is being used, as the container to hold data and send it through the data service.
Saturday, November 4, 2017 12:12 AM