"тнιѕ вℓσg ¢συℓ∂ ѕανє уσυя мσηєу ιƒ тιмє = мσηєу" - ∂.мαηנαℓу

Sunday, 31 May 2020

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/


Sunday, 10 May 2020

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 : )

Saturday, 9 May 2020

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.

Friday, 8 May 2020

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


Sunday, 5 April 2020

Deprecation of Office365 authentication type and OrganizationServiceProxy class for connecting to Common Data Service

Recently came across this article from Microsoft. Please see the below link for further details.

https://docs.microsoft.com/en-us/power-platform/important-changes-coming#deprecation-of-office365-authentication-type-and-organizationserviceproxy-class-for-connecting-to-common-data-service

Effective Feb 4, 2020, the WS-Trust authentication type that is used by custom clients to connect to Common Data Service is deprecated. This change affects applications that utilize Microsoft.Xrm.Sdk.Client.OrganizationServiceProxy and Microsoft.Xrm.Tooling.Connector.CrmServiceClient classes for the authentication type of "Office365".

Important dates are the below

To allow for transition of customers and partner applications:
  • Effective April 2021, we intend to retire this authentication protocol for all new environments.
  • Effective April 2022, the authentication protocol will be retired for all new and existing environments.

Dynamics 365 Attachments Issue with Azure Blob

Attachment Management for Dynamics 365 Online using Azure blob storage. If you are not familiar with this solution here is the post.

https://crmdm.blogspot.com/2018/11/dynamics-365-attachments-in-azure-blob.html

Recently we noticed that some documents were showing as 0 KB in a lower environment. After some investigation it was found that the SAS token was expired. Attachment management solution is configured using SAS token. After fixing the SAS token all documents were displayed correctly. It's important to check the expiry of the SAS token