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

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

No comments:

Post a Comment