private int?AdjacentEntryID(int id, AdjacentEntryDirection direction)
{
var queryTokens = new[]
{
new { Aggregate = "MAX", Operator = "<" }, // previous [0]
new { Aggregate = "MIN", Operator = ">" } // next [1]
};
int?result = null;
using (SqlCeConnection cn = new SqlCeConnection(_connectionString))
{
cn.Open();
if (id != 0)
{
result = cn.Query <int?>($"SELECT {queryTokens[(int)direction].Aggregate}([ID]) FROM [JournalEntry] WHERE [ID]{queryTokens[(int)direction].Operator}@id", new { id = id }).SingleOrDefault();
}
else
{
if (direction == AdjacentEntryDirection.Previous)
{
result = cn.Query <int?>("SELECT MAX([ID]) FROM [JournalEntry]").SingleOrDefault();
}
}
}
return(result);
}
private void SetSameDayIndexes(SqlCeConnection cn)
{
var days = cn.Query <MultiEntryDay>(
@"SELECT
DATEPART(yy, [Date]) AS [Year],
DATEPART(m, [Date]) AS [Month],
DATEPART(d, [Date]) AS [Day],
COUNT(1) AS [Count]
FROM
[JournalEntry]
GROUP BY
DATEPART(yy, [Date]),
DATEPART(m, [Date]),
DATEPART(d, [Date])
HAVING
COUNT(1)>1" , null);
foreach (var day in days)
{
var entries = cn.Query <int>(
@"SELECT [ID] FROM [JournalEntry]
WHERE DATEPART(yy, [Date])=@year AND DATEPART(m, [Date])=@month AND DATEPART(d, [Date])=@day
ORDER BY [Date]" ,
new { year = day.Year, month = day.Month, day = day.Day });
int index = 0;
foreach (var entryID in entries)
{
index++;
cn.Execute("UPDATE [JournalEntry] SET [SameDayIndex]=@index WHERE [ID]=@id", new { index = index, id = entryID });
}
}
}
public void Add(Picture item)
{
var param = new
{
PictureBinary = item.PictureBinary,
MimeType = item.MimeType,
SeoFileName = item.SeoFileName,
IsNew = item.IsNew,
FilePath = item.FilePath,
PictureGuid = item.PictureGuid,
AddUser = "******",
AddDate = DateTime.Now,
DeleteFlag = false
};
using (SqlCeConnection conn = Connection2)
{
conn.Open();
var i = conn.Query <int>(@"INSERT INTO Picture
(pic_picture_binary,pic_mime_type,pic_seo_filename,pic_is_new,pic_file_path,pic_guid
,pic_add_user,pic_add_date,pic_delete_flag)
VALUES(@PictureBinary,@MimeType,@SeoFileName,@IsNew,@FilePath,@PictureGuid,@AddUser,@AddDate,
@DeleteFlag)", param);
var j = conn.Query <int>(@"Select pic_id from picture where pic_guid = @PictureGuid", new { PictureGuid = param.PictureGuid }).FirstOrDefault();
}
}
public bool AssignBracketsByStartSlot()
{
using (var connection = new SqlCeConnection(Settings.Cnn))
{
connection.Open();
connection.Execute("update match set racer1id=null, racer2id=null, winningracerid=null");
var racers = connection.Query<Racer>("select * from racer order by startslot").ToList();
var matches1 = connection.Query<Match>("select * from match where roundid=1 order by matchid").ToList();
var racerIndex = 0;
foreach (var match in matches1)
{
if (!matches1.Any(m => m.NextWinningMatchId == match.MatchId && m.NextWinningMatchSlot == 1))
{
var racer1Id = racers[racerIndex].RacerId;
match.Racer1Id = racer1Id;
connection.Execute("update match set racer1id=@racer1Id where matchid=@MatchId", new { racer1Id, match.MatchId });
racerIndex++;
}
if (!matches1.Any(m => m.NextWinningMatchId == match.MatchId && m.NextWinningMatchSlot == 2))
{
var racer2Id = racers[racerIndex].RacerId;
match.Racer2Id = racer2Id;
connection.Execute("update match set racer2id=@racer2Id where matchid=@MatchId", new { racer2Id, match.MatchId });
racerIndex++;
}
}
var matches2 = connection.Query<Match>("select * from match where roundid=2 order by matchid").ToList();
foreach (var match in matches2)
{
if (!matches1.Any(m => m.NextWinningMatchId == match.MatchId && m.NextWinningMatchSlot == 1))
{
var racer1Id = racers[racerIndex].RacerId;
match.Racer1Id = racer1Id;
connection.Execute("update match set racer1id=@racer1Id where matchid=@MatchId", new { racer1Id, match.MatchId });
racerIndex++;
}
}
}
return true;
}
public static User GetUser(int userId)
{
using (var connection = new SqlCeConnection($"Data Source=\"{AppDomain.CurrentDomain.BaseDirectory}Data\\UserData.sdf\"; Password=\"test_password\""))
{
return(connection.Query <User>("SELECT * FROM TestUser WHERE TestUserId = @TestUserId", new { TestUserId = userId }).SingleOrDefault());
}
}
public static List <User> GetUsers()
{
using (var connection = new SqlCeConnection($"Data Source=\"{AppDomain.CurrentDomain.BaseDirectory}Data\\UserData.sdf\"; Password=\"test_password\""))
{
return(connection.Query <User>("SELECT * FROM TestUser").ToList());
}
}
public static bool DatasetExists(int datasetId)
{
using (IDbConnection db = new SqlCeConnection(Connection.ConnectionString))
{
return(db.Query <int>($"SELECT 1 FROM Dataset WHERE DatasetId = {datasetId}").ToList().FirstOrDefault() == 1);
}
}
public static List <T> ReadAll <T>(string tableName)
{
using (IDbConnection db = new SqlCeConnection(Connection.ConnectionString))
{
return(db.Query <T>("SELECT * FROM " + tableName).ToList());
}
}
public HttpResponseMessage Put(int matchId, int winningracerid)
{
using (var connection = new SqlCeConnection(Settings.Cnn))
{
connection.Open();
if (winningracerid < 1)
{
connection.Execute("update match set winningracerid=null, modified=null where matchid=@matchId", new { matchId });
return new HttpResponseMessage(HttpStatusCode.OK);
}
connection.Execute("update match set winningracerid=@winningracerid, modified=GETDATE() where matchid=@matchId", new { matchId, winningracerid });
Match match = connection.Query<Match>("select * from match where matchId=@matchId", new {matchId}).First();
var sql = match.NextWinningMatchSlot == 1
? "update match set racer1id=@winningracerid where matchid=@NextWinningMatchId"
: "update match set racer2id=@winningracerid where matchid=@NextWinningMatchId";
connection.Execute(sql, new { match.NextWinningMatchId, winningracerid });
return new HttpResponseMessage(HttpStatusCode.OK);
}
}
public void Update(Category item)
{
var param = new
{
Name = item.Name,
Description = item.Description,
PictureID = item.PictureID,
ParentCategoryID = item.ParentCategoryID,
Alias = item.Alias,
ChangeUser = "******",
ChangeDate = DateTime.Now,
DeleteFlag = false,
ID = item.ID
};
using (SqlCeConnection cn = Connection2)
{
var i = cn.Query <int>(@"update CATEGORIES
set cat_name = @Name,
cat_description = @Description,
cat_alias = @Alias,
cat_parent_category_id = @ParentCategoryID,
cat_picture_id = @PictureID,
cat_change_date = @ChangeDate,
cat_change_user = @ChangeUser,
cat_delete_flag = @DeleteFlag
where cat_id = @ID", param);
}
}
public List<ErrorLog> GetLogs()
{
var result = new List<ErrorLog>();
IEnumerable<string> logs;
using (IDbConnection connection = new SqlCeConnection(Connection))
{
connection.Open();
var query = _settingsManager.GetMaxNumberOfLogs() > -1 ?
string.Format("SELECT TOP {0} [AllXml] FROM [ELMAH_Error] ORDER BY [Sequence] DESC;", _settingsManager.GetMaxNumberOfLogs()) :
"SELECT [AllXml] FROM [ELMAH_Error] ORDER BY [Sequence] DESC";
logs = connection.Query<string>(query);
}
foreach (var log in logs)
{
var errorLog = _parser.Parse(log);
if (errorLog == null)
{
_log.Error(string.Format("Failed to parse file: {0}", log));
continue;
}
result.Add(errorLog);
}
return result;
}
public static MessageItem GetSingleMessage(int id)
{
try
{
using (var cn = new SqlCeConnection(ConnString))
{
cn.Open();
var p = new DynamicParameters();
p.Add("@messageId", id);
var res = cn.Query("SELECT MessageId, MessageValue, OnLoginPage, OnHomePage, IsActive, Style FROM Messages WHERE MessageId=@MessageId", p);
if (res == null)
{
return(null);
}
return(res.Select(x => new MessageItem
{
Id = x.MessageId,
Value = x.MessageValue,
OnLoginPage = x.OnLoginPage,
OnHomePage = x.OnHomePage,
IsActive = x.IsActive,
Style = x.Style
}).FirstOrDefault());
}
}
catch (Exception)
{
return(null);
}
}
public void Add(Category item)
{
var param = new
{
Name = item.Name,
Description = item.Description,
MetaKeyword = item.MetaKeyword,
MetaDescription = item.MetaDescription,
MetaTitle = item.MetaTitle,
PageSize = item.PageSize,
PictureID = item.PictureID,
ParentCategoryID = item.ParentCategoryID,
AllowSelectedPageSize = item.AllowSelectedPageSize,
Alias = item.Alias,
PriceRanges = item.PriceRanges,
ShowOnHomePage = item.ShowOnHomePage,
DisplayOrder = item.DisplayOrder,
AddUser = "******",
AddDate = DateTime.Now,
DeleteFlag = false
};
using (SqlCeConnection cn = Connection2)
{
var i = cn.Query <int>(@"INSERT INTO CATEGORIES
(cat_name, cat_description,cat_alias,cat_meta_keyword,cat_parent_category_id,cat_display_order,
cat_picture_id,
cat_add_date, cat_add_user, cat_delete_flag)
VALUES(@Name,@Description,@Alias,@MetaKeyword,@ParentCategoryID,@DisplayOrder,@PictureID,@AddDate,@AddUser,@DeleteFlag)"
, param);
}
}
public static KeyValuePair <string, string> GetKeyValue(string key)
{
try
{
using (var cn = new SqlCeConnection(ConnString))
{
cn.Open();
var p = new DynamicParameters();
p.Add("@key", key);
var res = cn.Query("SELECT KeyName, ValueString FROM KeysTable WHERE KeyName=@key", p);
if (res == null)
{
return(new KeyValuePair <string, string>(key, ""));
}
return(res.Select(x => new KeyValuePair <string, string>(x.KeyName, x.ValueString)).FirstOrDefault());
}
}
catch (Exception)
{
return(new KeyValuePair <string, string>(key, ""));
}
}
public static IEnumerable <UserItem> GetUsers()
{
try
{
using (var cn = new SqlCeConnection(ConnString))
{
cn.Open();
var p = new DynamicParameters();
var res = cn.Query("SELECT UserId, UserName, Password, Rights, IsActive FROM Users", p);
if (res == null)
{
return(null);
}
return(res.Select(x => new UserItem
{
Id = x.UserId,
Name = x.UserName,
Password = x.Password,
Rights = x.Rights,
IsActive = x.IsActive
}));
}
}
catch (Exception)
{
return(null);
}
}
public static IEnumerable <BookItem> GetBooks()
{
try
{
using (var cn = new SqlCeConnection(ConnString))
{
cn.Open();
var p = new DynamicParameters();
var res = cn.Query("SELECT BookId, BookName, Chapters, Aliases FROM Books ORDER BY BookId", p);
if (res == null)
{
return(null);
}
return(res.Select(x => new BookItem
{
Id = x.BookId,
Name = x.BookName,
Chapters = x.Chapters,
Aliases = x.Aliases
}));
}
}
catch (Exception)
{
return(null);
}
}
public static IEnumerable <GlossaryItem> GetAllTermsFull()
{
try
{
using (var cn = new SqlCeConnection(ConnString))
{
cn.Open();
var p = new DynamicParameters();
p.Add("@lastDate", DateTime.Now.AddDays(-Convert.ToInt32(ConfigurationManager.AppSettings["days"])));
var res = cn.Query("SELECT TermId, Term, Definition, DateCreated, DateModified, CONVERT(bit,CASE WHEN DateModified > @lastDate THEN 1 ELSE 0 END) AS IsModified, CONVERT(bit,CASE WHEN DateCreated > @lastDate THEN 1 ELSE 0 END) AS IsNew FROM GlossaryTerms ORDER BY Term", p);
if (res == null)
{
return(null);
}
return(res.Select(x => new GlossaryItem
{
Id = x.TermId,
Term = x.Term,
Definition = x.Definition,
DateCreated = x.DateCreated,
DateModified = x.DateModified,
IsModified = x.IsModified,
IsNew = x.IsNew
}));
}
}
catch (Exception)
{
return(null);
}
}
public void Add(Product item)
{
//using (IDbConnection cn = Connection)
using (SqlCeConnection cn = Connection2)
{
var parameter = new
{
Name = item.Name,
Description = item.Description,
Price = item.Price,
ShortDescription = item.ShortDescription,
SKU = item.SKU,
ManufacturePartNo = item.ManufacturePartNo,
StockQuantity = item.StockQuantity,
ProductGuid = item.ProductGuid,
AddUser = Environment.UserName,
AddDate = DateTime.Now,
DeleteFlag = false
};
cn.Open();
try
{
var i = cn.Query <int>(@"INSERT INTO Products (prd_name,prd_description,prd_price,prd_short_description,
prd_sku,prd_manufacturepart_no,prd_stock_quatity, prd_guid
,prd_add_user,prd_add_date,prd_delete_flag)
VALUES(@Name,@Description,@Price,@ShortDescription
,@SKU,@ManufacturePartNo,@StockQuantity,@ProductGuid,@AddUser,@AddDate,@DeleteFlag)", parameter);
}
catch { }
}
}
public bool AssignRandomStartSlots()
{
using (var connection = new SqlCeConnection(Settings.Cnn))
{
connection.Open();
connection.Execute("update racer set startslot=-1");
var numbers = Enumerable.Range(1, 100).ToList();
var racers = connection.Query<Racer>("select * from racer order by racerid").ToList();
var rnd = new Random();
foreach (var racer in racers)
{
var x = rnd.Next(0, numbers.Count);
racer.StartSlot = numbers[x];
numbers.RemoveAt(x);
connection.Execute("update racer set startslot=@StartSlot where racerid=@RacerId", new { racer.StartSlot, racer.RacerId });
}
}
return true;
}
public bool SelfInvoiceDelete(SelfInvoicesMaster selfInvoice)
{
using (var conn = new SqlCeConnection(
ConfigurationManager.ConnectionStrings["ContoDatabase"].ConnectionString))
{
conn.Open();
var selfInvoices = conn.Query <SelfInvoices>("SELECT * FROM SelfInvoices WHERE InvoiceGroupId = @InvoiceGroupId AND CashFlowId IS NULL", new
{
selfInvoice.InvoiceGroupId
});
var totInCashFlow = selfInvoices != null?selfInvoices.Count() : 0;
if (totInCashFlow == 0)
{
return(false);
}
conn.Execute("DELETE FROM SelfInvoices WHERE InvoiceGroupId = @InvoiceGroupId",
new
{
selfInvoice.InvoiceGroupId
});
}
return(true);
}
public void MultiRSSqlCE()
{
if (File.Exists("Test.sdf"))
File.Delete("Test.sdf");
var cnnStr = "Data Source = Test.sdf;";
var engine = new SqlCeEngine(cnnStr);
engine.CreateDatabase();
using (var cnn = new SqlCeConnection(cnnStr))
{
cnn.Open();
cnn.Execute("create table Posts (ID int, Title nvarchar(50), Body nvarchar(50), AuthorID int)");
cnn.Execute("create table Authors (ID int, Name nvarchar(50))");
cnn.Execute("insert Posts values (1,'title','body',1)");
cnn.Execute("insert Posts values(2,'title2','body2',null)");
cnn.Execute("insert Authors values(1,'sam')");
var data = cnn.Query<PostCE, AuthorCE, PostCE>(@"select * from Posts p left join Authors a on a.ID = p.AuthorID", (post, author) => { post.Author = author; return post; }).ToList();
var firstPost = data.First();
firstPost.Title.IsEqualTo("title");
firstPost.Author.Name.IsEqualTo("sam");
data[1].Author.IsNull();
cnn.Close();
}
}
public static IList <T> ConvertSqlQueryToIList <T>(this SqlCeConnection SqlConn, T domainClass, string sqlQueryCommand)
{
ConnectionState _ConnectionState = ConnectionState.Closed;
try
{
if (SqlConn.State != ConnectionState.Open)
{
SqlConn.Open();
_ConnectionState = SqlConn.State;
}
IList <T> result = SqlConn.Query <T>(sqlQueryCommand).AsList();
return(result);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (_ConnectionState == ConnectionState.Open)
{
SqlConn.Close();
}
}
}
public void MultiRSSqlCE()
{
if (File.Exists("Test.DB.sdf"))
{
File.Delete("Test.DB.sdf");
}
const string cnnStr = "Data Source = Test.DB.sdf;";
var engine = new SqlCeEngine(cnnStr);
engine.CreateDatabase();
using (var cnn = new SqlCeConnection(cnnStr))
{
cnn.Open();
cnn.Execute("create table Posts (ID int, Title nvarchar(50), Body nvarchar(50), AuthorID int)");
cnn.Execute("create table Authors (ID int, Name nvarchar(50))");
cnn.Execute("insert Posts values (1,'title','body',1)");
cnn.Execute("insert Posts values(2,'title2','body2',null)");
cnn.Execute("insert Authors values(1,'sam')");
var data = cnn.Query <PostCE, AuthorCE, PostCE>(@"select * from Posts p left join Authors a on a.ID = p.AuthorID", (post, author) => { post.Author = author; return(post); }).ToList();
var firstPost = data.First();
firstPost.Title.IsEqualTo("title");
firstPost.Author.Name.IsEqualTo("sam");
data[1].Author.IsNull();
}
}
public bool MaterialDelete(Material material)
{
using (var conn = new SqlCeConnection(
ConfigurationManager.ConnectionStrings["ContoDatabase"].ConnectionString))
{
conn.Open();
var ret = conn.Query <int>("SELECT Count(*) FROM SelfInvoices WHERE MaterialId = @Id", new
{
material.Id
});
var totUsedMaterials = ret != null?ret.First() : 0;
if (totUsedMaterials > 0)
{
return(false);
}
conn.Execute("DELETE FROM Materials WHERE Id = @Id",
new
{
material.Id
});
}
return(true);
}
public static IEnumerable <EmailItem> GetEmails()
{
try
{
using (var cn = new SqlCeConnection(ConnString))
{
cn.Open();
var p = new DynamicParameters();
var res = cn.Query("SELECT Email, VerifyCode FROM Emails ORDER BY Email", p);
if (res == null)
{
return(null);
}
return(res.Select(x => new EmailItem
{
Email = x.Email,
VerifyCode = x.VerifyCode
}));
}
}
catch (Exception)
{
return(null);
}
}
public static IEnumerable <MessageItem> GetActiveLoginMesssages()
{
try
{
using (var cn = new SqlCeConnection(ConnString))
{
cn.Open();
var p = new DynamicParameters();
var res = cn.Query("SELECT MessageId, MessageValue, OnLoginPage, OnHomePage, IsActive, Style FROM Messages WHERE IsActive=1 AND OnLoginPage=1", p);
if (res == null)
{
return(null);
}
return(res.Select(x => new MessageItem
{
Id = x.MessageId,
Value = x.MessageValue,
OnLoginPage = x.OnLoginPage,
OnHomePage = x.OnHomePage,
IsActive = x.IsActive,
Style = x.Style
}));
}
}
catch (Exception)
{
return(null);
}
}
public IEnumerable<Match> GetMatchByRound(int roundId)
{
using (var connection = new SqlCeConnection(Settings.Cnn))
{
connection.Open();
return connection.Query<Match>("select * from match where roundid=@roundId", new {roundId});
}
}
public IEnumerable<Match> Get()
{
using (var connection = new SqlCeConnection(Settings.Cnn))
{
connection.Open();
return connection.Query<Match>("select * from match");
}
}
// GET api/racers
public IEnumerable<Racer> Get()
{
using (var connection = new SqlCeConnection(Settings.Cnn))
{
connection.Open();
return connection.Query<Racer>("select * from racer order by RacerId");
}
}
public static MessageItem UpsertMessage(int id, string text, string style, bool isActive, bool onHomePage, bool onLoginPage)
{
try
{
using (var cn = new SqlCeConnection(ConnString))
{
cn.Open();
var p = new DynamicParameters();
p.Add("@messageId", id);
p.Add("@messagevalue", text);
p.Add("@style", style);
p.Add("@isActive", isActive);
p.Add("@onLoginPage", onLoginPage);
p.Add("@onHomePage", onHomePage);
string lastPart = "";
string firstquery = "";
string secondquery = "";
if (id > 0)
{
// edit
firstquery = "UPDATE Messages SET MessageValue=@messagevalue, style=@style, isActive=@isActive, onLoginPage=@onloginpage, onhomepage=@onhomepage WHERE MessageId=@MessageId; ";
lastPart = "@messageid";
}
else
{
// insert
firstquery = "INSERT Messages(MessageValue, Style, IsActive, OnLoginPage, OnHomePage) VALUES (@MessageValue, @Style, @IsActive, @OnLoginPage, @OnHomePage);";
lastPart = "@@IDENTITY";
}
secondquery = "SELECT MessageId, MessageValue, OnLoginPage, OnHomePage, IsActive, Style FROM Messages WHERE MessageId=" + lastPart;
var exec = cn.Execute(firstquery, p);
var res = cn.Query(secondquery, p);
if (res == null)
{
return(null);
}
return(res.Select(x => new MessageItem
{
Id = x.MessageId,
Value = x.MessageValue,
OnLoginPage = x.OnLoginPage,
OnHomePage = x.OnHomePage,
IsActive = x.IsActive,
Style = x.Style
}).FirstOrDefault());
}
}
catch (Exception ex)
{
throw ex;
}
}
static void Main(string[] args)
{
var oldFile = System.IO.Path.Combine(Environment.CurrentDirectory, "PoSh.sdf");
var newFile = System.IO.Path.Combine(Environment.CurrentDirectory, "PoShNew.sdf");
using (var oldConn = new SqlCeConnection("Data Source=" + oldFile))
using (var newConn = new SqlCeConnection("Data Source=" + newFile))
{
oldConn.Open();
newConn.Open();
newConn.Execute("delete from timecard");
newConn.Execute("delete from bartender");
var countBefore = newConn.Query <int>("select count(id) from bartender").Single();
var bts = oldConn.Query("select * from bartender");
newConn.Execute("insert into bartender(id, name, hire, fire, pin) values(@Id,@Name,@Hire,@Fire,@Pin)", bts);
var countAfter = newConn.Query <int>("select count(id) from bartender").Single();
bool worked = countAfter > countBefore;
var last = newConn.Query <DateTime>("select max(ClockOut) from timecard").Single();
var newTimecards = oldConn.Query <Timecard>("Select * from timecard where ClockOut > @LastClockOut", new
{
LastClockOut = last
}
);
newConn.Execute("insert into timecard(Id, BartenderId, ClockIn, ClockOut) values (@Id, @BartenderId, @ClockIn, @ClockOut)", newTimecards);
var result = newConn.Query("Select * from timecard where ClockOut > @LastClockOut", new
{
LastClockOut = last
}
);
foreach (var item in result)
{
Console.WriteLine("{0} {1} {2}", item.BartenderId, item.ClockIn, item.ClockOut);
}
}
Console.Read();
}
public Measures MeasureGet(int id)
{
using (var conn = new SqlCeConnection(
ConfigurationManager.ConnectionStrings["ContoDatabase"].ConnectionString))
{
conn.Open();
return(conn.Query <Measures>("SELECT * FROM Measures WHERE Id = @Id", new { Id = id }).SingleOrDefault());
}
}
public List <SelfInvoicesMaster> SelfInvoicesMasterGet()
{
using (var conn = new SqlCeConnection(
ConfigurationManager.ConnectionStrings["ContoDatabase"].ConnectionString))
{
conn.Open();
return(conn.Query <SelfInvoicesMaster>("SELECT SelfInvoices.InvoiceGroupId, Materials.Description AS MaterialDescription, SUM(SelfInvoices.Quantity) AS Quantity, SUM(SelfInvoices.InvoiceCost) AS Cost, SelfInvoices.InvoiceDate, COUNT(*) AS InvoiceCount, SelfInvoices.MeasureId, Measures.Description AS MeasuresDescription, SelfInvoices.MaterialId, SelfInvoices.VatExcept, SelfInvoices.InvoiceYear FROM SelfInvoices INNER JOIN Materials ON SelfInvoices.MaterialId = Materials.Id INNER JOIN Measures ON Measures.Id = SelfInvoices.MeasureId WHERE SelfInvoices.InCashFlow = 0 GROUP BY SelfInvoices.InvoiceGroupId, Materials.Description, SelfInvoices.InvoiceDate, SelfInvoices.MeasureId, Measures.Description, SelfInvoices.MaterialId, SelfInvoices.VatExcept, SelfInvoices.InvoiceYear ORDER BY SelfInvoices.InvoiceDate DESC").ToList());
}
}
public Settings GetSettings()
{
using (var conn = new SqlCeConnection(
ConfigurationManager.ConnectionStrings["ContoDatabase"].ConnectionString))
{
conn.Open();
return(conn.Query <Settings>("SELECT * FROM Settings").FirstOrDefault());
}
}
public List <SelfInvoices> SelfInvoicesGet()
{
using (var conn = new SqlCeConnection(
ConfigurationManager.ConnectionStrings["ContoDatabase"].ConnectionString))
{
conn.Open();
return(conn.Query <SelfInvoices>("SELECT * FROM SelfInvoices").ToList());
}
}
public List <Material> MaterialsGet()
{
using (var conn = new SqlCeConnection(
ConfigurationManager.ConnectionStrings["ContoDatabase"].ConnectionString))
{
conn.Open();
return(conn.Query <Material>("SELECT Materials.Id, Materials.Description, Materials.Price, Materials.MeasureId, Measures.Description AS MeasureDescription FROM Materials JOIN Measures ON Materials.MeasureId = Measures.Id").ToList());
}
}
public Material MaterialGet(long id)
{
using (var conn = new SqlCeConnection(
ConfigurationManager.ConnectionStrings["ContoDatabase"].ConnectionString))
{
conn.Open();
return(conn.Query <Material>("SELECT * FROM Materials WHERE Id = @Id", new { Id = id }).SingleOrDefault());
}
}
public IEnumerable <Category> GetProductByCategory()
{
using (SqlCeConnection cn = Connection2)
{
cn.Open();
var category = cn.Query <Category>("select * from categories");
return(category);
}
}
static void Main(string[] args)
{
// パターン１
//using (var cn = new SqlCeConnection(constr))
//{
// cn.Open();
// var sql = "select ID, Name , Age , Email From Employee;";
// var result = cn.Query(sql);
// foreach (var d in result)
// {
// Console.WriteLine("ID:{0} , Name:{1} , Age:{2} , Email:{3}",
// d.ID,
// d.Name,
// d.Age,
// d.Email);
// }
//}
// パターン2
//using (var cn = new SqlCeConnection(constr))
//{
// cn.Open();
// var sql = "select ID, Name , Age , Email From Employee;";
// var result = cn.Query<EmployeeEntity>(sql);
// foreach (var d in result)
// {
// Console.WriteLine("ID:{0} , Name:{1} , Age:{2} , Email:{3}",
// d.ID,
// d.Name,
// d.Age,
// d.Email);
// }
//}
// パターン3
using (var cn = new SqlCeConnection(constr))
{
cn.Open();
var sql = "select ID, Name , Age , Email From Employee where Age > @Age;";
var result = cn.Query<EmployeeEntity>(sql, new { Age = 25 });
foreach (var d in result)
{
Console.WriteLine("ID:{0} , Name:{1} , Age:{2} , Email:{3}",
d.ID,
d.Name,
d.Age,
d.Email);
}
}
Console.Read();
}
static void Main(string[] args)
{
var oldFile = System.IO.Path.Combine(Environment.CurrentDirectory, "PoSh.sdf");
var newFile = System.IO.Path.Combine(Environment.CurrentDirectory, "PoShNew.sdf");
using(var oldConn = new SqlCeConnection("Data Source=" + oldFile))
using(var newConn = new SqlCeConnection("Data Source=" + newFile))
{
oldConn.Open();
newConn.Open();
newConn.Execute("delete from timecard");
newConn.Execute("delete from bartender");
var countBefore = newConn.Query<int>("select count(id) from bartender").Single();
var bts = oldConn.Query("select * from bartender");
newConn.Execute("insert into bartender(id, name, hire, fire, pin) values(@Id,@Name,@Hire,@Fire,@Pin)", bts);
var countAfter = newConn.Query<int>("select count(id) from bartender").Single();
bool worked = countAfter > countBefore;
var last = newConn.Query<DateTime>("select max(ClockOut) from timecard").Single();
var newTimecards = oldConn.Query<Timecard>("Select * from timecard where ClockOut > @LastClockOut", new
{
LastClockOut = last
}
);
newConn.Execute("insert into timecard(Id, BartenderId, ClockIn, ClockOut) values (@Id, @BartenderId, @ClockIn, @ClockOut)", newTimecards);
var result = newConn.Query("Select * from timecard where ClockOut > @LastClockOut", new
{
LastClockOut = last
}
);
foreach(var item in result)
Console.WriteLine("{0} {1} {2}", item.BartenderId, item.ClockIn, item.ClockOut);
}
Console.Read();
}
public LineCoverage[] ReadAll()
{
if (!File.Exists(_filePath))
return new LineCoverage[0];
using (var connection = new SqlCeConnection(GetConnectionString()))
{
var data = connection.Query<LineCoverage>("SELECT * FROM Coverage");
return data.ToArray();
}
}
public ActionResult SignIn(string username, string password)
{
MyConfig myConfig;
using (var connection = new SqlCeConnection(Settings.Cnn))
{
connection.Open();
myConfig = connection.Query<MyConfig>("select * from MyConfig where MyConfigId=1").FirstOrDefault();
}
if (myConfig != null && myConfig.MyKey == username && myConfig.Myvalue == password)
{
System.Web.Security.FormsAuthentication.SetAuthCookie(username, false);
return RedirectToAction("Index", "Admin");
}
return RedirectToAction("Index");
}
public IEnumerable<int> Get()
{
using (var connection = new SqlCeConnection(Settings.Cnn))
{
connection.Open();
return connection.Query<int>("select * from round");
}
}
public string GetStats()
{
using (var connection = new SqlCeConnection(Settings.Cnn))
{
connection.Open();
var completedRaces = connection.Query<int>("select count(*) from match where winningracerid is not null;").FirstOrDefault();
var d1 = connection.Query("select min(modified) as minDate from match where winningracerid is not null;").FirstOrDefault();
var d2 = connection.Query("select max(modified) as maxDate from match where winningracerid is not null;").FirstOrDefault();
var minDate = ((DateTime)d1.minDate).AddHours(-7);
var maxDate = ((DateTime)d2.maxDate).AddHours(-7);
TimeSpan ts = maxDate - minDate;
double avgSec = ts.TotalSeconds/completedRaces;
double estimatedFinish = 99*avgSec;
var estimatedFinishdate = DateTime.Now.AddSeconds(estimatedFinish).AddHours(-7);
return string.Format("{{min:\"{0}\", \nmax:\"{1}\", \ntotalSec:\"{2}\", \navgSec:\"{3}\", \nestimatedFinish:\"{4}\"}}",
minDate, maxDate, ts.TotalSeconds, avgSec, estimatedFinishdate);
}
}
