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;
        }

Answer 1

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

Answer 2

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);

,

Answer 3

User475983607 posted

See the Entity Framework reference documentation.

https://docs.microsoft.com/en-us/aspnet/mvc/overview/getting-started/getting-started-with-ef-using-mvc/advanced-entity-framework-scenarios-for-an-mvc-web-application

Answer 4

User-1651183775 posted

anything better article than this i am from webforms background

Answer 5

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.

https://docs.microsoft.com/en-us/aspnet/web-forms/overview/getting-started/getting-started-with-aspnet-45-web-forms/introduction-and-overview

Answer 6

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.

  

Answer 7

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

Answer 8

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/11/07/tip-41-how-to-execute-t-sql-directly-against-the-database/ 

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.