OK, so I've been trying to install beta 1 of the Office 12 Server, with limited success.  My good friend Mauro Cardarelli warned me about installing this server on a domain controller, so I've create a virtual environment consisting of three virtual machines.  One is a domain controller, and the other two are member servers.  One member server is a SQL Server, and the second member server will be my Office 12 server ...

I have all three VMs up and running, and the member servers have been joined to the domain.  Once I got all the prerequisites installed on the Office 12 server (.NET Framework 2.0, ASP.NET, WWF 3.0, etc.) I installed the Office 12 server.  The installation completed, and life appeared to be good.  However, when I ran the SharePoint Products and Technologies Configuration Wizard, configuration task 5 of 9 fails with a "Failure to Register SharePoint Services" message.  Additional exception information is that "This access control list is not in canonical form and therefore cannot be modified." ...

Has anyone gotten this to work?

I've been looking at .NET localization and resource handling in .NET 2.0 and Visual Studio 2005.  If you've created a Windows Forms application using Visual Studio 2005, you'll notice a Properties folder in your project containing a default Resources.resx file.  Here, you can use the designer to easily add formatted string resources, as well as images and icons.  Even if you are not writing an localizable application, the resource file can be a handy, central location to contain string constants used by your application ...

To fetch a resource string in you application, simply create an instance of System.Resources.ResourceManager and invoke the GetString() method like so:

ResourceManager resMgr = new ResourceManager("MyProject.Properties.Resources", this.GetType().Assembly);
string s = regMgr.GetString("String1");

 

I've been looking at the latest version of the Database Access Application Block for my latest project, a VB6 to .NET 2.0 migration project.  I've used previous version of the DAAB in .NET 1.0 and 1.1 projects and found it to be a great tool to encapsulate and standardize all the database access logic ...

While earlier versions were made up of almost entirely static methods of a SQLHelper class that made a database connection, setup the commands, executed them, and tore down the connection, the new version is based on a Database object that wraps the DbConnection object and also handles the fetching of the connection string from the web.config file.  It also provides the flexibility to setup your own DbCommand object (with any appropriate SQLParameters), or make a method call that encapsulates the DbCommand.  The "Quick Start" also provides a useful test harness that provides examples of salient use cases ...

The DAAB is a great way to jumpstart any ADO.NET 2.0 development effort!

I built a simple photo album ASP.NET applicatioin with Visual Studio 2005 and I decided to use forms-based authentication to "password-protect" the application.  The good news is that I didn't write a single line of code to provide a login page.  However, there was a bit of configuration and installation that I had to do to get the SQL Server 2005 personalization database setup and working with my application.  Once I got it setup, deploying it to my production server was a breeze ...

Here are the important steps:

1.  Run the aspnet_regsql.exe application from the Visual Studio 2005 command prompt to create the aspnetdb database,

2.  Be sure to grant your anonymous Internet login aspnet_Membership_BasicAccess, aspnet_Personalization_BasicAccess, aspnet_Profile_BasicAccess, and aspnet_Roles_BacisAccess role memberships for the aspnetdb database,

3.  Using Visual Studio 2005, configure your web.config file with a connection string to the aspnet database, as well as membership, roleManager, and authorization nodes as shown below:

<?xml version="1.0"?>
<!--
Note: As an alternative to hand editing this file you can use the
web admin tool to configure settings for your application. Use
the Website->Asp.Net Configuration option in Visual Studio.
A full list of settings and comments can be found in
machine.config.comments usually located in
\Windows\Microsoft.Net\Framework\v2.x\Config
-->
<configuration xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0">
    <appSettings/>
    <connectionStrings>
        <add
name="MySqlConnection"
connectionString="Data Source=<your SQL Server here>;Initial Catalog=aspnetdb;Integrated Security=SSPI;" />
    
    <system.web>
        <!--
Set compilation debug="true" to insert debugging
symbols into the compiled page. Because this
affects performance, set this value to true only
during development.
-->
        <compilation debug="true"/>

        <authorization>
<allow users="guest" />
<deny users="*" />
</authorization>
        <!--
The <authentication> section enables configuration
of the security authentication mode used by
ASP.NET to identify an incoming user.
-->
        <membership defaultProvider="SqlProvider" userIsOnlineTimeWindow="15">
            <providers>
                <clear />
                <add
