DAAB - run SP

  • Thread starter Thread starter Mark Goldin
  • Start date Start date
M

Mark Goldin

I am learning how to use DAAB.
I need to execute a stored procedure and return the result as a stream.
Can someone help, please?
 
Mark:

That's a little bit of a vague statement, what is the end goal? Do you need
the whole query returned as a Stream (if so, binary, XML etc) of is it just
one field of a DB so that you can grab BLOB data out fo the DB?
 
My stored procedure looks like this
Select <root
Select somedata for xml aut
Select </root
I was told that if I execute that SP via a stream object that I will get well formed xml
Is that possible
 
Mark:

If your proc has that select statement in it, I think you are going to get
back three datatables which may not be what you want. You don't you try
adding ", Elements" after your xml auto predicate.

Also, you are going to have the execute the SP from a command object, a
stream alone isn't going to get you anything without a command/connection to
the db.

I'm not sure how the rest of your code looks, but if you wanted to get back
your code in well formed valid XML, this code snippet will do it for you.
I'm outputting the text to a text box, but you would just as easily add this
to a stream, write it to a file etc. Just substitue the second select
statement you have with my SELECT statment, change the connection string,
and check the output, I think this is what you want...

private void btnXMLReader_Click(object sender, System.EventArgs e)

{

SqlConnection cn = new SqlConnection("CONNECTION STRING");

SqlCommand cmd = new SqlCommand("Select somedata for xml AUTO, Elements",
cn);

if (cn.State != ConnectionState.Open){cn.Open();}

XmlReader reader = cmd.ExecuteXmlReader();

while(reader.Read())

{

tbOut.AppendText(reader.Value +"\r\n");

}

reader.Close();

cn.Close();


}



You could also wrap it in a code snippet like this within the reader to
write out datareader results if they Weren't in XML already:



private void btnGenXML_Click(object sender, System.EventArgs e)

{

XmlDocument doc = new XmlDocument();

XmlElement root = doc.CreateElement("Products");

doc.AppendChild(root);

XmlAttribute productInfo = doc.CreateAttribute("Date");

productInfo.InnerText = DateTime.Now.ToShortDateString();

root.SetAttributeNode(productInfo);

XmlElement product = doc.CreateElement("Product");

root.AppendChild(product);


XmlAttribute productName = doc.CreateAttribute("Name");

productName.InnerText = "DietCoke";

product.SetAttributeNode(productName);



XmlProcessingInstruction xmlInstruction =
doc.CreateProcessingInstruction("xml", "version='1.0'
encoding='ISO-8859-1'");

doc.InsertBefore(xmlInstruction, doc.ChildNodes[0]);


try

{

doc.Save(@"\Sample.xml");

}



HTH,



Bill
 
But I am trying to learn how to use DAAB
I am adapting our sample but I am not there yet
Can you help me with that? Sorry for stupid questions
 
Mark, No questions are stupid, no worries.

Anyway, it pays to know how things work even if you are going to use the
DAAB. If you go into the documentation on it, and search for XML you'll
find an ExecuteXMLReader method in SqlHelper which is essentially the
approach that I used. The XMLReader does provide access to the 'stream' you
mentioned needing earlier. AFAIK, all of the FOR XML predicates will work
provided they are valid, so FOR XML RAW, AUTO, AUTO ELEMENTS etc should
work. I think this will get you everything you need within the confines
of using DAAB.

Also, there is no Root element, but you don't need to fire an extra select.
Since you have everythign you need to get the string representation, you can
just append the outupt to an opening tag and append a closing tag on the
end. just modify the code I posted before with this...
tbOut.Clear();

tbOut.Text = @"<Root>";

while(reader.Read())

{

tbOut.AppendText(reader.Value);

}

tbOut.Text = @"</Root>";

You may also want to post the doc info but you should have a well formed xml
doc now.
 
If I understand correctly I have to write resulting xml into something to
get a complete string?
Is there a way to load it into an xml doc directly?
 
Back
Top