How To Create Store proecedure With Sql Transection

How To Create Store proecedure With Sql Transection 


GO
CREATE PROCEDURE dbo.sp_mysp
(
@RoleID int,
@AcessRights varchar(50),
@Name varchar(100),
@IsActive bit,
@IsDeleted bit,
@CreatedBy int,
@ModifiedBy int
)
AS
BEGIN
    BEGIN TRANSACTION;
    SAVE TRANSACTION MySavePoint;
    BEGIN TRY
 insert into UserLogin
 (
 [RoleID] , 
 [AccessRights] ,
 [Name],
 [IsActive] , 
 [IsDeleted] , 
 [CreatedBy] , 
 [CreatedDate], 
 [ModifiedBy] , 
 [ModifiedDate]
 ) 
 values(
 @RoleID ,
 @AcessRights ,
 @Name,
 @IsActive ,
 @IsDeleted ,
 @CreatedBy ,
 getdate() ,
 @ModifiedBy ,
 getdate()
 );
 //Write Table second Insert Query
 END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
        BEGIN
            ROLLBACK TRANSACTION MySavePoint; -- rollback to MySavePoint
        END
    END CATCH
    COMMIT TRANSACTION 
END;
GO



How To Create Database of Restorent Using Sql , MYSql

How To Create Database of Restorent Using Sql , MYSql


create table Restorent
(
id int primary key identity,
name varchar(100),
logourl varchar(200),
ownername varchar(100),
registrationno varchar(25),
tinno varchar(25),
country varchar(50),
state varchar(50),
city varchar(50),
address text,
phone varchar(15),
Email varchar(100)
addedon datetime default getdate(),
modifiedon datetime  default getdate(),
isactive datetime default false,
isdeleted isActive default false,
)

create table Roles
(
id int primary key identity,
name varchar(100),
addedon datetime default getdate(),
modifiedon datetime  default getdate(),
isactive datetime default false,
isdeleted isActive default false,
)

create table Users
(
id int primary key identity,
name varchar(100),
imageurl varchar(200),
uid varchar(25),
country varchar(50),
state varchar(50),
city varchar(50),
address text,
phone varchar(15),
Email varchar(100)
addedon datetime default getdate(),
modifiedon datetime  default getdate(),
isactive datetime default false,
isdeleted isActive default false
)

create UsersInRoles
(
id int primary key identity,
userid int foreign key refernces Users(id),
roleid int foreign key refernces Roles(id),
addedon datetime default getdate(),
modifiedon datetime  default getdate(),
isactive datetime default false,
isdeleted isActive default false,
)


Create table Category
(
id int primary key identity,
name varchar(100),
addedon datetime default getdate(),
modifiedon datetime  default getdate(),
isactive datetime default false,
isdeleted isActive default false
)
create table MenuType
(
id int primary key identity,
name varchar(100),
addedon datetime default getdate(),
modifiedon datetime  default getdate(),
isactive datetime default false,
isdeleted isActive default false
)

create table MenuItem
(
id int primary key identity,
name varchar(100),
description,
icon varchar(200),
price decimal(18,2),
Categoryid int int foreign key refernces Category(id),
Menutypeid int int foreign key refernces MenuType(id),
addedon datetime default getdate(),
modifiedon datetime  default getdate(),
isactive datetime default false,
isdeleted isActive default false
)

Create table sectiontype
(
id int primary key identity,
name varchar(100),
addedon datetime default getdate(),
modifiedon datetime  default getdate(),
isactive datetime default false,
isdeleted isActive default false
)

Create table sectionproperty
(
id int primary key identity,
name varchar(100),
addedon datetime default getdate(),
modifiedon datetime  default getdate(),
isactive datetime default false,
isdeleted isActive default false
)

create table section
(
id int primary key identity,
name varchar(100),
sectionpropertyid int int foreign key refernces sectionproperty(id),
sectiontypeid int int foreign key refernces sectiontype(id),
addedon datetime default getdate(),
modifiedon datetime  default getdate(),
isactive datetime default false,
isdeleted isActive default false
)
Create table diningtablestatus
(
id int primary key identity,
name varchar(100),
addedon datetime default getdate(),
modifiedon datetime  default getdate(),
isactive datetime default false,
isdeleted isActive default false
)

Create table diningtable
(
id int primary key identity,
charcount int,
sectionid int int foreign key refernces section(id),
diningtablestatusid int foreign key refernces diningtablestatus(id),
addedon datetime default getdate(),
modifiedon datetime  default getdate(),
isactive datetime default false,
isdeleted isActive default false
)


Create table ordertype
(
id int primary key identity,
name varchar(100),
addedon datetime default getdate(),
modifiedon datetime  default getdate(),
isactive datetime default false,
isdeleted isActive default false
)



create table order
(
id int primary key identity,
ordertypeid int foreign key refernces ordertype(id),
orderdate datetime
addedon datetime default getdate(),
modifiedon datetime  default getdate(),
isactive datetime default false,
isdeleted isActive default false
)


Create table orderdetails
(
id int primary key identity,
itemid int foreign  key references MenuItem(id),
Quantity int,
Amount decimal(18,2),
orderid int foreign key references order(id),
addedon datetime default getdate(),
modifiedon datetime  default getdate(),
isactive datetime default false,
isdeleted isActive default false
)


