How to Compare Two SQL Database Schemas from Visual Studio?

Is it possible to compare SQL Database schemas from Visual studio? The answer is yes. Recently I had to do some comparison of DBs and my colleague recommended this option. It's already described in the below link so I am not repeating the steps.

Just be careful when you deal with it. Make sure that you do proper backups and also test properly in lower environment schemas before making changes in production. It's always good to use production one as the guid in order to sync the lower environments.

https://www.sqlshack.com/how-to-compare-two-sql-databases-from-visual-studio/

How to Write to a SQL table using Entity Framework Core

Entity Framework is an object relational mapper from Microsoft. Difference between Entity Framework and Entity Framework Core is simple.  One is built using  .NET Core and other one .NET Framework. I have used an Azure Function ( Built on .NET Core) to demonstrate this.

Let's say we want to save some data to a sql table using C# code. There are different ways to do it. But using Enitty Framework its much simpler. Also Entity Framework supports a lot of databases.

List of supported providers of EF Core could be found here - https://docs.microsoft.com/en-us/ef/core/providers/?tabs=dotnet-core-cli

Let's say I have a table in SQL DB called Leads. Create query is given below.

CREATE TABLE [dbo].[Leads](
    [LeadNum] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](max) NULL,
    [Description] [nvarchar](max) NULL,
    [InsertedDateTime] [datetime] NULL,
    [Status] [nvarchar](50) NULL,
    [Customer] [nvarchar](max) NULL,
    [Email] [nvarchar](max) NULL,
    [Company] [nvarchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[Leads] ADD  CONSTRAINT [DF_Leads_InsertedDateTime]  DEFAULT (getdate()) FOR [InsertedDateTime]
GO

Now let's use some imagination. So the key advantage of using Entity Framework is that you could simply write to a Database ( In this context SQL DB) using C# style coding. It uses the structure of the classes. 

So I have used 3 main classes as shown below. You could either put them in one file or separate. Separate files are ideal. Please note the Key bit in the Lead Class. This is required. Because Entity Framework need a key. Also SqlConnectionString is stored in the Azure Function settings file ( This could be web.config / app. config depending on your scenario)

Namespaces used :
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Design;
using System;
using System.ComponentModel.DataAnnotations; 
------------------------------------------------------------------------------------------------------

    public  class Lead
    {
        [Key]
        public  int LeadNum { get; set; }
        public string Name { get; set; }
        public string Description { get; set; }
        public string Status { get; set; }
        public string Customer { get; set; }
        public string Company { get; set; }
        public string Email { get; set; }
    }
-------------------------------------------------------------------------------------------------------
    public class SQLDBContext : DbContext
    {
        public SQLDBContext(DbContextOptions<SQLDBContext> options)
            : base(options)
        { }
        public DbSet<Lead> Leads { get; set; }
    }
------------------------------------------------------------------------------------------------------
    public class SQLContextFactory : IDesignTimeDbContextFactory<SQLDBContext>
    {
        public SQLDBContext CreateDbContext(string[] args)
        {
            var optionsBuilder = new DbContextOptionsBuilder<SQLDBContext>();
            optionsBuilder.UseSqlServer(Environment.GetEnvironmentVariable("SqlConnectionString"));
            return new SQLDBContext(optionsBuilder.Options);
        }
    }


And then from the main Azure function ( this could be webjob , webapp etc ) I have used the below code snippet to write to the SQL DB. Feel free to change it wherever required as per your scenario.

                    string[] args = null;
                    SQLContextFactory sqlFactory = new SQLContextFactory();
                    SQLDBContext sqlDBContext= sqlFactory.CreateDbContext(args);
                    Lead leadRecord = new Lead();
                    leadRecord.Description = "Your Lead Description";
                    leadRecord.Name = "Your Subject";
                    leadRecord.Status = "Your Status";
                    leadRecord.Customer = "Your Name";
                    leadRecord.Email = "hello@youremail.com";
                    leadRecord.Company = "Your CompanyName";
                    sqlDBContext.Add(leadRecord);
                    sqlDBContext.SaveChanges();

And you could see the data in the SQL Table after the code is executed. Happy Ending : )

Assembly Reference Errors in Visual Studio

If you are using a lot of Nuget assemblies in your project you might come across this issue. You got reference to the Nuget but still Visual studio complains about the assembly reference not found. Behaves like it has no clue about your Nuget packages.

It was working fine yesterday!!!! And no additional changes made as well.

The simple step below helped me a lot when the solution failed to builld. Right click on the solution and click restore Nuget Packages. After this step, Clean the solution and then Rebuild solution.

How to Send Messages from Azure Function to Azure Service Bus Queue / Azure Service Bus Output Binding

Azure Functions provide a very easy way to send messages to Azure Service Bus Queue. Recently a colleague mentioned to me about this.So gave it a try and it worked like a charm.

I used to follow the below option. 
Aynchronously Send messages to Azure Service Bus Queue.
https://docs.microsoft.com/en-us/dotnet/api/microsoft.servicebus.messaging.queueclient.sendasync?view=azure-dotnet

But in Azure functions there is a cool feature called Azure Service Bus output binding. 

In simple words, Let's say I have an Azure Function ( Could be any trigger Timer, Blob etc) and from this function I would like to send a message to Azure Service Bus Queue. Instead of using queueClient.SendAsync method, I just need to bind my function to my Azure Service Queue. Azure Function would take care of the rest. This is obviously less coding and more efficient.

In the below sample code the Azure Function is a http triggered one. From this function it pushes a message to the Azure Service Bus Queue connection provided at the return part. In this case it's only sending a text message. But in a real scenario this could be a serialzed JSON. After function execution is complete, you could see the message in the Azure Service Bus Queue.

Sample Code Snippet from Microsoft :

[FunctionName("ServiceBusOutput")]
[return: ServiceBus("myqueue", Connection = "ServiceBusConnection")]
public static string ServiceBusOutput([HttpTrigger] dynamic input, ILogger log)
{
    log.LogInformation($"C# function processed: {input.Text}");
    return input.Text;
}

Ref: https://docs.microsoft.com/en-us/azure/azure-functions/functions-bindings-service-bus-output?tabs=csharp

Quering CDS using SQL

It's time to show off your SQL skills again! 

CDS (Common Data Service) would allow to query using SQL soon. This feature is in preview at the moment. Reminded me the old days where we used to query Dynamics OnPremise. 

NB: It's not possible to modify data. SQL connection would be readonly. Also some datatypes are not supported.

https://docs.microsoft.com/en-us/powerapps/developer/common-data-service/cds-sql-query