I think Azure Functions are great, but the inability to use SQL Server is a major draw back. If we want to go serverless, but still use SQL Server are we just out of luck? Absolutely not! In this tutorial I'll design a simple Register User workflow using Azure Logic Apps and SQL Server. Let's get started by creating a logic app.
To start login into your Azure Portal, navigate to Resource Group you want to use (or create a new one) and then add a new Logic App. Make sure to select the correct subscription (if you have more than one), the resource group you want, location, and if you want analytics turned on.
I want this logic app to be triggered by a post request. For this example the post body will be a very simple JSON object that contains only the username and password. On the logic app designer search for a HTTP request trigger. Expand the advanced options and select POST as the method. If you are new to creating JSON schema you can generate the schema from a sample payload. The next step will be to add an action that checks if the user already exists in the database and then follow one of two code paths.
Before we add the if condition, we need to add the action that will check the database. To do that we need to add a SQL Server connector. Click on New Step then Add Action next click on SQL Server in the connectors section. At first I tried using the Get row action. The issue I ran into is that I could not find a way to check if the row exists. Maybe there is a simple way to do that, but I couldn't find it. After some google searches it seemed like the recommended solution was to use a stored procedure.
If you don't already have a connection to a database created you'll have to create one before you can connect to SQL Server. The setup is pretty simple, just follow the on screen prompts. Like I said earlier, I could not find a way to use 'Get Row' so I'm using the Execute stored procedure to determine if the user already exists in the database. Select the procedure in your database that you want to use and enter any parameters. I'm using the username that will be in the body of the http request. For the condition I'm checking the return code of the stored procedure and doing something based on that. If 1 is returned then the user already exists and I'll return an error code. Otherwise the user does not exist and then I'll check if the password is valid and if it is add the register the new user.
You can see that my stored procedure is really simple. I would much prefer not to create such a simple procedure, but this is the solution that I could get to work. ​Next I'm going to finish the path for if the username already exists.
If a status code of 1 is returned from the stored procedure then that username already exists. Since usernames have to be unique we want to return an error code to the user. To accomplish this we will add a Response action under the 'If true' condition. We are going to return the 409 Conflict code, but you could return anything here. We are also going to return a message to the user.
For testing I have added a sample user to the database. Open postman (or your API testing tool of choice) and copy the URL from the trigger to the address field, set the method to POST, add the content-type header and add the body to the request. All that's left is to click Send and see what response we get.
If you setup and saved everything then you should see a response like this when a user tries to register with a username that already exists.
That's it! We created a logic app that executes a stored procedure to check if a user already exists, and returns an error code if it does. We did all of that and the only code we wrote is the very simple stored procedure that is less than 10 lines of SQL. Next time we'll handle the path when the user does not already exist. Hope you learned something.
Happy Coding Sean Wernimont The Blind Squirrel Copyright 2015-2020
|
AuthorWelcome to The Blind Squirrel (because even a blind squirrel occasionally finds a nut). I'm a full-stack web and mobile developer that writes about tips and tricks that I've learned in Swift, C#, Azure, F# and more. Archives
April 2018
Categories
All
|