Note: this post is related to the 0.4.0 version of the owin project. The project has since been renamed to edge.js and has seen major improvements. Visit edge.js for the latest.

The owin project allows hosting .NET 4.5 code in node.js applications running on Windows. In my previous posts I described how owin can be used to implement express.js request handlers and connect middleware in .NET as well as run CPU-bound computations implemented in .NET within the node.js process.

In this post I will focus on showing how to access a SQL database from a node.js application by hosting CLR in-process and using asynchronous ADO.NET mechanisms, all without writing a single line of C# code.

What you need

You need Windows x64 with node.js 0.8.x x64 installed (the owin module had been developed against node.js 0.8.19). You also need .NET Framework 4.5 on the machine.

The code snippets below assume you have a working MS SQL connection string to a sample Northwind SQL database. If the database is deployed on a development machine, the connection string most of the time looks like Data Source=(local);Initial Catalog=Northwind;Integrated Security=True.

Select

First install the owin module with

1
   npm install [email protected]

Then in your test.js:

1
2
3
4
5
6
7
var owin = require('owin');  
  
owin.sql("select * from Region", function (error, result) {  
    if (error) throw error;  
    console.log(result);  
});
  

Before you run the code, you need to set the connection string to the Northwind SQL database using the OWIN_SQL_CONNECTION_STRING environment variable, e.g:

1
2
set OWIN_SQL_CONNECTION_STRING=Data Source=(local);Initial Catalog=Northwind;Integrated Security=True
  

Now you are ready to run the node.js app with:

1
2
node test.js
  

You will see the following output:

1
2
3
4
5
6
7
C:\projects\owin>;node test.js  
[ [ 'RegionID', 'RegionDescription' ],  
  [ 1, 'Eastern                                           ' ],  
  [ 2, 'Western                                           ' ],  
  [ 3, 'Northern                                          ' ],  
  [ 4, 'Southern                                          ' ] ]
  

The result of the query is a JavaScript array. The first element of the array is an array of column names. Subsequent elements of the array are rows representing the results of the SQL select query against the database.

Insert

You can insert data into a SQL database with the following code:

1
2
3
4
5
6
7
var owin = require('owin');  
  
owin.sql("insert into Region values (5, 'Pacific Northwest')", function (error, result) {  
    if (error) throw error;  
    console.log(result);  
});
  

The result of running this applicaiton indicates how many rows in a SQL table have been affected:

1
2
3
C:\projects\owin>node test.js  
1
  

Now when you run the same application again, instead of the result the JavaScript callback will receive an error containing a .NET ADO.NET exception indicating a primary key violation in a SQL database, since a row with this ID already exists:  

1
2
3
4
5
6
7
8
C:\projects\owin>node test.js  
C:\projects\owin\test.js:9  
        if (error) throw error;  
                         ^  
System.AggregateException: One or more errors occurred. ---> System.Data.SqlClient.SqlException: Violation of PRIMARY KE  
Y constraint 'PK_Region'. Cannot insert duplicate key in object 'dbo.Region'.  
The statement has been terminated.
  

Update and delete

Similarly to insert, you can execute SQL update command, followed by a select showing the state of the table:

1
2
3
4
5
6
7
8
9
10
11
var owin = require('owin');  
  
owin.sql("update Region set RegionDescription='Washington and Oregon' where RegionID=5", function (error, result) {  
    if (error) throw error;  
    console.log(result);  
    owin.sql("select * from Region", function (error, result) {  
        if (error) throw error;  
        console.log(result);  
    });  
});
  

The results of running this application are:

1
2
3
4
5
6
7
8
9
C:\projects\owin>node test.js  
1  
[ [ 'RegionID', 'RegionDescription' ],  
  [ 1, 'Eastern                                           ' ],  
  [ 2, 'Western                                           ' ],  
  [ 3, 'Northern                                          ' ],  
  [ 4, 'Southern                                          ' ],  
  [ 5, 'Washington and Oregon                             ' ] ]
  

The delete SQL command removes rows from the table:

1
2
3
4
5
6
7
var owin = require('owin');  
  
owin.sql("delete Region where RegionID > 4", function (error, result) {  
    if (error) throw error;  
    console.log(result);  
});
  

and similarly to insert and update reports the number of rows affected:

1
2
3
C:\projects\owin>node test.js  
1
  

How it works

Owin is a native node.js module implemented in C++\CLI that runs on Windows. It hosts CLR runtime within the node.js process. The owin.sql() function invokes ADO.NET asynchronously on a CLR thread within the node.exe process and uses .NET framework to perform the SQL operation. The owin module utilizes the OWIN (http://owin.org) interface to bridge between JavaScript, native, and CLR code. The module takes care of marshaling data between V8 and CLR heaps as well as reconciling threading models. The SQL operation is running asynchronously on CLR threads while the node.js event loop remains unblocked.

More

Visit the project page at https://github.com/tjanczuk/owin for the latest bits.

Make sure to check out implementing express.js request handlers and connect middleware in .NET as well as running CPU-bound computations implemented in .NET within the node.js process use cases as well.

Feedback and pull requests welcome.