CODEWITHSUNDEEP
kentico
masterlopau
masterlopau

Reputation: 563

Is there an existing method to pull the list of states from cms.state class in Kentico?

I need to pull the list of states (US) from cms_state table but wondering if there are existing api in Kentico that can do this before I use SQL.

Thanks!

Upvotes: 1

Views: 423

Answers (1)

Scott Baldric
Scott Baldric

Reputation: 465

There is no direct way of fetching the US states directly, unfortunately. However, using the DataQuery API introduced in Kentico 8, it is easy to construct a strongly typed query (or set of queries) that will fetch the list of US states.

There are two ways we can go about doing this. One assumes you have the country info object already, the other assumes that you don't and/or you'd like to execute the fetch of all the states in one database query. To note, these have been tested on Kentico 11, but should work as far back as Kentico 8 as both providers have all of the methods referenced below in the API documentation for CountryInfoProvider and StateInfoProvider.

CountryInfo Already Fetched

var country = CountryInfoProvider.GetCountryInfoByCode("USA");
var states = StateInfoProvider.GetStates()
    .WhereEquals(nameof(StateInfo.CountryID), country.CountryID);

This will fetch the country with the code USA, and then you will fetch all of the states that match the CountryID. This uses at most two SQL queries to fetch all of the states.

There is also a StateInfoProvider.GetCountryStates() method that is provided, but as of Kentico 11 it has been marked obsolete and the StateInfoProvider.GetStates() method is recommended instead.

Single Database Query

var states = StateInfoProvider.GetStates()
            .WhereEquals(nameof(StateInfo.CountryID),
                CountryInfoProvider.GetCountries()
                    .WhereEquals(nameof(CountryInfo.CountryThreeLetterCode), "USA")
                    .TopN(1)
                    .Column(nameof(CountryInfo.CountryID)));

This will create a SQL query that has a sub-select that fetches the first CountryID that matches the Three Letter Country Code USA, and then fetches all of the states that match that CountryID.

We don't use the .Source() method here because both the CMS_State and CMS_Country share the CountryID column which makes it slightly more complicated to access the appropriate columns in the where clause.

Upvotes: 1

Related Questions