命令对象中的TSQL
命令对象中的TSQL
提问于 2013-08-07 19:40:15
请参阅下面的子例程：
Private Sub UpdateGrade(ByVal studentID As Integer, ByVal grade As String)
Dim objCommand As SqlCommand
Dim objCon As SqlConnection
Dim id As Integer
Dim _ConString As String
Try
_ConString = ConfigurationManager.ConnectionStrings("TestConnection").ToString
objCon = New SqlConnection(_ConString)
objCommand = New SqlCommand("DECLARE @StudentID INT " & _
"DECLARE @Grade char(1) " & _
"SET @Grade = '" & grade & "'" & _
"SET @StudentID = '" & studentID & "'" & _
"If @Grade=1 " & _
"begin " & _
"update Student SET Grade = 'A' WHERE StudentID = @StudentID " & _
"end " & _
"Else If @Grade=2 " & _
"begin " & _
"update Student SET Grade = 'B' WHERE StudentID = @StudentID " & _
"end " & _
"If @Grade=3 " & _
"begin " & _
"update Student SET Grade = 'C' WHERE StudentID = @StudentID " & _
"end " & _
"Else If @Grade=4 " & _
"begin " & _
"update Student SET Grade = 'D' WHERE StudentID = @StudentID " & _
"end")
objCommand.Connection = objCon
objCon.Open()
objCommand.ExecuteNonQuery()
Catch ex As Exception
Throw
Finally
End Try将Command.CommandText设置为TSQL语句有什么问题吗？TSQL看起来如下(表示可实现性)：
DECLARE @StudentID INT
DECLARE @Grade char(1)
SET @Grade =
SET @StudentID =
If @Grade=1
begin
update Student SET Grade = 'A' WHERE StudentID = @StudentID
end
Else If @Grade=2
begin
update Student SET Grade = 'B' WHERE StudentID = @StudentID
end
If @Grade=3
begin
update Student SET Grade = 'C' WHERE StudentID = @StudentID
end
Else If @Grade=4
begin
update Student SET Grade = 'D' WHERE StudentID = @StudentID
end 在while循环中调用UpdateGrade。同时循环，循环通过500万学生。请注意，在这方面的实时系统更复杂，所以我提供了上面的代码作为说明目的。
我在过去曾遇到过从.NET代码调用存储过程的问题，XACT_ABORT解决了这些问题。
回答 1
Stack Overflow用户
回答已采纳
发布于 2013-08-07 19:45:50
您应该将命令的CommandText设置为TSQL语句，您已经在这样做了。不过，我强烈反对这一点：
"DECLARE @StudentID INT " & _
"DECLARE @Grade char(1) " & _
"SET @Grade = '" & grade & "'" & _
"SET @StudentID = '" & studentID & "'" & _这句话应该去掉，只要(对不起C#的分号；习惯的力量)：
objCommand.Parameters.AddWithValue("StudentID", studentID);
objCommand.Parameters.AddWithValue("Grade", grade);这样就不受SQL注入的影响。
另外，也可能是一个select case，即(在示例中使用C# )：
objCommand.CommandText = @"
update Student set Grade = select case @Grade
when 1 then 'A' when 2 then 'B'
when 3 then 'C' when 4 then 'C'
else Grade end
where StudentId = @StudentID";
objCommand.Parameters.AddWithValue("StudentID", studentID);
objCommand.Parameters.AddWithValue("Grade", grade);页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/18112355
复制相关文章
相似问题
腾讯云开发者
