Monday, January 16, 2012

Merge two DataTables by eliminating duplicate rows

// dtResumeData is the table used to store previous session data
// dtDomainPersonTypeFinal is the table used to store the current session data

for (int x = 0; x < dtResumeData.Rows.Count; x++)
{
for (int y = 0; y < dtDomainPersonTypeFinal.Rows.Count; y++)
{
//if (dtResumeData.Rows[x]["RowNumber"].ToString().Equals(dtDomainPersonTypeFinal.Rows[y]["RowNumber"].ToString()) && dtResumeData.Rows[x]["InformationDomainPersonTypeFieldId"].ToString().Equals(dtDomainPersonTypeFinal.Rows[y]["InformationDomainPersonTypeFieldId"].ToString()))
//{
if (dtResumeData.Rows[x]["PersonTypeID"].ToString().Equals(dtDomainPersonTypeFinal.Rows[y]["PersonTypeID"].ToString()) && dtResumeData.Rows[x]["InformationDomainPersonTypeFieldId"].ToString().Equals(dtDomainPersonTypeFinal.Rows[y]["InformationDomainPersonTypeFieldId"].ToString()))
{
dtDomainPersonTypeFinal.Rows[y].Delete();
y = y - 1;
}
}
}
//dtDomainPersonTypeFinal.Merge(dtResumeData);
dtResumeData.Merge(dtDomainPersonTypeFinal);


// dtResumeData contains the merge data of two tables

Multiple Records Insertion using XML

// As we are preparing XML Document pragmatically based on the available Data Table.

public string WriteXmlIdentityInformation(DataTable dataTable)
{
string returnXML = string.Empty;
XmlDocument doc = new XmlDocument();
//XmlDeclaration dec = doc.CreateXmlDeclaration("1.0", null, null);
//doc.AppendChild(dec);
// Create the root element
XmlElement root = doc.CreateElement("DocumentElement");
doc.AppendChild(root);
foreach (DataRow dr in dataTable.Rows)
{
XmlElement myElement = doc.CreateElement("myDt");
myElement.SetAttribute("Field1", dr[0].ToString());
myElement.SetAttribute("Field2", dr[1].ToString());
myElement.SetAttribute("Field3", dr[2].ToString());
myElement.SetAttribute("Field4", dr[3].ToString());
myElement.SetAttribute("Field5", dr[4].ToString());
root.AppendChild(myElement);
returnXML= doc.OuterXml;
}
return returnXML;
}

//Sending XML document through DB Method..

if (xmlIdentityInformation != string.Empty) cmdUpdate.Parameters.AddWithValue("@PersonInformationDomain", xmlIdentityInformation);
else
cmdUpdate.Parameters.AddWithValue("@PersonInformationDomain", DBNull.Value);


//Inserting records in the DB Table.

DECLARE @intPersonInfDomainDocHandle int

//Create an internal representation of the document.

EXEC sp_xml_preparedocument @intPersonInfDomainDocHandle OUTPUT, @PersonInformationDomain

INSERT INTO Directory.Table( field1, field2,field3,field4,field5)
SELECT field1, field2,field3,field4,field5
FROM OPENXML(@intPersonInfDomainDocHandle,'/DocumentElement/myDt')
WITH
(
field1 int, field2 INT,field3 INT,field4 INT, field5 int
)
Where some condition

-- Remove the internal representation.
EXEC sp_xml_removedocument @intPersonInfDomainDocHandle

Refeneces..

NO 1
NO 2
NO 3
NO 4