C# SqlCeConnection.Query方法代码示例

        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 [email protected] where [email protected]", 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 [email protected] where [email protected]", 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 [email protected] where [email protected]", new { racer1Id, match.MatchId });
                        racerIndex++;
                    }
                }
            }

            return true;
        }

        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 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 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 [email protected] where [email protected]", new { racer.StartSlot, racer.RacerId });
                }
            }

            return true;
        }

        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 [email protected]", new { matchId });
                    return new HttpResponseMessage(HttpStatusCode.OK);
                }

                connection.Execute("update match set [email protected], modified=GETDATE() where [email protected]", new { matchId, winningracerid });

                Match match = connection.Query<Match>("select * from match where [email protected]", new {matchId}).First();

                var sql = match.NextWinningMatchSlot == 1
                    ? "update match set [email protected] where [email protected]"
                    : "update match set [email protected] where [email protected]";

                connection.Execute(sql, new { match.NextWinningMatchId, winningracerid });

                return new HttpResponseMessage(HttpStatusCode.OK);
            }
        }

 // 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 IEnumerable<Match> GetMatchByRound(int roundId)
 {
     using (var connection = new SqlCeConnection(Settings.Cnn))
     {
         connection.Open();
         return connection.Query<Match>("select * from match where [email protected]", new {roundId});
     }
 }

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