C# SQLiteConnection.Query方法代码示例

        public void TestDapper()
        {
            var dataSource = @"test.sqlite";
            SQLiteConnection.CreateFile(dataSource);
            var builder = new SQLiteConnectionStringBuilder
            {
                DataSource = dataSource,
                LegacyFormat = false,
                Version = 3,
                SyncMode = SynchronizationModes.Off,
                JournalMode = SQLiteJournalModeEnum.Wal
            };

            using (var tran = new TransactionScope(TransactionScopeOption.Required))
            using (var connection = new SQLiteConnection(builder.ToString()))
            {
                connection.Open();
                CreateSampleSchema(connection);

                try
                {
                    connection.Execute("INSERT INTO table1 VALUES (@Id, @Name)", new { Id = 1, Name = "Name-1" });
                    connection.Execute("INSERT INTO table1 VALUES (@Id, @Name)", new { Id = 2, Name = "Name-2" });
                    connection.Execute("INSERT INTO table1 VALUES (@Id, @Name)", new { Id = 3, Name = "Name-3" });

                    dynamic dynamicObj = connection.Query("SELECT id, name FROM table1 LIMIT 1").FirstOrDefault();

                    Console.WriteLine("Id: {0}", dynamicObj.id);
                    Console.WriteLine("Name: {0}", dynamicObj.name);

                    var mappingObj = connection.Query<Table1>("SELECT id, name FROM table1 LIMIT 1").FirstOrDefault();

                    Console.WriteLine("Id: {0}", mappingObj.Id);
                    Console.WriteLine("Name: {0}", mappingObj.Name);

                    dynamic results = connection.Query("SELECT id, name FROM table1");
                    foreach (dynamic item in results)
                    {
                        Console.WriteLine(item);
                    }

                    tran.Complete();
                }
                catch
                {
                }
            }
        }

 public IEnumerable<Inventory> AvailableDryVans()
 {
     var connection = new SQLiteConnection(dbPath);
     connection.Open();
     var DryVans = connection.Query<Inventory>("select * from inventory where status != 'major' and type == 'dry_van' and reserved == 0");
     return DryVans;
 }

        public IToken Find(string userId)
        {
            if (string.IsNullOrWhiteSpace(userId))
                return null;

            using (var connection = new SQLiteConnection(ConnectionString))
            {
                connection.Open();

                var token = connection.Query<Token>(
                    @"SELECT UserId, OrganisationId, ConsumerKey, ConsumerSecret, TokenKey, TokenSecret, ExpiresAt, Session, SessionExpiresAt
                    FROM tokens
                    WHERE UserId = @UserId",
                    new
                    {
                        userId
                    }).FirstOrDefault();

                if (null != token && token.ExpiresAt.HasValue)
                {
                    // This is done because SQLite seems to be storing it as local time.
                    token.ExpiresAt = token.ExpiresAt.Value.ToUniversalTime();
                }

                return token;
            }
        }

 public IEnumerable<Inventory> AvailableFlatBeds()
 {
     var connection = new SQLiteConnection(dbPath);
     connection.Open();
     var FlatBeds = connection.Query<Inventory>("select * from inventory where status != 'major' and type == 'flatbed' and reserved == 0");
     return FlatBeds;
 }

 public IEnumerable<String> CustomerNames()
 {
     var connection = new SQLiteConnection(dbPath);
     connection.Open();
     var names = connection.Query<String>("select distinct customer_name from reservations");
     return names;
 }

 public IEnumerable<Inventory> AvailableReefers()
 {
     var connection = new SQLiteConnection(dbPath);
     connection.Open();
     var Reefers = connection.Query<Inventory>("select * from inventory where status != 'major' and type == 'reefer' and reserved == 0");
     return Reefers;
 }

 public IEnumerable<Inventory> AllReservations()
 {
     var connection = new SQLiteConnection(dbPath);
     connection.Open();
     var reservations = connection.Query<Inventory>("select * from inventory");
     return reservations;
 }

 public IEnumerable<Reservations> CustomerReservations()
 {
     var connection = new SQLiteConnection(dbPath);
     connection.Open();
     var reservationcounts = connection.Query<Reservations>("select * from reservations order by");
     return reservationcounts;
 }

        public ProfileViewModel GetDepartmentEmployees(string departmentName)
        {
            IEnumerable<EmployeeProfile> employees;
            using (var conn =new SQLiteConnection(ConfigurationManager.ConnectionStrings["reedDirectory"].ConnectionString))
            {
                conn.Open();

                var departmentId = conn.Query<long>("SELECT Id from Department_Lookup where Name = @name LIMIT 1", new {name = departmentName.Capitalize()});

                employees = conn.Query<EmployeeProfile>("SELECT dl.Id, dl.Name, ProfileId, FirstName, LastName, Role, Department, ImageUrl, CartoonUrl, Tags, Extension " +
                                                        " FROM employeeprofile e inner join department_lookup dl on e.department = dl.id" +
                                                        " group by dl.Id, dl.Name, ProfileId, FirstName, LastName, Role, Department, ImageUrl, Tags " +
                                                        " Having dl.Id = @departmentId ",
                                                        new { departmentId = departmentId});
            }
            return new ProfileViewModel(employees.LoadHelpers());
        }

 public IEnumerable<Log> GetLogs(DateTime? from = null)
 {
     var date = from.HasValue ? from.Value : DateTime.MinValue;
     using (var connection = new SQLiteConnection(ConnectionString))
     {
         var logs = connection.Query<Log>("select * from Log where date > @date", date);
         return logs;
     }
 }

        private static IEnumerable<MajorCity> QueryCities(string query, object paramaters = null)
        {
            using (IDbConnection con = new SQLiteConnection("Data Source=MajorCities.db"))
            {
                con.Open();

                return con.Query<MajorCity>(query, paramaters);
            }
        }

        public static IEnumerable<EmployeeProfile> LoadHelpers(this IEnumerable<EmployeeProfile> profiles)
        {
            using (DbConnection conn = new SQLiteConnection(ConfigurationManager.ConnectionStrings["reedDirectory"].ConnectionString))
            {
                conn.Open();

                foreach (var profile in profiles)
                {
                  profile.ImageUrl = LoadDefaultProfileImage(profile);
                    profile.RoleLookup = conn.Query<Role_Lookup>(CreateQuery(profileRoleQuery, "@RoleId"), new { RoleId = profile.Role }).SingleOrDefault();
                    profile.SeatingFloorLookup = conn.Query<SeatingFloor_Lookup>(CreateQuery(profileFloorQuery, "@FloorId"), new { FloorId = profile.SeatingFloor }).SingleOrDefault();
                    profile.DepartmentLookup = conn.Query<Department_Lookup>(CreateQuery(profileDepartmentQuery, "@DepartmentId"), new { DepartmentId = profile.Department }).SingleOrDefault();
                }

                conn.Close();
            }

            return profiles;
        }

        public static EmployeeProfile LoadHelper(this EmployeeProfile profile)
        {
            using (DbConnection conn = new SQLiteConnection(ConfigurationManager.ConnectionStrings["reedDirectory"].ConnectionString))
            {
              if (profile != null)
              {
                conn.Open();

                profile.ImageUrl = LoadDefaultProfileImage(profile);
                  profile.RoleLookup = conn.Query<Role_Lookup>(CreateQuery(profileRoleQuery, "@RoleId"), new { RoleId = profile.Role }).SingleOrDefault();

                    profile.SeatingFloorLookup = conn.Query<SeatingFloor_Lookup>(CreateQuery(profileFloorQuery, "@FloorId"), new { FloorId = profile.SeatingFloor}).SingleOrDefault();
                    profile.DepartmentLookup = conn.Query<Department_Lookup>(CreateQuery(profileDepartmentQuery, "@DepartmentId"), new { DepartmentId = profile.Department }).SingleOrDefault();
                conn.Close();
              }
            }

            return profile;
        }

        public HttpResponseMessage GetPersons()
        {
            var dbpath = System.Web.Hosting.HostingEnvironment.MapPath("~/App_Data");
              var dbfile = dbpath + "/people.db3";

              using (var conn = new SQLiteConnection("Data Source=" + dbfile))
              {
            var people = conn.Query<Person>("select * from person");
            return Request.CreateResponse(HttpStatusCode.OK, people);
              }
        }

        /*
        public static Choices GetGrammar(string contextname = null)
        {
            if (String.IsNullOrEmpty(contextname))
            {
                var commands = new Choices();
                using (SQLiteConnection sqlite = new SQLiteConnection(GlobalManager.SQLCHAIN))
                {

                    List<SENTENCES> sentence = sqlite.Query<SENTENCES>("SELECT * from SENTENCES").ToList();
                    foreach (SENTENCES sen in sentence)
                    {
                        sen.CMD = sqlite.Query<COMMANDS>(String.Format("SELECT * FROM COMMANDS where COMMANDS.ID in (select CMDID from TRIGGERCMD where SENID = {0})", sen.SENID)).Single();
                        commands.Add(new SemanticResultValue(sen.SENTENCE, sen.CMD.CMD));
                    }
                }
                return commands;
            }
            return null;
        }*/
        public static Context GetContext(string ContextName = null)
        {
            SYSCONTEXT = new Context();
            using (SQLiteConnection sqlite = new SQLiteConnection(GlobalManager.SQLCHAIN))
            {
                string sql = "select * from SENTENCES INNER JOIN COMMANDS on COMMANDS.ID in (select CMDID from TRIGGERCMD where SENTENCES.SENID = TRIGGERCMD.SENID) INNER JOIN MODULES on MODULES.NAME = COMMANDS.MODULENAME";
                SYSCONTEXT.SENTENCESLIST = new List<SENTENCES>();
                SYSCONTEXT.SENTENCESLIST = sqlite.Query<SENTENCES, COMMANDS, MODULES, SENTENCES>(sql, (sentence, command, module) => { command.MODULE = module; sentence.CMD = command; return sentence; }).ToList<SENTENCES>();
            }
            return SYSCONTEXT;
        }