vb.net 在 ssis 脚本任务中执行 sql 不起作用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18128792/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
executing sql inside ssis script task is not working
提问by user1254579
Trying to execute the sql statement inside the vb.net (script task ssis).For this created the connections,requsted open connection and executed and closed(as shown in the below code).The value of a variable (which holds the sql result set) has to be returned a correct count,instead it returned -1 all the times ...any idea y does it so?Seems to be the query is not executing?
尝试执行vb.net中的sql语句（脚本任务ssis）。为此创建了连接，要求打开连接并执行并关闭（如下代码所示）。变量的值（保存sql结果set) 必须返回一个正确的计数，而不是它一直返回 -1 ......你知道吗？似乎是查询没有执行？
how to get the correct count value using this below vb codes(please have a look) in ssis script task?
如何在 ssis 脚本任务中使用下面的 vb 代码（请看一看）获得正确的计数值？
Public Sub Main()
公共子主（）
Dim fireAgain As Boolean = True
Dim rowsAffected As Integer
Dim sqlConn As System.Data.SqlClient.SqlConnection
Dim sqlComm As System.Data.SqlClient.SqlCommand
Dim cm As ConnectionManager = Dts.Connections("cnn") ''Retrive the reference to the managed Connections
'' Request an open connection
sqlConn = cm.AcquireConnection(Dts.Transaction)
Dts.Events.FireInformation(0, "", "Connection is: " + sqlConn.State.ToString(), "", 0, fireAgain)
''Do your work
sqlComm = New System.Data.SqlClient.SqlCommand("SELECT COUNT(*) AS CNT FROM [Table_1]", sqlConn)
rowsAffected = sqlComm.ExecuteNonQuery()
MsgBox(rowsAffected.ToString()) '''' the value of variable is -1 ???
''Inform SSIS you're done your work
cm.ReleaseConnection(sqlConn)
Dts.Events.FireInformation(0, "", rowsAffected.ToString() + " rows updated.", "", 0, fireAgain)
End Sub
Thanks
谢谢
回答by mr.Reband
When you use ExecuteNonQuery, you are saying that you do not require any results back from the query.
当您使用 时ExecuteNonQuery，您是说您不需要从查询返回任何结果。
To read the result from your query, use ExecuteScalar:
要从查询中读取结果，请使用ExecuteScalar：
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executescalar.aspx
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executescalar.aspx
If your query returns more than one cell, use ExecuteReaderand a SQLDataReaderobject:
如果您的查询返回多个单元格，请使用ExecuteReader一个SQLDataReader对象：
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.aspx
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.aspx
回答by bastos.sergio
ExecuteNonQueryreturns the number of affected rows by the query. In the case of the COUNT function it always returns -1.
ExecuteNonQuery返回查询受影响的行数。对于 COUNT 函数，它总是返回 -1。
For what you are trying to achieve, you should be using sqlComm.ExecuteScalar()which returns the value of the first row/column from the resultset.
对于您想要实现的目标，您应该使用sqlComm.ExecuteScalar()which 返回结果集中第一行/列的值。
