public Employee[] GetEmployee(string lastName)
{
SqlXmlCommand command = new SqlXmlCommand(_SqlXmlconnectionString);
command.CommandType = SqlXmlCommandType.Sql;
command.CommandText = "exec FetchEmployee ?";
SqlXmlParameter param = command.CreateParameter();
param.Value = lastName;
return(DeserializeEmployee(command.ExecuteXmlReader()));
}
//RETORNA TABELA NO FORMATO DE STRING XML
public string ReturnStrXml()
{
string strXml = "";
try
{
DateTime datStart = DateTime.Now;
if (this._XmlCmd == null)
{
SetConn();
GenericCLX.CS.Util objUtil = new GenericCLX.CS.Util();
//objUtil.GetConfig("");
_XmlCmd = new SqlXmlCommand(objUtil.ConnectionStringXML());
_XmlCmd.OutputEncoding = "ISO-8859-1";
_XmlCmd.RootTag = "ROOT";
}
_XmlCmd.CommandText = this._Sql;
System.Xml.XmlReader objXr = _XmlCmd.ExecuteXmlReader();
XmlDocument objXd = new XmlDocument();
objXd.Load(objXr);
strXml = objXd.OuterXml;
objXd = null;
objXr.Close();
objXr = null;
if (this._CloseConn)
{
_XmlCmd = null;
}
this._TimeQuery = Functions.DateDiff(datStart.ToLocalTime().ToString());
}
catch (Exception e)
{
if (TreatedError(e.Message))
{
this.ReturnStrXml();
}
}
return(strXml);
}
public void Load(IDataSet dataSet, string xpathQuery)
{
//Temporary variables.
IDataSet dsTmp = FactoryInstance.CreateDataSet(dataSet.SchemaFile, typeof(BaseDataSet));
bool inserted;
//Read schemas inside the datasets.
dsTmp.ReadXmlSchema(dsTmp.SchemaFile);
dataSet.ReadXmlSchema(dataSet.SchemaFile);
//Split the query in parts, with the element name and its filters.
string[] parts = xpathQuery.Split('/');
//Reject queries with parent or current node filter expressions.
//i.e.: publishers[pub_id="1389" and ./publisherTitles/price>20] should be converted to
// publishers[pub_id="1389"]/publisherTitles[price>20]
if (xpathQuery.IndexOf(".") != -1)
{
throw new ArgumentException("Parent or current node references are not allowed in the expression.", "xpathQuery");
}
//Save a collection with elements in the query, without its filters.
StringCollection queryelements = new StringCollection();
for (int i = 0; i < parts.Length; i++)
{
if (parts[i].IndexOf("[") == -1)
{
queryelements.Add(parts[i]);
}
else
{
queryelements.Add(parts[i].Substring(0, parts[i].IndexOf("[")));
}
}
//Build a collection of filters, with keys pointing to the element name.
StringDictionary queryfilters = new StringDictionary();
for (int i = 0; i < parts.Length; i++)
{
if (parts[i].IndexOf("[") != -1)
{
//Append element replacing brackets with node paths.
queryfilters.Add(queryelements[i], (parts[i].Replace("[", "/")).Replace("]", ""));
}
}
//Rebuild the query to retrieve the firt node and all the children.
StringBuilder sb = new StringBuilder();
inserted = false;
sb.Append(parts[0].Replace("]", ""));
for (int i = 1; i < queryelements.Count; i++)
{
//If there's a filter in the element, add the condition to the query.
if (queryfilters.ContainsKey(queryelements[i]))
{
inserted = true;
sb.Append(" and .");
for (int j = 1; j < i; j++)
{
sb.Append("/" + queryelements[j]);
}
sb.Append("/" + queryfilters[queryelements[i]]);
}
}
//Remove additional "and" if no filter is present in the first element.
if (inserted && parts[0].IndexOf("[") == -1)
{
//Recover the length in characters, not bytes, from the string.
int qty = parts[0].ToCharArray().Length;
sb.Remove(qty, 5);
sb.Insert(qty, "[");
}
if (inserted || (queryfilters.Count == 1 && parts[0].IndexOf("[") != -1))
{
sb.Append("]");
}
//Look for an element matching the dataset name, and add it to the query if present in the schema.
XmlDocument doc = new XmlDocument();
doc.Load(dataSet.SchemaFile);
//Add a namespace resolver for "xsd" prefix.
XmlNamespaceManager mgr = new XmlNamespaceManager(doc.NameTable);
mgr.AddNamespace("xsd", "http://www.w3.org/2001/XMLSchema");
//If we find the enclosing dataset element, insert it at the beginning of the query.
if (doc.SelectNodes("//xsd:element[@name='" + dsTmp.DataSetName + "']", mgr).Count != 0)
{
sb.Insert(0, dsTmp.DataSetName + "/");
}
//Load document with all the children to start filtering undesired nodes.
SqlXmlCommand cmd = new SqlXmlCommand(_connection);
//Set the root equal to the DataSetName.
cmd.RootTag = dsTmp.DataSetName;
cmd.CommandText = sb.ToString();
cmd.CommandType = SqlXmlCommandType.XPath;
cmd.SchemaPath = dataSet.SchemaFile;
MemoryStream stm = new MemoryStream();
cmd.CommandStream = stm;
XmlReader r = cmd.ExecuteXmlReader();
dsTmp.ReadXml(r);
SqlXmlAdapter ad = new SqlXmlAdapter(cmd);
ad.Fill(dsTmp.GetState());
XmlDataDocument docsrc = new XmlDataDocument(dsTmp.GetState());
XmlDataDocument docdest = new XmlDataDocument(dataSet.GetState());
dataSet.EnforceConstraints = false;
//Rebuild the query to retrieve the filtered nodes, and append them without their children.
sb = new StringBuilder();
for (int i = 0; i < queryelements.Count; i++)
{
//Append all the previous node elements in the "chain".
for (int j = 0; j < i; j++)
{
sb.Append(parts[j] + "/");
}
//Add the current element filter, without the closing bracket, to append the next conditions.
sb.Append(parts[i].Replace("]", ""));
inserted = false;
//Look for subsequent elements to add to the query.
for (int j = i + 1; j < queryelements.Count; j++)
{
//If there's a filter in the element, add the condition to the query, otherwise ignore it.
if (queryfilters.ContainsKey(queryelements[j]))
{
inserted = true;
sb.Append(" and .");
//Append again all the previous node elements in this element "chain".
for (int k = i + 1; k < j; k++)
{
sb.Append("/" + queryelements[k]);
}
//Add the current filter found.
sb.Append("/" + queryfilters[queryelements[j]]);
}
}
//Remove additional "and" if no filter is present in the first element.
if (inserted && parts[i].IndexOf("[") == -1)
{
//Recover the length in characters, not bytes, from the string.
int qty = 0;
for (int k = 0; k <= i; k++)
{
qty += parts[k].ToCharArray().Length;
}
//Add the number of forward slashes appended to concatenate filters.
qty += i;
sb.Remove(qty, 5);
sb.Insert(qty, "[");
}
//Close the filter expression.
if (inserted || parts[i].IndexOf("[") != -1)
{
sb.Append("]");
}
//Execute the XPath query starting from the root document element.
XmlNodeList nodes = docsrc.SelectNodes("//" + sb.ToString());
//Iterate the nodes found with the query.
foreach (XmlNode node in nodes)
{
//Retrieve the row corresponding to the node found.
DataRow row = docsrc.GetRowFromElement((XmlElement)node);
//Import the row into the destination DataSet.
dataSet.Tables[row.Table.TableName].ImportRow(row);
}
sb = new StringBuilder();
}
dataSet.EnforceConstraints = true;
}