C# Timer Azure SQL function

Question

Hi I'm trying to create an azure timer trigger c# function which executes every 5 minutes(just to test - will be changing to daily) to update a SQL database columns. Here is my code as follows in the TimerTriggerCSharp1 Function app

 using System;
  using System.Data.SqlClient;
 using System.Text.RegularExpressions;
using Microsoft.SqlServer.Server;




 public static void Run(TimerInfo myTimer, TraceWriter log)
 {
log.Info($"C# Timer trigger function executed at: {DateTime.Now}");
        string connStr = "someconstring";
             SqlConnection conn = new SqlConnection(connStr);
            conn.Open();
            string query = "UPDATE dbo.test_workstations SET email =         @email,     duration = @duration, status = @status, startDate = @startDate, endDate = @endDate WHERE endDate = @endDateToRemove";
        SqlCommand cmd = new SqlCommand(query, conn);
        cmd.Parameters.AddWithValue("@email", DBNull.Value);
        cmd.Parameters.AddWithValue("@duration", DBNull.Value);
        cmd.Parameters.AddWithValue("@status", 0);
        cmd.Parameters.AddWithValue("@startDate", DBNull.Value);
        cmd.Parameters.AddWithValue("@endDate", DBNull.Value);
        cmd.Parameters.AddWithValue("@endDateToRemove", DateTime.Now.ToShortDateString());
        cmd.ExecuteNonQuery();
      }



 }

Basically what I'm trying to do is update table to set null values based on if a column is showing todays date.

Any help please i'm getting a bunch of errors in azure - it works in visual studio!

2017-08-28T15:46:27.422 run.csx(4,17): error CS0234: The type or namespace name 'SqlServer' does not exist in the namespace 'Microsoft' (are you missing an assembly reference?)

2017-08-28T15:46:27.422 run.csx(13,13): error CS0246: The type or namespace name 'SqlConnection' could not be found (are you missing a using directive or an assembly reference?)

2017-08-28T15:46:27.422 run.csx(13,38): error CS0246: The type or namespace name 'SqlConnection' could not be found (are you missing a using directive or an assembly reference?)

2017-08-28T15:46:27.422 run.csx(16,13): error CS0246: The type or namespace name 'SqlCommand' could not be found (are you missing a using directive or an assembly reference?)

2017-08-28T15:46:27.422 run.csx(16,34): error CS0246: The type or namespace name 'SqlCommand' could not be found (are you missing a using directive or an assembly reference?)

2017-08-28T15:46:27.454 Exception while executing function: Functions.TimerTriggerCSharp1. Microsoft.Azure.WebJobs.Script: Script compilation failed.

2017-08-28T15:46:27.485 Function completed (Failure, Id=612e3097-849f-4816-b026-777cfb50c46a, Duration=157ms)

what's the reason for using "Microsoft.SqlServer.Server"? importing this namespace seems redundant. — Kevin Brydon

David Browne - Microsoft David Browne - Microsoft · Accepted Answer · 2017-08-28T16:01:53

You need an Assembly Reference to System.Data.dll, which in C# Script you do like this:

#r "System.Data"

using System;
using System.Data.SqlClient;
. . .

System.Data.dll is part of the .NET Framework Base Class Library (BCL) (so it's guaranteed to be present), but it's not referenced by default by the Azure Functions environment. This is explained here in the Azure Functions docs.

And you don't need to import the Microsoft.SqlServer.Server namespace. Everything you need is in System.Data.SqlClient.

C# Timer Azure SQL function

1 Answers