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;
}
}
