(1)Convert List type to xml
======================================================================
List<DiagnosisDetailsBO> objDiagnosisDetailsBO=new List<DiagnosisDetailsBO>();
string xmldataDiagnosis = CreateXML(objDiagnosisDetailsBO);
public string CreateXML(Object objMyBO)
{
XmlDocument xmlDoc = new XmlDocument(); //Represents an XML document,
// Initializes a new instance of the XmlDocument class.
XmlSerializer xmlSerializer = new XmlSerializer(objMyBO.GetType());
// Creates a stream whose backing store is memory.
using (MemoryStream xmlStream = new MemoryStream())
{
xmlSerializer.Serialize(xmlStream, objMyBO);
xmlStream.Position = 0;
//Loads the XML document from the specified string.
xmlDoc.Load(xmlStream);
return xmlDoc.InnerXml;
}
}
(2)Ado.Net code for call procedure
=====================================================================
string xmldataDiagnosis = CreateXML(objDiagnosisDetailsBO);
SqlParameter[] objParam = new SqlParameter[2];
objParam[0] = new SqlParameter("@XmlData", SqlDbType.Xml);
objParam[0].Value = xmldataDiagnosis;
objParam[1] = new SqlParameter("@Id", SqlDbType.Int);
objParam[1].Value = 1;
//result = "";
result = Convert.ToString(SqlHelper.ExecuteNonQuery(SqlHelper.Connect(), CommandType.StoredProcedure, "Proc_InsertOrUpdateDiagnosisDetails", objParam));
(3) procedure to insert for new data and update for already exist data
=====================================================================
-- =============================================
-- Author: <Author,Revathi>
-- Create date: <Create Date,05/08/2015>
-- Description: <Description,Insert or update diagnosis details>
--[dbo].[Proc_InsertOrUpdateDiagnosisDetails] '<?xml version="1.0"?><ArrayOfDiagnosisDetailsBO xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"><DiagnosisDetailsBO><DiagnosisInfoId xsi:nil="true" /><PatientId>114</PatientId><DiagnosisnDate>2012-11-11T00:00:00</DiagnosisnDate><Diagnosis>MGUS~Myeloma~Light Chain Deposition Disease~Heavy Chain Deposition Disease</Diagnosis><Myeloma>lg A,lg M,lg E,Lambda</Myeloma><CreatedIp>::1</CreatedIp><CreatedBy>1</CreatedBy></DiagnosisDetailsBO></ArrayOfDiagnosisDetailsBO>',1
-- =============================================
ALTER PROCEDURE [dbo].[Proc_InsertOrUpdateDiagnosisDetails]
@XmlData XML,
@Id int
AS
BEGIN
SET NOCOUNT ON;
if(@Id=1)
BEGIN
declare @handle1 int
exec sp_xml_preparedocument @handle1 out, @XmlData
SELECT * into #tblDiagnosisNew from openxml(@handle1, '/ArrayOfDiagnosisDetailsBO/DiagnosisDetailsBO', 2) with (DiagnosisInfoId int,PatientId int,ConvertedDiagnosisDate DATETIME,Diagnosis VARCHAR(MAX),Myeloma VARCHAR(100),CreatedBy int,CreatedIP Varchar(50)) WHERE DiagnosisInfoId=0
INSERT INTO Trans_PatientXDiagnosisDetails(PatientId,DiagnosisDate,Diagnosis,Myeloma,CreatedBy,CreatedIP)(select PatientId,ConvertedDiagnosisDate,Diagnosis,Myeloma,CreatedBy,CreatedIP from #tblDiagnosisNew)
SELECT * into #tblDiagnosisUpdate from openxml(@handle1, '/ArrayOfDiagnosisDetailsBO/DiagnosisDetailsBO', 2) with (DiagnosisInfoId int,PatientId int,ConvertedDiagnosisDate varchar(50),Diagnosis VARCHAR(MAX),Myeloma VARCHAR(100),CreatedBy int,CreatedIP Varchar(50)) WHERE DiagnosisInfoId<>0
exec sp_xml_removedocument @handle1
WHILE EXISTS(SELECT * FROM #tblDiagnosisUpdate)
BEGIN
DECLARE @DiagnosisInfoId int
DECLARE @PatientId int
DECLARE @DiagnosisnDate varchar(50)
DECLARE @Diagnosis VARCHAR(MAX)
DECLARE @Myeloma VARCHAR(100)
DECLARE @CreatedBy int
DECLARE @CreatedIP VARCHAR(50)
SELECT Top 1 @DiagnosisInfoId=DiagnosisInfoId,@PatientId=PatientId,@DiagnosisnDate=ConvertedDiagnosisDate,@Diagnosis=Diagnosis,@Myeloma=Myeloma,@CreatedBy=CreatedBy,@CreatedIP=CreatedIP FROM #tblDiagnosisUpdate
Update [dbo].[Trans_PatientXDiagnosisDetails]
SET [DiagnosisDate]=@DiagnosisnDate
,[Diagnosis]=@Diagnosis
,[Myeloma]=@Myeloma
,[ModifiedOn]=getdate()
,[ModifiedBy]=@CreatedBy
,[ModifiedIP]=@CreatedIP
WHERE PatientId=@PatientId AND DiagnosisInfoId = @DiagnosisInfoId
Delete #tblDiagnosisUpdate Where DiagnosisInfoId =@DiagnosisInfoId
END
END
END
