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. 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")