Why use Store procedure
第一:提高性能
当存储过程被创建,它要经过以下几步,第一步,它所包含的T-SQL语句将被分析和解析,并被存储.当第一次被执行时,它将被调出并被优化.SQLServer会根据statistics自动选择相应的优化策略.
此后查询计划将被存储在高速缓存中,以利于将来使用.由于不用被重新编译,所以可以大大提高效率.
第二:减少网络流量
你可能使用T-SQL语句来对表执行插入操作.但是如果我们创建一个存储过程来进行这样操作的话,每次插入的时候只要传输存储过程名,参数和这些参数的数值,当这些操作非常频繁时我们将会发现使用存储过程可以减少额外的网络传输.这在我们使用Internet时进行传输时非常有用.
INSERT INTO EmployeeTerritories (EmployeeID, TerritoryID) VALUES (3,12345) Ins_EmployeeTerritories @empId=3,@terrId=12345
What if an image data type was being uploaded or downloaded?
Anything that is of binary data type, such as images or sounds, and so on, is sent as binary values. These are converted to character strings, and this will double the size of the ad-hoc query that we are sending, when using T-SQL inline.
第一个语句有74个字符,第二个有46个字符,相比而言网络传输量减少了37.84%,如果我们这个插入语句中包括有更多要插入数据的列,并且每天被执行10,000次,将会学杂费280K左右的带宽,
第三:减少注入式攻击
3.1易受注入式攻击的代码:
// DANGER! User input used to generate database query
string sql = String.Format ("select count (*) " +
"from users where username=\
"(password as varbinary)=cast (\
varbinary)", username, password);
SqlCommand command = new SqlCommand (sql, connection);
int count = (int) command.ExecuteScalar ();
3.2优化过的代码
下面的代码被注入式攻击的机率要少些
// BETTER: Input passed to parameterized command
SqlCommand command = new SqlCommand
("select count (*) from users where " +
"username=@username and cast (password as " +
"varbinary)=cast (@password as varbinary)",
connection);
command.Parameters.Add ("@username", SqlDbType.VarChar).Value = username;
command.Parameters.Add ("@password", SqlDbType.VarChar).Value = password;
int count = (int) command.ExecuteScalar ();
3.3使用存储过程来减少注入攻击
通过存储过程来执行效果会更好
// BEST: Input passed to stored procedure
SqlCommand command = new SqlCommand ("proc_IsUserValid", connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add ("@username", SqlDbType.VarChar).Value = username;
command.Parameters.Add ("@password", SqlDbType.VarChar).Value = password;
command.Parameters.Add ("@return", SqlDbType.Int).Direction =
ParameterDirection.ReturnValue;
int count = (int) command.ExecuteScalar ();
所以我们应该尽量在我们的应用系统中使用存储过程.
