{"id":4480,"date":"2023-05-31T16:09:51","date_gmt":"2023-05-31T16:09:51","guid":{"rendered":"https:\/\/himbap.com\/blog\/?p=4480"},"modified":"2023-05-31T16:09:51","modified_gmt":"2023-05-31T16:09:51","slug":"connecting-azure-functions-with-postgresql","status":"publish","type":"post","link":"https:\/\/himbap.com\/blog\/?p=4480","title":{"rendered":"Connecting Azure Functions with PostgreSQL"},"content":{"rendered":"

Introduction<\/strong>:
\nIn the world of cloud computing, Azure Functions have become a popular choice for developing serverless applications. These lightweight, event-driven functions offer scalability and cost-efficiency, making them an ideal solution for various use cases. Recently we got one such requirement where we need to read PostgreSQL database view and need to update data in Dynamics 365. We used Azure function to connect with PostgreSQL, a powerful open-source relational database management system. In this blog post, we will see sample code to connect with PostgreSQL from Azure Function.<\/p>\n

Details<\/strong>
\nOur requirement was to Fetch data from PostgreSQL daily and update Dynamics 365 CE data, so we used Azure Timer Trigger Function to connect data. Below are the high level steps to setup connection<\/p>\n

1: Set Up Azure Timer Trigger Function App.
\n2. We need to Add reference to Npgsql (https:\/\/www.nuget.org\/packages\/Npgsql\/)<\/p>\n

\r\n string connectionstring = string.Empty;\r\n string viewquery = string.Empty;\r\n\r\n string host = Environment.GetEnvironmentVariable("Host");\r\n string port = Environment.GetEnvironmentVariable("Port");\r\n string database = Environment.GetEnvironmentVariable("Database");\r\n string username = Environment.GetEnvironmentVariable("User");\r\n string password = Environment.GetEnvironmentVariable("Password");\r\n string timeout = Environment.GetEnvironmentVariable("Timeout");\r\n\r\n            \/\/prepare connection string\r\n            connectionstring = "Host=" + host + ";Port=" + port + ";Database=" + database + ";Username=" + username + ";Password=" + password + ";Timeout=" + timeout;\r\n\r\n            viewquery = "select * from <ViewName>";\r\n using (NpgsqlConnection connection = new NpgsqlConnection(connectionstring))\r\n            {\r\n                try\r\n                {\r\n                    logger.LogInformation("Opening connection to Postgres...");\r\n                    connection.Open();\r\n                    logger.LogInformation("Connected to Host:" + host);\r\n                    logger.LogInformation("Feteching data from <ViewName>");\r\n\r\n                    using (var command = new NpgsqlCommand(viewquery, connection))\r\n                    {\r\n\r\n                        using (var reader = command.ExecuteReader())\r\n                        {\r\n\t\t\t\t\t\t\/\/PostgreSQLView is class where we have properties that we want to fetch from view\r\n                         List<PostgreSQLView> list = new List<PostgreSQLView>();\r\n\r\n                            while (reader.Read())\r\n                            {\r\n                                PostgreSQLView tbl=new PostgreSQLView();\r\n                                tbl.id= reader.GetInt32(0).ToString();\r\n                                tbl.name = reader.GetString(1).ToString();\r\n                                list.Add(tbl);\r\n                              \r\n                            }\r\n                        }\r\n                    }\r\n\r\n                    connection.Close();\r\n                  }\r\n                catch (Exception ex)\r\n                {\r\n                    logger.LogInformation(ex.Message);\r\n\r\n                }\r\n\r\n\r\n\r\n            }\r\n<\/pre>\n
Summary:
\nOnce we have data from view we can connect to Dynamics 365 and update data based on the identifiers. You can check our earlier blogs for Azure Function<\/p>\n
Developing Scheduler for Dynamics 365 CE using Azure Function Part 1<\/a>
\nDeveloping Scheduler for Dynamics 365 CE using Azure Function Part 2<\/a>
\nUsing Configuration with Azure Function<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
Introduction: In the world of cloud computing, Azure Functions have become a popular choice for developing serverless applications. These lightweight, event-driven functions offer scalability and cost-efficiency, making them an ideal solution for various use cases. Recently we got one such requirement where we need to read PostgreSQL database view and need to update data in Dynamics 365. We used Azure… Read more »<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[491,402,522],"tags":[764,1038,1040,1039],"_links":{"self":[{"href":"https:\/\/himbap.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/4480"}],"collection":[{"href":"https:\/\/himbap.com\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/himbap.com\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/himbap.com\/blog\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/himbap.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=4480"}],"version-history":[{"count":1,"href":"https:\/\/himbap.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/4480\/revisions"}],"predecessor-version":[{"id":4481,"href":"https:\/\/himbap.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/4480\/revisions\/4481"}],"wp:attachment":[{"href":"https:\/\/himbap.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=4480"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/himbap.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=4480"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/himbap.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=4480"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}