Getting SQL output messages and record counts from .Net

This code will allow you to get information messages and record counts by running SQL in .Net, besides the recordsets and output parameters.

The InfoMessage event will give you the output of PRINT and other low priority messages.
The StatementCompleted event will give you the record counts affected by each statement (like DELETE, INSERT, UPDATE and SELECT).

Tracking these outputs may help you debug your application in production more easily. The InfoMessage event gives you the line number the message was raised at.

(Using MS Enterprise Library)

Database db = DatabaseFactory.CreateDatabase();
SqlCommand dbc = (SqlCommand)db.GetSqlStringCommand(Properties.Resources.SqlPrepareRecords);
dbc.CommandTimeout = 0;
dbc.StatementCompleted += new StatementCompletedEventHandler(OnStatementCompleted);
using (dbc.Connection = (SqlConnection)db.CreateConnection())
{
dbc.Connection.FireInfoMessageEventOnUserErrors = true;
dbc.Connection.InfoMessage += new SqlInfoMessageEventHandler(OnInfoMessage);
dbc.Connection.Open();
dbc.ExecuteNonQuery();
dbc.Connection.Close();
}

public void OnStatementCompleted(Object sender, StatementCompletedEventArgs args)
{
Console.WriteLine("{0:G} - {1} records affected", DateTime.Now, args.RecordCount);
}

protected void OnInfoMessage(object sender, SqlInfoMessageEventArgs args)
{
foreach (SqlError err in args.Errors)
{
Console.WriteLine(String.Format("{0:G} - Line: {1} Message: {2}", DateTime.Now, err.LineNumber, err.Message);
}
}

Changing Asp.net configuration file programmatically

If you need to change the web.config file of your web site programatically ASP.net provides the WebConfigurationManager class to achive that task.

Here is a code piece that will allow you to change the culture settings in the web.config file.

I put this into the Application_Start method of Global.aspx.cs so that the web.confi is changed when the site is initialized. In order to prevent the web site from restarting over and over again, I need to check the value that I changed to see that it's there:

Configuration conf = WebConfigurationManager.OpenWebConfiguration("~");
SystemWebSectionGroup webSection = (SystemWebSectionGroup)conf.GetSectionGroup("system.web");
if (webSection.Globalization.Culture != MyConfiguration.Culture)
{
webSection.Globalization.Culture = MyConfiguration.Culture;
webSection.Globalization.UICulture = MyConfiguration.Culture;
conf.Save();
}

In this case above, I am changing the culture settings in the configuration file.

ASP.net caching not working?

You may have developed a site in your development environment, which is usually your desktop or notebook. You carefully created a site using the ASP.net caching features. It works great in development. Caching works just as expected. Pages, controls expire just when you expect them to. Everything is perfect.

Then you put your web site on production hoping caching features will make your site faster and leave some breathing room on your server. When you notice caching didn't work on your production web site you can get pretty frustrated.

That's what happened to me when I noticed my web site www.zirve100.com didn't cache the components it was supposed to. I added hidden datetime values in my pages to see if caching was working as predicted.

What I noticed was, caching was working for a while and later it would stop altogether. None of the components that required caching would cache anymore.

After got convinced that I had the correct settings in my configuration files and some searching on the web I found out that asp.net would stop caching your pages after memory utilization was over 90%. My SQL server is on the same machine with IIS, so SQL and IIS were racing for more memory leaving only about 3-4% free.

After fixing SQL server memory to a limit where it wouldn't affect IIS memory usage ASP.net caching started working again.

Fix: Forms authentication failed for the request. Reason: The ticket supplied has expired.

On my windows 2003 server, I've been seeing a lot of information messages like:

Forms authentication failed for the request. Reason: The ticket supplied has expired.

Thrown from ASP.NET 2.0.50727.0

After doing a little search I identified that the issue was caused by not having a fixed validationKey/decryptionKey pair in my machine.config.

Create the key pair by using the code in the Microsoft article below. Insert the key into your machine.config file (which is usually in C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\CONFIG for .net 2.0). Put the key under the system.web tag.

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q312906

Create Html table from datasets

If you're looking for an elegant way to convert your dataset to an html table you can achieve that by using an xslt transformation on the dataset xml output.

Use this xslt to transform your xml dataset:

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">

<xsl:output method="html" version="4.0"/>

<xsl:template match="NewDataSet">
   <div>
      <table border="1">
         <xsl:for-each select="/NewDataSet/TABLENAME0" >
            <xsl:call-template name="DoLine" />
         </xsl:for-each>
      </table>
      <br />
      <table border="1">
         <xsl:for-each select="/NewDataSet/TABLENAME1" >
            <xsl:call-template name="DoLine" />
         </xsl:for-each>
      </table>
   </div>
</xsl:template>

<xsl:template name="DoLine" >
   <xsl:if test="position()=1">
      <tr>
         <td>
            <xsl:value-of select="local-name()" />           
         </td>
      </tr>
      <tr class="header" >
         <xsl:for-each select="./*">
            <td>
               <xsl:value-of select="local-name()" />
            </td>
         </xsl:for-each>
      </tr>
   </xsl:if>

   <tr class="row" >
      <xsl:for-each select="./*">
         <td>
            <xsl:value-of select="." />
         </td>
      </xsl:for-each>
   </tr>
</xsl:template>

</xsl:stylesheet>

Here is the code that converts the dataset to an html table:

DataSet ds = db.ExecuteDataSet(dbc, transaction);
ds.Tables[0].TableName = "Table1";
ds.Tables[1].TableName = "Table2";
ds.Tables[2].TableName = "Table3";
XmlDataDocument dataDocument = new XmlDataDocument(ds);
XslCompiledTransform xslTransform = new XslCompiledTransform();
XmlDocument xmlDocument = new XmlDocument();
string xslSource = Properties.Resources.XslDatasetXmlToHtmlTable;
xslSource = xslSource.Replace("TABLENAME0", ds.Tables[0].TableName); xslSource = xslSource.Replace("TABLENAME1", ds.Tables[1].TableName); xslSource = xslSource.Replace("TABLENAME2", ds.Tables[2].TableName); xmlDocument.LoadXml(xslSource);
xslTransform.Load(xmlDocument);
StringBuilder stringBuilder = new StringBuilder();
XmlWriter xmlWriter = XmlWriter.Create(stringBuilder);
xslTransform.Transform(dataDocument, xmlWriter);
xmlWriter.Flush();