HMS Project Query SQL

 

-- =============================================

-- Create date: 12-11-2022

-- Description: Procedure for Inserting Data in table

-- =============================================

 

create database HMSProject

use HMSProject

 

 

create Table EmployeesInfo

(

Empid int Primary Key identity(1,1),

EmpName varchar(15),

EmpPhone varchar(15),

EmpEmail varchar(30) Not Null,

EmpAddress varchar(15),

EmpPassword varchar(15),

RegisterDate Datetime

)

create Table DoctorInfo

(

Did int Primary Key identity(1,1),

DName varchar(15),

DCNIC varchar(15),

DFatherName varchar(30),

DSpeciality varchar(15),

DFee int,

DQualification varchar(15),

DRegisterDate Datetime

)

create Table PatientInfo

(

Pid int Primary Key identity(1,1),

PName varchar(15),

PCNIC varchar(15),

PDisease varchar(15),

PFatherName varchar(30),

 

PAppointmentDate varchar(20),

PConsultDoctor varchar(15),

PCheckYesNo varchar(15)CHECK (PCheckYesNo IN ( 'Yes', 'No','yes','no','Waiting')) DEFAULT 'Waiting' ,

PRegisterDate Datetime

)

create Table Billing

(

Bid int Primary Key identity(1,1),

BPatientID varchar(15),

BPatientName varchar(15),

BDoctorID varchar(15),

BMedicineFee varchar(15),

BPatientDisease varchar(30),

 

BTimeDuration varchar(20),

BTotalBill varchar(15),

BPaidOrNot varchar(15),

BCreateDate Datetime

)

-------------------------------------insert EmployeeInfos Produre Data------------------

alter proc EmployeesInfoInsert

(

@EmpName varchar(15),

@EmpPhone varchar(15),

@EmpEmail varchar(30),

@EmpAddress varchar(15),

@EmpPassword varchar(15),

--@RegisterDate Datetime

@Msg   varchar(30) output

)

AS

Begin

BEGIN TRY

      if (exists(select * from EmployeesInfo where EmpEmail=@EmpEmail))

      Begin

            SET @Msg='Already Use'

      End

      else

      begin

      INSERT INTO EmployeesInfo

            VALUES

            (

            @EmpName ,

            @EmpPhone ,

            @EmpEmail ,

            @EmpAddress ,

            @EmpPassword ,

            GETDATE()

            )

      SET @Msg='Table Detail Saved Successfully.'

      End

   

END TRY

BEGIN CATCH

    SET @Msg=ERROR_MESSAGE()

END CATCH

end

 

---------------------------------End Proc EmployeeInfo-----------------------------------

---------------------------------execute-----------------------------------

DECLARE @Msg varchar(30)

--SET @VAL = 1 PRINT @VAL

DECLARE  @EmpName varchar(15)

DECLARE  @EmpPhone varchar(15)

DECLARE @EmpEmail varchar(30)='muzamil'

DECLARE @EmpAddress varchar(15)

DECLARE @EmpPassword varchar(15)

EXEC EmployeesInfoInsert

       @EmpName ,

      @EmpPhone ,

      @EmpEmail ,

      @EmpAddress ,

      @EmpPassword ,

      @msg output

PRINT @msg

----------------------------------------------------End Execute------------------

insert into EmployeesInfo values('muzamil','356','','jhkuhj','123' ,GETDATE())

select * from EmployeesInfo

delete EmployeesInfo

------------------------------------------------------------------------End------------------------------------

-------------------------------------insert DoctorInfo Produre Data------------------

alter proc DoctorInfoInsert

(

@DName varchar(15),

@DCNIC varchar(15),

@DFatherName varchar(30),

@DSpeciality varchar(15),

@DFee int,

@DQualification varchar(15),

@Msg   varchar(30) output

)

AS

Begin

