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) { // パターン1 //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); } }