Create table diningtabletrack
(
id int primary key identity,
orderid int foreign key refernces order(id),
diningtableid int foreign key refernces  diningtable(id),
addedon datetime default getdate(),
modifiedon datetime  default getdate(),
isactive datetime default false,
isdeleted isActive default false
)


Create table Bills
(
id int primary key identity,
orderid int foreign key references order(id),
billdate datetime,
Amount decimal(18,2),
Discount decimal(18,2),
addedon datetime default getdate(),
modifiedon datetime  default getdate(),
isactive datetime default false,
isdeleted isActive default false
)

Create table Customer
(
id int primary key identity,
name varchar(100),
imageurl varchar(200),
uid varchar(25),
country varchar(50),
state varchar(50),
city varchar(50),
address text,
phone varchar(15),
Email varchar(100)
addedon datetime default getdate(),
modifiedon datetime  default getdate(),
isactive datetime default false,
isdeleted isActive default false
)

Create table reservation
(
id int primary key identity,
reservationdate datetime,
customerid int foreign key references Customer(id),
diningtableid int foreign key references diningtable(id) 
addedon datetime default getdate(),
modifiedon datetime  default getdate(),
isactive datetime default false,
isdeleted isActive default false
)

--Possible values of this parameter:
--CC – for credit card payment mode
--DC – for debit card payment mode
--NB – for net banking payment mode
--Telco – for Operator Billing
--PPI – For Paytm Cash

Create table Paymenttype
(
id int primary key identity,
code varchar(10),
Description varchar(100),
addedon datetime default getdate(),
modifiedon datetime  default getdate(),
isactive datetime default false,
isdeleted isActive default false
)

Create table cardtype
(
id int primary key identity,
Name varchar(50),
addedon datetime default getdate(),
modifiedon datetime  default getdate(),
isactive datetime default false,
isdeleted isActive default false
)
--SEAMLESS
--DEFAULT
--SUBSCRIBE
--RENEW_SUBSCRIPTION
Create table requesttype
(
id int primary key identity,
Name varchar(50),
addedon datetime default getdate(),
modifiedon datetime  default getdate(),
isactive datetime default false,
isdeleted isActive default false
)
--TXN_SUCCESS
--TXN_FAILURE
--TXN_PENDING
create table paytmstatus
(
id int primary key identity,
Name varchar(50),
addedon datetime default getdate(),
modifiedon datetime  default getdate(),
isactive datetime default false,
isdeleted isActive default false
)


How to  show multiple  checkbox on asp.Net mvc website and get value of checkbox in action method of post request of mvc controller using model property 

//METHOD FOR GET VALUES FROM DB

public List<AccessRight> getAcessRights()
        {
            return _entities.AccessRights.Where(t => t.IsActive == true).ToList();
        }

        public RoleAcessRight getRoleAcessRights(int id)
        {
             var s=_entities.RoleMasters.Where(t => t.IsActive == true && t.RoleID==id).FirstOrDefault();
            RoleAcessRight acessRight = new RoleAcessRight()
            {
                AccessRights = s.DefaultAccessRights.Split(',').Select(int.Parse).ToList(),
                ID = s.RoleID
            };
            return acessRight;
        }
     

// model class

Public class mymodelModel
{
public List<string> AccessRight { set; get; }
}

//View of action method

@{
    if (ViewBag.AccessRightList != null)
    {
        var AccessRightList = (List<AccessRight>)ViewBag.AccessRightList;
        var RoleAccessRightList = (RoleAcessRight)ViewBag.RoleAccessRightList;

        <div class="form-group">
            <div class="col-sm-offset-2 col-sm-6">
                @{
               
                    for (int i = 0; i < AccessRightList.Count(); i++)
                    {
                        var flag = RoleAccessRightList.AccessRights.Contains(AccessRightList[i].AccessRightsID);
                        <label class="checkbox-inline">
                            <input type="checkbox" Name="CreateProfileUser.AccessRight" @(flag == true ? "checked" : "" ) value="@AccessRightList[i].AccessRightsID" />@AccessRightList[i].Description
                        </label>


                    }
                }
            </div>
        </div>
    }
}

//action method Get request

 public ActionResult CreateUser(mymodelModel user)
 {
var lsttadig = _globalFunction.getRoleAcessRights(type);
            ViewBag.RoleAccessRightList = lsttadig;
            ViewBag.AccessRightList = _globalFunction.getAcessRights();
            return PartialView("GetRoleAcessRights");
}

//post request action method

[HttpPost]
        public ActionResult CreateUser(mymodelModel user)
        {
            ViewBag.operatorList = _globalFunction.getSpOperator((int)ProfileValue.Group);
            ViewBag.RoleList = new SelectList(_globalFunction.getRoles(), "ID", "Name");
            if (!ModelState.IsValid)
            {
             
                return View(user);
            }
            else
             
            return Json(new { Data = user }, JsonRequestBehavior.AllowGet);
        }

Featured Post

how to find n number of nodes have child or cildren

 how to find n number of nodes have child or cildren for that we use recursive function  const   data = {     'id' : '0' ,...

Popular Posts