name="SqlProvider"
type="System.Web.Security.SqlMembershipProvider"
connectionStringName="MySqlConnection"
applicationName="<your app name here>"
enablePasswordRetrieval="false"
enablePasswordReset="true"
requiresQuestionAndAnswer="true"
requiresUniqueEmail="true"
passwordFormat="Hashed" />
            </providers>
        </membership>
        
        <roleManager>
            <providers>
                <clear />
                <add
connectionStringName="MySqlConnection"
applicationName="<your app name here>"
name="AspNetSqlRoleProvider"
type="System.Web.Security.SqlRoleProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
                <add
applicationName="PatsPhotos"
name="AspNetWindowsTokenRoleProvider"
type="System.Web.Security.WindowsTokenRoleProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
            </providers>
        </roleManager>

        <authentication mode="Forms">
            <forms name=".ASPXFORMSAUTH" />
        </authentication>

        <!--
The <customErrors> section enables configuration
of what to do if/when an unhandled error occurs
during the execution of a request. Specifically,
it enables developers to configure html error pages
to be displayed in place of a error stack trace.

<customErrors mode="RemoteOnly" defaultRedirect="GenericErrorPage.htm">
<error statusCode="403" redirect="NoAccess.htm" />
<error statusCode="404" redirect="FileNotFound.htm" />
</customErrors>
-->
    </system.web>
</configuration>

4.  From the Website, ASP.NET Configuration menu in Visual Studio 2005, add the appropriate users, roles, and access rules using the Website Administration Tool,

5.  For my app, I created a new, blank ASP.NET page named login.aspx and dragged a Login control onto the page from the Toolbox.  I selected one of the built-in auto formats for the Login control ...

Et voila!  I had added forms-based authentication using a SQL Server 2005 database without writing a single line of ASP.NET code!

After testing my application, I repeated steps 1 and 2 on my production SQL Server, and modified my web.config file to add a "production" connection string ...

I just went through the process of integrating Visual SourceSafe with Visual Studio 2005.  A new version of Visual SourceSafe, Visual SourceSafe 2005, is packaged with the Team Edition of Microsoft Visual Studio 2005.  The bad news is that Visual Studio 2005 does not support integration with Visual SourceSafe 6.0, and furthermore, you cannot install Visual SourceSafe 6.0 side-by-side with Visual SourceSafe 2005.  However, the good news is that legacy versions of Microsoft Visual Studio, including Visual Studio 6.0, will integrate with the new version of SourceSafe ...

The installation process was a little tricky on my laptop.  Here are the steps I took:

1.  I uninstalled Microsoft Visual SourceSafe 6.0 via Add/Remove Programs.  This apparently was successful, yet my SourceSafe icon was still installed on my start menu and the application still worked,

2.  I ran Add/Remove Programs on my instance of Microsoft Visual Studio 6.0 and deselected the Visual SourceSafe option.  This was successful, but again, SourceSafe 6.0 was still installed and working,

3.  I went to the /Program Files/Microsoft Visual Studio/Common Files/VSS folder and unregistered all the DLLs in that folder.  Then, I deleted the folder.  I received no warning messages about applications or components being registered or installed,

4.  I installed Visual SourceSafe 2005 from my Visual Studio 2005 Team Edition for Developers disc,

5.  I opened Visual Studio 2005 and selected Microsoft Visual SourceSafe as my installed plug-in from the Tools, Options menu.

To my surprise, not only was Visual Studio 2005 integrated with this new version of SourceSafe, but my Visual Studio 6.0 environment (as well as my Visual Studio 2003) was as well ...

I've received a lot of interest around a previous entry I made about transforming a .NET DataSet to an Excel workbook.  While I did mention that it was possible to transform a DataSet with multiple tables to a workbook with multiple worksheets, my previous example did not demostrate this - it focused on the handling of the DateTime data type in Excel ...

Attached is a zip file that demonstrates this.  The contained stored procedure can be executed against your copy of the Pubs database, just code your connection string accordingly ...

MultiWorksheet.zip (2.02 KB)

