public void Save(DateTemperature data)
{
using (var db = SqlFuDao.GetConnection())
{
db.Update <DateTemperature>(data);
}
}
public void Load(string airport)
{
using (var db = SqlFuDao.GetConnection())
{
CheckDb(db);
LoadDataFromDb(db);
}
DownloadMissingData(airport);
}
internal static List <Blog> GetNewest3BlogsFromDB()
{
List <Blog> blogs = new List <Blog>();
string connection = System.Configuration.ConfigurationManager.ConnectionStrings["DBContext"].ConnectionString;
SqlFuDao.ConnectionStringIs(connection, DbEngine.SqlServer);
using (DbConnection db = SqlFuDao.GetConnection())
{
blogs = db.Query <Blog>("SELECT TOP 3 * FROM appadmin.Blogs ORDER BY CreatedOn DESC").ToList();
}
return(blogs);
}
internal static List <Participant> GetAllParticipantsFromDB()
{
List <Participant> participants = new List <Participant>();
string connection = System.Configuration.ConfigurationManager.ConnectionStrings["DBContext"].ConnectionString;
SqlFuDao.ConnectionStringIs(connection, DbEngine.SqlServer);
using (DbConnection db = SqlFuDao.GetConnection())
{
participants = db.Query <Participant>().ToList();
}
return(participants);
}
internal static LastInsertId PopulateBlogs(Blog blog)
{
LastInsertId retVal;
string connection = System.Configuration.ConfigurationManager.ConnectionStrings["DBContext"].ConnectionString;
SqlFuDao.ConnectionStringIs(connection, DbEngine.SqlServer);
using (DbConnection db = SqlFuDao.GetConnection())
{
retVal = db.Insert <Blog>(blog);
}
return(retVal);
}
internal static Article FindArticleById(int id)
{
Article retVal = new Article();
string connection = System.Configuration.ConfigurationManager.ConnectionStrings["DBContext"].ConnectionString;
SqlFuDao.ConnectionStringIs(connection, DbEngine.SqlServer);
using (DbConnection db = SqlFuDao.GetConnection())
{
retVal = db.Query <Article>(a => a.ID == id).FirstOrDefault();
}
return(retVal);
}
internal static Workshop FindWorkshopByLabName(String name)
{
Workshop retVal = null;
string connection = System.Configuration.ConfigurationManager.ConnectionStrings["DBContext"].ConnectionString;
SqlFuDao.ConnectionStringIs(connection, DbEngine.SqlServer);
using (DbConnection db = SqlFuDao.GetConnection())
{
retVal = db.Query <Workshop>(w => w.Name == name).FirstOrDefault();
}
return(retVal);
}
internal static List <T> FindObjectsByType <T>(String query) where T : class
{
List <T> retVal;
string connection = System.Configuration.ConfigurationManager.ConnectionStrings["DBContext"].ConnectionString;
SqlFuDao.ConnectionStringIs(connection, DbEngine.SqlServer);
using (DbConnection db = SqlFuDao.GetConnection())
{
retVal = db.Query <T>(query).ToList();
}
return(retVal);
}
internal static void CreateTable <T>() where T : class
{
string connection = System.Configuration.ConfigurationManager.ConnectionStrings["DBContext"].ConnectionString;
SqlFuDao.ConnectionStringIs(connection, DbEngine.SqlServer);
using (DbConnection db = SqlFuDao.GetConnection())
{
//First time run only - the below will create table of a given class
//If CreationOptions is set to DropIt, the below will recreate the table
var builder = db.DatabaseTools().GetCreateTableBuilder <T>();
builder.ExecuteDDL();
}
}
internal static List <Cooperator> FindCooperatorsByType(String type)
{
List <Cooperator> retVal;
string connection = System.Configuration.ConfigurationManager.ConnectionStrings["DBContext"].ConnectionString;
SqlFuDao.ConnectionStringIs(connection, DbEngine.SqlServer);
using (DbConnection db = SqlFuDao.GetConnection())
{
retVal = db.Query <Cooperator>(c => c.Type == type).ToList();
}
return(retVal);
}
internal static List <Tag> GetTagsForBlog(Blog blog)
{
List <Tag> tags = new List <Tag>();
String sql = @"SELECT t.TagName, t.TagURL
FROM appadmin.Tags t
INNER JOIN appadmin.BlogTags bt ON t.ID = bt.TagID
WHERE bt.BlogID = {0}";
String query = String.Format(sql, blog.ID);
string connection = System.Configuration.ConfigurationManager.ConnectionStrings["DBContext"].ConnectionString;
SqlFuDao.ConnectionStringIs(connection, DbEngine.SqlServer);
using (DbConnection db = SqlFuDao.GetConnection())
{
tags = db.Query <Tag>(query).ToList();
}
return(tags);
}
internal static void CreateBlogTagsLinkerTable()
{
string connection = System.Configuration.ConfigurationManager.ConnectionStrings["DBContext"].ConnectionString;
SqlFuDao.ConnectionStringIs(connection, DbEngine.SqlServer);
using (DbConnection db = SqlFuDao.GetConnection())
{
var builder = db.DatabaseTools().GetCreateTableBuilder("BlogTags", IfTableExists.Ignore);
builder.Columns
.Add("BlogID", DbType.Int32, isNullable: false, autoIncrement: false)
.Add("TagID", DbType.Int32, isNullable: false, autoIncrement: false);
builder.Constraints.AddPrimaryKeyOn("BlogID,TagID", "PK_BlogTags");
builder.Constraints["PK_BlogTags"].IfDatabaseIs(DbEngine.SqlServer)
.PrimaryKeyOptions(SqlServerOptions.PrimaryKeyIs.Clustered());
builder.Indexes.AddIndexOn("TagID,BlogID", isUnique: true, indexName: "UX_BlogTags");
builder.ExecuteDDL();
}
}
private void DownloadMissingData(string airport)
{
var lastMeasure = Data.Any() ? Data.Max(x => x.Date) : DateTime.Today.AddYears(-1);
if (lastMeasure.Date == DateTime.Today)
{
return;
}
var missingData = DataDownloader.DownloadTemperature(airport, lastMeasure);
using (var db = SqlFuDao.GetConnection())
{
missingData.ForEach(x =>
{
x.Airport = airport;
db.Insert(x);
});
}
Data.AddRange(missingData);
}
internal static Blog FindBlogByTagName(string tagName)
{
Blog retVal = null;
string hashTagName = string.Empty;
if (!String.IsNullOrEmpty(tagName))
{
hashTagName = tagName;
if (!tagName.StartsWith("#"))
{
hashTagName = "#" + tagName;
}
}
string connection = System.Configuration.ConfigurationManager.ConnectionStrings["DBContext"].ConnectionString;
SqlFuDao.ConnectionStringIs(connection, DbEngine.SqlServer);
Tag tag;
using (DbConnection db = SqlFuDao.GetConnection())
{
if (tagName.Equals(hashTagName, StringComparison.InvariantCulture))
{
tag = db.Query <Tag>(t => t.TagName == tagName).FirstOrDefault();
}
else
{
tag = db.Query <Tag>(t => t.TagName == tagName || t.TagName == hashTagName).FirstOrDefault();
}
if (tag != null)
{
retVal = db.Query <Blog>(b => b.Tags.Contains(tag)).FirstOrDefault();
}
}
return(retVal);
}
public static DbConnection GetDb()
{
return(SqlFuDao.GetConnection());
}