We learn ASP.NET Core Web API 6.0 With EF & SQL RepositoryPattern,
STEP 1
Create ASP.NET Core Web API Project
STEP 2
STEP 3
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]
GOSQL
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
endSQL
Step 6
CREATE Proc [dbo].[Proc_Account]
as
select * from Tbl_account
retrunSQL
Step 7
Add EFCoreEntity & EFSql Entity from nuget package
Step 8
Add the Connection string in Appsettings.json file
"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"
}SQL
Step 9
Configure Connection String in Program.cs file
using Microsoft.EntityFrameworkCore;
builder.Services.AddDbContext<DataBaseContext>
(options => options.UseSqlServer(builder.Configuration.GetConnectionString("Con")));C#
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);
}
}C#
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; }
}
}C#
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);
}
}C#
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; }
}
}C#
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;
}
}
}
}C#
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);
}
}
}C#
Now run the application & check output.
