Thursday 8 September 2022
Sql server and Design pattern
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Manoj Kalla
-- Create date: 18th Nov 2017
-- Description: Return all members
-- =============================================
--Store procedure name is --> stpGetAllMembers
CREATE PROCEDURE stpGetAllMembers
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Select statements for procedure here
Select * from tblMembers
END
GO
=========================================================Table
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
column3 datatype,
....
);
================================================================Table Variable
DECLARE @ListOWeekDays TABLE
(
DyNumber INT,
DayAbb VARCHAR(40) ,
WeekName VARCHAR(40)
)
INSERT INTO @ListOWeekDays
VALUES
(1,'Mon','Monday') ,
(2,'Tue','Tuesday') ,
(3,'Wed','Wednesday') ,
(4,'Thu','Thursday'),
(5,'Fri','Friday'),
(6,'Sat','Saturday'),
(7,'Sun','Sunday')
SELECT * FROM @ListOWeekDays
======================================================================
Let's take a look at a CTE example to drive this home:
WITH Simple_CTE
AS (
SELECT dd.CalendarYear
,fs.OrderDateKey
,fs.ProductKey
,fs.OrderQuantity * fs.UnitPrice AS TotalSale
,dc.FirstName
,dc.LastName
FROM [dbo].[FactInternetSales] fs
INNER JOIN [dbo].[DimCustomer] dc ON dc.CustomerKey = fs.CustomerKey
INNER JOIN [dbo].[DimDate] dd ON dd.DateKey = fs.OrderDateKey
)
SELECT *
FROM Simple_CTE
===================================================================
There are two main types of user-defined functions in SQL based on the data they return: Scalar functions: These types of functions return a single value, i.e float, int, varchar, datetime, etc. Table-Valued functions: These functions return tables.
Below is the definition of a simple function. It takes in two numbers and returns their sum. Since this function returns a number, it is a scalar function.
CREATE FUNCTION scalar_func
(
@a AS INT, -- parameter a
@b AS INT -- parameter b
)
RETURNS INT -- return type
AS
BEGIN
RETURN @a + @b -- return statement
END;
--------------------
CREATE FUNCTION table_valued_func()
RETURNS TABLE
AS
RETURN
-- statement to calculate sum
SELECT num1 , num2, num1 + num2 AS 'SUM'
FROM TEST;
===================================================================
The following FOR trigger fires on the INSERT operation on the Employee table.
CREATE TRIGGER dbo.trgEmployeeInsert
ON dbo.Employee
FOR INSERT
AS
INSERT INTO dbo.EmpLog(EmpID, Operation, UpdatedDate)
SELECT EmployeeID ,'INSERT',GETDATE() FROM INSERTED; --virtual table INSERTED
==========================================================
CREATE VIEW EmployeeRecords
AS
SELECT *
FROM [HumanResources].[Employee];
=========================================================
A SQL Server cursor is a set of T-SQL logic to loop over a predetermined number of rows one at a time. The purpose for the cursor may be to update one row at a time or perform an administrative process such as SQL Server database backups in a sequential manner.
================================================
Factory Design pattern:
using System;
namespace GarbageCollectionDemo
{
class Program
{
static void Main(string[] args)
{
Console.WriteLine("Transaction doing SBI Bank");
IBank sbi = BankFactory.GetBankObject("SBI");
sbi.ValidateCard();
sbi.WithdrawMoney();
sbi.CheckBalanace();
sbi.BankTransfer();
sbi.MiniStatement();
Console.WriteLine("\nTransaction doing AXIX Bank");
IBank AXIX = BankFactory.GetBankObject("AXIX");
AXIX.ValidateCard();
AXIX.WithdrawMoney();
AXIX.CheckBalanace();
AXIX.BankTransfer();
AXIX.MiniStatement();
Console.Read();
}
}
public interface IBank
{
void ValidateCard();
void WithdrawMoney();
void CheckBalanace();
void BankTransfer();
void MiniStatement();
}
public class BankFactory
{
public static IBank GetBankObject(string bankType)
{
IBank BankObject = null;
if (bankType == "SBI")
{
BankObject = new SBI();
}
else if (bankType == "AXIX")
{
BankObject = new AXIX();
}
return BankObject;
}
}
public class SBI : IBank
{
public void BankTransfer()
{
Console.WriteLine("SBI Bank Bank Transfer");
}
public void CheckBalanace()
{
Console.WriteLine("SBI Bank Check Balanace");
}
public void MiniStatement()
{
Console.WriteLine("SBI Bank Mini Statement");
}
public void ValidateCard()
{
Console.WriteLine("SBI Bank Validate Card");
}
public void WithdrawMoney()
{
Console.WriteLine("SBI Bank Withdraw Money");
}
}
public class AXIX : IBank
{
public void BankTransfer()
{
Console.WriteLine("AXIX Bank Bank Transfer");
}
public void CheckBalanace()
{
Console.WriteLine("AXIX Bank Check Balanace");
}
public void MiniStatement()
{
Console.WriteLine("AXIX Bank Mini Statement");
}
public void ValidateCard()
{
Console.WriteLine("AXIX Bank Validate Card");
}
public void WithdrawMoney()
{
Console.WriteLine("AXIX Bank Withdraw Money");
}
}
}
=========================================================Abstract Factory Design pattern
using System;
namespace GarbageCollectionDemo
{
class Program
{
static void Main(string[] args)
{
Console.WriteLine("Transaction doing SBI Bank");
IBank sbi = BankFactory.GetBankObject("SBI");
sbi.ValidateCard();
sbi.WithdrawMoney();
sbi.CheckBalanace();
sbi.BankTransfer();
sbi.MiniStatement();
Console.WriteLine("\nTransaction doing AXIX Bank");
IBank AXIX = BankFactory.GetBankObject("AXIX");
AXIX.ValidateCard();
AXIX.WithdrawMoney();
AXIX.CheckBalanace();
AXIX.BankTransfer();
AXIX.MiniStatement();
Console.Read();
}
}
public abstract class IBank
{
public abstract void ValidateCard();
public abstract void WithdrawMoney();
public abstract void CheckBalanace();
public abstract void BankTransfer();
public abstract void MiniStatement();
}
public class BankFactory
{
public static IBank GetBankObject(string bankType)
{
IBank BankObject = null;
if (bankType == "SBI")
{
BankObject = new SBI();
}
else if (bankType == "AXIX")
{
BankObject = new AXIX();
}
return BankObject;
}
}
public class SBI : IBank
{
public override void BankTransfer()
{
Console.WriteLine("SBI Bank Bank Transfer");
}
public override void CheckBalanace()
{
Console.WriteLine("SBI Bank Check Balanace");
}
public override void MiniStatement()
{
Console.WriteLine("SBI Bank Mini Statement");
}
public override void ValidateCard()
{
Console.WriteLine("SBI Bank Validate Card");
}
public override void WithdrawMoney()
{
Console.WriteLine("SBI Bank Withdraw Money");
}
}
public class AXIX : IBank
{
public override void BankTransfer()
{
Console.WriteLine("AXIX Bank Bank Transfer");
}
public override void CheckBalanace()
{
Console.WriteLine("AXIX Bank Check Balanace");
}
public override void MiniStatement()
{
Console.WriteLine("AXIX Bank Mini Statement");
}
public override void ValidateCard()
{
Console.WriteLine("AXIX Bank Validate Card");
}
public override void WithdrawMoney()
{
Console.WriteLine("AXIX Bank Withdraw Money");
}
}
}
======================================================================Singleton1 Design pattern
public sealed class Singleton1 {
private Singleton1() {}
private static Singleton1 instance = null;
public static Singleton1 Instance {
get {
if (instance == null) {
instance = new Singleton1();
}
return instance;
}
}
}
========================================================================
namespace RepositoryUsingEFinMVC.GenericRepository
{
public class GenericRepository : IGenericRepository where T : class
{
public EmployeeDBContext _context = null;
public DbSet table = null;
public GenericRepository()
{
this._context = new EmployeeDBContext();
table = _context.Set();
}
public GenericRepository(EmployeeDBContext _context)
{
this._context = _context;
table = _context.Set();
}
public IEnumerable GetAll()
{
return table.ToList();
}
public T GetById(object id)
{
return table.Find(id);
}
public void Insert(T obj)
{
table.Add(obj);
}
public void Update(T obj)
{
table.Attach(obj);
_context.Entry(obj).State = EntityState.Modified;
}
public void Delete(object id)
{
T existing = table.Find(id);
table.Remove(existing);
}
public void Save()
{
_context.SaveChanges();
}
}
}
Subscribe to:
Posts (Atom)