[已解决]关于SqlDataReader的一个问题
最近在做毕业设计时遇到了一些问题，希望大家能够帮我解决。我是按照.NET PetShop 4.0的结构设计了一个系统，但是在数据接口层遇到了一些问题，主要在用SqlDataReader读取数据时总是出错。具体情况如下：
DAL.Posts
public IList<PostInfo> GetThreads(int forumID)
{
using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLConnString1"].ConnectionString))
{
using (SqlCommand command = new SqlCommand("GetThreads", connection))
{
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(new SqlParameter("@forumID", forumID));
connection.Open();
IList<PostInfo> posts = new List<PostInfo>();
using (SqlDataReader rdr = command.ExecuteReader())
{
while (rdr.Read())
{
int i = rdr.FieldCount;
PostInfo post = new PostInfo(rdr.GetInt32(0),
rdr.GetDateTime(1),
rdr.GetString(2),
rdr.GetString(3),
rdr.GetInt32(4),
rdr.GetString(5),
rdr.GetInt32(6),
rdr.GetString(7),
null,
rdr.GetBoolean(9),
rdr.GetBoolean(10),
rdr.GetInt32(11),
rdr.GetInt32(12),
rdr.GetDateTime(13),
rdr.GetString(14));
posts.Add(post);
}
}
return posts;
}
}
}
在这里数据表的结构就不详细介绍了，其中用到的存储过程"GetThreads"为：
ALTER PROCEDURE GetThreads
@forumID int
AS
SELECT Posts.PostID, Posts.AddedDate, Posts.AddedBy, Posts.AddedByIP, Posts.ForumID, Posts.ParentPostID, Posts.Title, Posts.Approved, Posts.Closed, Posts.ViewCount, Posts.ReplyCount, Posts.LastPostDate, Posts.LastPostBy
FROM Posts INNER JOIN Forums
ON Posts.ForumID = Forums.ForumID
WHERE Posts.ForumID = @forumID AND Posts.ParentPostID = 0 AND Posts.Approved = 1
RETURN
前台页面BrowseThreads.aspx为：
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="BrowseThreads.aspx.cs" Inherits="Web.BrowseThreads" Title="Browse Threads"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:ObjectDataSource ID="ObjectDataSource1" runat="server" SelectMethod="GetThreads"
TypeName="RFID.BLL.Post">
<SelectParameters>
<asp:QueryStringParameter Name="forumID" QueryStringField="ForumID" Type="Int32" />
</SelectParameters>
</asp:ObjectDataSource>
</div>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="ObjectDataSource1">
<Columns>
<asp:BoundField DataField="ViewCount" HeaderText="ViewCount" SortExpression="ViewCount" />
<asp:BoundField DataField="ForumTitle" HeaderText="ForumTitle" SortExpression="ForumTitle" />
<asp:BoundField DataField="PostID" HeaderText="PostID" SortExpression="PostID" />
<asp:BoundField DataField="LastPostDate" HeaderText="LastPostDate" SortExpression="LastPostDate" />
<asp:BoundField DataField="AddedDate" HeaderText="AddedDate" SortExpression="AddedDate" />
<asp:BoundField DataField="Body" HeaderText="Body" SortExpression="Body" />
<asp:BoundField DataField="LastPostBy" HeaderText="LastPostBy" SortExpression="LastPostBy" />
<asp:BoundField DataField="Title" HeaderText="Title" SortExpression="Title" />
<asp:BoundField DataField="AddedBy" HeaderText="AddedBy" SortExpression="AddedBy" />
<asp:BoundField DataField="ReplyCount" HeaderText="ReplyCount" SortExpression="ReplyCount" />
<asp:BoundField DataField="ForumId" HeaderText="ForumId" SortExpression="ForumId" />
<asp:BoundField DataField="AddedByIP" HeaderText="AddedByIP" SortExpression="AddedByIP" />
<asp:CheckBoxField DataField="Approved" HeaderText="Approved" SortExpression="Approved" />
<asp:BoundField DataField="ParentPostID" HeaderText="ParentPostID" SortExpression="ParentPostID" />
<asp:CheckBoxField DataField="Closed" HeaderText="Closed" SortExpression="Closed" />
</Columns>
</asp:GridView>
</form>
</body>
</html>
但是显示是却总是出错，如下图所示：
我先Google了一下问题，大部分说的都可能是由于SqlDataReader读出的数据项为空值，但是单独取某一项的值时却没有问题，比如说下面的语句是可以正确得到返回值的。这里当然要提到Posts表的主键PostID为Int类型的，并且为自动增值的（每次+1）。int i = rdr.GetInt32(0);
我换了很多方法来读取还是没有解决问题，希望大家能够帮我一起解决问题。
PS：问题已经解决了，原因是存储过程中的有个字段在迁移的时候遗漏了，导致读取时类型不匹配，但是郁闷的是编译器总是提示第一个字段有错误，真的搞了好久才解决，还好是解决了。^_^
DAL.Posts
public IList<PostInfo> GetThreads(int forumID) {
using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLConnString1"].ConnectionString)) {
using (SqlCommand command = new SqlCommand("GetThreads", connection)) { command.CommandType = CommandType.StoredProcedure; command.Parameters.Add(new SqlParameter("@forumID", forumID)); connection.Open(); IList<PostInfo> posts = new List<PostInfo>(); using (SqlDataReader rdr = command.ExecuteReader()) { while (rdr.Read()) { int i = rdr.FieldCount; PostInfo post = new PostInfo(rdr.GetInt32(0), rdr.GetDateTime(1), rdr.GetString(2), rdr.GetString(3), rdr.GetInt32(4), rdr.GetString(5), rdr.GetInt32(6), rdr.GetString(7), null, rdr.GetBoolean(9), rdr.GetBoolean(10), rdr.GetInt32(11), rdr.GetInt32(12), rdr.GetDateTime(13), rdr.GetString(14)); posts.Add(post); } } return posts; } }}ALTER PROCEDURE GetThreads @forumID intASSELECT Posts.PostID, Posts.AddedDate, Posts.AddedBy, Posts.AddedByIP, Posts.ForumID, Posts.ParentPostID, Posts.Title, Posts.Approved, Posts.Closed, Posts.ViewCount, Posts.ReplyCount, Posts.LastPostDate, Posts.LastPostByFROM Posts INNER JOIN Forums ON Posts.ForumID = Forums.ForumIDWHERE Posts.ForumID = @forumID AND Posts.ParentPostID = 0 AND Posts.Approved = 1RETURN<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="BrowseThreads.aspx.cs" Inherits="Web.BrowseThreads" Title="Browse Threads"%><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml" ><head runat="server"></head><body> <form id="form1" runat="server"> <div> <asp:ObjectDataSource ID="ObjectDataSource1" runat="server" SelectMethod="GetThreads" TypeName="RFID.BLL.Post"> <SelectParameters> <asp:QueryStringParameter Name="forumID" QueryStringField="ForumID" Type="Int32" /> </SelectParameters> </asp:ObjectDataSource> </div> <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="ObjectDataSource1"> <Columns> <asp:BoundField DataField="ViewCount" HeaderText="ViewCount" SortExpression="ViewCount" /> <asp:BoundField DataField="ForumTitle" HeaderText="ForumTitle" SortExpression="ForumTitle" /> <asp:BoundField DataField="PostID" HeaderText="PostID" SortExpression="PostID" /> <asp:BoundField DataField="LastPostDate" HeaderText="LastPostDate" SortExpression="LastPostDate" /> <asp:BoundField DataField="AddedDate" HeaderText="AddedDate" SortExpression="AddedDate" /> <asp:BoundField DataField="Body" HeaderText="Body" SortExpression="Body" /> <asp:BoundField DataField="LastPostBy" HeaderText="LastPostBy" SortExpression="LastPostBy" /> <asp:BoundField DataField="Title" HeaderText="Title" SortExpression="Title" /> <asp:BoundField DataField="AddedBy" HeaderText="AddedBy" SortExpression="AddedBy" /> <asp:BoundField DataField="ReplyCount" HeaderText="ReplyCount" SortExpression="ReplyCount" /> <asp:BoundField DataField="ForumId" HeaderText="ForumId" SortExpression="ForumId" /> <asp:BoundField DataField="AddedByIP" HeaderText="AddedByIP" SortExpression="AddedByIP" /> <asp:CheckBoxField DataField="Approved" HeaderText="Approved" SortExpression="Approved" /> <asp:BoundField DataField="ParentPostID" HeaderText="ParentPostID" SortExpression="ParentPostID" /> <asp:CheckBoxField DataField="Closed" HeaderText="Closed" SortExpression="Closed" /> </Columns> </asp:GridView> </form></body></html>我先Google了一下问题，大部分说的都可能是由于SqlDataReader读出的数据项为空值，但是单独取某一项的值时却没有问题，比如说下面的语句是可以正确得到返回值的。这里当然要提到Posts表的主键PostID为Int类型的，并且为自动增值的（每次+1）。
int i = rdr.GetInt32(0);我换了很多方法来读取还是没有解决问题，希望大家能够帮我一起解决问题。
PS：问题已经解决了，原因是存储过程中的有个字段在迁移的时候遗漏了，导致读取时类型不匹配，但是郁闷的是编译器总是提示第一个字段有错误，真的搞了好久才解决，还好是解决了。^_^
