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 }); } }
public IEnumerable<RotaItem> GetRota(int calendarId) { using (OleDbConnection cn = new OleDbConnection(connectionString)) { cn.Open(); var sql = @"select CalendarDateId as DiaryId, [CalendarDate], Rota from CalendarDates where calendarId = @CalendarId Order By CalendarDate"; return cn.Query<RotaItem>(sql, new { CalendarId = calendarId }); } }
public Contact GetContact(int contactId) { using (OleDbConnection cn = new OleDbConnection(connectionString)) { cn.Open(); var sql = String.Format(@"select ContactId,AgeGroup,Title,Name,Phone,Email from Contacts where contactid = {0}", contactId); return cn.Query<Contact>(sql, new { }).FirstOrDefault(); } }
public IEnumerable<DiaryFixture> GetSocialDiary() { var calendarId = 1; using (OleDbConnection cn = new OleDbConnection(connectionString)) { cn.Open(); var sql = @"select DiaryId, [Date] as FixtureDate, FixtureSocialDetail as FixtureDetail, FixtureSocialLink as FixtureLink, FixtureSocialPhotos as FixturePhotos from Diary where CalendarId = @CalendarId and FixtureSocialDetail <> '' order by [Date] "; return cn.Query<DiaryFixture>(sql, new { CalendarId = calendarId }); } }
public IEnumerable<Contact> GetContacts() { using (OleDbConnection cn = new OleDbConnection(connectionString)) { cn.Open(); var sql = String.Format(@"select ContactId,AgeGroup,Title,Name,Phone,Email from Contacts Order By ContactID"); return cn.Query<Contact>(sql, new { }); } }
public IEnumerable<DiaryDate> GetDiaryDates() { var calendarId = 1; using (OleDbConnection cn = new OleDbConnection(connectionString)) { cn.Open(); var dates = cn.Query<DiaryDate>(@"select DiaryId, [Date] as FixtureDate from Diary where calendarId = @CalendarId order by Date", new { CalendarId = calendarId }); return dates; } }
public Product AddProduct(string name, string id, double width, double height, double depth, string libraryProductPath) { string filename = id + ".cutx"; string connstr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + this.ProductListMDBPath; using (OleDbConnection conn = new OleDbConnection(connstr)) { conn.Open(); string cmdtext = string.Format("Insert Into ProductList (ItemNumber,Description,Qty,Width,Height,Depth,MatFile,FileName,Handle,ReleaseNumber,Parent1) Values('{0}','{1}',{2},{3},{4},{5},'{6}','{7}','{8}','{9}','{10}')", string.Format("{0}.00", Products.Count + 1), name, 1, width, height, depth, this.SpecificationGroups[0].Name, filename, id, "UnNamed", "Phase 1"); var cmd = new OleDbCommand(); cmd.Connection = conn; cmd.CommandText = cmdtext; cmd.ExecuteNonQuery(); Product product = conn.Query<Product>("Select * from ProductList Where Handle='" + id + "'") .SingleOrDefault(); product.Project = this; this.Products.Add(product); //add product cutx file to project IWorkbook book = Factory.GetWorkbook(libraryProductPath); var cells = book.Worksheets["Prompts"].Cells; cells[0, 1].Value = width; cells[1, 1].Value = height; cells[2, 1].Value = depth; book.SaveAs(Path.Combine(JobPath, filename), FileFormat.OpenXMLWorkbook); return product; } }