Skip to content

Commit 2a36f9a

Browse files
jodebruijodebrui
authored
Merge pull request microsoft#64 from JocaPC/master
NodeJS/JSON sample with documentation updates
2 parents f93e4bc + 6d29a5c commit 2a36f9a

File tree

14 files changed

+447
-7
lines changed

14 files changed

+447
-7
lines changed

‎.gitignore

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -24,4 +24,6 @@ samples/databases/wide-world-importers/sample-scripts/always-encrypted/PopulateA
2424
*.zip
2525
samples/features/in-memory/ticket-reservations/packages/CircularGauge.1.0.0/CreatePackageFile.bat
2626
samples/features/in-memory/ticket-reservations/TicketReservations/TicketReservations.dbmdl
27-
samples/databases/wide-world-importers/workload-drivers/vehicle-location-insert/MultithreadedInMemoryTableInsert/obj/Release/TemporaryGeneratedFile_E7A71F73-0F8D-4B9B-B56E-8E70B10BC5D3.cs
27+
samples/databases/wide-world-importers/workload-drivers/vehicle-location-insert/MultithreadedInMemoryTableInsert/obj/Release/TemporaryGeneratedFile_E7A71F73-0F8D-4B9B-B56E-8E70B10BC5D3.cs
28+
*.dat
29+
*.sln

‎samples/features/json/readme.md

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,10 @@
1+
# Samples that use JSON functionalities that are available in SQL Server 2016 (or higher) and Azure SQL Database
2+
3+
[Todo REST API - ASP.NET Core](todo-app/dotnet-rest-api)
4+
5+
This project contains an example implementation of ASP.NET Core REST API with CRUD operations on a simple Todo table. You can learn how to build REST API on the existing database schema using new JSON functionalities that are available in SQL Server 2016 (or higher) and Azure SQL Database.
6+
7+
[Todo REST API - NodeJS/Express4](todo-app/nodejs-express4-rest-api)
8+
9+
This project contains an example implementation of NodeJS REST API using Express4 framework and Tedious. REST API has basic CRUD operations on a simple Todo table. You can learn how to build REST API on the existing database schema using new JSON functionalities that are available in SQL Server 2016 (or higher) and Azure SQL Database.
10+

‎samples/features/json/todo-app/dotnet-rest-api/README.md

Lines changed: 6 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
# ASP.NET Core REST Web API that uses SQL/JSON functionalites
1+
# ASP.NET Core REST Web API that uses SQL/JSON functionalities
22

33
This project contains an example implementation of ASP.NET Core REST API with CRUD operations on a simple Todo table. You can learn how to build REST API on the existing database schema using new JSON functionalities that are available in SQL Server 2016 (or higher) and Azure SQL Database.
44

@@ -61,7 +61,7 @@ Service uses built-in JSON functionalities that are available in SQL Server 2016
6161
<a name=disclaimers></a>
6262

6363
## Disclaimers
64-
The code included in this sample is not intended demonstrate some general guidances and arhitectural patterns for web development. It contains minimal code required to create REST API, and it does not use some patterns such as Repository. Sample uses built-in ASP.NET Core Dependency Injection mechanism; however, this is not prerequisite.
64+
The code included in this sample is not intended demonstrate some general guidance and architectural patterns for web development. It contains minimal code required to create REST API, and it does not use some patterns such as Repository. Sample uses built-in ASP.NET Core Dependency Injection mechanism; however, this is not prerequisite.
6565
You can easily modify this code to fit the architecture of your application.
6666

6767
<a name=related-links></a>
@@ -70,8 +70,11 @@ You can easily modify this code to fit the architecture of your application.
7070

