none
Stored Procedure or LINQ? RRS feed

  • Question

  • Hi everyone,

    I'm working on my first WPF in C# with EF6. I want to populate a datagrid with query results from my SQL db. I am using a parameter and I also need to write to the database with the datagrid. For example, my query will return records with null columns. The user needs to enter data into the null columns and save. Can I do this with a SSMS Stored Procedure or should I do it in the application using LINQ?

    Thank you,

    - Justin

    Wednesday, November 7, 2018 9:08 PM

Answers

All replies

  • You should use the ADO.NET Entity Framework, an ORM,  that uses Linq to query the database tables, produces an object or a collection of objects from the ORM's virtual object model and the object or collection of objects ia bindable to a control. 

    An individual object or a collection of objects can be persisted back to the database based on what object or objects were changed by user actions at the grid control.

    In either case of querying the database using Linq or persisting objects to date database, the ORM engine generates the T-SQL that is submitted to the DB engine's internal stored procedure to be executed by the DB engine.

    Wednesday, November 7, 2018 9:26 PM
  • I am in deed usinf ADO.NET EF. I just didn't know if it is best practice to let SQL Server run the qry from the custom Stored Procedure I create and call from the application or write the query in the application to return a result. It sounds like Linq is the way to go and write the query in the application since I'm using EF. Any good article you can share on how to write to a database via Linq?
    Wednesday, November 7, 2018 9:39 PM
  • I would think the following post is a good read

    https://stackoverflow.com/questions/2698151/entity-framework-vs-linq-to-sql-vs-ado-net-with-stored-procedures

    It's old but still realavant.



    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by Justair07 Wednesday, November 7, 2018 10:41 PM
    Wednesday, November 7, 2018 10:01 PM
    Moderator
  • Any good article you can share on how to write to a database via Linq?

    Well lets get this straight, Linq is a query language that queries objects like a collection, array, a datatable, a database table,  etc. and etc, which are queryable objects in laymen terms, that produces a result of an individual object or a collection of objects.

    https://searchwindevelopment.techtarget.com/definition/LINQ

    What persists an EF object or objects to the database are methods in the ORM's Dbcontext and that is not Linq.

    http://www.entityframeworktutorial.net/EntityFramework4.3/persistence-in-entity-framework.aspx

    EF can be discussed at the EF forum.


    Wednesday, November 7, 2018 10:12 PM
  • That was very helpful! Thank you. For my application, Stored Procedures it is.
    Wednesday, November 7, 2018 10:42 PM
  • That was very helpful! Thank you. For my application, Stored Procedures it is.

    What's the purpose of using an ORM like EF if all you are doing is running sprocs? You might as well use straight up ADO.NET, SQL Command objects, parametrized sprcos, a data reader and the DTO pattern. You install an ORM and you don't use it effectively?

    If you don't want to use EF effectively, then use a micro ORM like Dapper that uses parmterized in-line T-SQL  or sproc.

    https://medium.com/dapper-net/get-started-with-dapper-net-591592c335aa

    https://www.davepaquette.com/archive/2018/01/28/using-stored-procedures-to-load-data-with-dapper.aspx



    • Edited by DA924x Thursday, November 8, 2018 12:25 AM
    Thursday, November 8, 2018 12:22 AM
  • So I was actually doing more research and if I'm correct and my stored procedure is trying to return multiple resultsets, I will run into complications. I saw that it is possible to map a stored procedure to multiple entities but it appears to defeat the purpose of why I wanted to use a stored procedure in the first place, simplicity. Here is part of my stored procedure, am I correct in my investigation?

        SET NOCOUNT ON;
    
    SELECT l.FirstName, l.LastName,s.SiteName, p.ProjectName, p.StartDate, p.EndDate, pz.PlannedSavings,pz.ActualSavings
    FROM ((((pt_Site as s
    inner join pt_ProjectsSites as ps on s.IDSite = ps.Site_id)
    inner join pt_Projects as p on ps.Project_id = p.IDProjects)
    inner join pt_ProjectSavings as pz on p.IDProjects = pz.Project_id)
    inner join pt_Personnel as l on p.Personnel_id = l.IDPersonnel)
    WHERE IDSite = @Site_ID


    • Edited by Justair07 Thursday, November 8, 2018 12:12 PM
    Thursday, November 8, 2018 12:11 PM
  • Just consider

    • Return one result set usually is done with a DataTable but could be done with the reader in a loop.
    • When there are multiple SELECT statements you can use NexResult in tangent with the reader.

    So with that see the example at the bottom of this page.

    https://support.microsoft.com/en-us/help/311274/how-to-handle-multiple-results-by-using-the-datareader-in-visual-c-net


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, November 8, 2018 12:58 PM
    Moderator
  • I'm confused, do I have to do stored procedure mapping in the .edmx diagram?
    • Edited by Justair07 Thursday, November 8, 2018 2:05 PM
    Thursday, November 8, 2018 2:05 PM
  • You can use the EF backdoor, use ADO.NET, SQL Command objects, use the sproc and use a datareader. You can use the datareader to populate an object on the ORM's virtual object model, do a reader.NextResult and read the next result in the resultset.

    https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqldatareader.nextresult?view=netframework-4.7.2

    https://stackoverflow.com/questions/12715620/how-do-i-return-multiple-result-sets-with-sqlcommand

    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/

    However, EF6 one must use the IObjectContextAdapter that I commented out to get the connection using the existing EF connection to the database so that I could do what the first link I gave is talking about to use T-SQL against the database using ADO.NET and SQL command objects, which could be a sproc too.      

    Now I did use the DTO pattern and left the EF object behind sending the DTO to the UI control.

     https://www.codeproject.com/Articles/1050468/Data-Transfer-Object-Design-Pattern-in-Csharp

     public List<DTOStudent> GetStudents()
            {
                var dtos = new List<DTOStudent>();
                using (var context = new CUDataEntities())
                {
                    //var adapter = (IObjectContextAdapter)context;
                    //var objectContext = adapter.ObjectContext;
                    
                    //var entityConn = objectContext.Connection as EntityConnection;
                    //var dbConn = entityConn.StoreConnection as SqlConnection;
                    //dbConn.Open();
                    var students = context.Students.ToList();
                    foreach(var stud in students)
                    {
                        var dto = new DTOStudent
                        {
                            StudentID = stud.StudentID,
                            FirstName = stud.FirstName,
                            LastName = stud.LastName,
                            EnrollmentDate = stud.EnrollmentDate
                        };
                        dtos.Add(dto);
                    }
                }
                return dtos;
            }


    • Edited by DA924x Thursday, November 8, 2018 2:12 PM
    Thursday, November 8, 2018 2:10 PM
  • I'm confused, do I have to do stored procedure mapping in the .edmx diagram?

    https://docs.microsoft.com/en-us/ef/ef6/modeling/designer/advanced/multiple-result-sets


    Thursday, November 8, 2018 3:55 PM