SQL

Collection of SQL functions.

Methods

execute

execute(connectionString: string | {

                                server: string,
                                authenticationType?: string,
                                userName: string,
                                password: string,
                                database: string,
                                port?: Number,
                                encrypt?: Boolean,
                            }, query: string, params?: { name: string, value: any, type: SQL.TYPES }[]) 

Executes a T-SQL query. Returns an array of rows with columns as key/value pairs.

Parameters

connectionString *string | {

                                server: string,
                                authenticationType?: string,
                                userName: string,
                                password: string,
                                database: string,
                                port?: Number,
                                encrypt?: Boolean,
                            }*

The connection string or object to the SQL database.

query string

The SQL statement to be executed.

params { name: string, value: any, type: SQL.TYPES }[]

(optional) Parameters to be used within the query. Use parameters to avoid SQL injection. See OWASP SQL Injection cheat sheet

Return type

{ [key: string]: any; }[]

Examples

// Calculates the average of the field 'age' for all users in the table 'Persons':
SQL.execute(connectionString, "SELECT AVG(age) as avg FROM Persons")     // [ { avg: 30 } ]

// Gets all persons with first name 'Joe' from  able 'Persons', using a parameter:
SQL.execute(connectionString, "SELECT * FROM Persons WHERE firstname = @firstname", { name: "firstname", value: "Joe", type: SQL.TYPES.NVarChar })

executeStoredProcedure

executeStoredProcedure(connectionString: string | {

                                server: string,
                                authenticationType: string,
                                userName: string,
                                password: string,
                                database: string,
                                port: Number,
                                encrypt: Boolean,
                            }, query: string, params?: { name: string, value: any, type: SQL.TYPES }[]) 

Executes a SQL Stored Procedure. Use parameters to avoid SQL injection. See OWASP SQL Injection cheat sheet

Parameters

connectionString *string | {

                                server: string,
                                authenticationType: string,
                                userName: string,
                                password: string,
                                database: string,
                                port: Number,
                                encrypt: Boolean,
                            }*

The connection string or object to the SQL database.

query string

The SQL statement to be executed.

params { name: string, value: any, type: SQL.TYPES }[]

(optional) Parameters to be used within the query. Use parameters to avoid SQL injection. See OWASP SQL Injection cheat sheet

Return type

{ [key: string]: any; }[]

Examples

// Execute the Stored Procedure / Stored Function / SQL Function with the name 'GetAllCustomers'
SQL.executeStoredProcedure(connectionString, "GetAllCustomers")