用表的数据填充网格视图，其中一列=列表中的一列

我有一个包含两列的列表：ID；int，得分：双。

此外，我在SQL SERVER中有一个表，其中包含多个列。其中一个是id:int。

我想有一个如下的查询：

从tbl中选择*，其中id=id s在我的列表中。

我的代码如下：

 protected void btnfind_Click(object sender, EventArgs e)
{
        List<KeyValuePair<int, double>> candidatelist = CalculateScores();
        FinalMatch(candidatelist);
        BindGrid(cmd2);//Contains codes for filling grid view with cmd2 , sql data reader
 }
protected void FinalMatch(List<KeyValuePair<int, double>> finallist)
{
    DataTable tvp = new DataTable();
    tvp = ConvertToDatatable(finallist);
    cmd2.CommandType = CommandType.StoredProcedure;
    cmd2.CommandText = "dbo.DoSomethingWithCandidates";
    SqlParameter tvparam = cmd2.Parameters.AddWithValue("@List", tvp);
    tvparam.SqlDbType = SqlDbType.Structured;
    cmd2.Connection = ClsDataBase.con;
}
protected DataTable ConvertToDatatable(List<KeyValuePair<int, double>> finallist)
{
    DataTable dt = new DataTable();
    dt.Columns.Add("ID");
    dt.Columns.Add("Score");
    foreach (var item in finallist)
    {
        var row = dt.NewRow();
        row["ID"] = item.Key;
        row["Score"] = item.Value;
        dt.Rows.Add(row);
    }
    return dt;
}
protected void BindGrid(SqlCommand cmd)
{
    if (ClsDataBase.con.State == ConnectionState.Closed)
        ClsDataBase.con.Open();
    SqlDataReader dr1 = cmd.ExecuteReader();
    try
    {
        if (dr1.HasRows)
        {
            gv_allresults.DataSource = dr1;
            gv_allresults.DataBind();
        }
        else
        {
            Response.Write("<script LANGUAGE='JavaScript' >alert('No Match')</script>");
        }
        if (dr1.IsClosed == false) dr1.Close();
    }
    catch (SqlException ex)
    {
        Response.Write("<script language='javascript'>alert('"" + ex.ToString() + "'")</script>");
    }
    catch (Exception ex)
    {
        Response.Write("<script language='javascript'>alert('"" + ex.ToString() + "'")</script>");
    }
    finally
    {
        ClsDataBase.con.Close();
    }
}

我在SQL server中的代码是：

CREATE TYPE dbo.CandidateList
AS TABLE
(
  ID INT,
   Score FLOAT
);
GO
CREATE PROCEDURE dbo.DoSomethingWithCandidates
   @List AS dbo.CandidateList READONLY
AS
BEGIN
  SET NOCOUNT ON;
  SELECT ID FROM @List; 
END
GO

我不知道结果。我的程序代码不完整。我不知道该怎么办。请帮帮我。非常感谢。

---

根据给定建议编辑代码：

 protected void FinalMatch(List<KeyValuePair<int, double>> finallist)
{
    int[] canArr = finallist.Select(x => x.Key).ToArray();
    string csv = string.Join(",", canArr);
    cmd2.CommandType = CommandType.StoredProcedure;
    cmd2.CommandText = "dbo.ReturnCandidates";
    cmd2.Parameters.AddWithValue("@LIST", csv);
    cmd2.Connection = ClsDataBase.con;
}

Sql服务器中的新代码是：

 SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[CSVToTable] (@InStr VARCHAR(MAX))
RETURNS @TempTab TABLE
  (id int not null)
AS
BEGIN
    ;-- Ensure input ends with comma
SET @InStr = REPLACE(@InStr + ',', ',,', ',')
DECLARE @SP INT
DECLARE @VALUE VARCHAR(1000)
WHILE PATINDEX('%,%', @INSTR ) <> 0 
BEGIN
   SELECT  @SP = PATINDEX('%,%',@INSTR)
   SELECT  @VALUE = LEFT(@INSTR , @SP - 1)
   SELECT  @INSTR = STUFF(@INSTR, 1, @SP, '')
   INSERT INTO @TempTab(id) VALUES (@VALUE)
END
RETURN
END
GO

CREATE PROCEDURE dbo.ReturnCandidates
(
 @LIST VARCHAR(200)
 ) 
AS
BEGIN
 SELECT  *
 FROM    tblspecifications
 WHERE   id IN ( SELECT * FROM dbo.CSVToTable(@LIST) )
END

我得到这个错误："过程或函数ReturnCandidate指定了太多参数"，在行下：

SqlDataReader dr1=cmd。ExecuteRead（）；

请帮帮我。非常感谢

int[] canArr= candidatelist.Select(x => x.Key).ToArray();
string csv = string.Join(",", canArr);