计数SQL Server记录并通过输出参数返回结果

Question

我想从多个表中计数记录并将结果返回给输出参数，以便我可以在Visual Studio C＃中使用它们。

下面是T-SQL和C＃代码：

T-SQL代码：

ALTER PROCEDURE CountRecordsForUpdates 
    @AreaOfLaw AS numeric OUTPUT, 
    @SubjectMatter AS numeric OUTPUT, 
    @Principles AS numeric OUTPUT, 
    @Judges AS numeric OUTPUT, 
    @Courts AS numeric OUTPUT 
AS 
    DECLARE @AreaOfLawOut AS numeric 
    DECLARE @SubjectMatterOut AS numeric 
    DECLARE @PrinciplesOut AS numeric 
    DECLARE @JudgesOut AS numeric 
    DECLARE @CourtsOut AS numeric 
    DECLARE @CasesOut AS numeric 
    DECLARE @ReportsOut AS numeric 
    DECLARE @IssuesDiscussedOut AS numeric 
    --DECLARE @Judgement AS numeric 
BEGIN 
    SET NOCOUNT ON; 

    SELECT @AreaOfLawOut = COUNT(AoLId) FROM [dbo].[CLR.AreaOfLaw] OUTPUT 
    SELECT @SubjectMatterOut = COUNT(SMId) FROM [dbo].[CLR.SubjectMatter] OUTPUT 
    SELECT @PrinciplesOut = COUNT(PId) FROM [dbo].[CLR.Principle] OUTPUT 
    SELECT @JudgesOut = COUNT(JId) FROM LocalJudgesView OUTPUT 
    SELECT @CourtsOut = COUNT(CourtId) FROM LocalCourtView OUTPUT 
    SELECT @CasesOut = COUNT(CaseId) FROM LocalReportedCaseView OUTPUT 
    SELECT @ReportsOut = COUNT(ReportId) FROM LocalReportView OUTPUT 
    SELECT @IssuesDiscussedOut = COUNT(IDId) FROM CLR_IssuesDiscussedView OUTPUT 
END 
GO 

C＃代码：

try 
{ 
    decimal aol = 0, sm = 0, p = 0, judge = 0, court = 0; 

    using (SqlConnection sqlConnection = new SqlConnection(DataSources.RemoteConnectionString())) 
    { 
     sqlConnection.Open(); 

     using (SqlCommand sqlCommand = new SqlCommand("[DB7934_businessmind].[CountRecordsForUpdates]", sqlConnection)) 
     { 
      sqlCommand.CommandType = System.Data.CommandType.StoredProcedure; 

      SqlParameter aolParam = new SqlParameter("@AreaOfLaw", aol) { Direction = System.Data.ParameterDirection.Output }; 
      sqlCommand.Parameters.Add(aolParam); 

      SqlParameter smParam = new SqlParameter("@SubjectMatter", sm) { Direction = System.Data.ParameterDirection.Output }; 
      sqlCommand.Parameters.Add(smParam); 

      SqlParameter pParam = new SqlParameter("@Principles", p) { Direction = System.Data.ParameterDirection.Output }; 
      sqlCommand.Parameters.Add(pParam); 

      SqlParameter judgeParam = new SqlParameter("@Judges", judge) { Direction = System.Data.ParameterDirection.Output }; 
      sqlCommand.Parameters.Add(judgeParam); 

      SqlParameter courtParam = new SqlParameter("@Courts", court) { Direction = System.Data.ParameterDirection.Output }; 
      sqlCommand.Parameters.Add(courtParam); 

      sqlCommand.ExecuteNonQuery(); 
      sqlCommand.Dispose(); 

      AoLRemoteCount = Convert.ToDecimal(aolParam.Value); 
      SMRemoteCount = Convert.ToDecimal(smParam.Value); 
      PRemoteCount = Convert.ToDecimal(pParam.Value); 
      CourtRemoteCount = Convert.ToDecimal(courtParam.Value); 
      JRemoteCount = Convert.ToDecimal(judgeParam.Value); 
     } 

     sqlConnection.Close(); 
    } 
} 
catch(Exception ex) 
{ 
    MessageBox.Show("An error has occurred while counting cloud records, the original error is: " 
     + ex.Message, "Cloud Records", MessageBoxButtons.OK, MessageBoxIcon.Error); 
} 

当我运行代码，我得到以下错误消息：

对象不能从DBNULL转换到其他类型。

我尝试了所有我能想到的，但无济于事。我需要帮助。我能够一一统计他们，但这意味着我必须每次打电话给互联网上的数据库，我认为这不利于流量。

我想要的就是一次性我能够统计大约6个表中的所有记录并返回值，以便我可以将它们分配给c＃中的属性以备将来使用。

Answer 1

当DB中的值为NULL时，它将返回为DBNull。您必须相应地检查：

SMRemoteCount = !DBNull.Value.Equals(smParam.Value) ? Convert.ToDecimal(smParam.Value) : 0m; 

UPDATE：

您还必须确保您在存储过程中分配返回值：

ALTER PROCEDURE CountRecordsForUpdates 
-- Add the parameters for the stored procedure here 
@AreaOfLaw AS numeric OUTPUT, 
@SubjectMatter AS numeric OUTPUT, 
@Principles AS numeric OUTPUT, 
@Judges AS numeric OUTPUT, 
@Courts AS numeric OUTPUT 

AS 
DECLARE @CasesOut AS numeric 
DECLARE @ReportsOut AS numeric 
DECLARE @IssuesDiscussedOut AS numeric 
--DECLARE @Judgement AS numeric 
BEGIN 
-- SET NOCOUNT ON added to prevent extra result sets from 
-- interfering with SELECT statements. 
SET NOCOUNT ON; 

-- Insert statements for procedure here 
SELECT @AreaOfLaw = COUNT(AoLId) FROM [dbo].[CLR.AreaOfLaw] OUTPUT 
SELECT @SubjectMatter = COUNT(SMId) FROM [dbo].[CLR.SubjectMatter] OUTPUT 
SELECT @Principles = COUNT(PId) FROM [dbo].[CLR.Principle] OUTPUT 
SELECT @Judges = COUNT(JId) FROM LocalJudgesView OUTPUT 
SELECT @Courts = COUNT(CourtId) FROM LocalCourtView OUTPUT 
SELECT @CasesOut = COUNT(CaseId) FROM LocalReportedCaseView OUTPUT 
SELECT @ReportsOut = COUNT(ReportId) FROM LocalReportView OUTPUT 
SELECT @IssuesDiscussedOut = COUNT(IDId) FROM CLR_IssuesDiscussedView OUTPUT 
END 
GO