Ok. For starters, I've only spent about 5 total hours learning C#. So be nice...
As part of my training, I'm trying to create some standard
functions for returning data from SQL Server 2000. What I would like to do is return a "disconnected" object that contains XML. Of course, I'd like to use the most efficient manner available. Thus, I'd like to try and avoid the overhead of any additional objects such as DataSets. I just want the values back while being disconnected from the database.
The Wrox Professional C# book says to use the objects specifically designed for SQL Server instead of the standard OLEDB objects. Okeydokey fine...
However, with the SQL Server objects, if you close the
"reader" object's "connection" reference WITHOUT closing the reader, the reader gets closed anyway. Thus, as soon as the oConn.Close() has actually finished, the oReader is no longer available. The OLEDB objects don't happen to behave like this.
Short of writing my own function to package up the XML, is there a method or methodology that allows me to return the XML either in a string or via an object reference AFTER the oConn.Close() method has been executed?
This seems like such a basic requirement. You'd think the
solution would be more obvious...
Try moving the While oReader.Read() stuff AFTER the oConn.Close() and you'll see what I mean.
using System;
using System.Xml;
using System.Data.SqlClient;
namespace prjADONet
{
class cADONet
{
static void Main(string[] args)
{
string sSQL = null;
XmlReader oReader = null;
cADONet oADO = new cADONet();
sSQL += "select contactname,companyname ";
sSQL += " from customers ";
sSQL += " where contactname in";
sSQL += "('Thomas Hardy','Maria Anders')";
sSQL += " FOR XML AUTO";
oReader = oADO.RecOpenSQL("northwind con str",sSQL);
}
public XmlReader RecOpenSQL(string sConStr,string sSQL)
{
XmlReader oReader = null;
try
{
SqlConnection oConn = new SqlConnection();
oConn.ConnectionString = sConStr;
oConn.Open();
SqlCommand oCmd = new SqlCommand(sSQL,oConn);
oReader = oCmd.ExecuteXmlReader();
while (oReader.Read())
{ Console.WriteLine(oReader.GetAttribute(0)); }
oConn.Close();
}
catch ( Exception e ) {Console.WriteLine(e.Message); }
finally {}
return oReader;
}
}
}