How To Insert Multiple table , Multiple Row In database using sql user define type table


How To Insert Multiple table , Multiple Row In database using sql user define type table




CREATE TABLE [dbo].[LabReport](
[Id] [int] IDENTITY(1,1) NOT NULL,
[LRCId] [int] NULL,
[LRTId] [int] NULL,
[Title] [varchar](200) NULL,
[LabName] [varchar](200) NULL,
[LabAddress] [varchar](400) NULL,
[LabContactNo] [varchar](15) NULL,
[LabEmail] [varchar](200) NULL,
[LabUserName] [varchar](200) NULL,
[Details] [varchar](500) NULL,
[DetailsData] [nvarchar](max) NULL,
[PID] [int] NULL,
[ReportDate] [datetime] NULL,
[AddedOn] [datetime] NULL,
[ModifiedOn] [datetime] NULL,
[IsActive] [bit] NULL,
 CONSTRAINT [PK__LabRepor__3214EC07C2B6BB24] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

 ALTER TABLE [dbo].[LabReport]  WITH CHECK ADD  CONSTRAINT [FK__LabReport__LRCId__12C8C788] FOREIGN KEY([LRCId])
REFERENCES [dbo].[LabReportCategory] ([Id])
GO

 ALTER TABLE [dbo].[LabReport] CHECK CONSTRAINT [FK__LabReport__LRCId__12C8C788]
GO

 ALTER TABLE [dbo].[LabReport]  WITH CHECK ADD  CONSTRAINT [FK__LabReport__LRTId__13BCEBC1] FOREIGN KEY([LRTId])
REFERENCES [dbo].[LabReportType] ([Id])
GO

 ALTER TABLE [dbo].[LabReport] CHECK CONSTRAINT [FK__LabReport__LRTId__13BCEBC1]
GO

 ALTER TABLE [dbo].[LabReport]  WITH CHECK ADD  CONSTRAINT [FK__LabReport__PID__14B10FFA] FOREIGN KEY([PID])
REFERENCES [dbo].[PatientDetail] ([PID])
GO

 ALTER TABLE [dbo].[LabReport] CHECK CONSTRAINT [FK__LabReport__PID__14B10FFA]
GO


/****** Object:  Table [dbo].[LabReportCategory]    Script Date: 26-09-2018 23:07:50 ******/
SET ANSI_NULLS ON
GO

 SET QUOTED_IDENTIFIER ON
GO

 CREATE TABLE [dbo].[LabReportCategory](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Title] [varchar](200) NULL,
[Code] [varchar](100) NULL,
[AddedOn] [datetime] NULL,
[ModifiedOn] [datetime] NULL,
[IsActive] [bit] NULL,
PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO






CREATE TABLE [dbo].[LabReportImegenary](
[Id] [int] IDENTITY(1,1) NOT NULL,
[LRId] [int] NULL,
[FIleType] [varchar](100) NULL,
[FileExtention] [varchar](10) NULL,
[FileUrl] [text] NULL,
[AddedOn] [datetime] NULL,
[ModifiedOn] [datetime] NULL,
[IsActive] [bit] NULL,
PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

 ALTER TABLE [dbo].[LabReportImegenary]  WITH CHECK ADD  CONSTRAINT [FK__LabReportI__LRId__178D7CA5] FOREIGN KEY([LRId])
REFERENCES [dbo].[LabReport] ([Id])
GO

 ALTER TABLE [dbo].[LabReportImegenary] CHECK CONSTRAINT [FK__LabReportI__LRId__178D7CA5]
GO


/****** Object:  Table [dbo].[LabReportType]    Script Date: 26-09-2018 23:07:57 ******/
SET ANSI_NULLS ON
GO

 SET QUOTED_IDENTIFIER ON
GO

 CREATE TABLE [dbo].[LabReportType](
[Id] [int] IDENTITY(1,1) NOT NULL,
[CatID] [int] NULL,
[Title] [varchar](200) NULL,
[Code] [varchar](100) NULL,
[AddedOn] [datetime] NULL,
[ModifiedOn] [datetime] NULL,
[IsActive] [bit] NULL,
PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

 ALTER TABLE [dbo].[LabReportType]  WITH CHECK ADD FOREIGN KEY([CatID])
REFERENCES [dbo].[LabReportCategory] ([Id])
GO




SET ANSI_NULLS ON
GO

 SET QUOTED_IDENTIFIER ON
GO

 CREATE TABLE [dbo].[PatientDetail](
[PID] [int] IDENTITY(1,1) NOT NULL,
[Title] [varchar](10) NULL,
[UserName] [varchar](50) NULL,
[FirstName] [varchar](50) NULL,
[MiddleName] [varchar](50) NULL,
[LastName] [varchar](50) NULL,
[Gender] [char](2) NULL,
[DOB] [date] NULL,
[DOD] [date] NULL,
[DOD_SubReasonID] [int] NULL,
[RegistrationDate] [date] NULL,
[SSN] [int] NULL,
[Mode_of_Communication_ID] [int] NULL,
[EmailID] [varchar](50) NULL,
[Address1] [varchar](50) NULL,
[Address2] [varchar](50) NULL,
[CityID] [int] NULL,
[StateID] [int] NULL,
[Zip] [char](10) NULL,
[CountryID] [int] NULL,
[ProviderID] [int] NULL,
[MobilePhone] [varchar](15) NULL,
[WorkPhone] [varchar](15) NULL,
[HomePhone] [varchar](15) NULL,
[EthnicityID] [int] NULL,
[RaceID] [int] NULL,
[Notification] [bit] NULL,
[MaritalStatusID] [int] NULL,
[PatientPhoto] [varchar](100) NULL,
[Emerg_Contact_Name] [varchar](50) NULL,
[Emerg_Phone] [varchar](15) NULL,
[Emerg_RelationID] [int] NULL,
[Decline_Clinical_Summary] [bit] NULL,
[Medication_History_Consent] [bit] NULL,
[Notes] [varchar](300) NULL,
[IsActive] [bit] NULL,
[CreatedOn] [datetime] NULL,
[ModifiedOn] [datetime] NULL,
[UserID] [int] NULL,
[SmokeID] [int] NULL,
[BloodGroupID] [int] NULL,
 CONSTRAINT [PK_PatientDetail] PRIMARY KEY CLUSTERED
(
[PID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO





CREATE TYPE [dbo].[LabReportImegenarys] AS TABLE(
[LRId] [int] NULL,
[FIleType] [varchar](100) NULL,
[FileExtention] [varchar](10) NULL,
[FileUrl] [text] NULL,
[AddedOn] [datetime] NULL,
[ModifiedOn] [datetime] NULL,
[IsActive] [bit] NULL
)
GO
CREATE TYPE [dbo].[LabReports] AS TABLE(
[LRCId] [int] NULL,
[LRTId] [int] NULL,
[Title] [varchar](200) NULL,
[LabName] [varchar](200) NULL,
[LabAddress] [varchar](400) NULL,
[LabContactNo] [varchar](15) NULL,
[LabEmail] [varchar](200) NULL,
[LabUserName] [varchar](200) NULL,
[Details] [varchar](500) NULL,
[DetailsData] [nvarchar](max) NULL,
[PID] [int] NULL,
[ReportDate] [datetime] NULL,
[AddedOn] [datetime] NULL,
[ModifiedOn] [datetime] NULL,
[IsActive] [bit] NULL
)
GO




 Create PROCEDURE [dbo].[sp_InsertLabReports]
(
@LabReport LabReports readonly,
@LabReportImegenarys  LabReportImegenarys readonly
)
AS
BEGIN
BEGIN TRANSACTION;
    SAVE TRANSACTION MySavePoint;
    BEGIN TRY

       SET NOCOUNT ON;
declare @ReportID int;
 
      INSERT INTO [dbo].[LabReport]
           ([LRCId]
           ,[LRTId]
           ,[Title]
           ,[LabName]
           ,[LabAddress]
           ,[LabContactNo]
           ,[LabEmail]
           ,[LabUserName]
           ,[Details]
           ,[DetailsData]
           ,[PID]
           ,[ReportDate]
           ,[AddedOn]
           ,[ModifiedOn]
           ,[IsActive])
      SELECT LRCId
           ,LRTId
           ,Title
           ,LabName
           ,LabAddress
           ,LabContactNo
           ,LabEmail
           ,LabUserName
           ,Details
           ,DetailsData
           ,PID
           ,ReportDate
           ,AddedOn
           ,ModifiedOn
           ,IsActive  FROM @LabReport

 set @ReportID=IDENT_CURRENT('LabReport')

 INSERT INTO [dbo].[LabReportImegenary]
           ([LRId]
           ,[FIleType]
           ,[FileExtention]
           ,[FileUrl]
           ,[AddedOn]
           ,[ModifiedOn]
           ,[IsActive]) SELECT @ReportID,FIleType,FileExtention,FileUrl,AddedOn,ModifiedOn,IsActive from @LabReportImegenarys


  END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
        BEGIN
            ROLLBACK TRANSACTION MySavePoint; -- rollback to MySavePoint
        END
    END CATCH
    COMMIT TRANSACTION
END;



         [ResponseType(typeof(Response<PatientLabReportViewModel>))]
        public IHttpActionResult PostPatientLabReport(PatientLabReportViewModel patientLabReportViewModel)
        {

             var file = HttpContext.Current.Request.Files.Count > 0 ? HttpContext.Current.Request.Files : null;
            string PatientPicture = Utils.ConfigKey("PatientLabReportImages");
            List<LabReportImagneriesModal> labReportImagneriesModal = new List<LabReportImagneriesModal>();
            string PictureName = "";
            var path = "";
            string oldpath = null;
            int count = -1;
            if (!ModelState.IsValid)
            {
                if (HttpContext.Current.Request.Form != null)
                {
                    patientLabReportViewModel = new PatientLabReportViewModel();
                    patientLabReportViewModel.labReport.Id = HttpContext.Current.Request.Form["Id"].ToSafeInt();
                    patientLabReportViewModel.labReport.PID = HttpContext.Current.Request.Form["PID"].ToSafeInt();
                    patientLabReportViewModel.labReport.Details = HttpContext.Current.Request.Form["Details"].ToSafeString();
                    patientLabReportViewModel.labReport.DetailsData = HttpContext.Current.Request.Form["DetailsData"].ToSafeString();
                    patientLabReportViewModel.labReport.LabAddress = HttpContext.Current.Request.Form["LabAddress"].ToSafeString();
                    patientLabReportViewModel.labReport.LabContactNo = HttpContext.Current.Request.Form["LabContactNo"].ToSafeString();
                    patientLabReportViewModel.labReport.LabEmail = HttpContext.Current.Request.Form["LabEmail"].ToSafeString();
                    patientLabReportViewModel.labReport.LabName = HttpContext.Current.Request.Form["LabName"].ToSafeString();
                    patientLabReportViewModel.labReport.LRCId = HttpContext.Current.Request.Form["LRCId"].ToSafeInt();
                    patientLabReportViewModel.labReport.AddedOn = DateTime.Now;
                    patientLabReportViewModel.labReport.ModifiedOn = DateTime.Now;
                    patientLabReportViewModel.labReport.IsActive = true;
                    patientLabReportViewModel.labReport.LRTId = HttpContext.Current.Request.Form["LRTId"].ToSafeInt();
                    patientLabReportViewModel.labReport.ReportDate = Convert.ToDateTime(HttpContext.Current.Request.Form["ReportDate"]);
                    patientLabReportViewModel.labReport.Title = HttpContext.Current.Request.Form["Title"].ToSafeString();

                 }
                else
                {
                    return Ok(new Response<PatientLabReportViewModel>(404, "Patient Report Not Valid", false, new PatientLabReportViewModel()));
                }
            }
            else
            {
                if (patientLabReportViewModel != null)
                {

                     patientLabReportViewModel.labReport.AddedOn = DateTime.Now;
                    patientLabReportViewModel.labReport.ModifiedOn = DateTime.Now;
                    patientLabReportViewModel.labReport.IsActive = true;

                 }
                else
                {
                    return Ok(new Response<PatientLabReportViewModel>(404, "Patient Emergancy Contact Not Valid", false, new PatientLabReportViewModel()));
                }
            }

            if (patientLabReportViewModel.labReport.Id == 0)
            {
                foreach (HttpPostedFile file_item in file)
                {
                    if (file_item != null && file_item.ContentLength > 0)
                    {
                        var uniqid = Guid.NewGuid();
                        FileInfo f1 = new FileInfo(file_item.FileName);
                        string ext = !(string.IsNullOrEmpty(f1.Extension)) ? f1.Extension : ".png";
                        PictureName = uniqid.ToSafeString() + "_" + JavaScriptDateConverter.Convert(DateTime.Now) + "_" + patientLabReportViewModel.labReport.PID + ext;
                        path = System.IO.Path.Combine(
                            HttpContext.Current.Server.MapPath(PatientPicture),
                            PictureName
                        );
                        labReportImagneriesModal.Add(new LabReportImagneriesModal { Id = 0, LRId = 0,file=file_item,FileExtention= f1.Extension, PictureName=PictureName,FIleType= MimeMapping.GetMimeMapping(file_item.FileName), FileUrl= path, AddedOn = DateTime.Now, ModifiedOn = DateTime.Now, IsActive = true });
                     
                    }
                }

             }
            DataTable dt = new DataTable();
            DataTable dt1 = new DataTable();
            dt.Columns.AddRange(new DataColumn[17]
            {
                    new DataColumn("Id", typeof(int)),
                    new DataColumn("PID", typeof(int)),
                    new DataColumn("Details",typeof(string)),
                     new DataColumn("LRCId", typeof(int)),
                    new DataColumn("LRTId", typeof(int)),
                    new DataColumn("Title",typeof(string)),
                     new DataColumn("LabName", typeof(string)),
                    new DataColumn("LabAddress", typeof(string)),
                    new DataColumn("LabContactNo",typeof(string)),
                      new DataColumn("LabEmail", typeof(string)),
                    new DataColumn("LabUserName", typeof(string)),
                    new DataColumn("Details",typeof(string)),
                     new DataColumn("DetailsData", typeof(string)),
                    new DataColumn("ReportDate",typeof(DateTime)),
                     new DataColumn("AddedOn", typeof(DateTime)),
                      new DataColumn("ModifiedOn",typeof(bool)),
                     new DataColumn("IsActive", typeof(bool)),
           });
   
            dt1.Columns.AddRange(new DataColumn[7]
            {
                    new DataColumn("LRId", typeof(int)),
                    new DataColumn("FIleType", typeof(int)),
                    new DataColumn("FileExtention",typeof(string)),
                     new DataColumn("FileUrl", typeof(int)),
                    new DataColumn("AddedOn", typeof(int)),
                    new DataColumn("ModifiedOn",typeof(string)),
                     new DataColumn("IsActive", typeof(string))
               
            });

             dt.Rows.Add(patientLabReportViewModel.labReport.Id,patientLabReportViewModel.labReport.PID,patientLabReportViewModel.labReport.Details,patientLabReportViewModel.labReport.LRCId,patientLabReportViewModel.labReport.LRTId,patientLabReportViewModel.labReport.Title,patientLabReportViewModel.labReport.LabName,patientLabReportViewModel.labReport.LabAddress,patientLabReportViewModel.labReport.LabContactNo,patientLabReportViewModel.labReport.LabEmail,patientLabReportViewModel.labReport.LabUserName,patientLabReportViewModel.labReport.Details,patientLabReportViewModel.labReport.DetailsData,patientLabReportViewModel.labReport.ReportDate,patientLabReportViewModel.labReport.AddedOn,patientLabReportViewModel.labReport.ModifiedOn,patientLabReportViewModel.labReport.IsActive);

             foreach (var i in labReportImagneriesModal)
            {
                dt1.Rows.Add(i.LRId, i.FIleType, i.FileExtention, i.FileUrl, i.AddedOn, i.ModifiedOn, i.IsActive);
            }

             if (dt.Rows.Count > 0)
            {
                string consString = ConfigurationManager.ConnectionStrings["sqlConString"].ConnectionString;
                using (SqlConnection con = new SqlConnection(consString))
                {
                    using (SqlCommand cmd = new SqlCommand("sp_InsertLabReports"))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Connection = con;
                        cmd.Parameters.AddWithValue("@LabReport", dt);
                        cmd.Parameters.AddWithValue("@LabReportImegenarys", dt1);
                        con.Open();
                        count=cmd.ExecuteNonQuery();
                        con.Close();
                    }
                }
            }

             if(count>0)
            {
                foreach (var f in labReportImagneriesModal)
                {
                    if (!string.IsNullOrEmpty(f.FileUrl))
                    {
                        //file.SaveAs(path);
                        Utils.SaveFile(f.file, HttpContext.Current.Server.MapPath(PatientPicture), f.PictureName);
                    }
                }
                return Ok(new Response<PatientLabReportViewModel>(200, "Patient Emergancy Contact Sucessfull Register", true, patientLabReportViewModel));
            }
            else
            return Ok(new Response<PatientLabReportViewModel>(404, "Patient Report Not Valid", false, new PatientLabReportViewModel()));


        }


  public class PatientLabReportViewModel
    {
     public LabReport labReport { set; get; }
     public List< LabReportImegenary> labReportImegenaries{ set; get; }
    }

 public class LabReportImagneriesModal
    {
        public HttpPostedFile file { set; get; }
        public string PictureName{ set; get; }
        public int Id { get; set; }
        public Nullable<int> LRId { get; set; }
        public string FIleType { get; set; }
        public string FileExtention { get; set; }
        public string FileUrl { get; set; }
        public Nullable<System.DateTime> AddedOn { get; set; }
        public Nullable<System.DateTime> ModifiedOn { get; set; }
        public Nullable<bool> IsActive { get; set; }
    }

at
Labels: , ,

No comments:

Post a Comment

Thank You For Your Great Contribution

Subscribe to: Post Comments (Atom)

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