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)

Bind DropDownList Using Cache Value

Here I have created an AppCacheManager Class where i define some methods for Insert to Cache,Remove From Cache,Refresh Cache etc..
We need to use a synchronization primitive lock which is provided by the .NET Framework. Basically this mechanism allows a thread to acquire a lock on an instance of an object. Which means, only one thread can acquire a lock on the object and other threads have to wait until the original thread releases the lock .But if we lock the cache object directly it will be a huge performance issue.Acctullay we have to make sure that a)multiple threads can read cache data simultaneously, b)only one thread is allowed to update the cache at a time c) When a thread is updating the cache, all the threads that want to read the cache data should wait until the update is complete
The .NET framework provides another thread synchronization primitive called ReaderWriterLock
My Cache Manager Class is like that
using System;
using System.Collections.Generic;
using System.Collections;
using System.Text;
using System.Data;
using System.Web.Caching;
using System.Threading;
using System.Reflection;
using AppConfigurationManagement
namespace CacheManagement
{
// contains the methods for cache management for Add ,Remove,Refresh
public static class AppCacheManager
{
private static System.Web.Caching.Cache _CacheManager = System.Web.HttpContext.Current.Cache;
private static ReaderWriterLock _ReadWriteLock;
static AppCacheManager ()
{
_ReadWriteLock = new ReaderWriterLock();
if (System.Web.HttpContext.Current != null)
{
_CacheManager = System.Web.HttpContext.Current.Cache;
}
else
{
_CacheManager = System.Web.HttpRuntime.Cache;
}
}
// Method to add object in cache
public static void AddToCache(EnumeratorsConfiguration.CacheKey key, object keyData)
{
try
{
// Aquire Writer Lock
_ReadWriteLock.AcquireWriterLock(60000);
_CacheManager.Insert(key.ToString(), keyData);
}
finally
{
if (_ReadWriteLock.IsWriterLockHeld)
{
_ReadWriteLock.ReleaseWriterLock();
}
}
}
/// Method to remove object from cache,
/// it removes object for all keys which starts with value of key
public static void RemoveFromCache(EnumeratorsConfiguration.CacheKey key)
{
try
{
_ReadWriteLock.AcquireWriterLock(60000);
System.Collections.IDictionaryEnumerator dicEnum;
dicEnum = _CacheManager.GetEnumerator();
while(dicEnum.MoveNext() == true)
{
if (((string)dicEnum.Key).StartsWith(key.ToString()))
{
_CacheManager.Remove((string)dicEnum.Key);
}
}
}
finally
{
//Release the write lock
if (_ReadWriteLock.IsWriterLockHeld)
{
_ReadWriteLock.ReleaseWriterLock();
}
}
}
/// Method to remove object from cache,
/// it removes object for all keys which starts with value of key
public static void RemoveFromCache()
{
try
{
_ReadWriteLock.AcquireWriterLock(60000);
System.Collections.IDictionaryEnumerator dicEnum;
dicEnum = _CacheManager.GetEnumerator();
while (dicEnum.MoveNext() == true)
{
if (IsCacheable(dicEnum.Key.ToString()))
_CacheManager.Remove((string)dicEnum.Key);
}
}
finally
{
//Release the write lock
if (_ReadWriteLock.IsWriterLockHeld)
{
_ReadWriteLock.ReleaseWriterLock();
}
}
}
public static DataTable GetCacheList()
{
try
{
_ReadWriteLock.AcquireWriterLock(60000);
System.Collections.IDictionaryEnumerator dicEnum;
dicEnum = _CacheManager.GetEnumerator();
DataTable dtCache = new DataTable();
dtCache.Columns.Add("Key");
while (dicEnum.MoveNext() == true)
{
if (IsCacheable(dicEnum.Key.ToString()))
{
DataRow drCache = dtCache.NewRow();
drCache["Key"] = dicEnum.Key.ToString();
dtCache.Rows.Add(drCache);
}
}
return dtCache;
}
finally
{
//Release the write lock
if (_ReadWriteLock.IsWriterLockHeld)
{
_ReadWriteLock.ReleaseWriterLock();
}
}
}
// It retrieves a object from cache for a key
public static object RetrieveFromCache(EnumeratorsConfiguration.CacheKey key)
{
try
{
_ReadWriteLock.AcquireReaderLock(60000);
return _CacheManager.Get(key.ToString());
}
finally
{
if (_ReadWriteLock.IsReaderLockHeld)
{
_ReadWriteLock.ReleaseReaderLock();
}
}
}
/// Rerurns whether the specified key Exists in cache.
public static bool ExistsInCache(EnumeratorsConfiguration.CacheKey key)
{
try
{
if (_CacheManager.Get(key.ToString()) != null)
{
return true;
}
else
{
return false;
}
}
catch (Exception ex)
{
return false;
}
}
}
}
Now let us say I want to bind a dropdownlist by the values stored in the cache.Here is way how I can do thatHere we are taking an Enum for the Cache Key Settings. If there exists various Chache Key ,we can use an Enum for that