BEGIN TRY

      if (exists(select * from DoctorInfo where DCNIC=@DCNIC))

      Begin

            SET @Msg='CNIC Already use, Place new add CNIC'

      End

      else

      begin

      INSERT INTO DoctorInfo

            VALUES

            (

            @DName ,

            @DCNIC ,

            @DFatherName ,

            @DSpeciality ,

            @DFee,

            @DQualification ,

            GETDATE()

            )

      SET @Msg='Saved Successfully.'

      End

   

END TRY

BEGIN CATCH

    SET @Msg=ERROR_MESSAGE()

END CATCH

end

 

---------------------------------End Proc DoctorInfo-----------------------------------

---------------------------------execute-----------------------------------

DECLARE @Msg varchar(30)

DECLARE @DName varchar(15)

DECLARE @DCNIC varchar(15)='1423777'

DECLARE @DFatherName varchar(30)

DECLARE @DSpeciality varchar(15)

DECLARE @DFee int

DECLARE @DQualification varchar(15)

EXEC DoctorInfoInsert

       @DName ,

       @DCNIC ,

       @DFatherName ,

       @DSpeciality ,

       @DFee,

       @DQualification ,

       @msg output

PRINT @msg

----------------------------------------------------End Execute------------------

insert into DoctorInfo values('muzamil','356','','jhkuhj','123' ,GETDATE())

select * from DoctorInfo

delete DoctorInfo

------------------------------------------------------------------------End------------------------------------

-------------------------------------insert PatientInfo Produre Data------------------

alter proc PatientInfoInsert

(

@PName varchar(15),

@PCNIC varchar(15),

@PDisease varchar(15),

@PFatherName varchar(30),

@PAppointmentDate varchar(20),

@PConsultDoctor varchar(15),

@Msg   varchar(30) output

)

AS

Begin

BEGIN TRY

      if (exists(select * from PatientInfo where PCheckYesNo ='Waiting' and PCNIC=@PCNIC))

      Begin

            SET @Msg='Already Added Patient for Waiting, Place Check Patient'

      End

      else

      begin

      INSERT INTO PatientInfo

            VALUES

            (

            @PName ,

            @PCNIC ,

            @PDisease ,

            @PFatherName ,

            @PAppointmentDate ,

            @PConsultDoctor ,

            'Waiting',

            GETDATE()

            )

      SET @Msg='Saved Successfully.'

      End

   

END TRY

BEGIN CATCH

    SET @Msg=ERROR_MESSAGE()

END CATCH

end

 

---------------------------------End Proc EmployeeInfo-----------------------------------

---------------------------------execute-----------------------------------

DECLARE @Msg varchar(30)

DECLARE @PName varchar(15)

DECLARE @PCNIC varchar(15)='8'

DECLARE @PDisease varchar(15)

DECLARE @PFatherName varchar(30)

DECLARE @PAppointmentDate varchar(20)

DECLARE @PConsultDoctor varchar(15)

EXEC PatientInfoInsert

      @PName ,

            @PCNIC,

            @PDisease ,

            @PFatherName ,

            @PAppointmentDate ,

            @PConsultDoctor ,

       @msg output

PRINT @msg

----------------------------------------------------End Execute------------------

insert into PatientInfo values('muzamil','356','','jhkuhj','123' ,GETDATE())

select * from PatientInfo

delete PatientInfo

------------------------------------------------------------------------End------------------------------------

-------------------------------------insert Billing Produre Data------------------

alter proc BillingInsert

(

@BPatientID varchar(15),

@BPatientName varchar(15),

@BDoctorID varchar(15),

@BMedicineFee varchar(15),

@BPatientDisease varchar(30),

 

@BTimeDuration varchar(20),

@BTotalBill varchar(15),

@BPaidOrNot varchar(15),

@Msg   varchar(30) output

)

AS

Begin

BEGIN TRY

      if (exists(select * from Billing where BPatientID =@BPatientID  and BPaidOrNot= 'unPaid'))

      Begin

            SET @Msg='old bill clare '+Convert(varchar,(select BTotalBill from Billing where BPatientID =@BPatientID ))

      End

      else

      begin

      INSERT INTO Billing

            VALUES

            (

            @BPatientID ,

            @BPatientName,

            @BDoctorID ,

            @BMedicineFee ,

            @BPatientDisease ,

 

            @BTimeDuration ,

            @BTotalBill,

            @BPaidOrNot,

            GETDATE()

            )

      SET @Msg='Saved Successfully.'

      End

   

