SQL查询返回两列，但DataSet只包含一列，另一列不存在

提问者：小点点

SQL查询返回两列，但DataSet只包含一列，另一列不存在

下面的代码执行存储过程并返回结果以填充DropDownList。

private DataSet GetData(string SPName, SqlParameter SPParameter)
{
        string CS = ConfigurationManager.ConnectionStrings["HMT2DBCS"].ConnectionString;

        using (SqlConnection con = new SqlConnection(CS))
        {
            SqlDataAdapter da = new SqlDataAdapter(SPName, con);
            da.SelectCommand.CommandType = CommandType.StoredProcedure;

            if (SPParameter != null)
            {
                da.SelectCommand.Parameters.Add(SPParameter);
            }

            con.Open();

            DataSet DS = new DataSet();
            da.Fill(DS);

            con.Close();

            return DS;
        }
}

public void PopulateTicket()
{
        DataSet DS = GetData("spGetTickets", null);
        ddlTicket.DataSource = DS;
        ddlTicket.DataTextField = "TicketInfo";
        ddlTicket.DataValueField = "MicroBT_ticket";
        ddlTicket.DataBind();

        ListItem liticket = new ListItem("--Select a ticket--", "-1");
        ddlTicket.Items.Insert(0, liticket);
}

执行此操作时，会出现以下错误:

System.Web.HttpException:“DataBinding:”System.Data.DataRowView“不包含名为”TicketInfo“得属性。”

当我从SSMS运行存储过程时，我得到以下结果表，其中确实包含列TicketInfo:

我已经双重检查了所有列和字段的拼写。我有一个VS项目的重复拷贝和一个重复的‘测试’数据库设置在另一台PC上和相同的代码在那里工作很好！我不知道是什么导致了这个错误。

更新:DataSet visualizer在我的数据集中显示以下内容，TicketInfo列由于某种原因丢失:

共2个答案

匿名用户

关于我在评论中提到的ORM。下面是一个关于如何为实体框架定义类的示例（代码优先），您可以非常容易地从数据库表反向工程它。实体框架为您实现了这一点。

下面是我之前在这个论坛上发布的另一个问题的例子。您可以看到使用LINQ从DB中的表中检索数据是多么容易，而且您还可以做更多的事情。

namespace ToPagedList
{
    using System.ComponentModel.DataAnnotations;
    using System.ComponentModel.DataAnnotations.Schema;

    [Table("Course")]
    public partial class Course
    {
        public int CourseId { get; set; }

        public int? CourseGroupId { get; set; }

        [StringLength(50)]
        public string CourseCode { get; set; }

        [Required]
        [StringLength(100)]
        public string Title { get; set; }

        [StringLength(1000)]
        public string ShortDescription { get; set; }

        [Column(TypeName = "ntext")]
        [Required]
        public string Contents { get; set; }

        [Column(TypeName = "ntext")]
        public string MailTextApplied { get; set; }

        [Column(TypeName = "ntext")]
        public string MailTextAccepted { get; set; }

        [Column(TypeName = "ntext")]
        public string MailTextRejected { get; set; }

        [Column(TypeName = "ntext")]
        public string MailTextDeleted { get; set; }

        [StringLength(1000)]
        public string ExternalRegistrationUrl { get; set; }

        public bool Visible { get; set; }

        [StringLength(1000)]
        public string ExternalCourseUrl { get; set; }

        public string Reviews { get; set; }

        [StringLength(50)]
        public string School { get; set; }

        [Column(TypeName = "ntext")]
        public string MailTextCertified { get; set; }

        [Column(TypeName = "ntext")]
        public string MailTextNoShow { get; set; }

        [Column(TypeName = "ntext")]
        public string MailTextPassed { get; set; }

        [Column(TypeName = "ntext")]
        public string MailTextPartlyCompleted { get; set; }

        [Column(TypeName = "ntext")]
        public string MailTextWaitingList { get; set; }

        [Column(TypeName = "ntext")]
        public string MailTextWithdrawn { get; set; }

        public bool ShowFromStartDate { get; set; }
    }
}

以下是检索数据的代码:

using System;
using System.Diagnostics;

namespace ToPagedList
{
    class Program
    {
        static void Main(string[] args)
        {
            Stopwatch sw = new Stopwatch();

            sw.Start();

            var list1 = Repository.GetCourses1();

            sw.Stop();
            Console.WriteLine($"Getting list 1 took {sw.ElapsedMilliseconds} milliseconds");


            sw.Reset();
            sw.Start();

            var list2 = Repository.GetCourses2();

            sw.Stop();
            Console.WriteLine($"Getting list 2 took {sw.ElapsedMilliseconds} milliseconds");

            Console.ReadKey();
        }
    }
}

和存储库类:

using System;
using System.Collections.Generic;
using System.Linq;

namespace ToPagedList
{
    public class Repository
    {
        public static List<DocumentsModel> GetCourses1(string school = null, string code = null, string title = null, int page = 0, int count = 15)
        {
            var courses = new DocumentModelEntities().Course;

            return courses.Where(course => string.IsNullOrEmpty(code) || course.CourseCode.Contains(code))
                                 .Where(course => String.IsNullOrEmpty(title) || course.Title.Contains(title))
                                 .Where(w => String.IsNullOrEmpty(school) || w.School == school)
                                 // From here your table is read from the DB using the where clauses
                                 .Select(s => new DocumentsModel
                                 {
                                     Code = code.Trim(),
                                     Title = s.Title
                                 })
                                 .OrderBy(o => o.Code)
                                 .Skip(page * count)
                                 .Take(count)
                                 .ToList();
        }

        public static List<DocumentsModel> GetCourses2(string school = null, string code = null, string title = null, int page = 0, int count = 15)
        {
            var courses = new DocumentModelEntities().Course;

            return courses.Where(course => string.IsNullOrEmpty(code) || course.CourseCode.Contains(code))
                             .Where(course => String.IsNullOrEmpty(title) || course.Title.Contains(title))
                             .Where(w => String.IsNullOrEmpty(school) || w.School == school)
                             .OrderBy(course => course.CourseCode)
                             .Skip(page * count)
                             .Take(count)
                             // From here your table is read from the DB using the where clauses, order by, skip and take
                             .Select(s => new DocumentsModel
                             {
                                 Code = code.Trim(),
                                 Title = s.Title
                             })
                             .ToList();
        }
    }
}

匿名用户

您需要使用的是DataTable而不是DataSet，因此您的代码应该如下所示:

DataTable DT = GetData("spGetTickets", null).Tables[0];
ddlTicket.DataSource = DT;