If you’ve ever worked with a database, you’ve likely worked with CRUD operations. CRUD operations are often used with SQL, a topic we’ve covered in depth (see this article, this one, and this one for some of our recent SQL tips and tricks). Since SQL is pretty prominent in the development community, it’s crucial for developers to understand how CRUD operations work. So, this article is meant khổng lồ bring you up lớn speed (if you’re not already) on CRUD operations.

Bạn đang xem: Crud

The Definition of CRUD

Within computer programming, the acronym CRUD stands for create, read, update và delete. These are the four basic functions of persistent storage. Also, each letter in the acronym can refer lớn all functions executed in relational database applications và mapped to lớn a standard HTTP method, SQL statement or DDS operation.

It can also describe user-interface conventions that allow viewing, searching & modifying information through computer-based forms and reports. In essence, entities are read, created, updated and deleted. Those same entities can be modified by taking the data from a service and changing the setting properties before sending the data back khổng lồ the service for an update. Plus, CRUD is data-oriented and the standardized use of HTTP action verbs.

*

Screenshot Source: Oracle

Most applications have some size of CRUD functionality. In fact, every programmer has had to khuyến mãi with CRUD at some point. Not to lớn mention, a CRUD application is one that utilizes forms khổng lồ retrieve và return data from a database.

The first reference lớn CRUD operations came from Haim Kilov in 1990 in an article titled, “From semantic khổng lồ object-oriented data modeling.” However, the term was first made popular by James Martin’s 1983 book, Managing the Data-base Environment. Here’s a breakdown:

CREATE procedures: Performs the INSERT statement to lớn create a new record.READ procedures: Reads the table records based on the primary keynoted within the đầu vào parameter.UPDATE procedures: Executes an UPDATE statement on the table based on the specified primary key for a record within the WHERE clause of the statement.DELETE procedures: Deletes a specified row in the WHERE clause.

How CRUD Works: Executing Operations & Examples

Based on the requirements of a system, varying user may have different CRUD cycles. A customer may use CRUD to lớn create an account and access that tài khoản when returning lớn a particular site. The user may then update personal data or change billing information. On the other hand, an operations manager might create hàng hóa records, then hotline them when needed or modify line items.

During the website 2.0 era, CRUD operations were at the foundation of most dynamic websites. However, you should differentiate CRUD from the HTTP action verbs. For example, if you want lớn create a new record you should use “POST.” lớn update a record, you would use “PUT” or “PATCH.” If you wanted khổng lồ delete a record, you would use “DELETE.” Through CRUD, users and administrators had the access rights to lớn edit, delete, create or browse online records.

An application designer has many options for executing CRUD operations. One of the most efficient of choices is to create a set of stored procedures in SQL to lớn execute operations. With regard lớn CRUD stored procedures, here are a few common naming conventions:

The procedure name should kết thúc with the implemented name of the CRUD operation. The prefix should not be the same as the prefix used for other user-defined stored procedures.CRUD procedures for the same table will be grouped together if you use the table name after the prefix.After adding CRUD procedures, you can update the database schema by identifying the database entity where CRUD operations will be implemented.

Here’s an example from Tutlane, illustrating an asp.net MVC 4 CRUD operation using ADO.NET:

*

You can check out a few more examples at the following resources:

 

Benefits of CRUD

Instead of using ad-hoc SQL statements, many programmers prefer khổng lồ use CRUD because of its performance. When a stored procedure is first executed, the execution plan is stored in SQL Server’s procedure cache and reused for all applications of the stored procedure.

When a SQL statement is executed in SQL Server, the relational engine searches the procedure cache lớn ensure an existing execution plan for that particular SQL statement is available và uses the current plan to decrease the need for optimization, parsing & recompiling steps for the SQL statement.

If an execution plan is not available, then the SQL server will create a new execution plan for the query. Moreover, when you remove SQL statements from the application code, all the SQL can be kept in the database while only stored procedure invocations are in the client application. When you use stored procedures, it helps khổng lồ decrease database coupling.

Furthermore, using CRUD operations helps to prevent SQL injection attacks. By utilizing stored procedures instead of string concatenation lớn build dynamic queries from user input data for all SQL Statements means that everything placed into a parameter gets quoted.

CRUD Prevents Casual Browsing và Changes

Application roles are a SQL hệ thống technique that lets code switch identities without informing the user. To lớn work with ad hoc SQL statements, users must have the required permissions on the database tables. Once permission is granted, users can read và manipulate data in applications such as Excel, Word, và others. Users can even bypass the application’s business rules.

Yet, this is an unwanted situation that can be prevented through the Application Role. Through integrated security for database access và an Application Role, these types of loopholes can be closed. CRUD comes in since Application roles are added to the database using a stored procedure. It is also implemented by granting permission khổng lồ execute the CRUD stored procedures & revoking direct access lớn the tables.

Once an Application Role is added, permissions are assigned, and a password is given. The password is also coded into the application, making it difficult khổng lồ change. For manipulating data, CRUD is the method to use.

Xem thêm: Cách Dùng Stalkscan - Hướng Dẫn Sử Dụng Stalkscan

Additional Resources & Tutorials on CRUD Operations

For more information on CRUD operations, including tutorials on using and executing CRUD operations effectively with different languages, visit the following links:

Try aryannations88.com’s miễn phí code profiler, Prefix, khổng lồ write better code on your workstation. Prefix works with .NET, Java, PHP, Node.js, Ruby, and Python.