avue-crud 使用

The Data Storage Layer consists of SQL Server and database objects. The Data Access Layer is client code written in a language such as C#, VB, VB.Net, Java, PHP etc. The Data Access Layer communicates with the Data Storage Layer to perform CRUD operations. CRUD represents an acronym for the database operations Create, Read, Update, and Delete. The communication between two layers could be in the form of ad hoc SQL statements such as INSERT, SELECT, UPDATE, and DELETE. The stored procedures approach foregoes these SQL statements and uses only the EXECUTE statement on stored procedures.

数据存储层由SQL Server和数据库对象组成。 数据访问层是用诸如C＃，VB，VB.Net，Java，PHP等语言编写的客户端代码。数据访问层与数据存储层进行通信以执行CRUD操作。 CRUD代表数据库操作C reate， R ead， U pdate和D elete的首字母缩写。 两层之间的通信可以采用临时SQL语句的形式，例如INSERT，SELECT，UPDATE和DELETE。 存储过程方法放弃了这些SQL语句，仅对存储过程使用EXECUTE语句。

为什么选择CRUD？ (Why CRUD?)

There are several reasons for using stored procedures to perform CRUD operations instead of ad-hoc SQL statements:

使用存储过程而不是即席SQL语句来执行CRUD操作的原因有很多：

性能 (Performance)

After the first execution of a stored procedure, the procedures execution plan is stored in SQL Server’s procedure cache and reused for all following invocations of the stored procedure.

在第一次执行存储过程之后，过程执行计划将存储在SQL Server的过程高速缓存中，并在以后对存储过程的所有调用中重复使用。

When any SQL statement is executed in SQL Server, the relational engine will first look through the procedure cache to verify that an existing execution plan for the specified SQL statement exists and reuse any existing plan, saving the overhead of parsing, optimization, and recompiling steps for the SQL statement. If the execution plan doesn’t exist which is the case with the ad-hoc SQL statements, SQL Server will generate a new execution plan for the query.

在SQL Server中执行任何SQL语句时，关系引擎将首先浏览过程高速缓存以验证是否存在用于指定SQL语句的现有执行计划，并重用任何现有计划，从而节省了解析，优化和重新编译步骤的开销用于SQL语句。 如果临时SQL语句不存在执行计划，则SQL Server将为查询生成一个新的执行计划。

将SQL代码与应用程序的其他层解耦 (Decouples the SQL code from the other layers of the application)

By removing the SQL statements from the application code, all the SQL can be kept in the database and nothing but stored procedure invocations in the client application. Using stored procedures to encapsulate the database access is also an effective way to decrease database coupling.

通过从应用程序代码中删除SQL语句，所有SQL都可以保留在数据库中，而客户端应用程序中的存储过程调用除外。 使用存储过程封装数据库访问也是减少数据库耦合的有效方法。

防止SQL注入攻击 (Prevents SQL injection attacks)

Using stored procedures instead of string concatenation to build dynamic queries from user input data for all SQL Statements reduces the chance of SQL injection attacks because everything placed into a parameter gets quoted in the process.

使用存储过程而不是字符串连接来从所有SQL语句的用户输入数据中构建动态查询可减少SQL注入攻击的可能性，因为在该过程中引用参数的所有内容均被引用。

CRUD存储过程 (CRUD stored procedures)

There are some common naming conventions to differ CRUD procedures from other stored procedures in the database including:

有一些通用的命名约定可以使CRUD过程与数据库中的其他存储过程有所不同，包括：

• The prefix should differ from the prefix used for other user defined stored procedures
  该前缀应不同于用于其他用户定义的存储过程的前缀
• Using the table name after the prefix insures that the CRUD procedures for the same table are grouped together
  在前缀之后使用表名称可确保同一表的CRUD过程分组在一起
• The procedure name should end with the name of the CRUD operation that it implements
  过程名称应以其实现的CRUD操作的名称结尾

