索引超出范围异常

Question

我正在尝试在存储过程的帮助下在 Visual Studio 端填充我的列表。

我正在使用以下存储过程：

CREATE PROCEDURE [dbo].[GetColletcion]
AS
BEGIN   
         select  CollectionType.Name ,GlassesCollection.Name 
    from    GlassesCollection
    inner join CollectionType
    on GlassesCollection.CollectionType=CollectionType.CollTypeID
END

这是隐藏的代码：

protected void Button1_Click(object sender, EventArgs e)
        {

            List<GlassesCollection> list = new List<GlassesCollection>();
            using (SqlConnection conn = new SqlConnection("Server=(local);DataBase=ISeeOptic;Integrated Security=SSPI"))
            {

                GlassesCollection gln = new GlassesCollection();
                SqlCommand cmd = new SqlCommand();
                SqlDataReader reader;

                cmd.Connection = conn;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "GetColletcion";

                conn.Open();
                reader = cmd.ExecuteReader();
                  while (reader.Read())
                    {
                        gln.Name = (string)reader["CollectionType.Name"];
                        gln.CollectionType = (string)reader["GlassesCollection.Name"];

                        list.Add(gln);
                    }


                reader.Close();
                conn.Close();
            }

        }

但是当涉及到这一行时：

gln.Name = (string)reader["CollectionType.Name"];

我得到这个异常：

Exception Details: System.IndexOutOfRangeException: CollectionType.Name

索引超出范围，尽管在数据库中有多个记录。 我该如何解决我的问题？

Answer 1

最好使用列别名并通过这些别名来引用列。

CREATE PROCEDURE [dbo].[GetColletcion]
AS
BEGIN   
         select  CollectionType.Name As TypeName ,GlassesCollection.Name As GlassesName
    from    GlassesCollection
    inner join CollectionType
    on GlassesCollection.CollectionType=CollectionType.CollTypeID
END

然后使用

(string)reader["TypeName"];
(string)reader["GlassesName"];

如果您无法更改存储过程，那么您可以使用原始位置：

(string)reader[0]; //CollectionType.Name
(string)reader[1]; //GlassesCollection.Name

Answer 2

我也纠正了你的错字。 (GetCollection)

CREATE PROCEDURE [dbo].[GetCollection]
AS
BEGIN   
         select  CollectionType.Name AS CollectionType_Name, GlassesCollection.Name AS GlassesCollection_Name 
    from    GlassesCollection
    inner join CollectionType
    on GlassesCollection.CollectionType=CollectionType.CollTypeID
END

背后的代码：

protected void Button1_Click(object sender, EventArgs e)
    {
        List<GlassesCollection> list = new List<GlassesCollection>();
        using (SqlConnection conn = new SqlConnection("Server=(local);DataBase=ISeeOptic;Integrated Security=SSPI"))
        {

            GlassesCollection gln = new GlassesCollection();
            SqlCommand cmd = new SqlCommand();
            SqlDataReader reader;

            cmd.Connection = conn;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "GetCollection";

            conn.Open();
            reader = cmd.ExecuteReader();
              while (reader.Read())
                {
                    gln.Name = (string)reader["GlassesCollection_Name"];
                    gln.CollectionType = (string)reader["CollectionType_Name"];

                    list.Add(gln);
                }


            reader.Close();
            conn.Close();
        }

    }