Shulou(Shulou.com)06/01 Report--

Most database systems are based on the four simplest data operations, called CRUD. The acronym stands for create, read, update, and delete.

When developing and managing a database, you can use CRUD stored procedures to perform all data-related tasks. The advantage of this stored procedure is that once it is written once, it can be reused as many times as needed without having to write new code each time. This is a major improvement on temporary SQL statements, which should be rewritten each time they are used.

Let's take a closer look at each CRUD stored procedure.

Study CRUD stored procedures carefully

Before we continue, let's talk about the naming of CRUD stored procedures. It is usually a good practice to name them that each procedure contains the name of the table they apply and ends with the name of the operation they perform. In this way, all procedures written for the same table will be grouped together and easier to search.

However, it's not mandatory, and you can stick to any naming pattern you like.

Now, let's look at the first type of process.

Create a program

These will execute the INSERT statement to create a new record. Such a procedure should accept a parameter for each column of the table.

IF OBJECT_ID ('Sales.usp_Currency_Insert') IS NOT NULL BEGIN DROP PROC Sales.usp_Currency_Insert END GO CREATE PROC Sales.usp_Currency_Insert @ CurrencyCode NCHAR (3), @ Name dbo.Name, @ ModifiedDate datetime AS SET NOCOUNT ON SET XACT_ABORT ON BEGIN TRAN INSERT INTO Sales.Currency (CurrencyCode, Name, ModifiedDate) SELECT @ CurrencyCode, @ Name, @ ModifiedDate / *-- Begin Return row code block SELECT CurrencyCode, Name ModifiedDateFROM Sales.CurrencyWHERE CurrencyCode = @ CurrencyCode AND Name = @ Name AND ModifiedDate = @ ModifiedDate-- End Return row code block * / COMMITGO

Reader program

The READ process retrieves the table record based on the primary key provided in the input parameters.

IF OBJECT_ID ('Sales.usp_Currency_Select') IS NOT NULL BEGIN DROP PROC Sales.usp_Currency_Select END GO CREATE PROC Sales.usp_Currency_Select @ CurrencyCode NCHAR (3), @ Name dbo.Name AS SET NOCOUNT ON SET XACT_ABORT ON BEGIN TRAN SELECT CurrencyCode, Name, ModifiedDate FROM Sales.CurrencyWHERE CurrencyCode = @ CurrencyCode AND Name = @ Name COMMITGO

Update program

These procedures execute UPDATE statements on the table using the primary key of the record specified in the WHERE clause. Just like the CREATE procedure, it accepts one parameter for each table column.

IF OBJECT_ID ('Sales.usp_Currency_Update') IS NOT NULL BEGIN DROP PROC Sales.usp_Currency_Update END GO CREATE PROC Sales.usp_Currency_Update @ CurrencyCode NCHAR (3), @ Name dbo.Name, @ ModifiedDate datetime AS SET NOCOUNT ON SET XACT_ABORT ON BEGIN TRAN UPDATE Sales.CurrencySET ModifiedDate = @ ModifiedDateWHERE CurrencyCode = @ CurrencyCode AND Name = @ Name / *-- Begin Return row code block SELECT ModifiedDateFROM Sales.CurrencyWHERE CurrencyCode = @ CurrencyCode AND Name = @ Name-- End Return row code block * / COMMITGO

Delete program

This procedure deletes the rows provided in the WHERE clause of the statement.

IF OBJECT_ID ('Sales.usp_Currency_Delete') IS NOT NULL BEGIN DROP PROC Sales.usp_Currency_Delete END GO CREATE PROC Sales.usp_Currency_Delete @ CurrencyCode NCHAR (3), @ Name dbo.Name AS SET NOCOUNT ON SET XACT_ABORT ON BEGIN TRAN DELETEFROM Sales.CurrencyWHERE CurrencyCode = @ CurrencyCode AND Name = @ Name COMMITGO

Use dbForge SQL Complete to generate CRUD procedures

With dbForge SQL Complete plug-ins available in both SSMS and Visual Studio, we can generate CRUD procedures with just a few clicks and have a variety of options that allow you to configure how these procedures are generated. In this article, we will use SSMS to demonstrate the functionality of SQL Complete.

To generate a CRUD procedure for a table, right-click the table, go to the SQL Complete menu, and click Script Table as CRUD:

When this is done, a new SQL file will be opened. Here, you can see all the CRUD operations for the table.

Change CRUD build settings

To configure how dbForge SQL Complete generates CRUD, you first need to go to the SQL Complete menu at the top of the window, and then click options:

The result will open in the options window, go to the CRUD menu, and then click General:

In this tab, you can specify which procedures are included in the CRUD generation process, and specify the order of which columns to use-alphabetically or sequentially.

You can also configure each procedure individually by selecting the appropriate option in the CRUD menu. First, you can manually change the name of the generated procedure:

Next, each process has a unique option.

For SELECT, if the input parameter is null, there is a Return all data check box.

For INSERT, you can specify whether to return the inserted row when it is finished.

A similar option is available for UPDATE-it allows you to choose whether or not to return updated rows.

There are no other unique options for DELETE.

Finally, for each process, there is a Code template section. In this section, you can change how the code for the specified procedure is generated. In the code template, parameters are provided in the format $name $(for example, $schema $or $columns $). By changing these parameters, you can modify the code of the generated process.

Conclusion

As you can see, using CRUD commands to implement and manage data manipulation is preferable to using temporary SQL statements, and this can be easily done with the dbForge SQL Complete plug-in.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.