{"id":1704,"date":"2023-02-28T07:59:01","date_gmt":"2023-02-28T15:59:01","guid":{"rendered":"https:\/\/devblogs.microsoft.com\/azure-sql\/?p=1704"},"modified":"2023-03-01T10:38:43","modified_gmt":"2023-03-01T18:38:43","slug":"getting-started-with-azure-sql-bindings-part-1","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/azure-sql\/getting-started-with-azure-sql-bindings-part-1\/","title":{"rendered":"Getting Started with Azure SQL Bindings: Part 1"},"content":{"rendered":"

\"Image<\/a>Expanding on the excellent post by Drew Skwiers-Koballa;<\/a>\u00a0Developing with Azure SQL bindings and Azure SQL trigger for Azure Functions<\/a>, I’d like to take a chance to walk you through creating your first SQL Binding using a completely local development environment. Part 1 will cover creating a SQL Binding to get data from an Azure SQL Database. Parts 2 and 3 will then cover inserts, updates, and deletes as well as calling stored procedures.<\/p>\n

 <\/p>\n

<\/h2>\n

Prerequisites and Setup<\/h2>\n

Our local development environment will be using VS Code and Docker. If you don’t have either installed, you can get VS Code here<\/a> and Docker Desktop here<\/a>. You can also run through this post with either Windows or macOS. VS Code and Docker are available for both platforms so install both before proceeding.<\/p>\n

The Azure Functions Core Tools are needed so that we can run Azure functions right on your computer, no cloud needed until you are ready to deploy. You can get the installer here<\/a>.<\/p>\n

.NET is also needed for our local development environment. This post will be using version 7 which can be downloaded here<\/a> or is probably already installed if you have Visual Studio already installed. You can check the version by issuing a dotnet –version at a terminal prompt on your OS.<\/p>\n

\"Image<\/a><\/p>\n

While at the command prompt, run the following command to update SQL Projects with some new cool features:<\/p>\n

dotnet new -i Microsoft.Build.Sql.Templates<\/code><\/pre>\n
Then create a directory<\/strong> called bindings101 for the project in the terminal window (mkdir bindings101<\/strong>). Once it’s created, enter<\/strong> the directory.<\/p>\n
\"Image<\/a><\/p>\n
In the newly created directory, issue the following commands:<\/p>\n
dotnet new sqlproj -n \"bindingsProject\"<\/span> -tp \"SqlAzureV12\"<\/span><\/code><\/pre>\n
code .<\/code><\/pre>\n
\"Image<\/a><\/p>\n
Using code .<\/strong> in a directory instantly opens VS Code and pointing at the directory we were just in. There is also a database project ready to be used there.<\/p>\n
Install VS Code Extensions<\/h2>\n
Before we start building the binding, there are some extensions that are needed. The first one is the MS SQL Extension<\/strong>. Using the VS Code extensions panel, search for ms sql. Click the install …<\/p>\n
\"Image<\/a><\/p>\n
 <\/p>\n
The next extension is the C# extension<\/strong>. Again, using the search, enter c# and install this extension.<\/p>\n
\"Image<\/a><\/p>\n
 <\/p>\n
Finally, search for the Azure Functions extension<\/strong> and install it.<\/p>\n
\"Image<\/a><\/p>\n
 <\/p>\n
Using Azure Functions Core Tools<\/h2>\n
With VS Code open, open a New Terminal<\/strong> using the Terminal Dropdown menu.<\/p>\n
\"Image<\/a><\/p>\n
Our first SQl Binding is going to use .NET as the language. To initialize the project for a .NET binding, enter the following command in the VS Code terminal:<\/p>\n
func init --worker-runtime dotnet<\/pre>\n
Looking at the file navigator, the func process added the necessary files to use Azure functions locally right here within VS Code!<\/p>\n
\"Image<\/a><\/p>\n
Using the terminal in VS Code again, we are going to add the extension needed for SQL Bindings. Run the following command:<\/p>\n
dotnet add package Microsoft.Azure.WebJobs.Extensions.Sql --prerelease<\/pre>\n
Create a Database and Table<\/h2>\n
On the left side of VS Code, click the Database Projects icon<\/strong>.<\/p>\n
\"Image<\/a><\/p>\n
You can see the project that was created when you previously issued the command “new sqlproj”, ready to be used, here in Database Projects,<\/p>\n
Right click on bindingsProject<\/strong> and select Add Table<\/strong>.<\/p>\n
\"Image<\/a><\/p>\n
Name the table customers<\/strong> and then press enter<\/strong>.<\/p>\n
\"Image<\/a><\/p>\n
Replace the code in the customers.sql<\/strong> file with the following:<\/p>\n
\n
CREATE TABLE [dbo].[customers]\r\n(\r\n  [Id] INT NOT NULL PRIMARY KEY,\r\n  [Name] NVARCHAR(200) NOT NULL,\r\n  [Address] NVARCHAR(300) NOT NULL,\r\n  [State] NVARCHAR(20) NOT NULL,\r\n  [Phone] NVARCHAR(20) NOT NULL\r\n)\r\n\r\n<\/pre>\n
Then save the file.<\/div>\n
\"Image<\/a><\/div>\n
<\/div>\n
Right click on the bindingsProject<\/strong> and select Publish<\/strong>.<\/div>\n
\"Image<\/a><\/div>\n
<\/div>\n
This will start the process of creating a MS SQL instance in a Docker container and deploying the table into that database for the local development environment. Ensure Docker Desktop is up and running before proceeding to the next section. A series of dialog boxes appear on the top of VS Code app to create the local database instance.<\/div>\n
<\/div>\n
\n
\n
Select Publish to a new Azure Database emulator<\/strong> for the Select where to publish the project to dialog window:<\/div>\n
\n
\"Image<\/a><\/p>\n<\/div>\n<\/div>\n
Use the default port number 1433 for the SQL Server port and press enter.<\/div>\n
\n
\"Image<\/a><\/p>\n<\/div>\n
<\/div>\n
Enter a password you want to use for the admin user (sa user) in this dialog box. Ensure the password is a complex password that includes letters, numbers, and special characters.<\/div>\n
\n
\"Image<\/a><\/p>\n<\/div>\n
<\/div>\n
Enter the same password again.<\/div>\n
\n
\"Image<\/a><\/p>\n<\/div>\n
<\/div>\n
The wizard is then going to ask you about what docker image to pull. If you are on windows on an x86\/x64 based CPU, select the Full<\/strong> image<\/strong>. If you are on windows on ARM or an Apple Silicon Mac, select the Lite image.<\/div>\n
\n
\"Image<\/a><\/p>\n<\/div>\n
<\/div>\n
Now, accept<\/strong> the license agreement.<\/div>\n
\n
\"Image<\/a><\/p>\n<\/div>\n
<\/div>\n
And select the latest Default<\/strong> as the image tag<\/div>\n
\n
\"Image<\/a><\/p>\n<\/div>\n
<\/div>\n
Select Don’t use profile<\/strong> for the publish profile dialog box<\/div>\n
\n
\"Image<\/a><\/p>\n<\/div>\n
<\/div>\n
And finally, select bindingsProject<\/strong> as the database name we will create.<\/div>\n
\n
\"Image<\/a><\/p>\n<\/div>\n
When the database has started, and the project has been published,<\/div>\n<\/div>\n<\/div>\n
\"Image<\/a><\/div>\n
<\/div>\n
VS Code will also create a connection to the database. Open the Tables folder<\/strong> to see the customers table.<\/div>\n
\n
\"Image<\/a><\/div>\n<\/div>\n
\n
<\/div>\n<\/div>\n
Create an Input Binding<\/h2>\n
Press ‘F1’<\/strong> in VS Code and search for Azure Functions: Create Function<\/strong>\u00a0and select it.<\/p>\n
\"Image<\/a><\/p>\n
 <\/p>\n
Choose HttpTrigger<\/strong><\/p>\n
\"Image<\/a><\/p>\n
 <\/p>\n
Provide a function name.<\/strong> Use getCustomer<\/strong><\/p>\n
\"Image<\/a><\/p>\n
 <\/p>\n
Enter a namespace<\/strong>. Use Customer.Function<\/strong><\/p>\n
\"Image<\/a><\/p>\n
 <\/p>\n
Finally for Access Rights<\/strong>, use Anonymous<\/strong>.<\/p>\n
\"Image<\/a><\/p>\n
 <\/p>\n
In the file that opens in VS Code (named getCustomers.cs<\/strong>), replace the public static async Task<IActionResult> Run<\/strong> block<\/p>\n
\"Image<\/a><\/p>\n
with the below code.<\/p>\n
\n
  \u00a0 \u00a0 \u00a0 public static async Task<IActionResult> Run(\r\n  \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 [HttpTrigger(AuthorizationLevel.Anonymous, \"get\", \"post\", Route = \"customer\")] HttpRequest req,\r\n  \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 ILogger log,\r\n  \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 [Sql(\"select * from dbo.customers\",\r\n  \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \"SqlConnectionString\")]\r\n  \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 IEnumerable<Customer> customer)\r\n  \u00a0 \u00a0 \u00a0 {\r\n  \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 return new OkObjectResult(customer);\r\n\u00a0 \u00a0 \u00a0 \u00a0 }<\/pre>\n<\/div>\n
\"Image<\/a><\/p>\n
Save<\/strong> the file.<\/p>\n
About the SQL Binding Structure<\/h3>\n
Providing code without an explanation is never good, so this next section will break down the following code:<\/p>\n
 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 [Sql(\"select * from dbo.customers\",\r\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \"SqlConnectionString\")]<\/pre>\n
There are four parts to the Input Binding as represented in the table (from the documentation<\/a>):<\/p>\n\n\n\n\n\n\n\n\n
Attribute property<\/th>\nDescription<\/th>\n<\/tr>\n<\/thead>\n
CommandText<\/strong><\/td>\nRequired. The Transact-SQL query command or name of the stored procedure executed by the binding.<\/td>\n<\/tr>\n
CommandType<\/strong><\/td>\nRequired. A\u00a0CommandType<\/a>\u00a0value, which is\u00a0Text<\/a>\u00a0for a query and\u00a0StoredProcedure<\/a>\u00a0for a stored procedure.<\/td>\n<\/tr>\n
Parameters<\/strong><\/td>\nOptional. Zero or more parameter values passed to the command during execution as a single string. Must follow the format\u00a0@param1=param1,@param2=param2<\/code>. Neither the parameter name nor the parameter value can contain a comma (,<\/code>) or an equals sign (=<\/code>).<\/td>\n<\/tr>\n
ConnectionStringSetting<\/strong><\/td>\nRequired. The name of an app setting that contains the connection string for the database against which the query or stored procedure is being executed. This value isn’t the actual connection string and must instead resolve to an environment variable name.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
And to expand it further:<\/p>\n
\n
[Sql(CommandText,\r\n  \u00a0 CommandType,\r\n  \u00a0 Parameters,\r\n\u00a0 \u00a0 ConnectionStringSetting\r\n\r\n)]<\/pre>\n<\/div>\n
Looking at the code in the getCustomers.cs file<\/strong><\/p>\n
 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 [Sql(\"select * from dbo.customers\",\r\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \"SqlConnectionString\")]<\/pre>\n
CommandText is the SQL query, CommandType defaults to CommandType.Text and not present in the call. There are no Parameters to pass in this example so it is not needed. And the last parameter, as indicated in the table is the connect string to the database. This will be entered later in this post and point to the local SQL Database we published the Database Project to. In parts 2 and 3, when Output and Trigger bindings used, more of these parameters will be passed to the binding.<\/p>\n
Add a Class<\/h3>\n
While editing the getCustomers.cs<\/strong> file, add ‘using System.Collections.Generic;<\/strong>‘ to the namespaces list at the top<\/strong> of the page.<\/p>\n
\"Image<\/a><\/p>\n
Save<\/strong> the file.<\/p>\n
Right click in the File Navigator<\/strong> under the project files and select New File<\/strong>.<\/p>\n
\"Image<\/a><\/p>\n
 <\/p>\n
Then, name<\/strong> the new file Customer.cs<\/strong><\/p>\n
\"Image<\/a><\/p>\n
Paste the code below into the file. These are the column names of our database table.<\/p>\n
Note that the casing of the Object field names and the table column names must match.<\/strong><\/p>\n
namespace Customer.Function<\/span><\/p>\n
\n
  \u00a0 { public class Customer {\r\n  \u00a0 \u00a0 \u00a0 public int Id { get; set; }\r\n  \u00a0 \u00a0 \u00a0 public string Name { get; set; }\r\n  \u00a0 \u00a0 \u00a0 public string Address { get; set; }\r\n  \u00a0 \u00a0 \u00a0 public string State { get; set; }\r\n  \u00a0 \u00a0 \u00a0 public string Phone { get; set; }\r\n  \u00a0 \u00a0 \u00a0 }\r\n  \u00a0 }<\/pre>\n<\/div>\n
Save<\/strong> the file.<\/p>\n
Using the File Navigator, click<\/strong> on the local.settings.json<\/strong> file.<\/p>\n
\"Image<\/a><\/p>\n
Add the following line in the top of the Values<\/strong> section and replace<\/strong> yourstrongpassword with the password you used when you created the database.<\/p>\n
\"SqlConnectionString\": \"Server=localhost,1433;Database=bindingsProject;User ID=sa;Password=yourstrongpassword;Encrypt=True;TrustServerCertificate=True;Connection Timeout=30;\",<\/pre>\n
\"Image<\/a><\/p>\n
While still in the local.settings.json file, set AzureWebJobsStorage<\/strong> to UseDevelopmentStorage=false<\/strong><\/p>\n
\n
\"AzureWebJobsStorage\": \"UseDevelopmentStorage=false\",<\/pre>\n
Save<\/strong> the file.<\/span><\/div>\n<\/div>\n
 <\/p>\n
Try the Function<\/h2>\n
Navigate back to the getCustomers.cs<\/strong> file and press ‘F5<\/strong>‘ to run your code. This will start up the Functions Host with a local HTTP Trigger and SQL Input Binding. In the output section on the bottom of VS Code, a URL will be show similar to<\/span><\/p>\n
getCustomers: [GET,POST] http:\/\/localhost:7071\/api\/customer\r\n\"Image<\/a>\r\n\r\n<\/span><\/pre>\n
Control Click<\/strong> or copy and paste the URL<\/strong> into a browser. The result in the browser should be an empty array<\/strong> because there is no data in the table.<\/p>\n
\"Image<\/a><\/p>\n
We can add data to the table by clicking the SQL Server icon<\/strong> on the left side of VS Code<\/strong><\/p>\n
\"Image<\/a><\/p>\n
 <\/p>\n
then right clicking the server name<\/strong> and selecting New Query<\/strong><\/p>\n
\"Image<\/a><\/p>\n
 <\/p>\n
Copy and paste the following SQL into the query sheet<\/p>\n
\n
insert into dbo.customers values (1, N'Ben', N'123 West Street', N'Any State', N'(555)-555-0000');\r\n\r\n<\/pre>\n
Click the run<\/strong> button to insert the row into the table<\/span><\/div>\n<\/div>\n
\"Image<\/a><\/p>\n
 <\/p>\n
and refresh<\/strong> the browser. The row is now seen in JSON format from the SQL Binding function in your browser.<\/p>\n
\"Image<\/a><\/p>\n
Congratulations!<\/strong> You have successfully created your first SQL input binding!<\/p>\n
Part 2 and 3<\/h2>\n
In the next parts, Output and Trigger SQL Bindings will be covered with samples and a walkthrough for each.<\/p>\n","protected":false},"excerpt":{"rendered":"
Expanding on the excellent post by Drew Skwiers-Koballa;\u00a0Developing with Azure SQL bindings and Azure SQL trigger for Azure Functions, I’d like to take a chance to walk you through creating your first SQL Binding using a completely local development environment. Part 1 will cover creating a SQL Binding to get data from an Azure SQL […]<\/p>\n","protected":false},"author":95874,"featured_media":1774,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[555,1,556,533],"tags":[244,510,541,557,558],"class_list":["post-1704","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-azure-functions","category-azure-sql","category-sql-bindings","category-visual-studio-code","tag-net","tag-azure-sql-database","tag-azure-functions","tag-sql-bindings","tag-vs-code"],"acf":[],"blog_post_summary":"
Expanding on the excellent post by Drew Skwiers-Koballa;\u00a0Developing with Azure SQL bindings and Azure SQL trigger for Azure Functions, I’d like to take a chance to walk you through creating your first SQL Binding using a completely local development environment. Part 1 will cover creating a SQL Binding to get data from an Azure SQL […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/posts\/1704","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/users\/95874"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/comments?post=1704"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/posts\/1704\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/media\/1774"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/media?parent=1704"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/categories?post=1704"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/tags?post=1704"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}