Sto lavorando a un progetto di integrazione dei dati, in cui ho bisogno di estrarre i dati dal sorgente di Oracle e caricarli in un file XML. Il requisito è quello di ottenere l'elenco dei clienti e foreach cliente creare un file xml con i dati dei clienti e dei prodotti associati e i dati relativi al prodotto. Il modo in cui ho progettato come segue.
-
L'uso di execute sql task ottiene l'elenco dei clienti e lo tiene nella variabile object.
-
Uso del ciclo di loop del ciclo foreach attraverso ogni cliente.
-
All'interno del ciclo foreach, ho come compito di script di connettermi a oracle usando odp.net e ottenere i dati richiesti e scriverlo su xml usando la classe xmlwriter. Il codice all'interno dell'attività di script è inferiore a
-
try
{
using (XmlWriter writer = XmlWriter.Create("FilePath" + customer + "_" + serviceType + ".xml", settings))
{
//XML Header
writer.WriteStartDocument();
writer.WriteStartElement("ATOBTO"); //end
writer.WriteStartElement("ATOBTOSSet"); //end
writer.WriteStartElement("ATOBTHEADER"); //end
writer.WriteStartElement("ACTIONID");
writer.WriteString(DateTime.Now.ToString("yyyyMMddHHmmss"));
writer.WriteEndElement();
writer.WriteStartElement("ATOBTSOURCE");
writer.WriteString("MOI");
writer.WriteEndElement();
//Connect To Oracle DB using ODP.net Driver this driver buffers data hence requires minimum RAM usage
con = new OracleConnection(moiConnection);
con.Open();
// Execute PL/SQL For Customer---Only one row will be returned
OracleCommand cmdCustomer = con.CreateCommand();
cmdCustomer.CommandText = sqlCustomer;
OracleDataReader readerCustomer = cmdCustomer.ExecuteReader();
while (readerCustomer.Read())
{
writer.WriteStartElement("ATOBTCUSTOMER");
WriteFullElementString(writer, "CUSTOMERID", readerCustomer["CUSTOMERID"].ToString());
WriteFullElementString(writer, "CUSTOMERNUMBER", readerCustomer["CUSTOMERNUMBER"].ToString());
WriteFullElementString(writer, "CUSTOMERNAME", readerCustomer["CUSTOMERNAME"].ToString());
WriteFullElementString(writer, "CUSTOMERSINCE", readerCustomer["CUSTOMERSINCE"].ToString());
WriteFullElementString(writer, "CUSTOMERSOURCE", readerCustomer["CUSTOMERESOURCE"].ToString());
WriteFullElementString(writer, "SERVICEGROUP", readerCustomer["SERVICEGROUP"].ToString());
WriteSpec(writer, "ATOOBTCUSTOMERSPEC", "ATSPECIFICATIONNAME","SERVICECATEGORY", readerCustomer["SERVICECATEGORY"].ToString());
WriteSpec(writer, "ATOOBTCUSTOMERSPEC", "ATSPECIFICATIONNAME", "PORTALNUMBER", readerCustomer["PORTALNUMBER"].ToString());
WriteSpec(writer, "ATOOBTCUSTOMERSPEC", "ATSPECIFICATIONNAME", "SERVICE_CODE", readerCustomer["SERVICECODE"].ToString());
WriteSpec(writer, "ATOOBTCUSTOMERSPEC", "ATSPECIFICATIONNAME", "SERVICE_DESCRIPTION", readerCustomer["SERVICEGROUP"].ToString());
writer.WriteEndElement(); //ATOBTCUSTOMER
}
// Clean up CustomerCommand
readerCustomer.Dispose();
cmdCustomer.Dispose();
// Execute PL/SQL For CircuitCI -- 100 to 1000's of rows can be returned
OracleCommand cmdCircuitCI = con.CreateCommand();
cmdCircuitCI.CommandText = sqlCircuitCi;
cmdCircuitCI.InitialLOBFetchSize = -1;
OracleDataReader readerCircuitCI = cmdCircuitCI.ExecuteReader();
readerCircuitCI.FetchSize = cmdCircuitCI.RowSize * 100;
//Write CircuitCI XML
while (readerCircuitCI.Read())
{
writer.WriteStartElement("ATOBTCI"); //end
WriteFullElementString(writer, "CICLASSIFICATION", "6103");
WriteFullElementString(writer,"CINUM", readerCircuitCI["CINUM"].ToString());
//CLOB DATA--Call method to clean up invalid xml charecters
string notes = CleanInvalidXmlChars(readerCircuitCI["notes"].ToString());
WriteFullElementString(writer, "CIDESCRIPTION_LONGDESCRIPTION", notes);
WriteFullElementString(writer, "CUSTDEV", readerCircuitCI["CUSTDEV"].ToString());
WriteFullElementString(writer, "INSTALLEDDATE", readerCircuitCI["INSTALLEDDATE"].ToString());
WriteFullElementString(writer, "STATUS", "OPERATING");
// Execute PL/SQL For ServiceAttributes -- Less than 10 to 30 rows for each serviceid
OracleCommand cmdServAttr = con.CreateCommand();
cmdServAttr.CommandText = sqlServiceAttr + " AND SERVICEID=" + readerCircuitCI["SERVICEID"].ToString();
OracleDataReader readerServAttr = cmdServAttr.ExecuteReader();
readerServAttr.FetchSize = cmdServAttr.RowSize * 100;
//Write ServiceAttributes XML
while (readerServAttr.Read())
{
WriteSpec(writer, "ATSPECIFICATION", "ATSPECIFICATIONNAME", readerServAttr["Name"].ToString(), readerServAttr["Value"].ToString());
}
// Clean up ServAttrCommand
readerServAttr.Dispose();
cmdServAttr.Dispose();
// Execute PL/SQL For CircuitAttributes Less than 10 to 30 rows for each circuitid
OracleCommand cmdCircAttr = con.CreateCommand();
cmdCircAttr.CommandText = sqlCircuitAttr + " AND CIRCUITID=" + readerCircuitCI["CIRCUITID"].ToString();
OracleDataReader readerCircAttr = cmdCircAttr.ExecuteReader();
readerCircAttr.FetchSize = cmdCircAttr.RowSize * 100;
//Write CircAttr XML
while (readerCircAttr.Read())
{
WriteSpec(writer, "ATSPECIFICATION", "ATSPECIFICATIONNAME", readerCircAttr["Name"].ToString(), readerCircAttr["Value"].ToString());
}
// Clean up CircAttrCommand
readerCircAttr.Dispose();
cmdCircAttr.Dispose();
// Execute PL/SQL For ContractSLA -- One row for each serviceid
OracleCommand cmdContractSLA = con.CreateCommand();
cmdContractSLA.CommandText = sqlContractSla + " AND SERVICEID=" + readerCircuitCI["SERVICEID"].ToString();
OracleDataReader readerContractSLA = cmdContractSLA.ExecuteReader();
readerContractSLA.FetchSize = cmdContractSLA.RowSize * 100;
//Write ContractSLA XML
while (readerContractSLA.Read())
{
WriteSpec(writer, "ATSPECIFICATION", "ATSPECIFICATIONNAME", "HDCONTROL", readerContractSLA["HDCONTROL"].ToString());
WriteSpec(writer, "ATSPECIFICATION", "ATSPECIFICATIONNAME", "MTTRTARGET", readerContractSLA["MTTRTARGET"].ToString());
WriteSpec(writer, "ATSPECIFICATION", "ATSPECIFICATIONNAME", "RURALMTTR", readerContractSLA["RURALMTTR"].ToString());
WriteSpec(writer, "ATSPECIFICATION", "ATSPECIFICATIONNAME", "SLAINFOURL", readerContractSLA["SLAINFOURL"].ToString());
WriteSpec(writer, "ATSPECIFICATION", "ATSPECIFICATIONNAME", "SLASIGNED", readerContractSLA["SLASIGNED"].ToString());
WriteSpec(writer, "ATSPECIFICATION", "ATSPECIFICATIONNAME", "UPDATEINTERVALMINUTES", readerContractSLA["URBANMTTR"].ToString());
WriteSpec(writer, "ATSPECIFICATION", "ATSPECIFICATIONNAME", "URBANMTTR", readerContractSLA["MTTRTARGET"].ToString());
WriteSpec(writer, "ATSPECIFICATION", "ATSPECIFICATIONNAME", "SEGMENT", readerContractSLA["MTTRTARGET"].ToString());
WriteSpec(writer, "ATSPECIFICATION", "ATSPECIFICATIONNAME", "STATUS", readerContractSLA["MTTRTARGET"].ToString());
WriteSpec(writer, "ATSPECIFICATION", "ATSPECIFICATIONNAME", "CONTRACTTYPEKEY", readerContractSLA["MTTRTARGET"].ToString());
WriteSpec(writer, "ATSPECIFICATION", "ATSPECIFICATIONNAME", "DESCRIPTION", readerContractSLA["MTTRTARGET"].ToString());
WriteSpec(writer, "ATSPECIFICATION", "ATSPECIFICATIONNAME", "CONTRACTNUMBER__CONTRACT_NAME_", readerContractSLA["MTTRTARGET"].ToString()); //CONTRACTCOVERAGE
WriteSpec(writer, "ATSPECIFICATION", "ATSPECIFICATIONNAME", "CONTRACTCOVERAGE", readerContractSLA["MTTRTARGET"].ToString());
}
// Clean up ContractSLA Command
readerContractSLA.Dispose();
cmdContractSLA.Dispose();
writer.WriteEndElement(); //Close Root XML Element
}
// Clean up CircuitCI Command
readerCircuitCI.Dispose();
cmdCircuitCI.Dispose();
} // Dispose XMLWriter
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
Dts.Events.FireError(18, "Error in Creating XML", ex.Message, "", 0);
}
finally { con.Dispose(); } //Close DB Connection
Dts.TaskResult = (int)ScriptResults.Success;
}
public static void WriteFullElementString(XmlWriter writer, string localName, string value)
{
if (value != "" && value!=null)
{
writer.WriteStartElement(localName);
writer.WriteString(value);
writer.WriteFullEndElement();
}
}
public static void WriteSpec(XmlWriter writer, string specName, string localName, string localValue, string value)
{
if (value != "" && value != null)
{
writer.WriteStartElement(specName);
writer.WriteElementString(localName, localValue);
writer.WriteElementString("VALUE", value);
writer.WriteFullEndElement();
}
}
//Method to clean Invalid XML Charecters
public static string CleanInvalidXmlChars(string text)
{
string regex = @"[^\x09\x0A\x0D\x20-\uD7FF\uE000-\uFFFD\u10000-\u10FFFF]";
return Regex.Replace(text, regex, "");
}
Il problema che sto affrontando con questo approccio è quando i dati del cliente sono enormi (circa 200mb), l'attività di script è molto molto lenta, potenzialmente impiega più di 5 ore.
Ho cercato di ottimizzare il codice il più possibile con un miglioramento delle prestazioni minimo o nullo. È questo l'approccio giusto per questo progetto o esistono altre soluzioni efficaci per questo?