avue-crud使⽤_创建和使⽤CRUD存储过程
avue-crud 使⽤
The Data Storage Layer consists of SQL Server and databa 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 reprents an acronym for the databa operations C C reate, R R ead, U U pdate, and D D elete.
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 the SQL statements and us only the EXECUTE statement on stored procedures.
数据存储层由SQL Server和数据库对象组成。 数据访问层是⽤诸如C＃，VB，VB.Net，Java，PHP等语⾔编写的客户端代码。数据访问层与数据存储层进⾏通信以执⾏CRUD操作。 CRUD代表数据库操作C C reate， R R ead， U U pdate和D D elete的⾸字母缩写。 两层之间的通信可以采⽤临时SQL语句的形式，例如INSERT，SELECT，UPDATE和DELETE。 存储过程⽅法放弃了这些SQL语句，仅对存储过程使⽤EXECUTE语句。
desirable为什么选择CRUD？ (Why CRUD?)
There are veral reasons for using stored procedures to perform CRUD operations instead of ad-hoc SQL statements:
使⽤存储过程⽽不是即席SQL语句来执⾏CRUD操作的原因有很多：arletta
性能 (Performance)
After the first execution of a stored procedure, the procedures execution plan is stored in SQL Server’s procedure cache and reud 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 reu 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 ca 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 databa and nothing but stored procedure invocations in the client application. Using stored procedures to encapsulate the databa access is also an effective way to decrea databa coupling.
通过从应⽤程序代码中删除SQL语句，所有SQL都可以保留在数据库中，⽽客户端应⽤程序中的存储过程调⽤除外。 使⽤存储过程封装数据库访问也是减少数据库耦合的有效⽅法。
防⽌SQL注⼊攻击 (Prevents SQL injection attacks)
Using stored procedures instead of string concatenation to build dynamic queries from ur input data for all SQL Statements reduces the chance of SQL injection attacks becau 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 databa including:
有⼀些通⽤的命名约定可以使CRUD过程与数据库中的其他存储过程有所不同，包括：
The prefix should differ from the prefix ud for other ur 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 databa schema after adding CRUD procedures, first identify the databa entity for which the CRUD methods will be implemented. We’ll u 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 C C reate procedure performs the INSERT statement which will create a new record. It has one parameter for every
column in the table:
C reate过程执⾏INSERT语句，该语句将创建⼀个新记录。 该表中的每⼀列都有⼀个参数：
GO
CREATE PROCEDURE usp_CustomerCreate
@FirstName varchar(20),
@LastName varchar(20),
@Email varchar(20),
@PhoneNumber int
brandedAS
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 inrted 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 R R ead procedure reads the table records bad on the primary key specified in the input parameter:
R ead过程根据输⼊参数中指定的主键读取表记录：
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 U U pdate procedure performs an UPDATE statement on the table bad on the primary key for a record specified in the WHERE clau of the statement. Same as the Create procedure it has one parameter for every column in the table:
U pdate过程基于该语句的WHERE⼦句中指定的记录的主键在表上执⾏UPDATE语句。 与创建过程相同，表中的每⼀列都有⼀个参数：ebra
IF OBJECT_ID('cusp_CustomerUpdate') IS NOT NULL
BEGIN
DROP PROC cusp_CustomerUpdate
END
GO
CREATE PROC cusp_CustomerUpdate
disneyenglish
@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 D D elete procedure deletes a row specified in the WHERE clau:
D elete过程删除WHERE⼦句中指定的⾏：
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 choo the Add->New Item option:
右键单击“解决⽅案资源管理器”窗格中的应⽤程序⽂件夹，然后选择“添加”->“新建项⽬”选项：
Select DataSet from the Add New Item window:
从添加新项窗⼝中选择数据集：
Right click in the opened window and choo the Add->TableAdapter option:
在打开的窗⼝中右键单击，然后选择Add-> TableAdapter选项：
In the TableAdapter Configuration Wizard choo the data connection and in the next window choo the Create new
Create new stored procedures option:
stored procedures
创建新的存储过程”选项：
在“ TableAdapter配置向导”中，选择数据连接，然后在下⼀个窗⼝中选择“ 创建新的存储过程”
In the next window enter a SELECT statement for the R R ead stored procedure:
在下⼀个窗⼝中，为R R ead存储过程输⼊SELECT语句：
Delete statement, the U optimistic concurrency
U optimistic concurrency,
Generate Inrt, Update
In the Advanced Options lect the Generate Inrt
Update, and Delete statement
Refresh the data table options:
and the Refresh the data table
使⽤开放式并发 ”和“ 刷新数据表”
刷新数据表”选项：
删除”语句 ，“ 使⽤开放式并发
傲骨贤妻 第四季⽣成插⼊” ，“ 更新
更新 ”和“ 删除”语句
在“⾼级选项”中，选择“ ⽣成插⼊”
Update, and Delete statement
Delete statement option generates Inrt, Update, and Delete statements bad on the The Generate Inrt
Generate Inrt, Update
specified Select statement
删除语句选项基于指定的选择语句⽣成插⼊，更新和删除语句
更新和删除语句
⽣成插⼊
⽣成插⼊ ， 更新
U optimistic concurrency option does not lock a record when reading it and becau there is no locking of records The U optimistic concurrency
and therefore no additional rver resources requirements using optimistic concurrency may improve performance. Also, connections to the rver are can rve a larger number of clients in less time becau a persistent connection to the databa rver is not required in order to maintain record locks.
使⽤乐观并发
使⽤乐观并发选项在读取记录时不会将其锁定，并且因为没有记录的锁定，因此使⽤乐观并发的其他服务器资源要求也不会提⾼性能。 同样，与服务器的连接可以在更短的时间内为⼤量的客户端提供服务，因为为了维护记录锁定，不需要与数据库服务器的持久连接。
