edge-sql

MS SQL Server compiler for Edge.js.

This library is based on https://github.com/tjanczuk/edge-sql all credit for original work goes to Tomasz Janczuk.

Overview

• Supports returning multiple results from queries
• Supports any type of SQL statement allowing to run complex queries that declare variables, temp tables etc...
• Supports stored procedures with return parameters

NOTE SQL Server Geography and Geometry types are not supported.

Supported .NET frameworks

• .NET 4.6.2
• .NET Core - netstandard 2.0

SQL statement interpretation (statement starts with...)

| SQL Statement | C# Implemetation | | --------------- | -------------------- | | select | ExecuteReaderAsync | | update | ExecuteNonQueryAsync | | insert | ExecuteNonQueryAsync | | delete | ExecuteNonQueryAsync | | exec/execute | ExecuteReaderAsync* | | other | ExecuteReaderAsync |

*Stored procedures with output parameters are executed using ExecuteNonQueryAsync, see examples below

Options

| Option | Usage | | ------------------ | -------------------- | | connectionString | Required. Use environment variable or input option | | source | Optional if no other options are specified | | commandTimeout | Optional |

Simple queries without options

npm install edge-js
npm install edge-sql

You can set your SQL connection string using environment variable. For passing connection string as an option see Using options.

set EDGE_SQL_CONNECTION_STRING=Data Source=localhost;Initial Catalog=Northwind;Integrated Security=True

Simple select

const edge = require('edge-js');

var getTop10Products = edge.func('sql', function () {/*
    select top 10 * from Products
*/});

getTop10Products(null, function (error, result) {
    if (error) throw error;
    console.log(result);
});

Parameterized queries

You can construct a parameterized query once and provide parameter values on a per-call basis:

SELECT

const edge = require('edge-js');

var getProduct = edge.func('sql', function () {/*
    select * from Products 
    where ProductId = @myProductId
*/});

getProduct({ myProductId: 10 }, function (error, result) {
    if (error) throw error;
    console.log(result);
});

UPDATE

const edge = require('edge-js');

var updateProductName = edge.func('sql', function () {/*
    update Products
    set ProductName = @newName 
    where ProductId = @myProductId
*/});

updateProductName({ myProductId: 10, newName: 'New Product' }, function (error, result) {
    if (error) throw error;
    console.log(result);
});

Using options

Passing options to function

const edge = require('edge-js');

var select = edge.func('sql', {
    source: 'select top 10 * from Products',
    connectionString: 'SERVER=myserver;DATABASE=mydatabase;Integrated Security=SSPI',
    commandTimeout: 100
});

select(null, function (error, result) {
    if (error) throw error;
    console.log(result);
});

Select with multiple results

const edge = require('edge-js');

var select = edge.func('sql', {
    source: 'select top 5 * from Authors; select top 5 * from Books',
    connectionString: 'SERVER=myserver;DATABASE=mydatabase;Integrated Security=SSPI',
    commandTimeout: 100
});

select(null, function (error, result) {
    if (error) throw error;
    console.log(result);
});

Result

{
  Authors: [
    { Id: 1, Name: 'Author - 1', Country: 'Country - 1' },
    { Id: 2, Name: 'Author - 2', Country: 'Country - 2' },
    { Id: 3, Name: 'Author - 3', Country: 'Country - 3' },
    { Id: 4, Name: 'Author - 4', Country: 'Country - 4' },
    { Id: 5, Name: 'Author - 5', Country: 'Country - 5' }
  ],
  Books: [
    { Id: 1, Author_id: 485, Price: 64, Edition: 9 },
    { Id: 2, Author_id: 310, Price: 53, Edition: 8 },
    { Id: 3, Author_id: 138, Price: 86, Edition: 3 },
    { Id: 4, Author_id: 88, Price: 62, Edition: 5 },
    { Id: 5, Author_id: 165, Price: 91, Edition: 2 } 
  ]
}

Stored proc with input parameters

const edge = require('edge-js');

var params = {inputParm1: 'input1', inputParam2: 25};

var execProc = edge.func('sql', {
   source: 'exec myStoredProc',
   connectionString: 'SERVER=myserver;DATABASE=mydatabase;Integrated Security=SSPI'
});

execProc(params, function (error, result) {
   if (error) throw error;
   console.log(result);
});

Stored proc with output parameters

Example SQL

CREATE Table Authors
(
   Id int identity primary key,
   Name nvarchar(50),
   Country nvarchar(50)
)

CREATE PROCEDURE GetAuthorDetails
(
    @AuthorID INT,
    @Name NVARCHAR(100) OUTPUT,
    @Country NVARCHAR(100) OUTPUT
)
AS
BEGIN
    SELECT @Name = Name, @Country = Country
    FROM Authors WHERE Id = @AuthorID
END

Javascript

• Return parameter names must start with @returnParam
• Return parameter values must correspond to stored proc output names
• Return parameters will be treated as nvarchar(max)
• Result will return stored proc output names

const edge = require('edge-js');

var execProc = edge.func('sql', {
    source: 'exec GetAuthorDetails',
    connectionString: 'SERVER=myserver;DATABASE=mydatabase;Integrated Security=SSPI'
});

execProc({ AuthorID: 1, '@returnParam1': 'Name', '@returnParam2': 'Country' }, function (error, result) {
    if (error) throw error;
    console.log(result);
});

Result

{ Name: 'Author - 1', Country: 'Country - 1' }