Speeding up a DataGrid with XML

[Matt]

I have a datagrid that gets a SQL query returned containing one column that
is XML. While I build the datagrid I have to split and parse the XML to get
the information that I want for each row. The query will return 350 rows in
about 1 second, but when the page builds the dataGrid it takes one minute.
Can anyone point me in a direction that could help to speed this up? This is
in ASP.NET and C# for a web application. The code is below.

Thanks in advance,
Matt
private void BindGrid(System.DateTime beginDateTime, System.DateTime
endDateTime, int partnerID)
{
try
{
CareVu.WebServices.PartnerServices partner = new
CareVu.WebServices.PartnerServices();
// ********************
DataSet dsPRejects = new DataSet();
dsPRejects.Clear();

dsPRejects = partner.GetPartnerRejectedClaims(beginDateTime,
endDateTime, partnerID, CheckBox1.Checked);

/// Build the dataTable for the DataGrid
DataTable rejectsTable = new DataTable();
rejectsTable.Clear();

DataColumn c0 = new DataColumn("TransactionID");
DataColumn c1 = new DataColumn("Patient Name");
DataColumn c2 = new DataColumn("Destination");
DataColumn c3 = new DataColumn("Trans Type");
DataColumn c4 = new DataColumn("Batch");
DataColumn c5 = new DataColumn("Date");

string xmlString = "//NewDataSet/Table";

DataColumn c6 = new DataColumn("Reason for Rejection");
DataColumn c7 = new DataColumn("Remove");
DataColumn c8 = new DataColumn("SourcePartnerID");

rejectsTable.Columns.Add(c0);
rejectsTable.Columns.Add(c1);
rejectsTable.Columns.Add(c2);
rejectsTable.Columns.Add(c3);
rejectsTable.Columns.Add(c4);
rejectsTable.Columns.Add(c5);
rejectsTable.Columns.Add(c6);
rejectsTable.Columns.Add(c7);
rejectsTable.Columns.Add(c8);

int num=0;
DataRow errorRow;
foreach(DataRow r in dsPRejects.Tables[0].Rows)
{
errorRow = rejectsTable.NewRow();

TranID = r["TransactionID"].ToString();
errorRow["TransactionID"] = r["TransactionID"].ToString();
errorRow["Trans Type"] = r["Trans Type"].ToString();
errorRow["Batch"] = r["Batch"].ToString();
errorRow["Date"] = r["Date"].ToString();
errorRow["Remove"] = @"<A href=partnerclaimrejects.aspx?removeid=" +
TranID + @">" + "Remove" + @"</A>";

num++;
// *******************************
Rejection(xmlString, dsPRejects, num, errorRow, TranID);

errorRow["Patient Name"] = r["Patient Name"].ToString();
errorRow["Destination"] = r["Destination"].ToString();
errorRow["SourcePartnerID"] = r["SourcePartnerID"].ToString();
rejectsTable.Rows.Add(errorRow);
}

rejectsTable.AcceptChanges();
DataView dv = new DataView(rejectsTable);

partnersGrd.AutoGenerateColumns = false;
// ********************
lblReportType.Text = "Report of Rejected Claims";
lblReportType.Visible = true;
Session["ProClaims"] = dv;

partnersGrd.DataSource = dv;
partnersGrd.DataBind();
dsPRejects.Dispose();
}
catch(Exception exc)

Microsoft.ApplicationBlocks.ExceptionManagement.Ex ceptionManager.Publish(exc);
Response.Redirect(Request.ApplicationPath + @"/error.aspx");
}
}

private void Rejection(string xmlString, DataSet ds, int num, DataRow
errorRow, string tranId)
{
try
{
string PayorError = "";
Ack = "";
errRow = "";
Session["ProResponse"] = "";
XmlDocument xmlDoc = new XmlDocument();
xmlDoc.LoadXml(ds.GetXml());

XmlNodeList elemList;
elemList =
xmlDoc.GetElementsByTagName("Reason_x0020_for_x002 0_Rejection");

int x = num-1;

// Get the Local error message
System.IO.StringReader sr = new
System.IO.StringReader(elemList[x].InnerText.ToString());
XmlTextReader xTxt = new XmlTextReader(sr);
XmlReader xRdr = xTxt;
do
{
xRdr.Read();
}
while(!xRdr.EOF && xRdr.Name != "ErrorRecord");
if(!xRdr.EOF)
{
xRdr.MoveToAttribute("error_description");
try
{
Ack = xRdr.Value.ToString();
}
catch
{
}
}
else
{
// Pull the Response Error Message
DataSet dsResponse = new DataSet();
CareVu.WebServices.TransactionServices txn = new
CareVu.WebServices.TransactionServices();
if(Session["ProResponse"].ToString() == "")
{
dsResponse = txn.GetRejectedPayorResponses(Int32.Parse(tranId)) ;
}
else
{
dsResponse = (DataSet) Session["ProResponse"];
}

int i = 1;
foreach(DataRow p in dsResponse.Tables[0].Rows)
{
System.IO.StringReader srOrig2 = new
System.IO.StringReader(dsResponse.GetXml());
XmlTextReader xOrig2 = new XmlTextReader(srOrig2);
XmlReader xrOrig2 = xOrig2;
string acknowledgment2 = "";
do
{
xrOrig2.Read();
}
while(!xrOrig2.EOF && xrOrig2.Name !=
"Reason_x0020_for_x0020_Rejection");
if(!xrOrig2.EOF)
{
acknowledgment2 = xrOrig2.ReadString();
}

if(p["Reason for Rejection"].ToString() != "")
{
Ack = p["Reason for Rejection"].ToString();
errorRow["Reason for Rejection"] = "";

System.IO.StringReader sr2 = new System.IO.StringReader(Ack);
XmlTextReader xTxt2 = new XmlTextReader(sr2);
XmlReader xRdr2 = xTxt2;
do
{
xRdr2.Read();
}
while(!xRdr2.EOF && xRdr2.Name != "ClaimStatusMessage");
if(!xRdr2.EOF)
{
try
{
Ack = " -- Msg " + i++ + " -- " + xRdr2.ReadString();
}
catch
{
}
PayorError = PayorError + Ack.Replace("...", " ");
}
}
}

Session["ProResponse"] = dsResponse;
dsResponse.Clear();
dsResponse.Dispose();
}
if(PayorError != "")
{
errRow = PayorError;
}
else if(Ack != "")
{
errRow = Ack;
}
else
{
errRow = "Payor response message is not available!";
}
errorRow["Reason for Rejection"] = errRow;
}
catch(Exception exc)
{
Microsoft.ApplicationBlocks.ExceptionManagement.Ex ceptionManager.Publish(exc);
Response.Redirect(Request.ApplicationPath + @"/error.aspx");
}
}