If you don't have a large number of stored procedures, you can write a macro in Visual Studio to generate the code (see this for example). Actually, depending on how you write the macro, it can handle a large number too.
Check out my SqlSharpener project. It can parse the sql files in your SSDT project at design-time and then you can call an included T4 template to create C# wrappers for all your stored procedures, or you can build your own T4 template if you want.
t4s stored procedure generator 16
For queries, the TPC strategy is an improvement over TPT because it ensures that the information for a given entity instance is always stored in a single table. This means the TPC strategy can be useful when the mapped hierarchy is large and has many concrete (usually leaf) types, each with a large number of properties, and where only a small subset of types are used in most queries.
But when using TPC, the primary key for an animal is stored in the table for the concrete type of that animal. For example, a cat's primary key is stored in the Cats.Id column, while a dog's primary key is stored in the Dogs.Id column, and so on. This means an FK constraint cannot be created for this relationship.
Stored procedures are mapped in OnModelCreating using InsertUsingStoredProcedure, UpdateUsingStoredProcedure, and DeleteUsingStoredProcedure. For example, to map stored procedures for a Person entity type:
Stored procedures do not need to be used for every type in the model, or for all operations on a given type. For example, if only DeleteUsingStoredProcedure is specified for a given type, then EF Core will generate SQL as normal for insert and update operations and only use the stored procedure for deletes.
The first argument passed to each method is the stored procedure name. This can be omitted, in which case EF Core will use the table name appended with "_Insert", "_Update", or "_Delete". So, in the example above, since the table is called "People", the stored procedure names can be removed with no change in functionality.
Parameters can be named, but EF Core always calls stored procedures using positional arguments rather than named arguments. Vote for Allow configuring sproc mapping to use parameter names for invocation if calling by name is something you are interested in.
Original value parameters must be used for key values in "update" and "delete" stored procedures. This ensures that the correct row will be updated in future versions of EF Core that support mutable key values.
Values returned from stored procedures are often used for generated, default, or computed values, such as from an Identity key or a computed column. For example, the following configuration specifies four result columns:
If using a single stored procedure per concrete type regardless of the mapping strategy is something you are interested in, then vote for Support using a single sproc per concrete type regardless of the inheritance mapping strategy.
Currently stored procedures for insert, update, and delete only support owned types must be mapped to separate tables. That is, the owned type cannot be represented by columns in the owner table. Vote for Add "table" splitting support to CUD sproc mapping if this is a limitation that you would like to see removed.
SQL Server Unicode strings as represented by the nchar and nvarchar data types are stored as UTF-16. In addition, the char and varchar data types are used to store non-Unicode strings with support for various character sets.
The problem here is that the standard EF6 T4 generator uses the conceptual model. It's because ExecuteFunction and CreateQuery work with EntityCommands (Entity SQL) which later are transformed to "store" commands (raw SQL). While ExecuteStoreCommand[Async] and ExecuteStoreQuery[Async] work directly with "store" commands (raw SQL).
Update You really need to use the aforementioned "mapping" approach. FunctionImport provides the necessary information for defining the C# method (name, arguments, return type) while TargetFunction provides the information needed to call the db function/procedure.
When writingSQL Server stored procedures, people have different opinions aboutT-SQL codingstyles [1]. Organizations often recommend some coding conventions and enforce theseconventions through code review. A good practice is to create stored procedure templatesso that everyone in an organization uses the same templates to write stored procedures.Some T-SQL development tools, such asMicrosoft SQL Server Management Studio (i.e.,SSMS) and Visual Studio 2019, provide built-in templates; however, each organizationhas specific requirements. Therefore, we want to add custom stored procedure templatesto these tools. We should design templates that others can accept, install, anduse. With this in mind, two questions come up: "What content should a storedprocedure template have?" and "How can we add the template to SSMS and VisualStudio 2019?"
Coding conventions are necessary for successful software development. Many professionalshave contributed their programming experience to the global SQL Server community.Sheldon [1] introduced some excellent T-SQL coding practices. Worthen [2] and Spaghettidba [3]shared their templates. Microsoft SQL Docs also provides some sample stored procedures[4]. We can incorporate all these recourses and produce templates for our organizations.Designing templates is an evolving and collaborative process; we improve these templatesbased on changing technologies and resource knowledge. In this tip, we introducea stored procedure template. We encourage users to test and update the template.
To encourage others to efficiently use this template, we explore a process thatadds the custom stored procedure template to SSMS (v18.6). To add the template toVisual Studio 2019, we also present instructions in a comfortable, step-by-stepformat.
Click on the menu item to bring up the "Specify Values for Template Parameters"dialog, as shown in Figure 3. We assign parameter values inthis dialog. Click on the "OK" button to generate a new stored procedure.The default template is beneficial; at least, we do not need to write a stored procedurefrom scratch.
When we write more stored procedures for an organization, gradually, patternsappear. For example, nearly every stored procedure contains a try-catch block tohandle errors. When a stored procedure implements a series of database changes,all these changes are in one logical operation. We want to commit to all changesonly when all operations are successful; otherwise, we cancel all changes. Whenwe write new stored procedures, we want to follow these patterns. Therefore, wecompile these patterns into a template:
We used the default stored procedure template to create a new stored procedurein the "Object Explorer" pane. We can also use templates through thepane "Template Explorer." If the pane is hidden, we can display thepane by selecting the menu item "View -> Template Explorer," as shownin Figure 4.
The "Template Explorer" pane with the title "Template Browser"exhibits many T-SQL templates. In this text, we call this pane "Template Explorer".As shown in Figure 5, we find the "Create StoredProcedure (New Menu)" under the stored procedure node.
Double-click on the "Create Stored Procedure (New Menu)" node tocreate a new stored procedure using this built-in template. To add the custom templateunder the "Stored Procedure" node, we right-click on the "StoredProcedure" node and select "New -> Template" from the contextmenu, as shown in Figure 6. Click on the "Template"menu item to create a new template with default name "New SQL Server Template".
Copy the custom stored procedure template to the code editor. Save the templateby clicking on the save icon on the toolbar. We can view the location of this templatewhen we move the mouse over the tab header, as shown inFigure 8.Through the "Template Explorer" pane, we can use this custom templatein the same manner as other built-in templates.
To use the custom template when creating a new stored procedure in the "ObjectExplorer," we can modify the template "C:\Program Files (x86)\SQL ServerManagement Studio 18\ Common7\IDE\SqlWorkbenchProjectItems\Sql\Stored Procedure\CreateStored Procedure (New Menu).sql". It worth noting that we should keep a copyof the original file before making any changes. When we follow the steps shown in Figure 1to create a new stored procedure, the new one should use the custom template.
We generated a stored procedure by using the built-in template. Because of thereasons we mentioned in Section 1, we want to use a custom template that looks likethe following code. The procedure name "$safeitemname$" is the reservedtemplate parameter [5]. The template uses the file name as the stored procedurename when we create a stored procedure.
Since we want to create an item template, we select the item template optionand click on the "Next" button. The "Select Item to Export"dialog appears. Select the stored procedure we just created, as shown inFigure 13.
Close the Visual Studio 2019 and relaunch it. Follow the steps illustrated inFigure 9to bring up the "Add New Item" dialog. The dialog should look likeFigure 18.On the left pane, there is a group "Custom Templates." The custom storedprocedure template is in the group. We can use this custom template in the sameway as we use other built-in templates.
We described the process of using built-in templates to create a stored procedure.To add more features into the built-in templates, we introduced a custom storedprocedure template that can handle errors and transactions. Then, we presented step-by-stepinstructions to add custom templates to SSMS and Visual Studio 2019.
NPoco/PetaPoco DAL libraries use strings to provide database optimised queries and these are prone to typing errors which we aren't able to catch during compile time but rather during runtime, when effects can be much more devastating. That's why it would be great if we could somehow avoid these magic string values and just used C# code with string types and not make a mistake. We can't really avoid magic strings when writing direct TSQL but if we write stored procedures, we can do something about it. 2ff7e9595c
Comments