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; }