To update the database schema after adding CRUD procedures, first identify the database entity for which the CRUD methods will be implemented. We’ll use a table Customer to show the implementation of the CRUD operations using the stored procedures:

要在添加CRUD过程之后更新数据库架构，请首先标识将为其实施CRUD方法的数据库实体。 我们将使用客户表来显示使用存储过程的CRUD操作的实现：

CREATE TABLE [dbo].[Customer](
       [CustomerID] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,
	   [FirstName] [varchar](20) NULL,
       [LastName] [varchar](20) NULL,
       [Email] [varchar](20) NULL,
       [PhoneNumber] [int] NULL
 
)    

The CRUD operations are implemented by four stored procedures:

CRUD操作由四个存储过程实现：

创建程序 (CREATE procedures)

The Create procedure performs the INSERT statement which will create a new record. It has one parameter for every column in the table:

C reate过程执行INSERT语句，该语句将创建一个新记录。 该表中的每一列都有一个参数：

IF OBJECT_ID('cusp_CustomerCreate') IS NOT NULL
BEGIN 
DROP PROC usp_CustomerCreate 
END
GO
CREATE PROCEDURE usp_CustomerCreate
	   @FirstName varchar(20),
	   @LastName varchar(20),
	   @Email	varchar(20),
	   @PhoneNumber int
	 
AS
BEGIN
INSERT INTO Customer  (
	   FirstName,
	   LastName,
	   Email,
	   PhoneNumber)
    VALUES (
	   @FirstName,
	   @LastName,
	   @Email,
	   @PhoneNumber)
 
SET @CustomerID = SCOPE_IDENTITY()
 
SELECT 
	   FirstName = @FirstName,
	   LastName = @LastName,
	   Email	= @Email,
	   PhoneNumber =@PhoneNumber
FROM Customer 
WHERE  CustomerID = @CustomerID
END

The line SET @CustomerID = SCOPE_IDENTITY() captures the identity value. The SCOPE_IDENTITY() function returns the last identity value inserted into an identity column in the same scope (a stored procedure, trigger, function, or batch). Two statements are in the same scope if they are in the same stored procedure, function, or batch.

SET @CustomerID = SCOPE_IDENTITY（）行捕获身份值。 SCOPE_IDENTITY（）函数返回插入到同一作用域（存储过程，触发器，函数或批处理）的标识列中的最后一个标识值。 如果两个语句位于同一存储过程，函数或批处理中，则它们属于同一范围。

阅读程序 (READ procedures)

The Read procedure reads the table records based on the primary key specified in the input parameter:

R ead过程根据输入参数中指定的主键读取表记录：

IF OBJECT_ID('cusp_CustomerRead') IS NOT NULL
BEGIN 
    DROP PROC cusp_CustomerRead
END 
GO
CREATE PROC cusp_CustomerRead
    @CustomerID int
AS 
BEGIN 
 
    SELECT CustomerID, FirstName, LastName, Email, PhoneNumber
    FROM   Customer  
    WHERE  (CustomerID = @CustomerID) 
END
GO

更新程序 (UPDATE procedures)

The Update procedure performs an UPDATE statement on the table based on the primary key for a record specified in the WHERE clause of the statement. Same as the Create procedure it has one parameter for every column in the table:

U pdate过程基于该语句的WHERE子句中指定的记录的主键在表上执行UPDATE语句。 与创建过程相同，表中的每一列都有一个参数：

IF OBJECT_ID('cusp_CustomerUpdate') IS NOT NULL
BEGIN 
DROP PROC cusp_CustomerUpdate
END 
GO
CREATE PROC cusp_CustomerUpdate
    @CustomerID int,
    @FirstName varchar(20),
    @LastName varchar(20),
    @Email varchar(20),
    @PhoneNumber int
  
AS 
BEGIN 
 