using System;
using System.Collections.Generic;
using System.Text;
namespace AppConfigurationManagement
{
public static class EnumeratorsConfiguration
{
#region Enum
public enum CacheKey
{
Location = 1,
Region =2,
Branch = 3
}
#endregion
}
}

Now let us call the Cache Methods.If the data for the specified cache key remains in the cache then we will call it ,otherwise we will add the value to the cache.

using System;
using System.Collections.Generic;
using System.Text;
using AppConfigurationManagement;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using BusinessEntity;
namespace BusinessLogic
{
public static class AppMetadataManager
{
#region Public Methods
/// Contains the methods for Metadata Management
public static object GetMetadata(EnumeratorsConfiguration.CacheKey key)
{
object metadata = null;
try
{
if (AppCacheManager.ExistsInCache(key) && ConfigurationManager.AppSettings["EnableCache"].ToString() == "1")
{
/*if the data is in cache returns that from the cache*/
metadata = AppCacheManager.RetrieveFromCache(key);
if (metadata == null)
metadata = GetMetadataFromDB(key);
}
else
{
metadata = GetMetadataFromDB(key);
}
}
catch (Exception e)
{
throw new Exception() ;
}
return metadata;
}
/// Flushes the Metadata
public static void FlushMetadata(EnumeratorsConfiguration.CacheKey key)
{
if (AppCacheManager.ExistsInCache(key))
{
AppCacheManager.RemoveFromCache(key);
}
}
private static object GetMetadataFromDB(EnumeratorsConfiguration.CacheKey key)
{
object metadata = null;
/*data is not in cache, so get the data from the database and put that in cache*/
switch (key)
{
case EnumeratorsConfiguration.CacheKey.Location:
DataSet dsLocation = BackEndManager.GetLocationList();
AppCacheManager.AddToCache(key, dsLocation);
metadata = dsLocation;
break;
case EnumeratorsConfiguration.CacheKey.Region :
List lstRegion = BackEndManager.GetRegionList();
AppCacheManager.AddToCache(key, lstRegion);
metadata = lstRegion;
break;
}
return metadata;
}
#endregion
}
}

Now in our Page_Load Event we can do the following
protected void Page_Load(object sender, EventArgs e)
{
ddlRegion.Items.Clear();
ddlRegion.Items.Add(new ListItem("---Select---", "0"));
List lstRegion = (List)MetadataManager.GetMetadata(ConfigurationEnumerators.CacheKey.Region);
foreach (Region objRegion in lstRegion)
{
ListItem li = new ListItem();
li.Text = objRegion.Name.ToString();
li.Value = objRegion.RegionID.ToString();
ddlRegion.Items.Add(li);
}
ddlRegion.DataBind();
}
I will post Upadte and Remove Cache very soon
However, the problem with the MS Caching application block is that it carries a lot of overhead and has a lot of features that may not be useful for your application