Data Insert By XML

I have created two types of Xml based data insert Update etc..here.Basically for Bulck insert we use such process to enter or update data inside database and can avoide the hectick parameter passing process by passing the whole xml as a single string.We collect data and generate the dynamic xml and pass it to the stolred procedure.

Here is my class architecture.

For the Faculty there exists one to many mapping between Faculty - College (Consider guest lecturer) and Faculty-Department.So List collections are taken for College and Department to save Faculty data.
have a look at my Faculty class methods

#endregion
#region Internal Methods
internal string GetXML()
{
XmlDocument xDoc = new XmlDocument();
XmlElement xStudentDetail;
xStudentDetail = (XmlElement)xDoc.CreateElement("FacultyDetails");
//this.IsActive = true;
xStudentDetail.SetAttribute("FacultyDetailsID", this.IdNo.ToString());
xStudentDetail.SetAttribute("PersonalAddress", this.PersonalAddress.ToString());
xStudentDetail.SetAttribute("Name", this.Name.ToString());
xStudentDetail.SetAttribute("OtherCharges", this.OtherCharges.ToString());
if (this.CollegeDetails != null)
{
XmlElement xStudentDetails;
xStudentDetails = (XmlElement)xDoc.CreateElement("CollegeDetails");
foreach (College objCollegeDetails in this.CollegeDetails)
{
xStudentDetails.InnerXml = xStudentDetails.InnerXml + objCollegeDetails.GetXML();
}
xRentAgreement.AppendChild(xStudentDetails);
}
xDoc.AppendChild(xStudentDetail);
return xDoc.InnerXml;
}
///

/// This internal method is used for saving the Student details into the database
///

///
internal int SaveFacultyInfo()
{
DataAccess dataAccess = null;
DataSet dsFaculty;
int iFunctionId = 0;
try
{
DataAccess = new DataAccess(_connectionString);
SqlParameter[] sqlParams = new SqlParameter[1];
sqlParams[0] = new SqlParameter("@sFacultyXML", this.GetXML());
dsFaculty = dataAccess.ExecuteDataSet("spSaveFacultyDetails", CommandType.StoredProcedure, sqlParams);
foreach (DataRow dr in dsFaculty.Tables[0].Rows)
{
iFunctionId = Convert.ToInt32(dr[0]);
}
}
finally
{
if (dataAccess != null)
{
if (dataAccess.DataConn != null)
{
//Close the dataset connection
dataAccess.DataConn.Close();
}
}
}
return iFunctionId;
}

///

/// This internal method is used for getting the student details from the database
///

///
internal void GetFacultyInfo()
{
DataAccess dataAccess = null;
DataSet dsFaculty;
DataRow[] drs;
try
{
DataAccess = new DataAccess(_connectionString);
SqlParameter[] sqlParams = new SqlParameter[1];
sqlParams[0] = new SqlParameter("@sFId", studentId);
dsFaculty = dataAccess.ExecuteDataSet("spGetFacultyDetails", CommandType.StoredProcedure, sqlParams);
}
finally
{
if (dataAccess != null)
{
if (dataAccess.DataConn != null)
{
//Close the dataset connection
dataAccess.DataConn.Close();
}
}
}
}
#endregion
}
}



the GetXML() method will collect the information and populate it into a XML

#region Internal Methods
internal string GetXML()
{
XmlDocument xDoc = new XmlDocument();
XmlElement xStudentDetail;
xStudentDetail = (XmlElement)xDoc.CreateElement("FacultyDetails");
//this.IsActive = true;
xStudentDetail.SetAttribute("FacultyDetailsID", this.IdNo.ToString());
xStudentDetail.SetAttribute("PersonalAddress", this.PersonalAddress.ToString());
xStudentDetail.SetAttribute("Name", this.Name.ToString());
xStudentDetail.SetAttribute("OtherCharges", this.OtherCharges.ToString());
if (this.CollegeDetails != null)
{
XmlElement xStudentDetails;
xStudentDetails = (XmlElement)xDoc.CreateElement("CollegeDetails");
foreach (College objCollegeDetails in this.CollegeDetails)
{
xStudentDetails.InnerXml = xStudentDetails.InnerXml + objCollegeDetails.GetXML();
}
xRentAgreement.AppendChild(xStudentDetails);
}
xDoc.AppendChild(xStudentDetail);
return xDoc.InnerXml;
}
For Collecting the College information there is also a GetXML method inside College Class.It will add the ollege info as a child node to the faculty XML.

#region Internal Methods
///

/// this method will get the object in xml format
///

///
/// Subhamay Sur
internal string GetXML()
{
XmlDocument xDoc = new XmlDocument();
XmlElement xCollege;
xCollege = (XmlElement)xDoc.CreateElement("CollegeInfo");
xCollege.SetAttribute("CollegeInfo", this.CollegeID.ToString());
xCollege.SetAttribute("UniversityRegNo", this.UniversityRegNo.ToString());
xCollege.SetAttribute("CollegeName", this.CollegeName.ToString());
xCollege.SetAttribute("CollegeAddress", this.CollegeAddress.ToString());
xCollege.SetAttribute("CollegeStatus", this.CollegeStatus.ToString());
xCollege.SetAttribute("IsAICTEApproved", this.IsAICTEApproved.ToString());
xCollege.SetAttribute("IsWBSEBApproved", this.IsWBSEBApproved.ToString());
if (this.DepartmentDetails != null)
{
XmlElement xDeparmentDetails;
xDeparmentDetails = (XmlElement)xDoc.CreateElement("DepartmentDetails");
foreach (Department objDepartmentDetails in this.FacultyDetails)
{
xDeparmentDetails.InnerXml = xDeparmentDetails.InnerXml + objDepartmentDetails.GetXML();
}
xCollege.AppendChild(xCollegeDetails);
}
xDoc.AppendChild(xCollege);
return xDoc.InnerXml;
}

#endregion
To save this string formatted string the Stored Procedure will be like

INSERT INTO #TempFaculty
(
[PersonalAddress],
[Department],
[Name],
[OtherCharges]
)
SELECT
T.F.value('@PersonalAddress','VARCHAR(200)'),
T.F.value('@Name','VARCHAR(200)'),
T.F.value('@OtherCharges','decimal(14, 2)')
FROM @sFacultyXML.nodes('/FacultyDetails') T(F)


CREATE TABLE #TempTableRentAgreementVendorMap
(
[RentAgreementID] INT,
[VendorNo] VARCHAR(200),
[AmountShareRent] DECIMAL(14, 2),
[AmountShareMaintenance] DECIMAL(14, 2),
[AmountShareOthers] DECIMAL(14, 2)
)
INSERT INTO #TempTableFacultyCollegeMap
(
[CollegeId],
[UniversityRegNo],
[CollegeName],
[CollegeAddress],
[IsAICTEApproved] ,
[IsWBSEBApproved]
)
SELECT
T.F.value('@CollegeId','INT'),
T.F.value('@UniversityRegNo','VARCHAR(200)'),
T.c.value('@CollegeName','decimal(14, 2)'),
T.F.value('@CollegeAddress','decimal(14, 2)'),
T.F.value('@IsAICTEApproved','decimal(14, 2)') ,
T.F.value('@IsWBSEBApproved','decimal(14, 2)')
FROM @sFacultyXML.nodes('/FacultyDetails/CollegeInfo') T(F)