I ran the SQL Server 2005 Upgrade Advisor against a test database that I am currently working with.  I'd say it's a pretty typical SQL Server 2000 database: lots of complicated stored procedures, user-defined functions, triggers, etc.  Here are the issues that the upgrade advisor reported:

1. Remove references to undocumented system tables
2. Outer join operators *= and =* are not supported in 90 compatiblilty mode
3. Non-integer constants are not allowed in the ORDER BY clause in 90 compatibility mode
4. Changes to behavior in syslockinfo and sp_lock
5. Other Database Engine upgrade issues

Obviously, #5 bears looking into more.  #2 is the result of that old outer join syntax that I learned when I was working with Sybase way back when.  [I guess I'm really going to have to commit to using the OUTER JOIN clause ...]

None of these issues would prevent the database from being upgraded, or cause the upgrade to fail.  However, they would definitely need to be addressed before going to production (particularly #2 and #3) ...

I've installed SQL Server 2005 on my development machine (which already has the Developer Edition of SQL Server 2000 installed) and the process very smoothly.  My old SQL Server 2000 instance was named the "default" instance, meaning that I had to create a named instance for SQL Server 2005.  Other than that, the installation process went without a hitch ...

One nice feature is that the new SQL Server Management Studio allows me to connect to both versions of SQL Server and browse a list of database objects both in the SQL Server 2000 and SQL Server 2005 databases.  Presumably, it can connect to SQL Server 7 databases as well ...

In a recent post, I talked about quickly transforming a .NET DataSet to Excel XML format using an XSL transform.  One of the problems I encountered is Excel's handling of the DateTime data type.  DataSets serialize a DateTime data type in UTC format.  Excel does not recognize this format; specifically, it does not handle the time zone information that is part of the UTC format ...

To solve this problem, I created an XSL extension object that implements a custom routine to strip the trailing ten characters of time zone information.  Here's a code sample:

        private void button1_Click(object sender, System.EventArgs e)
        {
            // Get a DataSet ...
            string conn = "<your connection string here ...>";
            DataSet ds = SqlHelper.ExecuteDataset(conn, CommandType.Text,
                "select getdate() as ADate, 1.3 as ANumber1, 2.7 as ANumber2, 'Hello world' as AString");

            // Load the DataSet in an XmlDataDocument ...
            XmlDataDocument xmlDoc = new XmlDataDocument(ds);

            // Load the transform ...
            XslTransform xslTran = new XslTransform();
            xslTran.Load("transform.xslt");

            // Add the DateConverter extension object so we can call the FormatDateTime()
            // method from the transform ...
            DateConverter dc = new DateConverter();
            XsltArgumentList args = new XsltArgumentList();
            args.AddExtensionObject("urn:my-scripts", dc);

            // Create an XmlTextWriter for the output Excel workbook ...
            XmlTextWriter tw = new XmlTextWriter("test.xls", Encoding.UTF8);
            tw.Formatting = Formatting.Indented;
            tw.Indentation = 3;

            // Transform the XmlDataDocument ...
            tw.WriteStartDocument();
            xslTran.Transform(xmlDoc, args, tw, null);
            tw.Close();

        }
    }

    public class DateConverter
    {
        public DateConverter() {}

        // Excel cannot handle DateTime in UTC format. We'll simply strip
        // the last 10 characters that contain the time zone info ...
        public string FormatDateTime(string data)
        {
            return data.Remove(23, 10);
        }
    }

To run this sample, create a Windows Forms application with a button.  Use the button click handler and DateConverter class shown above.  The following are the contents of a "transform.xslt" file that you put in the same directory as your executable:

<xsl:stylesheet version="1.0"
xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:msxsl="urn:schemas-microsoft-com:xslt"
    xmlns:o="urn:schemas-microsoft-com:office:office"
    xmlns:x="urn:schemas-microsoft-com:office:excel"
    xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
    xmlns:custom="urn:my-scripts" >

