c# - 无效的对象名称 'dbo.CategoryIdArray'

Question

我的动机是将一长串 ID 作为参数传递给存储过程，并根据 ID 选择数据。所以我在 SQL Server 中创建了 Type

CREATE TYPE [dbo].[CategoryIdArray] AS TABLE(
[CategoryId] [bigint] NULL
)

GO

和存储过程

ALTER  PROCEDURE [dbo].[GetNewestArticleByCatsPageWise]
  @dt as [dbo].[CategoryIdArray] READONLY,
  @PageIndex INT = 1
  ,@PageSize INT = 10
  ,@PageCount INT OUTPUT
AS
BEGIN
      SET NOCOUNT ON;
      SELECT ROW_NUMBER() OVER
            (
                  ORDER BY [dateadded] 
            )AS RowNumber,[desid]


INTO #Results
  FROM [DB_user1212].[dbo].[discussions] as d , [DB_user1212].[dbo].[CategoryMap] as c where d.desid=c.[Topic Id] and c.[Category Id] in (select CategoryId from [dbo].[CategoryIdArray]) and [TopicType]='1' order by [dateadded]

  DECLARE @RecordCount INT
  SELECT @RecordCount = COUNT(*) FROM #Results

  SET @PageCount = CEILING(CAST(@RecordCount AS DECIMAL(10, 2)) / CAST(@PageSize AS DECIMAL(10, 2)))
  PRINT       @PageCount

  SELECT * FROM #Results
  WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1

  DROP TABLE #Results
END

试图通过下面的代码使用上面的存储过程

 public List<String> getNewestArticleByCategoryPageWise( long[] categoryId)
  {
        List<string> topicId= new List<string>();
      try
      {

        DataTable dt_Categories = new DataTable();
        dt_Categories.Columns.Add("Category", typeof(String));
        DataRow workRow;
        foreach(long cat in categoryId)
        {
          workRow = dt_Categories.NewRow();
          workRow["Category"] = cat;
          dt_Categories.Rows.Add(workRow);
        }
        int pageIndex = 1;
            SqlCommand cmd = new SqlCommand("dbo.GetNewestArticleByCatsPageWise", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@PageIndex", pageIndex);
            cmd.Parameters.AddWithValue("@PageSize", 10);
            cmd.Parameters.Add("@PageCount", SqlDbType.Int, 4).Direction = ParameterDirection.Output;
            SqlParameter tvparam = cmd.Parameters.AddWithValue("@dt", dt_Categories);
            tvparam.SqlDbType = SqlDbType.Structured;
          con.Open();
            sdr= cmd.ExecuteReader();
      while(sdr.Read())
      {
          topicId.Add(sdr.GetString(0));
      }
          con.Close();
      }
      catch(Exception ex)
      {
          con.Close();
          throw ex;
      }
      return topicId;

  }

当我运行上面的函数时，抛出异常 无效的对象名称 'dbo.CategoryIdArray'。 但我将其创建为类型。帮我解决我错过的事情。我推荐了this .

Answer 1

问题出在存储过程中的这一行

从 [dbo].[CategoryIdArray] 中选择 CategoryId。

我们不能从这样的类型中选择，我们应该使用

从@dt中选择CategoryId