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' }