That's a lot of back and forth messaging. If you have one person that has all facts and the knowledge to make decisions, you just need to ask one question and their brain will process everything internally to come up with an answer. No deliberation with another person is involved. Naturally it's going to be faster. That's not to say it's necessarily better. For such people it's imperative to have any logic written in the same language that the MVC uses because it speeds up development for them.
In some cases this is also a "non-issue" since in the case of some RDMBS you can write sprocs in the same language as the one used by the MVC examples:. The advantages of encapsulation are well documented. You wouldn't ever create a class and then expect users of that class to interact with the inner workings of the class. What if something changed; what if you changed a variable type? How would you know where all the code that accessed that variable was?
Solution, use an interface. Your database is no different, it's an object, so give it an interface. For SQL Server databases, that means stored procedures. Having worked as a DBA for over 20 years, I've lost count of the number of times developers have describe a problem with code that they wrote as "A database problem", and suggested "Talk to the DBA" as a solution. Now, I don't mind this. When I'm fixing your mistakes, and you're not fixing mine But the least you can do is code in a fashion which allows me to fix your mistakes in the shortest time possible.
That means, put your code in stored procedures. When stored procedures cause performance issues, that's bread and butter for any decent DBA. But when ORMs cause performance issues, that's a nightmare for anyone.
At least give your DBA a fighting chance when he's trying to help you. And if you can't code in stored procedures, or it really takes you that much longer to do so, then you might want to think about a change of career. Starting with the. NET Framework 4. However, you can still use compiled LINQ queries to reduce this cost in later executions and compiled queries can be more efficient than LINQ queries that are automatically cached. How are we doing?
Please help us improve Stack Overflow. Normally you should take a good hard look at what data store you are going to use since it is more rare and sometimes even foolish to switch databases.
A Bit like a class. Makes it a lot simpler to keep everything together rather than a lot off little stored procs. Having all access to the database performed via a stored procedure isolation layer, allows database changes to be made without having to refactor application code.
As long as the stored proc layer provides the same result set and the call parameters remain the same, the database structure can be changed without these changes affecting the application. The second and perhaps the greatest advantage is architectural — this isolation layer enables data governance, where the business users are responsible for producing the data dictionary that underpins the database structures and for governing the quality of the data in the database.
If they decide that the definition needs to change e. Implement a stored proc isolation layer so that the database structures can be changed and only a few stored procs need to be rewritten, rather than have pervasive associated application changes having to be made, and the problem disappears.
Mark — slow up a little and read the post, chief. In this case, you are not talking to a junior database administrator but to someone who has 40 years of data architecture experience and who has delivered the Charles Schwab enterprise data warehouse, as well as recovered the project to integrate the BI platforms for Lloyds Bank and HBOS — two of the largest banks in the world.
Look up from what is good for a single application and consider the larger IT picture. Scattering database access code across multiple applications is a really bad idea when it comes to the big picture. It essentially disables any data governance initiative due to the effort involved in making the changes needed to have a common data dictionary, which in turn makes integrating systems virtually impossible and building enterprise data warehouses and MDM systems extremely difficult.
I have heard this promise many times over the years, but unfortunately I have yet to even hear an anecdotal story of it paying off. Reason being is that the change is so big you have to refactor the app anyway, or small enough to do with a view.
Or … these kinds of refactors just are never needed. All are basically free. And our experience confirms. In our shop, it paid off for about of the s of data-access only therefore written just for that purpose SPs we wrote over the past 15 years.
Al, yes, I have seen this done successfully, in multiple cases. What it does require, however, is active management of the library of stored procedures that are developed and a planned approach by the data architect as to which SPs get built. The approach no only enables data governance, with its multiple associated benefits, but also has a number of benefits with regards to database efficiency, code maintainability, enables the database engine deadlock detection and resolution mechanism, has security and encryption benefits, etc.
The same messed up query will poor perform inside or outside an SP. If you need fine tuning or have high performance requirements, hire an SQL Developer to write efficient queries, in code or in SPs preferably in SPs because of all the reasons you mentioned. Your email address will not be published. Don't subscribe All Replies to my comments Notify me of followup comments via e-mail. You can also subscribe without commenting. Post Comment. Want to advertise here and reach my savvy readers?
Should we use stored procedures or queries built in the app? Last Updated March 27, Brent Ozar. Leave new Budd. Jason Markantes. Greg Low. Jeff Mergler. Brent Ozar. Jeff Moden. Obviously, I have the opposite scars of Brent.
John Zabroski. That still happens today ? I would love to read what they were doing. Bryan Rebok. Alex Friedman. Visual Studio Vs Visual Studio Understanding Matplotlib With Examples. Understanding Numpy With Examples.
C Evolution. Once I open a terminal window, I type in the command:. Change to the new directory using the CD command in the terminal window, as shown in the command below. A few seconds after opening the folder, you should be prompted with a dialog that looks like Figure 1.
Click on the Yes button to add the required assets to the new project. To work with a table in a database, you need to add EF into your project and set up an entity class to map each field in your table to a property in a class. In addition, you need to create a DbContext class, add a connection string to point to your database table and configure some DbSet properties in your DbContext class.
Finally, you need to create an instance of the DbContext class in the Startup class to allow this instance to be used with Dependency Injection DI. To use the Entity Framework in the. Go back to the terminal window that should still be open in the EFSample folder. Type in the following command to add the Entity Framework to the project. An entity class is a representation of a table or view contained in your database.
In Listing 1 , you can see an example of a Product class that has a property for each field in the SalesLT. Product table contained in the AdventureWorksLT sample database. You need to have a [Table] attribute to identity the table and schema in which this table is located. You may have additional data annotations such as the [Key] or [Required] attributes above various properties, however, I've left most of them off for this sample.
In your project, right mouse-click on the Models folder and add a new file named Product. Into this file, add the code shown in Listing 1. In addition to the properties, add an override of the ToString method. This method is useful when looking at lists of data in the debugger as you're going to do throughout this article. An essential piece of using EF is an instance of a DbContext class. Right mouse-click on the Models folder and add a new file named AdvDbContext.
Add the code shown in the code snippet below. The Entity Framework needs a connection string in order to connect to your database. Open the appSettings. This property's value is a literal object with a single property named DefaultConnection.
The value for this property is the actual connection string. Change the values for Server , Initial Catalog and Integrated Security as appropriate for your database and server. Instead of creating an instance of the AdvDbContext class each time you need to use it, it's best to allow. Open the Startup. Within the Startup class, locate the ConfigureServices method. Just after the one line that's already in this method, add an instance of the AdvDbContext to the services collection using the AddDbContext method.
To this method, you pass an options object where you tell it you're using SQL Server as your database, and you retrieve the connection string from the appSettings. You now have everything configured correctly in order to connect to your database and call any stored procedures within this database. In this first sample, build a stored procedure without any parameters and call that stored procedure to load a collection of product objects. Place this stored procedure in the SalesLT schema.
This stored procedure retrieves all rows of product data and returns them to your application. Next, add another parameter to the HomeController constructor. NET looks at all parameters being passed into the HomeController and if they're in the collection of services, automatically fills in the parameters with an instance from the services collection object.
Now that you have the instance of the AdvDbContext class, you simply need to call the appropriate method with the name of the stored procedure you just created. Add a new method named GetAll to the HomeController class and make it look like the following code snippet. Create a variable to hold the SQL statement with the name of the stored procedure to call on the database server.
0コメント