static void Main(string[] args)
{
SqlXmlCommand cmd = new SqlXmlCommand(connectionString);
cmd.CommandText = "Person.Contact";
cmd.CommandType = SqlXmlCommandType.XPath;
cmd.SchemaPath = "Person.Contact.xsd";
cmd.ClientSideXml = true;
cmd.RootTag = "Person.Contact";
SqlXmlAdapter da = new SqlXmlAdapter(cmd);
DataSet ds = new DataSet();
try
{
// Fill the dataset
da.Fill(ds);
// Make some change
ds.Tables[0].Rows[1]["LastName"] = "Unabel";
// Update the data back to the database.
da.Update(ds.GetChanges());
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
}
private void Form1_Load(object sender, EventArgs e)
{
cmd = new SqlXmlCommand(strConn);
cmd.RootTag = "ROOT";
cmd.CommandText = "Employees";
cmd.CommandType = SqlXmlCommandType.XPath;
cmd.SchemaPath = $"{Application.StartupPath}\\employees.xsd";
ds = new DataSet();
da = new SqlXmlAdapter(cmd);
da.Fill(ds);
dataGridView1.DataSource = ds.Tables[0].DefaultView;
}
private void Form1_Load(object sender, EventArgs e)
{
string strConn = @"Provider=SQLOLEDB;server=.;database=northwind;integrated security=SSPI";
SqlXmlCommand cmd = new SqlXmlCommand(strConn);
cmd.CommandType = SqlXmlCommandType.TemplateFile;
cmd.CommandText = $"{Application.StartupPath}\\querytemplate.xml";
DataSet ds = new DataSet();
SqlXmlAdapter da = new SqlXmlAdapter(cmd);
da.Fill(ds);
dataGridView1.DataSource = ds.Tables[0].DefaultView;
}
private void Form1_Load(object sender, EventArgs e)
{
string strConn = @"Provider=SQLOLEDB;server=.;database=northwind;integrated security=SSPI";
string sql = "select employeeid,firstname,lastname from employees for xml auto";
SqlXmlCommand cmd = new SqlXmlCommand(strConn);
cmd.CommandText = sql;
DataSet ds = new DataSet();
SqlXmlAdapter da = new SqlXmlAdapter(cmd);
da.Fill(ds);
dataGridView1.DataSource = ds.Tables[0].DefaultView;
}
/// <summary>
/// Newsgroup test method.
/// </summary>
public DataSet Test(string schemaFile)
{
try
{
DataSet dataSet = new DataSet("test");
dataSet.ReadXmlSchema(schemaFile);
string sql = "select * from publishers, titles where publishers.pub_id=titles.pub_id";
SqlXmlAdapter ad = new SqlXmlAdapter(sql, SqlXmlCommandType.Sql, _connection);
ad.Fill(dataSet);
return(dataSet);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message + Environment.NewLine + ex.StackTrace);
}
return(null);
}
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;
}