This is going to be a series of posts in which I'll show how I managed to get a simple yet functional ReactJS application working. I plan to write 1 blog post per major component detailing how it works and integrates into the app.

This was my start with ReactJS and I worked in this application for almost 1 month as seen by the git commits here. I went from 0% ReactJS knowledge to to some % knowledge of how things work in this UI\presentation framework.

Motivation

The motivation to learn it is that it's mainstream nowadays. ReactJS is also used to develop mobile apps... besides that, for a job application test I had to develop a To Do application with a set of requirements.

Requirements

Programming assignment for web development candidates

You can choose any programming language and web development framework, database, and web server you like. The web application you need to build is a basic todo list application with the following requirements:

- Users can view their todo list;
- Users can add, remove, modify and delete todo entries;
- Each todo entry includes a single line of text, due date and priority;
- Users can assign priorities and due dates to the entries;
- Users can sort todo lists using due date and priority;
- Users can mark an entry as completed;
- You don't need to spend time on UI/UX design, if you do, it will be a bonus;
- Provide a RESTful API which will allow a third-party application to trigger actions on your app (same actions available in the app);
- Provide authentication and authorization service for both the app and the API;
- As complementary item to the last requirement, you should be able to create users in the system via an interface, eg a signup/register screen.

Web Tech Stack

I took the challenge! Developed a SPA - Single Page Application using ReactJS. Why not!? I also used Visual Studio Code as the IDE in tandem with the web tech stack in which I'm experienced with: C#, ASP.NET Core Web API, SQL Server, etc.

The App

The following is the app's homepage:

Figure 1 - App home page

The left side menu when clicked gives access to the Todos form and grid.

Figure 2 - Left side menu

The right side menu allows the user to login\logout and check their profile.

Figure 3 - Right side menu

In order to be able to access the Todos form\grid the user must be authenticated.

Figure 4 - Todo form + grid

Database

Everything was developed in Mac OS and as such the database that stores todo data is an SQL Server container image running in Docker.

Figure 5 - SQL Server database image running in Docker

Web API

The Web API was implemented using an ASP.NET Core Web API project. There's also a Swagger protected help page configured.

Figure 6 - Web API Swagger help page

Source code structure

The following screenshots show how the code is structured:

Figure 7 - App structure in Visual Studio Code Explorer window

- src folder holds all the source code files for the ReactJs app.

- TodoApi holds the source code files for an ASP.NET Web API project.

The ReactJS project skeleton was created\bootstrapped with Create React App as described in the README file.

Expanding the src folder we have this:

Figure 8 - ReactJS SPA app source code files

Expanding the TodoApi folder we have this:

Figure 9 - Todo ASP.NET Web API source code files

App dependencies

The ReactJS app uses the following dependencies as listed in package.json:

"dependencies": {

"@auth0/auth0-spa-js": "^1.6.3",

"@date-io/date-fns": "^1.3.13",

"@material-ui/core": "^4.9.1",

"@material-ui/icons": "^4.9.1",

"@material-ui/pickers": "^3.2.10",

"@testing-library/jest-dom": "^5.1.1",

"@testing-library/react": "^9.4.0",

"@testing-library/user-event": "^8.1.0",

"axios": "^0.19.2",

"date-fns": "^2.9.0",

"formik": "^2.1.4",

"moment": "^2.24.0",

"react": "^16.12.0",

"react-dom": "^16.12.0",

"react-router-dom": "^5.1.2",

"react-scripts": "^3.3.1",

"react-toastify": "^5.5.0",

"yup": "^0.28.1"

},

Git history

The git history is a nice way to remember what I did along the way:

Git History on master by all authors

Adding Auth0 placeholders...
340657a (HEAD -> master, origin/master) by Leniel Macaferi , Sat May 02 2020 (54 minutes ago)
1 file changed, 2 insertions(+), 2 deletions(-)

Small fixes...
5b98458 by Leniel Macaferi , Fri Feb 21 2020 (2 months ago)
2 files changed, 3 insertions(+), 3 deletions(-)

- Added Roles claim... it's retrieved from Auth0 access_token; - Improved footer; - Added missing toastify messages; - Improved Profile.
2cd5cd3 by Leniel Macaferi , Tue Feb 11 2020 (3 months ago)
5 files changed, 84 insertions(+), 19 deletions(-)

