Skip to content

Commit 6db7961

Browse files
jodebruijodebrui
authored
Merge pull request microsoft#59 from JocaPC/json
Added .Net Todo REST API sample
2 parents fad29f0 + 53a2b02 commit 6db7961

File tree

13 files changed

+6004
-0
lines changed

13 files changed

+6004
-0
lines changed

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

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

‎samples/features/json/todo-app/dotnet-rest-api/Controllers/TodoController.cs

Lines changed: 96 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,96 @@
1+
using Belgrade.SqlClient;
2+
using Microsoft.AspNetCore.Mvc;
3+
using System.Data.SqlClient;
4+
using System.IO;
5+
using System.Threading.Tasks;
6+
7+
namespace TodoApp.Controllers
8+
{
9+
[Route("api/[controller]")]
10+
public class TodoController : Controller
11+
{
12+
private readonly IQueryPipe SqlPipe;
13+
private readonly ICommand SqlCommand;
14+
15+
public TodoController(ICommand sqlCommand, IQueryPipe sqlPipe)
16+
{
17+
this.SqlCommand = sqlCommand;
18+
this.SqlPipe = sqlPipe;
19+
}
20+
21+
// GET api/Todo
22+
[HttpGet]
23+
public async Task Get()
24+
{
25+
await SqlPipe.Stream("select * from Todo FOR JSON PATH", Response.Body, "[]");
26+
}
27+
28+
// GET api/Todo/5
29+
[HttpGet("{id}")]
30+
public async Task Get(int id)
31+
{
32+
var cmd = new SqlCommand("select * from Todo where Id = @id FOR JSON PATH, WITHOUT_ARRAY_WRAPPER");
33+
cmd.Parameters.AddWithValue("id", id);
34+
await SqlPipe.Stream(cmd, Response.Body, "{}");
35+
}
36+
37+
// POST api/Todo
38+
[HttpPost]
39+
public async Task Post()
40+
{
41+
string todo = new StreamReader(Request.Body).ReadToEnd();
42+
var cmd = new SqlCommand(
43+
@"insert into Todo
44+
select *
45+
from OPENJSON(@todo)
46+
WITH( Title nvarchar(30), Description nvarchar(4000), Completed bit, TargetDate datetime2)");
47+
cmd.Parameters.AddWithValue("todo", todo);
48+
await SqlCommand.ExecuteNonQuery(cmd);
49+
}
50+
51+
// PATCH api/Todo
52+
[HttpPatch]
53+
public async Task Patch(int id)
54+
{
55+
string todo = new StreamReader(Request.Body).ReadToEnd();
56+
var cmd = new SqlCommand(
57+
@"update Todo
58+
set Title = ISNULL(json.Title, Title), Description = ISNULL(json.Description, Description),
59+
Completed = ISNULL(json.Completed, Completed), TargetDate = ISNULL(json.TargetDate, TargetDate)
60+
from OPENJSON(@todo)
61+
WITH( Title nvarchar(30), Description nvarchar(4000),
62+
Completed bit, TargetDate datetime2) AS json
63+
where Id = @id");
64+
cmd.Parameters.AddWithValue("id", id);
65+
cmd.Parameters.AddWithValue("todo", todo);
66+
await SqlCommand.ExecuteNonQuery(cmd);
67+
}
68+
69+
// PUT api/Todo/5
70+
[HttpPut("{id}")]
71+
public async Task Put(int id)
72+
{
73+
string todo = new StreamReader(Request.Body).ReadToEnd();
74+
var cmd = new SqlCommand(
75+
@"update Todo
76+
set Title = json.Title, Description = json.Description,
77+
Completed = json.completed, TargetDate = json.TargetDate
78+
from OPENJSON( @todo )
79+
WITH( Title nvarchar(30), Description nvarchar(4000),
80+
Completed bit, TargetDate datetime2) AS json
81+
where Id = @id");
82+
cmd.Parameters.AddWithValue("id", id);
83+
cmd.Parameters.AddWithValue("todo", todo);
84+
await SqlCommand.ExecuteNonQuery(cmd);
85+
}
86+
87+
// DELETE api/Todo/5
88+
[HttpDelete("{id}")]
89+
public async Task Delete(int id)
90+
{
91+
var cmd = new SqlCommand(@"delete Todo where Id = @id");
92+
cmd.Parameters.AddWithValue("id", id);
93+
await SqlCommand.ExecuteNonQuery(cmd);
94+
}
95+
}
96+
}

‎samples/features/json/todo-app/dotnet-rest-api/Program.cs

Lines changed: 25 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,25 @@
1+
using System;
2+
using System.Collections.Generic;
3+
using System.IO;
4+
using System.Linq;
5+
using System.Threading.Tasks;
6+
using Microsoft.AspNetCore.Hosting;
7+
using Microsoft.AspNetCore.Builder;
8+
9+
namespace TodoApp
10+
{
11+
public class Program
12+
{
13+
public static void Main(string[] args)
14+
{
15+
var host = new WebHostBuilder()
16+
.UseKestrel()
17+
.UseContentRoot(Directory.GetCurrentDirectory())
18+
.UseIISIntegration()
19+
.UseStartup<Startup>()
20+
.Build();
21+
22+
host.Run();
23+
}
24+
}
25+
}

‎samples/features/json/todo-app/dotnet-rest-api/Project_Readme.html

Lines changed: 184 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,184 @@
1+
<!DOCTYPE html>
2+
<html lang="en">
3+
<head>
4+
<meta charset="utf-8" />
5+
<title>Welcome to ASP.NET Core Web API REST Service</title>
6+
<style>
7+
html {
8+
background: #f1f1f1;
9+
height: 100%;
10+
}
11+
12+
body {
13+
background: #fff;
14+
color: #505050;
15+
font: 14px 'Segoe UI', tahoma, arial, helvetica, sans-serif;
16+
margin: 1%;
17+
min-height: 95.5%;
18+
border: 1px solid silver;
19+
position: relative;
20+
}
21+
22+
#header {
23+
padding: 0;
24+
}
25+
26+
#header h1 {
27+
font-size: 44px;
28+
font-weight: normal;
29+
margin: 0;
30+
padding: 10px 30px 10px 30px;
31+
}
32+
33+
#header span {
34+
margin: 0;
35+
padding: 0 30px;
36+
display: block;
37+
}
38+
39+
#header p {
40+
font-size: 20px;
41+
color: #fff;
42+
background: #007acc;
43+
padding: 0 30px;
44+
line-height: 50px;
45+
margin-top: 25px;
46+
47+
}
48+
49+
#header p a {
50+
color: #fff;
51+
text-decoration: underline;
52+
font-weight: bold;
53+
padding-right: 35px;
54+
background: no-repeat right bottom url(data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAABcAAAAWCAMAAAAcqPc3AAAANlBMVEUAAAAAeswfitI9mthXp91us+KCvuaTx+mjz+2x1u+83PLH4vTR5/ba7Pjj8Pns9fv1+v3////wy3dWAAAAAXRSTlMAQObYZgAAAHxJREFUeNp9kVcSwCAIRMHUYoH7XzaxOxJ9P8oyQ1uIqNPwh3s2aLmIM2YtqrLcQIeQEylhuCeUOlhgve5yoBCfWmlnlgkN4H8ykbpaE7gR03AbUHiwoOxUH9Xp+ubd41p1HF3mBPrfC87BHeTdaB3ceeKL9HGpcvX9zu6+DdMWT9KQPvYAAAAASUVORK5CYII=);
55+
}
56+
57+
#main {
58+
padding: 5px 30px;
59+
clear: both;
60+
}
61+
62+
.section {
63+
width: 21.7%;
64+
float: left;
65+
margin: 0 0 0 4%;
66+
}
67+
68+
.section h2 {
69+
font-size: 13px;
70+
text-transform: uppercase;
71+
margin: 0;
72+
border-bottom: 1px solid silver;
73+
padding-bottom: 12px;
74+
margin-bottom: 8px;
75+
}
76+
77+
.section.first {
78+
margin-left: 0;
79+
}
80+
81+
.section.first h2 {
82+
font-size: 24px;
83+
text-transform: none;
84+
margin-bottom: 25px;
85+
border: none;
86+
}
87+
88+
.section.first li {
89+
border-top: 1px solid silver;
90+
padding: 8px 0;
91+
}
92+
93+
.section.last {
94+
margin-right: 0;
95+
}
96+
97+
ul {
98+
list-style: none;
99+
padding: 0;
100+
margin: 0;
101+
line-height: 20px;
102+
}
103+
104+
li {
105+
padding: 4px 0;
106+
}
107+
108+
a {
109+
color: #267cb2;
110+
text-decoration: none;
111+
}
112+
113+
a:hover {
114+
text-decoration: underline;
115+
}
116+
117+
#footer {
118+
clear: both;
119+
padding-top: 50px;
120+
}
121+
122+
#footer p {
123+
position: absolute;
124+
bottom: 10px;
125+
}
126+
127+
</style>
128+
</head>
129+
<body>
130+
131+
<div id="header">
132+
<h1>Welcome to ASP.NET Core REST API Project</h1>
133+
<span>
134+
In this example you can see how to easily create REST API with CRUD operations using ASP.NET Core Framework and
135+
built-in JSON functionalities in SQL Server 2016 and Azure SQL Database.
136+
</span>
137+
<p>You can find detailed explanation in <a href="http://www.codeproject.com/Articles/1106622/Building-REST-services-with-ASP-NET-Core-Web-API-a">Code Project</a> article.</p>
138+
</div>
139+
140+
<div id="main">
141+
<div class="section first">
142+
<h2>This application consists of:</h2>
143+
<ul>
144+
<li>Simple database with one Todo table.</li>
145+
<li>Simple Controller that implements CRUD operations on Todo table.</li>
146+
</ul>
147+
</div>
148+
<div class="section">
149+
<h2>Setup and configure</h2>
150+
<ul>
151+
<li>Create new database on SQL Server 2016 or Azure SQL.</li>
152+
<li>If you are using Azure SQL make sure that firewall rules in Azure enable you to make connections between your host and Azure SQL database.</li>
153+
<li>Execute <a href="setup/setup.sql" target="_blank">setup.sql</a> script to create Todo table and populate it with sample data.</li>
154+
<li>Open .xproj file in Visual Studio 2015 and set connection string in <a href="Startup.cs" target="_blank">Startup.cs</a> file:
155+
<code>
156+
157+
158+
public void ConfigureServices(IServiceCollection&nbsp;services)
159+
{
160+
161+
const string ConnString = "Server=SERVERNAME.database.windows.net;Database=DATABASENAME;User&nbsp;Id=USERNAME;Password=PASSWORD";
162+
163+
</code>
164+
165+
</li>
166+
</ul>
167+
</div>
168+
169+
<div class="section last">
170+
<h2>Run</h2>
171+
<ul>
172+
<li>Run the project and see results returned from /api/Todo URL.</li>
173+
<li>Open /api/Todo/1 URL to try GET method that returns single Todo item.</li>
174+
<li>Use some tool that send POST, PUT, PATCH, or DELETE requests to web server (e.g. Chrome Poster) to try other methods.</li>
175+
</ul>
176+
</div>
177+
178+
<div id="footer">
179+
<p>We would love to hear your <a href="mailto:sqlserversamples@microsoft.com">feedback</a>!</p>
180+
</div>
181+
</div>
182+
183+
</body>
184+
</html>

‎samples/features/json/todo-app/dotnet-rest-api/Properties/launchSettings.json

Lines changed: 28 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,28 @@
1+
{
2+
"iisSettings": {
3+
"windowsAuthentication": false,
4+
"anonymousAuthentication": true,
5+
"iisExpress": {
6+
"applicationUrl": "http://localhost:15194/",
7+
"sslPort": 0
8+
}
9+
},
10+
"profiles": {
11+
"IIS Express": {
12+
"commandName": "IISExpress",
13+
"launchBrowser": true,
14+
"launchUrl": "api/Todo",
15+
"environmentVariables": {
16+
"ASPNETCORE_ENVIRONMENT": "Development"
17+
}
18+
},
19+
"TodoApp": {
20+
"commandName": "Project",
21+
"launchBrowser": true,
22+
"launchUrl": "http://localhost:5000/api/Todo",
23+
"environmentVariables": {
24+
"ASPNETCORE_ENVIRONMENT": "Development"
25+
}
26+
}
27+
}
28+
}

0 commit comments

Comments
 (0)