Post
Ask Question

ASP.NET Core Web API Using 6.0 With Entity FrameWork And SQL Procedure

We learn ASP.NET Core Web API 6.0 With EF & SQL RepositoryPattern,

STEP 1

Create ASP.NET Core Web API Project 

ASP.NET Core Web Api using 6.0 With Entity FrameWork & Sql Procedure

STEP 2

ASP.NET Core Web Api using 6.0 With Entity FrameWork & Sql Procedure

STEP 3

ASP.NET Core Web Api using 6.0 With Entity FrameWork & Sql Procedure

STEP 4

Create Table in SQL 

CREATE TABLE [dbo].[Tbl_Account](
	[AC_ID] [bigint] IDENTITY(1,1) NOT NULL,
	[AC_Name] [varchar](max) NULL,
	[AC_Number] [nvarchar](max) NULL,
	[AC_isactive] [bit] NULL,
PRIMARY KEY CLUSTERED 
(
	[AC_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Step 5

Create Procedure in SQL

CREATE  proc [dbo].[Proc_InsertAccount]
(
    @AC_ID bigint=null,
    @AC_Name varchar(max)=null,
    @AC_Number varchar(max)=null,
    @AC_Isactive bit=null,
    @Flag varchar(max)=null,
    @AC_IDOutput bigint output
)
as
begin

IF(@Flag ='IN')
BEGIN

insert into Tbl_Account(AC_Name,AC_Number,AC_isactive)
values (@AC_Name,@AC_Number,@AC_Isactive)

SET @AC_IDOutput = @@IDENTITY

END
ELSE IF(@Flag='UP')
BEGIN

UPDATE Tbl_Account SET AC_Name=@AC_Name,AC_Number=@AC_Number,AC_isactive=@AC_isactive WHERE AC_ID=@AC_ID
SET @AC_IDOutput = @AC_ID
END
ELSE IF(@Flag='DE')
BEGIN

DELETE FROM Tbl_Account  WHERE AC_ID=@AC_ID

END

end

Step 6

CREATE Proc [dbo].[Proc_Account]
as
select * from Tbl_account
retrun

Step 7

Add EFCoreEntity & EFSql Entity from nuget package

Step 8

Add the Connection string in Appsettings.json file

ASP.NET Core Web Api using 6.0 With Entity FrameWork & Sql Procedure

"ConnectionStrings": {
    "Con": "Data Source=SERVER-8777;User ID=sa;Password=8777;Initial Catalog=SqlBankCore;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False"
  }

Step 9

Configure Connection String in Program.cs file

using Microsoft.EntityFrameworkCore;
builder.Services.AddDbContext<DataBaseContext>
    (options => options.UseSqlServer(builder.Configuration.GetConnectionString("Con")));

Step 9

Add interface folder under solution & add Interface for Account

using SqlBankApi7.Model;

namespace SqlBankApi7.Interface
{
    public interface IAccount
    {
        public List<Common> GetAccountDetails();
        public Common AddAccount(Account account);
        public void UpdateAccount(Account account);
        public Account DeleteAccount(Account account);
    }
}

Step 10

Create model folder under solution & create class Account,

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace SqlBankApi7.Model
{
    public class Account
    {
        [Key]
        public long AC_ID { get; set; }
        public string? AC_Name { get; set; }
        public string? AC_Number { get; set; }
        public bool AC_isactive { get; set; }
        [NotMapped]
        public string? Flag { get; set; }
    }
}

Step 11

Add DatabaseContext folder under project solution & create Database context class,

using Microsoft.EntityFrameworkCore;
using SqlBankApi7.Model;
namespace SqlBankApi7.DataBaseContext
{
    public partial class DataBaseContext : DbContext
    {

        public DataBaseContext()
        {
        }
        public DataBaseContext(DbContextOptions<DataBaseContext> options)
            : base(options)
        {
        }
        public virtual DbSet<Account>? Accounts { get; set; }
        public virtual DbSet<User>? Users { get; set; }
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<User>(user =>
            {
                user.HasKey(u => u.UserID);
                user.ToTable("Tbl_User");
                user.Property(u => u.UserID).HasColumnName("UserID");
                user.Property(u => u.DisplayName).HasMaxLength(50).IsUnicode(false);
                user.Property(u => u.UserName).HasMaxLength(50).IsUnicode(false);
                user.Property(u => u.Email).HasMaxLength(50).IsUnicode(false);
                user.Property(u => u.Password).HasMaxLength(50).IsUnicode(false);
                user.Property(u => u.CreatedDate).IsUnicode(false);
            });
            modelBuilder.Entity<Account>(entity =>
            {
                entity.HasKey(e => e.AC_ID);
                entity.ToTable("Tbl_Account");
                entity.Property(e => e.AC_Name).HasMaxLength(100).IsUnicode(true);
                entity.Property(e => e.AC_Number).HasMaxLength(100).IsUnicode(true);
                entity.Property(e => e.AC_isactive).HasMaxLength(100).IsUnicode(true);
                OnModelCreatingPartial(modelBuilder);
            });
        }
        partial void OnModelCreatingPartial(ModelBuilder modelBuilder);
    }
}

Step 12

We are using Repository Pattern for this core web API. Now create repository folder under solution & create Common class & AccountRepository.cs class

using SqlBankApi7.Model;
using System.ComponentModel.DataAnnotations.Schema;

namespace SqlBankApi7
{
    public class Common
    {
      public  List<Account>? Table { get; set; }
        [NotMapped]
        public string? Message { get; set; }
        [NotMapped]
        public int StatusCode { get; set; }
        [NotMapped]
        public string? Status { get; set; }
    }
}

 

using SqlBankApi7.Interface;
using SqlBankApi7.Model;
using SqlBankApi7.DataBaseContext;
using Microsoft.EntityFrameworkCore;
using Microsoft.Data.SqlClient;

namespace SqlBankApi7.Repository
{
    public class AccountRepository : IAccount
    {
        readonly DataBaseContext.DataBaseContext _dbContext = new();

        public AccountRepository(DataBaseContext.DataBaseContext dbContext)
        {
            _dbContext = dbContext;
        }

        public Common AddAccount(Account account)
        {
            Common cmm = new Common();
            cmm.Table = new List<Account>();
            List<Account> lstaccounts = new List<Account>();
            try    
            {
               
                // string Proc_InsertAccount = "Exec Proc_InsertAccount @AC_Name = '" + account.AC_Name + "',@AC_Number= '" + account.AC_Number + "',@AC_Isactive='" + account.AC_isactive + "'";
                SqlParameter[] sqlParameters = new SqlParameter[6];
                sqlParameters[0] = new SqlParameter("@AC_ID", System.Data.SqlDbType.BigInt);
                sqlParameters[1] = new SqlParameter("@AC_Name", System.Data.SqlDbType.NVarChar);
                sqlParameters[2] = new SqlParameter("@AC_Number", System.Data.SqlDbType.NVarChar);
                sqlParameters[3] = new SqlParameter("@AC_Isactive", System.Data.SqlDbType.Bit);
                sqlParameters[4] = new SqlParameter("@Flag", System.Data.SqlDbType.VarChar);
                sqlParameters[5] = new SqlParameter("@AC_IDOutput", System.Data.SqlDbType.BigInt);
                sqlParameters[0].Value = account.AC_ID;
                sqlParameters[1].Value = account.AC_Name;
                sqlParameters[2].Value = account.AC_Number;
                sqlParameters[3].Value = account.AC_isactive;
                sqlParameters[4].Value = account.Flag;
                sqlParameters[5].Direction = System.Data.ParameterDirection.Output;
                int Status=   _dbContext.Database.ExecuteSqlRaw("Proc_InsertAccount @AC_ID, @AC_Name,@AC_Number, @AC_Isactive,@Flag, @AC_IDOutput output ", sqlParameters);
                _dbContext.SaveChanges();
                if (Status == 1)
                {
                    account.AC_ID =Convert.ToInt64(sqlParameters[5].Value);
                    lstaccounts.Add(account);
                    cmm.Table = lstaccounts;
                    cmm.Status = "Sucess";
                    cmm.StatusCode = 1;
                    cmm.Message = "Record Inserted Sucessfully...";
                }
                else
                {
                  //  lstaccounts.Add(account);
                    cmm.Table = null;
                    cmm.Status = "Un-Sucess";
                    cmm.StatusCode = 0;
                    cmm.Message = "Record Inserted Failed...?";
                }

                #region below COde for insert Data by using Entity framework
                _dbContext.Accounts.Add(account);
                _dbContext.SaveChanges();
                #endregion
            }
            catch (Exception ex)
            {
                cmm.Table = null;
                cmm.Status = "Un-Sucess";
                cmm.StatusCode = 0;
                cmm.Message = ex.Message;
             //   throw;
            }
            return cmm;
        }

        public Account DeleteAccount(Account account)
        {
            try
            {
                SqlParameter[] sqlParameters = new SqlParameter[5];
                sqlParameters[0] = new SqlParameter("@AC_ID", System.Data.SqlDbType.BigInt);
                sqlParameters[1] = new SqlParameter("@AC_Name", System.Data.SqlDbType.NVarChar);
                sqlParameters[2] = new SqlParameter("@AC_Number", System.Data.SqlDbType.NVarChar);
                sqlParameters[3] = new SqlParameter("@AC_Isactive", System.Data.SqlDbType.Bit);
                sqlParameters[4] = new SqlParameter("@Flag", System.Data.SqlDbType.VarChar);
                sqlParameters[0].Value = account.AC_ID;
                sqlParameters[1].Value = account.AC_Name;
                sqlParameters[2].Value = account.AC_Number;
                sqlParameters[3].Value = account.AC_isactive;
                sqlParameters[4].Value = account.Flag;
                _dbContext.Database.ExecuteSqlRaw("Proc_InsertAccount @AC_ID , @AC_Name,@AC_Number, @AC_Isactive,@Flag ", sqlParameters);
                _dbContext.SaveChanges();

                #region Below code for update data by using Entityframework
                Account? acc = _dbContext.Accounts.Find(account.AC_ID);
                if (acc != null)
                {
                    _dbContext.Entry(account).State = EntityState.Modified;
                    _dbContext.SaveChanges();
                }
                #endregion
            }
            catch
            {

                throw;
            }
            //_dbContext.Accounts.Remove(account);
            //_dbContext.SaveChanges();
            return account;
        }

        public List<Common> GetAccountDetails()
        {
            Common Ac = new Common();
           List<Account> accountList = new List<Account>();
            List<Common> lstCom = new List<Common>();
            try
            {
                accountList = _dbContext.Accounts.FromSqlRaw("Proc_Account").ToList();
                if (accountList != null)
                {
                    if (accountList != null)
                    {
                        Ac.Table = accountList;
                        Ac.StatusCode = 1;
                        Ac.Message = "Record fetching sucessfully...";
                        Ac.Status = "Sucess";
                    }
                }
                else
                {
                  //  if (Ac.Table != null)
                    {
                        Ac.Table = null;
                        Ac.StatusCode = 1;
                        Ac.Message = "No Records found...?";
                        Ac.Status = "Fail";
                    }

                }
            }
            catch 
            {
                throw;
            }
            lstCom.Add(Ac);
            return lstCom;
        }
        public void UpdateAccount(Account account)
        {
            try
            {
                SqlParameter[] sqlParameters = new SqlParameter[5];

                sqlParameters[0] = new SqlParameter("@AC_ID", System.Data.SqlDbType.BigInt);
                sqlParameters[1] = new SqlParameter("@AC_Name", System.Data.SqlDbType.NVarChar);
                sqlParameters[2] = new SqlParameter("@AC_Number", System.Data.SqlDbType.NVarChar);
                sqlParameters[3] = new SqlParameter("@AC_Isactive", System.Data.SqlDbType.Bit);
                sqlParameters[4] = new SqlParameter("@Flag", System.Data.SqlDbType.VarChar);
                sqlParameters[0].Value = account.AC_ID;
                sqlParameters[1].Value = account.AC_Name;
                sqlParameters[2].Value = account.AC_Number;
                sqlParameters[3].Value = account.AC_isactive;
                sqlParameters[4].Value = account.Flag;
                _dbContext.Database.ExecuteSqlRaw("Proc_InsertAccount @AC_ID , @AC_Name,@AC_Number, @AC_Isactive,@Flag ", sqlParameters);
                _dbContext.SaveChanges(); 
            }
            catch 
            {
                throw;
            }
        }
    }
}

Add Controller folder under solution & add controller class AccountController.cs

using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Mvc;
using SqlBankApi7.Interface;
using SqlBankApi7.Model;
namespace SqlBankApi7.Controllers
{
    [Authorize]

    [ApiController]
    
    public class AccountController : ControllerBase
    {
        private readonly IAccount _IAccount;
        public AccountController(IAccount IAccounts)
        {
            _IAccount = IAccounts;
        }
        [Route("api/GetAccountDetails")]
        [HttpGet]
        public async Task<ActionResult<IEnumerable<Common>>> GetAccountDetails()
        {
            return await Task.FromResult(_IAccount.GetAccountDetails());
        }
        [Route("api/AddAccount")]
        [HttpPost]
        public Common AddAccount(Account account)
        {
         return   _IAccount.AddAccount(account);
        }
        [Route("api/UpdateAccount")]
        [HttpPut]
        public void UpdateAccount(Account account)
        { 
            _IAccount.UpdateAccount(account);
        }
        [Route("api/DeleteEmployee")]
        [HttpDelete]
        public void DeleteEmployee(Account account)
        {
            _IAccount.DeleteAccount(account);
        }
    }
}

Now run the application & check output.

ASP.NET Core Web Api using 6.0 With Entity FrameWork & Sql Procedure

ASP.NET Core Web Api using 6.0 With Entity FrameWork & Sql Procedure