- Added Bearer token support to Swagger so that it's now possible to authorize with a token prior to testing the Web API endpoints; - In TodoItemsController the current user is now extracted while getting Todos so that the user can only retrieve their todos; - Added User property to TodoItem model; - Externalized getUser from Auth0 so that it can be called from stateful ReactJS components like Todo.js; - Made use of getUser() on Todo component; - Beautified Profile component.
b704348 by Leniel Macaferi , Mon Feb 10 2020 (3 months ago)
8 files changed, 122 insertions(+), 19 deletions(-)

- Added scopes and protected Get Todos Web API call with read:todos scope. https://auth0.com/docs/quickstart/backend/aspnet-core-webapi/01-authorization#validate-access-tokens - Added Profile component
13a8675 by Leniel Macaferi , Sun Feb 09 2020 (3 months ago)
13 files changed, 173 insertions(+), 62 deletions(-)

- Made Get Todos protected by using [Authorize] attribute; - Added JwtBearer support to the ASP.NET Core Web API; - Moved BrowserRouter to index.js so that the works as expected. More info here: https://stackoverflow.com/q/60123391/114029 - Externalized getTokenSilently in Auth.js to be able to pass it to axios request interceptor.
ab74c14 by Leniel Macaferi , Sat Feb 08 2020 (3 months ago)
12 files changed, 182 insertions(+), 51 deletions(-)

Added Auth0 to be able to authenticate and authorize users. https://auth0.com/
7ab2538 by Leniel Macaferi , Fri Feb 07 2020 (3 months ago)
9 files changed, 892 insertions(+), 170 deletions(-)

- Added search todo functionality; - Improved the todos table by adding Table pagination actions and made the header sticky; - Improved todo form by disabling the save button when there are errors or when the form is not dirty yet, that is, the user has not changed anything yet; - Added global error message with the help of toastify;
2d59cf4 by Leniel Macaferi , Thu Feb 06 2020 (3 months ago)
5 files changed, 190 insertions(+), 44 deletions(-)

- Commented out InMemoryDatabase in the Web API project and started using a full featured SQL Server database with UseSqlServer. Accomplished that on Mac OS side using Docker and a Developer version of SQL Server 2017; More info here: https://database.guide/how-to-install-sql-server-on-a-mac/ https://stackoverflow.com/a/60080206/114029 - Added Swagger to the Web API project; https://docs.microsoft.com/en-us/aspnet/core/tutorials/getting-started-with-swashbuckle?view=aspnetcore-3.1&tabs=visual-studio - Changed Web API default port from 7777 to 8888; - Improved global Progress bar. Set its position to absolute so that it won't push content down when it is displayed; - Moved todo related components to their own folder.
581da74 by Leniel Macaferi , Wed Feb 05 2020 (3 months ago)
16 files changed, 72 insertions(+), 29 deletions(-)

- Added cancel button to todo form. This is useful when the user is editing a todo and wants to cancel the action; - Implemented an improved version of todo form using the HOC [Higher Order Component] withFormik; this allowed passing a parent component [Todo] function down to the child component EnhancedTodoForm which is hooked to the the TodoForm component; Amazing stuff! :-) https://jaredpalmer.com/formik/docs/api/withFormik - route icons are now dynamically retrieved; - Added react-toastify libray to be able to show customized messages to user. https://github.com/fkhadra/react-toastify
469e858 by Leniel Macaferi , Tue Feb 04 2020 (3 months ago)
12 files changed, 328 insertions(+), 100 deletions(-)

- Added request and response interceptors to axios: https://github.com/axios/axios#interceptors ... this will allow a hooking place to display toasts (messages) to the user. This is one of the next thing to be added; - Added a menu tp AppBar using a Drawer component" https://material-ui.com/components/drawers/; - Started using react-router: https://github.com/ReactTraining/react-router; - routes are exported from routes.js; - Added About and Home pages.
7412ec3 by Leniel Macaferi , Mon Feb 03 2020 (3 months ago)
17 files changed, 3682 insertions(+), 3186 deletions(-)

- Got editTodo working; - Improved themes; - Removed unused npm packages.
4afd71e by Leniel Macaferi , Sat Feb 01 2020 (3 months ago)
8 files changed, 158 insertions(+), 866 deletions(-)

