SQL For XML Page

[TheVillageIdiot27]

I am currently upgrading a site from ASP classic to ASP.net (which is learning experiance for me), that relies heavily on SQL server 'FOR XML' stored procedures, which are used to create components all over the site.

it is my intention to create on page which has a query string called "sql" i.e. "?sql=SELECT whatever FROM wherever FOR XML AUTO", which produces a page of XML, which could then be resused whenever.

While I can do this in ASP classic I really have no idea how to go about it in .net.

I wonder if anyone has done something similar before of can point me in the right direction.

Thanks In Advance

 

[jmeckley]

i would not put a sql statement in the query string that puts way too much information at the surface for end users to see.

also, why the heavy use of For Xml. Now that you are upgrading the code, it may be a good time to reevaluate the procs. If I had to guess the proc results came back as xml so you could use xslt to format the page.

I would look into an implementation of MVC instead (MonoRail is very popular for this).

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[TheVillageIdiot27]

My intention was to reuse the code using something like this

<asp:xml runat="server" id="myid" DocumentSource="mypage.aspx?sql=EXEC myprocedure"  TransformSource="xslt/mystylesheet.xsl"/>

So the query string wouldn't be at the surface - I am rather assuming at the moment that this would work.

This is very much the first time I have picked up .net so I would happy just to see some XML out the database right now.

There are a large number of for XML queries because there is a lot of relational data (it's an intranet largly for BI) - in the original version these queries worked well so I probably have no reason to change them.

 

[jmeckley]

changing from asp to asp.net is not a language upgrade. it's a new paradigm about how to develope applications.
translating xml using xslt can be done in any language as the real work is done by xslt, not the programming language.

So the query string wouldn't be at the surface

yes it would because [tt]mypage.aspx[/tt] is a public url. technically a handler (ashx) would be best for this scenrio.

if your going to stick with the xml/xslt method then go this route

<asp:xml runat="server" id="XmlToDisplay" TransformSource="xslt/mystylesheet.xsl"/>

protected override void OnLoad(EventArgs e)
{
   base.OnLoad(e);
   if(!IsPostback)
   {
      XmlToDisplay.Xml = GetDataFromStoredProc();
   }
}

private string GetDataFromStoredProc()
{
   ConnectionStringSettings settings = ConfigurationManager.ConnectionStrings["NameOfDbConnection"];
   using(IDbConnection connection = DbProviderFactories.GetFactory(settings.ProviderName).CreateConnection())
   {
      connection.ConnectionString = settings.ConnectionString;
      connection.Open();
      using(IDbCommand command = connection.CreateCommand())
      {
         command.CommandType = CommandType.StoredProcedure;
         command.CommandText = "select * from table for xml";
         return (string) command.ExecuteScalar();
      }
   }
}

I haven't worked with for xml, I would assume the result is a scalar string, not a row set or non-query so ExecuteScalar() would be the best option.

this is alot of room to refactor this. I would recommend creating a factory to create a db connection/command which you can then customize foreach page.

before going too much farther I would take a few days to research the asp.net page life cycle and some of the key features of asp.net
HttpApplication
HttpModules
WebHandlers (ashx files are generic handlers)
WebForm
WebUserControl
Postback/ViewState (blessing and a curse)

sure, you can jump in and get it working, M$ prides itself on RAD tools. but the code will quickly become unmaintainable.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[TheVillageIdiot27]

Cheers for this you are quite right about it being a public url, I will probably take your comments on board.

For anyone else looking at this the execute scaler method doesn't quite work although I am merging the code above and the code this code taken from the good people at microsoft, which seems to be doing the trick for me. Thanks for your help

        Try
            Dim cn As New System.Data.SqlClient.SqlConnection()
            cn.ConnectionString = _
            "data source=(local);initial catalog=pubs;" & _
            "User ID=myuser;Password=mypassword"
            cn.Open()

            Dim cmd As New System.Data.SqlClient.SqlCommand()
            cmd.Connection = cn
            cmd.CommandText = "SELECT * FROM authors FOR XML AUTO, XMLDATA"

            Dim xmlr As System.Xml.XmlReader
            xmlr = cmd.ExecuteXmlReader()
            xmlr.Read()
            Do While xmlr.ReadState <> Xml.ReadState.EndOfFile
                System.Diagnostics.Debug.WriteLine(xmlr.ReadOuterXml())
            Loop
            MessageBox.Show("Reached the end. Check the output window.")

        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try