UPDATE Customer
SET  FirstName = @FirstName,
     LastName = @LastName, 
     Email = @Email,
     PhoneNumber = @PhoneNumber
WHERE  CustomerID = @CustomerID
END
GO

删除程序 (DELETE procedures)

The Delete procedure deletes a row specified in the WHERE clause:

D elete过程删除WHERE子句中指定的行：

IF OBJECT_ID('cusp_CustomerDelete') IS NOT NULL
BEGIN 
DROP PROC cusp_CustomerDelete
END 
GO
CREATE PROC cusp_CustomerDelete 
    @CustomerID int
AS 
BEGIN 
DELETE
FROM   Customer
WHERE  CustomerID = @CustomerID
 
END
GO

使用Visual Studio生成CRUD过程 (Generating CRUD procedures using Visual Studio)

Right click on the application folder in the Solution Explorer pane and choose the Add->New Item option:

右键单击“解决方案资源管理器”窗格中的应用程序文件夹，然后选择“添加”->“新建项目”选项：

Select DataSet from the Add New Item window:

从添加新项窗口中选择数据集：

Right click in the opened window and choose the Add->TableAdapter option:

在打开的窗口中右键单击，然后选择Add-> TableAdapter选项：

In the TableAdapter Configuration Wizard choose the data connection and in the next window choose the Create new stored procedures option:

在“ TableAdapter配置向导”中，选择数据连接，然后在下一个窗口中选择“ 创建新的存储过程”选项：

In the next window enter a SELECT statement for the Read stored procedure:

在下一个窗口中，为R ead存储过程输入SELECT语句：

In the Advanced Options select the Generate Insert, Update, and Delete statement, the Use optimistic concurrency, and the Refresh the data table options:

在“高级选项”中，选择“ 生成插入” ，“ 更新 ”和“ 删除”语句 ，“ 使用开放式并发 ”和“ 刷新数据表”选项：

The Generate Insert, Update, and Delete statement option generates Insert, Update, and Delete statements based on the specified Select statement

生成插入 ， 更新和删除语句选项基于指定的选择语句生成插入，更新和删除语句

The Use optimistic concurrency option does not lock a record when reading it and because there is no locking of records and therefore no additional server resources requirements using optimistic concurrency may improve performance. Also, connections to the server are can serve a larger number of clients in less time because a persistent connection to the database server is not required in order to maintain record locks.

使用乐观并发选项在读取记录时不会将其锁定，并且因为没有记录的锁定，因此使用乐观并发的其他服务器资源要求也不会提高性能。 同样，与服务器的连接可以在更短的时间内为大量的客户端提供服务，因为为了维护记录锁定，不需要与数据库服务器的持久连接。

In the next window name the stored procedures and click the Finish button:

在下一个窗口中命名存储过程，然后单击“完成”按钮：

Use the Preview SQL Script button to preview the script and use it for your own procedures:

使用“预览SQL脚本”按钮预览脚本并将其用于您自己的过程：

Here is code for the cusp_CustomerCreate procedure opened in SSMS:

这是在SSMS中打开的cusp_CustomerCreate过程的代码：

/****** Object:  StoredProcedure [dbo].[cusp_CustomerCreate]    
Script Date: 26-Mar-14 7:17:03 PM ******/
 
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE PROCEDURE [dbo].[cusp_CustomerCreate]
(
	@FirstName varchar(20),
	@LastName varchar(20),
	@Email varchar(20),
	@PhoneNumber int
)
AS
SET NOCOUNT OFF;
INSERT INTO [Customer] 
([FirstName], [LastName], [Email], [PhoneNumber]) 
VALUES (@FirstName, @LastName, @Email, @PhoneNumber);
	
SELECT CustomerID, FirstName, LastName, Email, PhoneNumber 
FROM Customer 
WHERE (CustomerID = SCOPE_IDENTITY())
GO

翻译自: https://www.sqlshack.com/creating-using-crud-stored-procedures/

avue-crud 使用