END TRY

BEGIN CATCH

    SET @Msg=ERROR_MESSAGE()

END CATCH

end

 

---------------------------------End Proc Billing-----------------------------------

---------------------------------execute-----------------------------------

DECLARE @Msg varchar(30)

DECLARE @BPatientID varchar(15)

DECLARE @BPatientName varchar(15)

DECLARE @BDoctorID varchar(15)

DECLARE @BMedicineFee varchar(15)

DECLARE @BPatientDisease varchar(30)

 

DECLARE @BTimeDuration varchar(20)

DECLARE @BTotalBill varchar(15)

DECLARE @BPaidOrNot varchar(15)

EXEC BillingInsert

            @BPatientID ,

            @BPatientName,

            @BDoctorID ,

            @BMedicineFee ,

            @BPatientDisease ,

            @BTimeDuration ,

            @BTotalBill,

            @BPaidOrNot,

          @msg output

PRINT @msg

----------------------------------------------------End Execute------------------

insert into Billing values('muzamil','356','','jhkuhj','123' ,GETDATE())

select * from Billing

delete Billing

------------------------------------------------------------------------End------------------------------------

 

 

 

 

Visual Studio code

String dbpath = @"Data Source=DESKTOP-MFE18NA\SQLEXPRESS;Initial Catalog=HMSProject;Integrated Security=True";

 

 SqlConnection sqlCon ;

            try

            {

                using (sqlCon = new SqlConnection(dbpath))

                {

                    sqlCon.Open();

                    SqlCommand sql_cmnd = new SqlCommand("EmployeesInfoInsert", sqlCon);

                    sql_cmnd.CommandType = CommandType.StoredProcedure;

                    sql_cmnd.Parameters.AddWithValue("@EmpName ", txtSignupName.Text);

                    sql_cmnd.Parameters.AddWithValue("@EmpPhone ", txtSignupPhone.Text);

                    sql_cmnd.Parameters.AddWithValue("@EmpEmail ", txtSignupEmail.Text);

                    sql_cmnd.Parameters.AddWithValue("@EmpAddress ", txtSignupAddress.Text);

                    sql_cmnd.Parameters.AddWithValue("@EmpPassword ", txtSignupPass.Text);

                    sql_cmnd.Parameters.Add("@Msg", SqlDbType.VarChar, 50).Direction = ParameterDirection.Output;

                    //sql_cmnd.SelectCommand.Parameters["@jamjarim_out"].Direction = ParameterDirection.Output;

                    //sql_cmnd.Parameters["@Msg"].Direction = ParameterDirection.Output;

 

                    sql_cmnd.ExecuteNonQuery();

                    String msg2 = sql_cmnd.Parameters["@Msg"].Value.ToString();

                    MessageBox.Show("" + msg2);

                    sqlCon.Close();

                }

            }

            catch (Exception ex)

            {

                MessageBox.Show(ex.Message);

            }

///////////////////////////////////////////////////////////////

Comments

Post a Comment

Popular posts from this blog

Create Database SQL Server backup Script

Image
  To generate a SQL Server backup script that includes data, you can use the SQL Server Management Studio (SSMS) Generate Scripts wizard. Here are the steps to do so: Open SSMS and connect to the SQL Server instance you want to backup. Right-click on the database you want to backup and select "Tasks" -> "Generate Scripts". In the "Introduction" page of the wizard, click "Next". In the "Choose Objects" page, select "Select specific database objects" and select the tables you want to include in the backup. Click "Next". In the "Set Scripting Options" page, set the following options: "Script Data" to "True" "Types of data to script" to "Data only" "Script Drop and Create" to "False" "Script for Server Version" to the appropriate version of SQL Server you want to run the script on Click "Next" to review the summary of your settin...
Read more