Eurekode/script_stash_demo_1.md

## script_stash_demo_1.md

      
    Raw
  

              script_stash_demo_1.md
            
          
    Stash example - SQL queries

This page layout aמ example of scripts reuse scenario with SQL files.

The scripts folder :

Our sample scripts folder contains the next three template query files:


Query #1: sqlGetAllUsersWhere.sql
Gets all user table records with according to a given query condition and output user fields.

SELECT [CSV_FIELDS]
FROM USERS
WHERE [USERS_CONDITION]

Query #2: sqlGetAllUsersPhoneWhere.sql
Gets users given fields with related phone numbers from users and phones tables.

SELECT [CSV_FIELDS], PHONES_PhoneNumber as "Phone Number"
FROM USERS INNER JOIN PHONES ON USERS.USER_ID = PHONES.USER_ID
WHERE [USERS_CONDITION]

Query #3: sqlGetAllUsersAddressWhere.sql
Gets users given fields with related home address from users and address tables.

SELECT [CSV_FIELDS], ADDRESS_HomeAddr AS "Home Address"
FROM USERS INNER JOIN ADDRESS ON USERS.USER_ID = ADDRESS.USER_ID
WHERE [USERS_CONDITION]
[!] The [CSV_FIELDS] token will be used to set the queries selected user fields.

[!] The [USERS_CONDITION] token will be used to set the query condition.
The C# Code :

The next code uses ScriptStash.Net lib in order to create three query scripts ready to be send to the
database server. Let's say we need to get users names, country and ages off all users with age above 17,
and also same data with phone numbers or address.

// set up scripts folder location an d SQL file pattern to look for in it.
string pattern = "*.sql";
string scriptsLocation = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location) + "\\scripts";

// set up the same query tokens
string sql_fields_list = "USER_NAME AS \"Name\", USER_COUNTRY AS \"Country\", USER_AGE AS \"Age\"";
string sql_condition_str = "USER_AGE > 17";

// Create stash collection and use the inner tokens dictionary to set up same tokens values.
Stash stash = new Stash(scriptsLocation, pattern);
stash.Tokens[TOKEN_CSV_FIELDS] = sql_fields_list;
stash.Tokens[TOKEN_USERS_CONDITION] = sql_condition_str;

// Get the three queries as 3 strings with the tokens data injected to each template query.
Dictionary<string, string> queries = stash.InjectTokens();
// TBD: use the queries["file name"] to get the SQL string needed to each query and use it on the DB server.
[!] The example is from the unit test project. See code at : ScriptStashUnitTests.StashTests.InjectTokensInnerTokensTest(). Explore the unit tests files to learn about more flexiable ways to reuse script files with ScriptStash.Net.