- Delete todo(s) done; - WIP Edit todo; - Added axiosInterceptor which handles  the animation when any Web API is called. It shows a Material-UI ; - Externalized custom styles in themes.js; - Many improvements in Todos table.
9d3e032 by Leniel Macaferi , Fri Jan 31 2020 (3 months ago)
13 files changed, 236 insertions(+), 139 deletions(-)

Handling setSelected after deletion happens...
9be189f by Leniel Macaferi , Thu Jan 30 2020 (3 months ago)
1 file changed, 8 insertions(+), 3 deletions(-)

Added delete todo functionality with a gotcha: https://stackoverflow.com/a/33846760/114029
5ad3cbd by Leniel Macaferi , Thu Jan 30 2020 (3 months ago)
7 files changed, 77 insertions(+), 52 deletions(-)

Added material UI Table component to be able to better visualize and operate on Todos. https://material-ui.com/components/tables/
febb4b3 by Leniel Macaferi , Thu Jan 30 2020 (3 months ago)
3 files changed, 371 insertions(+), 17 deletions(-)

- Created a global theme with createMuiTheme; - Improved AppBar - Got addTodo POST call to Web API working and TodoList updating as expected.
6f8a5fb by Leniel Macaferi , Thu Jan 30 2020 (3 months ago)
8 files changed, 700 insertions(+), 174 deletions(-)

Styled App structure and TodoForm with Material UI components.
38f169d by Leniel Macaferi , Wed Jan 29 2020 (3 months ago)
8 files changed, 357 insertions(+), 196 deletions(-)

Got Formik form fields working with material-ui https://material-ui.com/.
b309899 by Leniel Macaferi , Wed Jan 29 2020 (3 months ago)
10 files changed, 1027 insertions(+), 246 deletions(-)

- Added formik, yup and react-formik-ui. - Created a Todo form with formik.
bd8b355 by Leniel Macaferi , Mon Jan 27 2020 (3 months ago)
7 files changed, 771 insertions(+), 60 deletions(-)

Started integrating UI with Web API...
614c68e by Leniel Macaferi , Sun Jan 26 2020 (3 months ago)
9 files changed, 243 insertions(+), 83 deletions(-)

- Added ASP.NET Core 3.1 Todo Web API https://docs.microsoft.com/en-us/aspnet/core/tutorials/first-web-api?view=aspnetcore-3.1&tabs=visual-studio-code
06ce6d3 by Leniel Macaferi , Sun Jan 26 2020 (3 months ago)
15 files changed, 409 insertions(+), 4 deletions(-)

- Added handleChange event handler to todo-item component; - Handled todo complete state with setState; - Added Bootstrap: https://getbootstrap.com/
c15ff51 by Leniel Macaferi , Sat Jan 25 2020 (3 months ago)
8 files changed, 147 insertions(+), 38 deletions(-)

Converted some functional components to ES6 classes.
64dad12 by Leniel Macaferi , Sat Jan 25 2020 (3 months ago)
3 files changed, 53 insertions(+), 30 deletions(-)

- Got todo items displayed from a todo list JSON file. - Used array.map to create a React TodoItem component dynamically avoiding code repetition.
84cf3fc by Leniel Macaferi , Sat Jan 25 2020 (3 months ago)
5 files changed, 94 insertions(+), 25 deletions(-)

Created basic components and applied some styles including dynamic styling with JavaScript.
90ac727 by Leniel Macaferi , Sat Jan 25 2020 (3 months ago)
6 files changed, 128 insertions(+), 18 deletions(-)

Added .eslintrc.json and modified app name.
f1953ac by Leniel Macaferi , Fri Jan 24 2020 (3 months ago)
4 files changed, 14480 insertions(+), 1 deletion(-)

Initial commit from Create React App
04291f4 by Leniel Macaferi , Fri Jan 24 2020 (3 months ago)
18 files changed, 9789 insertions(+)

Source code

GitHub repository: https://github.com/leniel/ReactToDo

Figure 10 - GitHub repo with % of source code by type

Next

So stay tuned because in the next part I'll start covering the components in a top down approach, that is, we'll start looking at the index.js file where everything gets hooked up.

Today I was working with a database using LocalDB server and I had set this specific database named CBA as the default one for my user LENIEL-PC\Leniel. Setting a default database for the user is useful because when you login using SQL Server Management Studio [ SSMS ] you go directly to that database, that is, when running queries that’s the database used and not the master one. This is life saver.