7171
For more information, see this [article](http://www.codeproject.com/Articles/1106622/Building-Web-API-REST-services-on-Azure-SQL-Databa).
7272

73+
## Code of Conduct
74+
This project has adopted the [Microsoft Open Source Code of Conduct](https://opensource.microsoft.com/codeofconduct/). For more information see the [Code of Conduct FAQ](https://opensource.microsoft.com/codeofconduct/faq/) or contact [opencode@microsoft.com](mailto:opencode@microsoft.com) with any additional questions or comments.
75+
7376
## License
7477
These samples and templates are all licensed under the MIT license. See the license.txt file in the root.
7578

7679
## Questions
77-
Email questions to: sqlserversamples@microsoft.com.
80+
Email questions to: [sqlserversamples@microsoft.com](mailto: sqlserversamples@microsoft.com).

‎samples/features/json/todo-app/nodejs-express4-rest-api/.gitignore

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,5 @@
1+
node_modules/*
2+
bin/*.dll
3+
obj/*
4+
*.sln
5+
*.log

‎samples/features/json/todo-app/nodejs-express4-rest-api/README.md

Lines changed: 102 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,102 @@
1+
# NodeJS Express4 REST API that uses SQL/JSON functionalities
2+
3+
This project contains an example implementation of NodeJS REST API with CRUD operations on a simple Todo table. You can learn how to build REST API on the existing database schema using NodeJS, Express4, and new JSON functionalities that are available in SQL Server 2016 (or higher) and Azure SQL Database.
4+
5+
### Contents
6+
7+
[About this sample](#about-this-sample)<br/>
8+
[Before you begin](#before-you-begin)<br/>
9+
[Run this sample](#run-this-sample)<br/>
10+
[Sample details](#sample-details)<br/>
11+
[Disclaimers](#disclaimers)<br/>
12+
[Related links](#related-links)<br/>
13+
14+
<a name=about-this-sample></a>
15+
16+
## About this sample
17+
18+
- **Applies to:** SQL Server 2016 (or higher), Azure SQL Database
19+
- **Key features:** JSON Functions in SQL Server 2016/Azure SQL Database - FOR JSON and OPENJSON
20+
- **Programming Language:** JavaScript (NodeJS)
21+
- **Authors:** Jovan Popovic
22+
23+
<a name=before-you-begin></a>
24+
25+
## Before you begin
26+
27+
To run this sample, you need the following prerequisites.
28+
29+
**Software prerequisites:**
30+
31+
1. SQL Server 2016 (or higher) or an Azure SQL Database
32+
2. Visual Studio 2015 (or higher) with the NodeJS
33+
34+
**Azure prerequisites:**
35+
36+
1. Permission to create an Azure SQL Database
37+
38+
<a name=run-this-sample></a>
39+
40+
## Run this sample
41+
42+
1. Navigate to the folder where you have downloaded sample and run **npm install** in command window, or run setup.bat if you are on Windows operating system. This command will install necessary npm packages defined in project.json.
43+
44+
2. From SQL Server Management Studio or SQL Server Data Tools connect to your SQL Server 2016 or Azure SQL database and execute setup.sql script that will create and populate Todo table in the database.
45+
46+
3. From Visual Studio, open the **TodoApp.xproj** file from the root directory,
47+
48+
4. Locate db.js file in the project, change database connection info in createConnection() method to reference your database. the following tokens should be replaced:
49+
1. SERVERNAME - name of the database server.
50+
2. DATABASE - Name of database where Todo table is stored.
51+
3. USERNAME - SQL Server login that can access table data and execute stored procedures.
52+
4. PASSWORD - Password associated to SQL Server login.
53+
54+
```
55+
var config = {
56+
server : "SERVER.database.windows.net",
57+
userName: "USER",
58+
password: "PASSWORD",
59+
// If you're on Azure, you will need this:
60+
options: { encrypt: true, database: 'DATABASE' }
61+
};
62+
```
63+
64+
5. Build project using Ctrl+Shift+B, right-click on project + Build, or Build/Build Solution from menu.
65+
66+
6. Run sample app using F5 or Ctrl+F5. /todo Url will be opened with a list of all Todo items as a JSON array,
67+
1. Open /api/Todo/1 Url to get details about a single Todo item with id 1,
68+
2. Send POST, PUT, or DELETE Http requests to update content of Todo table.
69+
70+
<a name=sample-details></a>
71+
72+
## Sample details
73+
74+
This sample application shows how to create simple REST API service that performs CRUD operations on a simple Todo table.
75+
NodeJS REST API is used to implement REST Service in the example.
76+
1. app.js file that contains startup code.
77+
2. db.js file that contains functions that wrap Tedious library
78+
3. todo.js file that contains action that will be called on GET, POST, PUT, and DELETE Http requests.
79+
80+
Service uses Tedious library for data access and built-in JSON functionalities that are available in SQL Server 2016 and Azure SQL Database.
81+
82+
<a name=disclaimers></a>
83+
84+
## Disclaimers
85+
The code included in this sample is not intended demonstrate some general guidance and architectural patterns for web development.
86+
It contains minimal code required to create REST API.
87+
You can easily modify this code to fit the architecture of your application.
88+
89+
<a name=related-links></a>
90+
91+
## Related Links
92+
93+
For more information, see this [MSDN documentation](https://msdn.microsoft.com/en-us/library/dn921897.aspx).
94+
95+
## Code of Conduct
96+
This project has adopted the [Microsoft Open Source Code of Conduct](https://opensource.microsoft.com/codeofconduct/). For more information see the [Code of Conduct FAQ](https://opensource.microsoft.com/codeofconduct/faq/) or contact [opencode@microsoft.com](mailto:opencode@microsoft.com) with any additional questions or comments.
97+
98+
## License
99+
These samples and templates are all licensed under the MIT license. See the license.txt file in the root.
100+
101+
## Questions
102+
Email questions to: [sqlserversamples@microsoft.com](mailto: sqlserversamples@microsoft.com).

‎samples/features/json/todo-app/nodejs-express4-rest-api/app.js

Lines changed: 15 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,15 @@
1+
var express = require('express');
2+
var bodyParser = require('body-parser');
3+
4+
var app = express();
5+
app.use(bodyParser.text());
6+
app.use('/todo', require('./routes/todo'));
7+
8+
// catch 404 and forward to error handler
9+
app.use(function (req, res, next) {
10+
var err = new Error('Not Found');
11+
err.status = 404;
12+
next(err);
13+
});
14+
15+
module.exports = app;

‎samples/features/json/todo-app/nodejs-express4-rest-api/bin/www

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,9 @@
1+
#!/usr/bin/env node
2+
var debug = require('debug')('nodejs_express4_rest_api');
3+
var app = require('../app');
4+
5+
app.set('port', process.env.PORT || 3000);
6+
7+
var server = app.listen(app.get('port'), function() {
8+
debug('Express server listening on port ' + server.address().port);
9+
});

‎samples/features/json/todo-app/nodejs-express4-rest-api/db.js

Lines changed: 69 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,69 @@
1+
function createConnection() {
2+
3+
var config = {
4+
server : "SERVER.database.windows.net",
5+
userName: "USER",
6+
password: "PASSWORD",
7+
// If you're on Azure, you will need this:
8+
options: { encrypt: true, database: 'DATABASE' }
9+
};
10+
11+
var Connection = require('tedious').Connection;
12+
var connection = new Connection(config);
13+
14+
return connection;
15+
}
16+
17+
function createRequest(query, connection) {
18+
var Request = require('tedious').Request;
19+
var req =
20+
new Request(query,
21+
function (err, rowCount) {
22+
if (err) {
23+
throw err;
24+
}
25+
connection && connection.close();
26+
});
27+
28+
return req;
29+
}
30+
31+
function stream (query, connection, output, defaultContent) {
32+
33+
errorHandler = function (ex) { throw ex; };
34+
var request = query;
35+
if (typeof query == "string") {
36+
request = this.createRequest(query, connection);
37+
}
38+
39+
var empty = true;
40+
request.on('row', function (columns) {
41+
empty = false;
42+
output.write(columns[0].value);
43+
});
44+
45+
request.on('done', function (rowCount, more, rows) {
46+
if (empty) {
47+
output.write(defaultContent);
48+
}
49+
output.end();
50+
});
51+
52+
request.on('doneProc', function (rowCount, more, rows) {
53+
if (empty) {
54+
output.write(defaultContent);
55+
}
56+
output.end();
57+
});
58+
59+
connection.on('connect', function (err) {
60+
if (err) {
61+
throw err;
62+
}
63+
connection.execSql(request);
64+
});
65+
}
66+
67+
module.exports.createConnection = createConnection;
68+
module.exports.createRequest = createRequest;
69+
module.exports.stream = stream;

‎samples/features/json/todo-app/nodejs-express4-rest-api/nodejs-express4-rest-api.njsproj

Lines changed: 88 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,88 @@
1+
<?xml version="1.0" encoding="utf-8"?>
2+
<Project DefaultTargets="Build" xmlns="http://schemas.microsoft.com/developer/msbuild/2003" ToolsVersion="4.0">
3+
<PropertyGroup>
4+
<VisualStudioVersion Condition="'$(VisualStudioVersion)' == ''">11.0</VisualStudioVersion>
5+
<VSToolsPath Condition="'$(VSToolsPath)' == ''">$(MSBuildExtensionsPath32)\Microsoft\VisualStudio\v$(VisualStudioVersion)</VSToolsPath>
6+
<Name>nodejs-express4-rest-api</Name>
7+
<RootNamespace>nodejs-express4-rest-api</RootNamespace>
8+
<LaunchUrl>todo</LaunchUrl>
9+
</PropertyGroup>
10+
<Import Project="$(MSBuildExtensionsPath)\$(MSBuildToolsVersion)\Microsoft.Common.props" Condition="Exists('$(MSBuildExtensionsPath)\$(MSBuildToolsVersion)\Microsoft.Common.props')" />
11+
<PropertyGroup>
12+
<Configuration Condition=" '$(Configuration)' == '' ">Debug</Configuration>
13+
<SchemaVersion>2.0</SchemaVersion>
14+
<ProjectGuid>da18f211-dee4-4b4a-8954-4c7505dc769b</ProjectGuid>
15+
<ProjectHome>.</ProjectHome>
16+
<StartupFile>bin\www</StartupFile>
17+
<SearchPath>
18+
</SearchPath>
19+
<WorkingDirectory>.</WorkingDirectory>
20+
<OutputPath>.</OutputPath>
21+
<TargetFrameworkVersion>v4.0</TargetFrameworkVersion>
22+
<ProjectTypeGuids>{3AF33F2E-1136-4D97-BBB7-1795711AC8B8};{349c5851-65df-11da-9384-00065b846f21};{9092AA53-FB77-4645-B42D-1CCCA6BD08BD}</ProjectTypeGuids>
23+
<ProjectView>ShowAllFiles</ProjectView>
24+
<NodejsPort>1337</NodejsPort>
25+
<StartWebBrowser>True</StartWebBrowser>
26+
</PropertyGroup>
27+
<PropertyGroup Condition=" '$(Configuration)' == 'Debug' ">
28+
<DebugSymbols>true</DebugSymbols>
29+
</PropertyGroup>
30+
<PropertyGroup Condition=" '$(Configuration)' == 'Release' ">
31+
<DebugSymbols>true</DebugSymbols>
32+
</PropertyGroup>
33+
<ItemGroup>
34+
<Compile Include="app.js" />
35+
<Compile Include="db.js" />
36+
<Compile Include="routes\todo.js" />
37+
<Compile Include="bin\www" />
38+
<Content Include="package.json" />
39+
<Content Include="README.md" />
40+
</ItemGroup>
41+
<ItemGroup>
42+
<Folder Include="bin\" />
43+
<Folder Include="util\" />
44+
<Folder Include="public\" />
45+
<Folder Include="routes\" />
46+
</ItemGroup>
47+
<!-- Do not delete the following Import Project. While this appears to do nothing it is a marker for setting TypeScript properties before our import that depends on them. -->
48+
<Import Project="$(MSBuildExtensionsPath32)\Microsoft\VisualStudio\v$(VisualStudioVersion)\TypeScript\Microsoft.TypeScript.targets" Condition="False" />
49+
<Import Project="$(VSToolsPath)\Node.js Tools\Microsoft.NodejsTools.targets" />
50+
<ProjectExtensions>
51+
<VisualStudio>
52+
<FlavorProperties GUID="{349c5851-65df-11da-9384-00065b846f21}">
53+
<WebProjectProperties>
54+
<UseIIS>False</UseIIS>
55+
<AutoAssignPort>True</AutoAssignPort>
56+
<DevelopmentServerPort>0</DevelopmentServerPort>
57+
<DevelopmentServerVPath>/</DevelopmentServerVPath>
58+
<IISUrl>http://localhost:48022/</IISUrl>
59+
<NTLMAuthentication>False</NTLMAuthentication>
60+
<UseCustomServer>True</UseCustomServer>
61+
<CustomServerUrl>http://localhost:1337</CustomServerUrl>
62+
<SaveServerSettingsInUserFile>False</SaveServerSettingsInUserFile>
63+
</WebProjectProperties>
64+
</FlavorProperties>
65+
<FlavorProperties GUID="{349c5851-65df-11da-9384-00065b846f21}" User="">
66+
<WebProjectProperties>
67+
<StartPageUrl>
68+
</StartPageUrl>
69+
<StartAction>CurrentPage</StartAction>
70+
<AspNetDebugging>True</AspNetDebugging>
71+
<SilverlightDebugging>False</SilverlightDebugging>
72+
<NativeDebugging>False</NativeDebugging>
73+
<SQLDebugging>False</SQLDebugging>
74+
<ExternalProgram>
75+
</ExternalProgram>
76+
<StartExternalURL>
77+
</StartExternalURL>
78+
<StartCmdLineArguments>
79+
</StartCmdLineArguments>
80+
<StartWorkingDirectory>
81+
</StartWorkingDirectory>
82+
<EnableENC>False</EnableENC>
83+
<AlwaysStartWebServerOnDebug>False</AlwaysStartWebServerOnDebug>
84+
</WebProjectProperties>
85+
</FlavorProperties>
86+
</VisualStudio>
87+
</ProjectExtensions>
88+
</Project>

‎samples/features/json/todo-app/nodejs-express4-rest-api/package.json

Lines changed: 19 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,19 @@
1+
{
2+
"name": "nodejs-express4-rest-api",
3+
"version": "0.0.0",
4+
"private": true,
5+
"scripts": {
6+
"start": "node ./bin/www"
7+
},
8+
"description": "nodejs-express4-rest-api",
9+
"author": {
10+
"name": "Jovan Popovic",
11+
"email": "jovanpop@microsoft.com"
12+
},
13+
"dependencies": {
14+
"body-parser": "^1.15.2",
15+
"debug": "^2.2.0",
16+
"express": "^4.14.0",
17+
"tedious": "^1.14.0"
18+
}
19+
}

0 commit comments

Comments
 (0)