Sql输出参数 [英] Sql Output Parameters

问题描述

我有以下问题。我想通过我的c＃代码访问一些SQL输出参数

。



程序如下：

Hi,
i have the following problem. I want to access some SQL output parameter
via my c# code.

The procedure looks like:

CREATE PROCEDURE sp_DoSome

@VALUE int output,@OTHER nvarchar(MAX) output

AS
BEGIN
	
SELECT @VALUE =  -198;
SELECT @OTHER = 'Works';

END
GO

和访问值的代码：

And the code to access the values:

 using (SqlCommand com = new SqlCommand 
{ CommandType = CommandType.StoredProcedure, 
  CommandText = "sp_AskForUpdate", 
  Connection = _connection })
                 
    {
      com.Parameters.AddWithValue("@VALUE", SqlDbType.Int);
      com.Parameters.AddWithValue("@OTHER", SqlDbType.NVarChar);
      com.Parameters["@VALUE"].Direction = ParameterDirection.Output;
      com.Parameters["@OTHER"].Direction = ParameterDirection.Output;
      _connection.Open();
      com.ExecuteNonQuery();
      temp = com.Parameters["@VALUE"].Value.ToString() + com.Parameters["@OTHER"].Value;
      _connection.Close();
                }

....goes on

只需使用int''工作良好。但在所有其他情况下产生异常

像这样（nvarchar）：

Doing this just with int''s works fine. But in all other cases generates an exception
like this (nvarchar):

04/11/2013 11:10:00 Error converting data type nvarchar(max) to int.Void OnError(System.Data.SqlClient.SqlException, Boolean, System.Action`1[System.Action])System.Data.SqlClient.SqlException (0x80131904): Error converting data type nvarchar(max) to int.
   bei System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   bei System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   bei System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   bei System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   bei System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite)
   bei System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
   bei System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
   bei System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   bei JuloGroupService.Service1.AskForUpdate(Int32 companyId, Int32 miaId, Boolean& free) in C:\Users\schafmann\Desktop\visual studio 2010\Projects\JuloGroupService\JuloGroupService\Service1.svc.cs:Zeile 331.
ClientConnectionId:d955c0e9-079a-452c-8538-005e91776d3a

我不明白为什么会产生转换错误???



感谢您的帮助

Andree

I dont understand why this generates a converting error???

Thanks for any help
Andree

推荐答案

现在我有以这种方式解决了问题：

For now i have solved the Problem in this way:

 using (SqlCommand com = new SqlCommand 
{ CommandType = CommandType.StoredProcedure, 
  CommandText = "sp_AskForUpdate", 
  Connection = _connection })
                 
    {
      com.Parameters.AddWithValue("@VALUE", SqlDbType.Int);
      com.Parameters.AddWithValue("@OTHER", SqlDbType.NVarChar);
      com.Parameters["@VALUE"].Direction = ParameterDirection.Output;
      com.Parameters["@OTHER"].Direction = ParameterDirection.Output;

      //Adding this to my code has solved the problem
      com.Parameters["@OTHER"].Size = 255;
      com.Parameters["@OTHER"].Value = " ";

      _connection.Open();
      com.ExecuteNonQuery();
      temp = com.Parameters["@VALUE"].Value.ToString() + com.Parameters["@OTHER"].Value;
      _connection.Close();
                }

这篇关于Sql输出参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！