I wanted to restore a backup file .bak I got from the production server using the Restore Database… command in SSMS. This is to test locally but first I had to drop my local database copy. I did drop my local copy while I was logged in to that database. I Selected the database in Object Explorer, right clicked it and selected Delete. Then checked the Close existing connections (bottom of the window), clicked OK and then the deletion succeeded. Pretty good… no.

What happened? The next time I tried to login again in SSMS this was what I got:

Figure 1 - SSMS | Cannot connect to (localdb)\v11.0.
Cannot open database requested by login. The login failed.
Login failed for user LENIEL-PC\Leniel (Microsoft SQL Server, Error: 4060)

Pretty obvious! I deleted the default database I had set to my user LENIEL-PC\Leniel. Now what? How can I login to the database server and change the default database? Good question…

This “simple” error was such a pain to fix…I tried lots of things like using SqlLocalDB.exe utility to stop and delete that specific LocalDB instance named v11.0. I would create it again afterwards if deleting it worked at least. This was the message logged in Windows Event logs when I tried to delete the instance:

Windows API call LogSqlDiagRec returned error code: 0. Windows system error message is: The operation completed successfully.
Reported at line: 3791. [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database "CBA" requested by the login. The login failed.

As you see the problem was the same. It was barking about the deleted database. Damn it!

I also tried the login with the sa account but guess what… the sa account was disabled for that LocalDB instance. Even if it was enabled I would not be able to login because I didn’t know the password for sa.

Googling more I managed to find this post by Pinal Dave:

SQL SERVER – Fix : Error: 4064 – Cannot open user default database. Login failed. Login failed for user

So guess what: the solution is ridiculously easy once you know what to do of course…

Click that Options >> button in Figure 1. Now select the Connection Properties tab.

Figure 2 - SSMS Connect to Server | Connection Properties | Connect to database option

I had to type master in Connect to database field since I did not have it in the list of available databases.

Now click connect and you’re done.

Great. Thanks God I was able to login and access the database server.

What’s left?

Reset my user Default database to master so that I can login again directly. By the way, the field Default database was empty since I had dropped the database!

Doing so there’s no need to set the Connect to database option anymore.

Figure 3 - SSMS | Setting Default database for user

Sometimes things are so easy but they are hidden somewhere we just can’t figure it out at first.

Hope it helps.

In a SQL Server database I have a table called users which has a column named signature. This column is of type Image.

My production SQL Server is located in a shared hosting environment.

One problem I’ve been facing lately is that I need permission to execute an UPDATE statement to insert a signature image for a given user. This problem occurs because to insert a signature image for a given user I have to execute a BULK statement like this for example:

--Update existing user
UPDATE users SET [signature] =(SELECT MyImage.* from Openrowset
Bulk 'C:\MyProject\trunk\MyCompany.Product\MyCompany.Product.Web\
Images\Signature.jpg', Single_Blob) MyImage) where Id 
= '1111aaaa-1111-11aa-a111-111111a1a1a1'

The query above works fine in my local machine but when I tried to execute it on the remote/production server, I got this beautiful message:You do not have permission to use the bulk load statement.

In the shared hosting environment the execution of Bulk command is disabled by default for security reasons. This is annoying but totally understandable!

Using Bulk load - allows the user to populate a database from a file. It’s not available in shared environment because it is necessary to insert client files on SQL server locally (the production server).

So… great! I need a way to bypass this limitation because I won’t spend tubes of money paying a dedicated server… it’d make sense if and only if I needed a dedicated server.

1st try: import a specific user row from my local SQL Server to the remote instance using SQL Server Management Studio Import task. I got an error about constraint key violation because I already had the same row (for that user) on both databases. It’s just a matter of updating the signature column in the production database. This seemed to be a pain path.

2nd try: consider a dedicated server? No thanks… hehehe

3rd try and solution: a few days later I found myself thinking about this problem again (this signature column updating thing is a recurring task) and so I decided to find another way and it came to light - link the remote server to my local SQL Server Express instance and write a beautiful SQL query that does the job.

First I stopped in this excellent blog post with a step by step guide written by jen: Create Linked Server SQL Server 2008
This post provided everything I needed to link both SQL Server instances.

To make sure you have linked your server correctly, you can execute this query in your local server:

select server_id, name, product, provider, data_source, [catalog], is_linked
from sys.servers

The above query gave me this result:

