C# OleDbConnection.Query方法代码示例

 public void QueryTest()
 {
     var connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 8.0;data source=" + "点评统计.xlsx";
     var connector = new OleDbConnection(connectionString);
     connector.Open();
     var result = connector.Query<int>("SELECT count(*) FROM [酒店点评$]");
     Console.WriteLine(JsonConvert.SerializeObject(result));
 }

        /// <summary>
        /// 读取订单的规格组信息
        /// </summary>
        private void loadSpecificationGroups()
        {
            //string connectStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + this.MicrovellumProjectPath;
            string connectStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + this.MicrovellumProjectPath;
            using (OleDbConnection conn = new OleDbConnection(connectStr))
            {
                conn.Open();

                this.SpecificationGroups = conn.Query<SpecificationGroup>
                    ("Select * from SpecificationGroups", null).ToList<SpecificationGroup>();
            }
        }

        private void loadProducts()
        {
            string connectStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + this.ProductListMDBPath;
            using (OleDbConnection conn = new OleDbConnection(connectStr))
            {
                conn.Open();

                this.Products = conn.Query<Product>
                    ("Select * from ProductList", null).ToList<Product>();

                if (this.Products == null)
                {
                    this.Products = new List<Product>();
                }
            }
        }

    public IEnumerable<Contact> GetOtherContacts()
    {
        using (OleDbConnection cn = new OleDbConnection(connectionString))
        {
            cn.Open();

            var sql = @"select ContactId,Title,Name,Phone,Email,Photo from Contacts
                    where AgeGroup='Other'
                    Order By ContactID";
            return cn.Query<Contact>(sql, new { });
        }
    }

 public IEnumerable<NewsItem> GetNewsItems()
 {
     using (OleDbConnection cn = new OleDbConnection(connectionString))
     {
         cn.Open();
         var sql = @"select * from News
                 Order By DateCreated desc";
         return cn.Query<NewsItem>(sql, new { });
     }
 }

 public NewsItem GetNewsItem(int newsId)
 {
     using (OleDbConnection cn = new OleDbConnection(connectionString))
     {
         cn.Open();
         var sql = String.Format(@"select id, headline, detail, datecreated, datearchived
                 from News
                 where id = {0}", newsId);
         return cn.Query<NewsItem>(sql, new { }).FirstOrDefault();
     }
 }

 public DiaryFixture GetDiaryFixture(int diaryId, AgeGroup group)
 {
     using (OleDbConnection cn = new OleDbConnection(connectionString))
     {
         cn.Open();
         var sql = String.Format(@"select DiaryId, [Date] as FixtureDate,
             Fixture{0}HA as FixtureHA,
             Fixture{0}Detail as FixtureDetail,
             Fixture{0}Link as FixtureLink,
             Fixture{0}Photos as FixturePhotos,
             Fixture{0}Report as FixtureReport,
             Fixture{0}League as FixtureLeague
         from Diary where DiaryId = @DiaryId", group.ToString());
         var dates = cn.Query<DiaryFixture>(sql, new { DiaryId = diaryId });
         return dates.First();
     }
 }

 public IEnumerable<Contact> GetAgeGroupContacts(List<string> ageGroups)
 {
     using (OleDbConnection cn = new OleDbConnection(connectionString))
     {
         cn.Open();
         string ageGroupSql = string.Join(", ", ageGroups.Select(a => "'" + a + "'").ToArray());
         var sql = String.Format(@"select ContactId,Title,Name,Phone,Email,Photo from Contacts
                 where AgeGroup in ({0})
                 Order By ContactID", ageGroupSql);
         return cn.Query<Contact>(sql, new { });
     }
 }

 public SiteSettings GetSiteSettings()
 {
     using (OleDbConnection cn = new OleDbConnection(connectionString))
     {
         cn.Open();
         var sql = @"select * from SiteSettings";
         return cn.Query<SiteSettings>(sql, new { }).FirstOrDefault();
     }
 }

 public IEnumerable<Contact> GetCommitteeContacts(string ageGroup)
 {
     using (OleDbConnection cn = new OleDbConnection(connectionString))
     {
         cn.Open();
         var sql = String.Format(@"select ContactId,Title,Name,Phone,Email,Photo from Contacts
                 where AgeGroup='Committee' AND Title Like '%{0}%'
                 Order By ContactID", ageGroup);
         return cn.Query<Contact>(sql, new { });
     }
 }

    public IEnumerable<Contact> GetAllAgeGroupContacts()
    {
        using (OleDbConnection cn = new OleDbConnection(connectionString))
        {
            cn.Open();

            var sql = @"select ContactId,Title,Name,Phone,Email,Photo from Contacts
                    where AgeGroup='Micros' or AgeGroup='Minis' or AgeGroup='Youth' or AgeGroup='Girls' or AgeGroup='Senior'
                    Order By ContactID";
            return cn.Query<Contact>(sql, new { });
        }
    }

 public IEnumerable<DiaryFixture> GetAgeGroupDiaryNew(AgeGroup ageGroup)
 {
     var calendarId = 1;
     using (OleDbConnection cn = new OleDbConnection(connectionString))
     {
         cn.Open();
         var sql = String.Format(@"SELECT f.FixtureID AS DiaryId, cd.CalendarDate AS FixtureDate, f.Details AS FixtureDetail, f.HA AS FixtureHA, f.Link AS FixtureLink, f.Photos AS FixturePhotos,
                      f.Report AS FixtureReport, f.League AS FixtureLeague
                      FROM (CalendarDates cd LEFT OUTER JOIN
                             (SELECT FixtureID, CalendarDateID, AgeGroup, Details, HA, Link, Photos, Report, League
                                FROM  Fixtures
                               WHERE (AgeGroup = '{0}')) f ON f.CalendarDateID = cd.CalendarDateID)
                      ORDER BY cd.CalendarDate", ageGroup.ToString());
         return cn.Query<DiaryFixture>(sql, new { CalendarId = calendarId });
     }
 }

 public IEnumerable<DiaryFixture> GetAgeGroupDiary(AgeGroup ageGroup)
 {
     var calendarId = 1;
     using (OleDbConnection cn = new OleDbConnection(connectionString))
     {
         cn.Open();
         var sql = String.Format(@"select DiaryId, [Date] as FixtureDate,
             Fixture{0}HA as FixtureHA,
             Fixture{0}Detail as FixtureDetail,
             Fixture{0}Link as FixtureLink,
             Fixture{0}Photos as FixturePhotos,
             Fixture{0}Report as FixtureReport,
             Fixture{0}League as FixtureLeague,
             FixtureSocialDetail <> '' as IsSocial
         from Diary
         where CalendarId = @CalendarId
         order by [Date]", ageGroup.ToString());
         return cn.Query<DiaryFixture>(sql, new { CalendarId = calendarId });
     }
 }

        /// <summary>
        /// 读取OverDrivePro.mdb数据库中的数据
        /// </summary>
        private void loadProjectInfo()
        {
            string connectStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + this.OverdriveProMDBPath;
            //string connectStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + this.OverdriveProMDBPath;

            using (OleDbConnection conn = new OleDbConnection(connectStr))
            {
                conn.Open();

                var info = conn.Query<ProjectInfo>("select * from Jobs", null)
                                           .SingleOrDefault();

                //Products.ForEach(a => a.Job = job);//设定每个产品的工作任务

                if (info == null)
                {
                    this.insertBlankProjectInfo(connectStr, (new DirectoryInfo(this.jobPath)).Name);//插入一个空的记录
                    this.ProjectInfo = new ProjectInfo();
                }
            }
        }

 public IEnumerable<RotaItem> GetRota()
 {
     var calendarId = 1;
     using (OleDbConnection cn = new OleDbConnection(connectionString))
     {
         cn.Open();
         var sql = @"select DiaryId,
             [Date],
             Rota
             from Diary
             where calendarId = @CalendarId
             Order By Date";
         return cn.Query<RotaItem>(sql, new { CalendarId = calendarId });
     }
 }