<xsl:template match="NewDataSet">

    <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
     xmlns:o="urn:schemas-microsoft-com:office:office"
     xmlns:x="urn:schemas-microsoft-com:office:excel"
     xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
     xmlns:html="http://www.w3.org/TR/REC-html40">

        <Styles>
            <Style ss:ID="Default" ss:Name="Normal">
                <Alignment ss:Vertical="Bottom"/>
                <Borders/>
                <Font/>
                <Interior/>
                <NumberFormat/>
                <Protection/>
            </Style>
            <Style ss:ID="s21">
                <Font ss:Bold="1"/>
                <Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
            </Style>
            <Style ss:ID="s22">
                <Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
            </Style>
            <Style ss:ID="s23">
                <NumberFormat ss:Format="_(* #,##0.00_);_(* \(#,##0.00\);_(@_)"/>
            </Style>
            <Style ss:ID="s24">
                <NumberFormat ss:Format="Short Date"/>
            </Style>
        </Styles>

        <Worksheet ss:Name="My Worksheet">
            <Table ss:ExpandedColumnCount="5" x:FullColumns="1" x:FullRows="1">
                <xsl:attribute name="ss:ExpandedRowCount" >
                    <xsl:value-of select="count(Table)+1"/>
                </xsl:attribute>

                <Column ss:AutoFitWidth="1"/>
                <Column ss:AutoFitWidth="1"/>
                <Column ss:AutoFitWidth="1"/>
                <Column ss:AutoFitWidth="1"/>
                <Column ss:AutoFitWidth="1"/>

                <Row>
                    <Cell ss:StyleID="s21">
                        <Data ss:Type="String">ADate</Data>
                    </Cell>
                    <Cell ss:StyleID="s21">
                        <Data ss:Type="String">AString</Data>
                    </Cell>
                    <Cell ss:StyleID="s21">
                        <Data ss:Type="String">ANumber1</Data>
                    </Cell>
                    <Cell ss:StyleID="s21">
                        <Data ss:Type="String">ANumber2</Data>
                    </Cell>
                    <Cell ss:StyleID="s21">
                        <Data ss:Type="String">Sum</Data>
                    </Cell>
                </Row>

                <xsl:apply-templates select="Table"/>

            </Table>
        </Worksheet>
    </Workbook>
</xsl:template>
    
<xsl:template match="Table">
    <Row>
        <Cell ss:StyleID="s24">
            <Data ss:Type="DateTime">
                <xsl:value-of select="custom:FormatDateTime(ADate)"/>
            </Data>
        </Cell>
        <Cell ss:StyleID="s22">
            <Data ss:Type="String">
                <xsl:value-of select="AString"/>
            </Data>
        </Cell>
        <Cell ss:StyleID="s23">
            <Data ss:Type="Number">
                <xsl:value-of select="ANumber1"/>
            </Data>
        </Cell>
        <Cell ss:StyleID="s23">
            <Data ss:Type="Number">
                <xsl:value-of select="ANumber2"/>
            </Data>
        </Cell>
        <Cell ss:StyleID="s23" ss:Formula="=SUM(RC[-2]:RC[-1])">
            <Data ss:Type="Number" />
        </Cell>
    </Row>
</xsl:template>

</xsl:stylesheet>

Note the use of the "urn:my-scripts" namespace which invokes the FormatDateTime() method of the DateConverter class in the transform ...

If you've been redirected to this blog from its old location at http://georgew.typepad.com/dotnet/ - Welcome!

I will continue to keep the old site running for a while longer, but will make all future posts here.  I've also migrated all the posts from the old location to the new.  I hope you enjoy the "portal" look and feel!

We currently have the need to export the contents of a .NET DataSet (containing several DataTables) to an Excel workbook containing several worksheets.  There are a couple of ways to do this:

1.   Use the Excel object model to programmatically create an instance of Excel, create an Excel Workbook with several Worksheets, iterate over the DataRows in each DataTable, and insert the results in each Cell.  Along the way, you can also apply the appropriate cell and number formatting to the cells.  This technique is very serviceable, but also very slow.  If you have a DataTable of any size at all (even 100 rows), the performance may be unacceptable ...

2.  The second techique involves serializing the DataSet using an XmlTextWriter, and apply an XSL Transform to the result to transform the XML to the Excel format.  This technique is at least an order of magnitude faster, while providing the ability to create multiple datasheets within a single workbook and handle cell formatting ...

I'll post a specific example here within the next few days ...

I mentioned in a previous post (which I posted from the PDC in September) that RSS support will become ubiquitous in future Microsoft releases: Office 12, IE, Vista, etc.  Here are some interesting things you can do with RSS that you may have never considered ...