Figure 1 - Linked servers linked to my local SQL Server Express instance

Then I Googled about Update with Select + SQL Server or something like that and found this StackOverflow question: Updating a table with multiple values from a select statement where the date matches. Lieven’s answer helped. I just had to adapt it to my case. This is the SQL code that does the dirty work:

UPDATE  U
SET     U.[signature] = users.[signature]
FROM    [LOCAWEB].[laudotech].[dbo].[users] U INNER JOIN users
ON users.id = U.id AND
U.id = '1234aaaa-5678-90aa-b123-456789a0a1a2'

The above query must be executed within the context of the local SQL Server instance of course. Where the linked server resides.

To give you a view… this is how all this is configured inside SQL Server Management Studio (SSMS):

Figure 2 - SSMS Object Explorer and the Linked Server LOCAWEB in my local SQL Server Express instance

There’s so many things one can do with SSMS that I feel really happy in learning one more of those things. Last week I blogged about Import/Export SQL Server database data with SSMS. Take a look at it.

Man! Have I said that I Iike working with databases!?

Hope it helps.

This post is a visual guide about one of the most powerful features of Microsoft SQL Server Management Studio (SSMS) 2008: Import/Export database data.

These tasks are useful for example when you need to debug an application with real world/production data to find possible bugs in your code. You can import the production database data to your local development database and find what’s causing problems. These tasks can be used to create a protection layer because you won’t be working “directly” with production data.

As you see, if I right click a database in SSMS I get access to this rich menu full of interesting options that deserve a careful study:

Looking at the above picture you see in Object Explorer that I’m connected to two SQL database servers. The first one is a remote SQL Server (version 10.0.2775) and the other one is my local SQL Server Express (version 10.50.1617) instance.

With this configuration I have the perfect scenario to visually show you how the Import/Export Data… menu options work.

In this blog post I’ll execute the Export Data task. This allows me to test my web application using my local SQL Server instance with real world/production data.
The Import Data… task does practically the same thing but in reverse, logically. I’ll let the execution of the Import task to you dear reader!

OK… If I proceed and select the Export Data… menu option I’ll be able to bring new data to the database that is located on my machine. Data comes from the remote/production server to my local copy of the same database.

After clicking Export Data…, SQL Server Import and Export Wizard pops up. I then select the Data source/Server name (from which Data source type and Server I want to get the data to be imported). In this case, the data comes from the remote SQL server. I use SQL Server Authentication, fill the user name and password and select the Database that’ll provide the data. In Server name field I used the IP address of my remote server.

Now is the time to choose the Destination. In this case it’s my local SQL Server instance. LENIEL-PC is my Windows 7 machine name. SQLEXPRESS is the default SQL Express server name. I also selected the Database I want to receive the imported data. As you see both databases (source and destination) have the same name to make things easy.

The wizard then asks if I want to copy data directly from table to table or if I want to write a custom SQL query to shape the data according to specific needs. I’ll select the first option to keep things simple.

In the next screen we get a beautiful list of available tables and views. I’ve selected two tables that I want to copy.

If you click the Edit Mappings… button (which applies to each table selected in the grid), I can also specify if I want to delete rows before importing data or if I want to append rows to the destination table. I’ll append rows because I have already erased both tables (exams and files) on my local database.

Table files is linked to table exams through a foreign key. I disabled this foreign key constraint in my local database to avoid conflicts/errors during the import process. Beware because conflicts do appear most of the time. Conflicts are generally related to key violation/duplication.

As I’m using the free SQL Server Express Edition, the following screen has an interesting information:

In SQL Server Express, Web, or Workgroup, you can run the package that the Import and Export Wizard creates, but cannot save it. To save packages that the wizard creates, you must upgrade to SQL Server Standard, Enterprise, Developer or Evaluation.

This is telling me that I’ll have to go over the wizard (#7 steps) again if I happen to execute this very import process. Really annoying… … but that’s the price you must pay when using free tools.

Next button once more (the last one hallelujah) and we’re ready to go. It’s just a matter of reviewing what’ll be done and pressing the finish button.

After clicking Finish I got this last window:

Hope this simple and visual guide helps you understand how the data Import and Export processes work when dealing with a SQL Server database inside SSMS.

Free tools
Microsoft SQL Server Express Edition
Microsoft SQL Server 2008 Management Studio Express (SSMSE)