List customers (SELECT)

best practice for production apps is to use rest as the data layer to access data and not directly integrate to sql using the sql data provider the sql data provider will be squiggled in blue to indicate it is not recommended, together with a message to use rest docid\ jrbansm ojn3nf4 dn hu instead see rest endpoints from azure sql docid\ eoui2cpyynsdruk tobdp for more information scenario create a list of customers showing their names, email addresses, and locations on the left is an avatar, and on the right is the country the customer resides in resources scripts for creating azure sql tables and stored procedures database scripts docid\ jq3eq0tn7t5yl8vng 7qh configuring the sql connection docid\ o8lfnt4lwq1fvfvg1lelh jigx code the azure sql docs solution is on github list of customers how it works this example selects a list of data from a customer table in an azure sql database and returns it to the jigx solution on the device using the sql data provider's function, where it is stored in the sqlite database in the list {{jig}} the data is selected from the sqlite database using a sql query in a data source which in turn is used by the list {{jig}} to render the items functions the {{jigx}} function is listed twice, once for executing a stored procedure and once for executing a query a store procedure based version of get customers jigx the following {{jigx}} function uses a stored procedure to fetch a list of data from azure sql get customers jigx # jigx sql function executing a stored procedure to select all customers from a table provider data provider sql connection customer azure # use manage jigx com to configure a sql connection method execute #use sql stored procedure to interact with the data in sql procedure sp getallcustomers a query based version of get customers jigx the following {{jigx}} function uses a sql query to fetch a list of data from azure sql get customers jigx # jigx sql function executing a query to select all customers from a table provider data provider sql connection customer azure # use manage jigx com to configure a sql connection method query #use sql statements to interact with the data in sql query | select id, first name, last name, email, phone number, address line1, address line2, city, state, zip code, country from customers {{jig}} (screen) use a list type to configure a list of customers since the data is already synced to the local sqlite data provider, the {{jig}} s datasource is configured with a query to provide the data for use in the list expressions are used to reference the exact data property required in each component listcustomers jigx # a sample list jig that uses a sql function to return and display a list of customers from azure sql title list customers description show a list of all customers in a sql database type jig list icon contact \# header section displaying an image at the top of the screen header type component jig header options height medium children type component image options source uri https //images unsplash com/photo 1553413077 190dd305871c?ixlib=rb 4 0 3\&ixid=mnwxmja3fdb8mhxwag90by1wywdlfhx8fgvufdb8fhx8\&auto=format\&fit=crop\&w=1035\&q=80 \# onfocus is triggered whenever the jig is displayed the sync entities action calls the jigx sql function and populates the local sqlite tables on the device with the data returned from azure sql onfocus type action sync entities options provider data provider sql entities \ entity customers function get customers \# the mydata data source selects the data from the local sqlite database datasources mydata type datasource sqlite options provider data provider local entities \ entity customers query | select id, '$ first name', '$ last name', '$ email', '$ phone number', '$ address line1', '$ address line2', '$ city', '$ state', '$ zip code', '$ country' from \[customers] \# the list and its list items are configured below this is a list jig; therefore, its properties, such as data and item, are top level properties \# the data property binds the list to a specific data source data =@ctx datasources mydata \# the item property specifies the list item type and its attributes item type component list item options title =@ctx current item first name & ' ' & @ctx current item last name subtitle =@ctx current item email description | \=@ctx current item address line1 & ' ' & @ctx current item city & ' ' & @ctx current item state & ' ' & @ctx current item zip code label title =@ctx current item country leftelement element avatar \# the text property of the left element is specified using a jsonata expression that builds a two letter string by concatenating the first letters of the customer's first and last names text =$substring(@ctx current item first name,0,1) & $substring(@ctx current item last name,0,1) divider solid index add the list of customers {{jig}} to the home screen index jigx name azure sql docs title azure sql docs category analytics tabs home jigid listcustomers icon home apps logo