C# .NET - ADO.NET starting to tick me off... - Asked By Robbe Morris on 18-Sep-01 04:56 PM

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

Try this - Asked By Peter Bromberg on 18-Sep-01 08:52 PM

Looks like you want to do something like the following:

<%@ Page Language="C#" Debug="true" %>
<%@Import Namespace="System.IO"%>
<%@Import Namespace="System.Data"%>
<%@Import Namespace="System.Data.SqlClient"%>
<%@Import Namespace="System.Xml"%>
<%@Import Namespace="System.Xml.Xsl"%>

    public void Page_Load(object sender, EventArgs e) {
        string sql = "SELECT ContactName,CompanyName FROM Customers WHERE Contactname in ('Thomas Hardy','Maria Anders')";
        string connStr = "server=localhost;database=Northwind;uid=sa;pwd=;";
        StringBuilder sb = new StringBuilder();
        StringWriter sw = new StringWriter(sb);
        SqlConnection dataConn = new SqlConnection(connStr);
        dataConn.Open();
        DataSet ds = new DataSet("Northwind");
        SqlDataAdapter dsc = new SqlDataAdapter(sql,dataConn);
        dsc.Fill(ds,"Customers");
        XmlDataDocument dataDoc = new XmlDataDocument(ds);
        XslTransform transform = new XslTransform();
        transform.Load(Server.MapPath("customers.xsl"));
       transform.Transform(dataDoc,null,sw);
        xslOutput.InnerHtml = sb.ToString();
        dataConn.Close();   
        sw.Close();
    }


    
        <div id="xslOutput" runat="server" />
    


<!--separate stylesheet "customers.xsl" follows-->
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
	<xsl:template match="/">
	<xsl:apply-templates />
	</xsl:template>
	<xsl:template match="Northwind">
		<xsl:apply-templates />
	</xsl:template>
	<xsl:template match="Customers">
	<table bgcolor="ffcc66" cellpadding="5" width="50%">
	<tr><td><font size="5"><b><xsl:value-of select="ContactName" /></b></font></td>
	<td>
	<b>Company:</b>
	<xsl:value-of select="CompanyName" />
	</td>
	</tr>
	</table>		
	</xsl:template>
</xsl:stylesheet>

Sounds like a test is due... - Asked By Robbe Morris on 20-Jul-14 10:40 AM

...Think I'm gonna write my own set of little test functions and time them.  If there is no performance penalty with using the DataSet and its methods to create the disconnected XML, then I'll use it.

Don't forget - Asked By Peter Bromberg on 18-Sep-01 09:01 PM

If your primary objective is to set up a "test" you can just as easily use the classic COM MSXML3 references vs. the .NET base classes, both from your ASP .NET code.
I'd stay away from the console though <grin/>
I can use a test app that is console based... - Asked By Robbe Morris on 18-Sep-01 09:02 PM
...as long as I don't interface with the console to write results right?
Where will you write the results - to a file? - Asked By Peter Bromberg on 18-Sep-01 09:15 PM
It doesn't really matter, there is nothing "wrong"
with console apps. Just that most of the stuff I would think you do (at least for me) is oriented toward the browser so why not practice getting more comfortable with that? That's the biggest problem I have with these .NET books - even Troelson's book - is 95% of their samples are console apps! DUH! You can put all this into ASPX pages and do your logging and writing to the file system just the same. Plus I would think debugging in the IDE would be easier maybe for an ASPX page?
Nothing that complicated... - Asked By Robbe Morris on 20-Jul-14 10:41 AM
I was just gonna store it in variables and review the results in debug mode.

I just wanna get a good feel for efficiencies.  I don't wanna spend a lot of extra time with extraneous files.  My training time for this intermittent at best.

Actually, debugging in Console apps is fairly easy.
Decided to create some tests for this - Asked By Robbe Morris on 20-Sep-01 10:32 AM
...Here are the initial results:

http://www.eggheadcafe.com/articles/20010919.asp