Brent Gardner's Blog

Copying a WebSite in IIS 5

2007-06-26T21:25:00Z

IIS 6 was a heck of a product. I particularly like the ability to backup / import individual sites, and the xml based metabase. Unfortunately every once in a while I need to do the same thing in IIS 5. Like when I need to copy the metabase settings from an IIS 5 production server to a IIS 5 dev server to reproduce a bug. Doing this by hand is tedious at best, so I wrote a utility that uses ADSI to scan through the metabase of one server, and copy those entries onto another server. This may be a marginally useful thing with IIS 7 on the way, but I figured I would put it out there in case it would help anyone.

Another nice feature, it *should* work to copy in between IIS 5 & 6, and probably 7. So this might be a nice way to migrate sites to a new version of IIS.

Cheers,

Brent

Retrieve IUSR Password

2007-06-26T18:25:00Z

Anytime you setup a password for a service it gets stored with reversible encryption. The software needs to be able to retrieve the password to log in as that user. IIS is no exception. When you setup the IUSR password, it gets encrypted and stored on disk, but since the encryption is reversible, you can still get at it. MS seems to want to hide this from you: AdsUtil and MetaBaseExplorer have access to the password, but obfuscate it before display. Luckily I wrote my own utility which will let you see the password.

If you haven't yet, just download AdsiExplorer, and browse to your website. The AnonymousUserPass property contains the IUSR password in plain text!

Happy password retrieval,

Brent

ADSI Explorer

2007-06-26T18:13:00Z

I've seen allot of ADSI related utilies out there. There is metaedit, adsiedit, adsutil.vbs. All of these are either limited (metaedit) or a pain in the butt (adsutil). So I wrote a nice little graphical explorer for ADSI that works much like wbemtest for WMI. It will let you browse Active Directory, IIS, LDAP or whatever. I have attached both the quickly hacked together source and the executable. I've found it incredibly useful myself, I hope others find it useful too.

Cheers,

Brent

Creating a Hierarchical DataSource from a DataSet

2007-06-15T05:22:00Z

I've already covered creating custom tabular DataSources in previous posts, but sometimes they just aren't enough. For example, the ASP.NET 2.0 Menu and TreeView controls are designed to bind to a new type of HierarchicalDataSource. Bertrand Le Roy has a fantastic blog post on this. As creative as his example was, I wanted to tweak it a little bit to fit my needs. For example, Mr. Le Roy uses multiple tabular DataSources, and binds them together with his Relation parameters. This seemed a little round-about to me. Why take a DataSet, break it up into tabular DataSources, then combine them again back into a DataSet, when you could just serve up the DataSet whole, and provide much less information to the Controls? Also, the above example did not have a SelectMethod, instead it simply showed the contents of an entire table.

The code to use this control is really straight forward, so rather than go into a detailed analysis, how about a screenshot?

You can download the sample project below. It is the same one from my post on the FriendlyDal, and it will probably continue to be expanded I blog about more fun stuff.

Cheers,

Brent

Pre-Loading vs. Lazy Loading

2007-06-15T03:54:00Z

Well, it has been a while since my last post. No, I wasn't in Turkish prison, I just got busy with, well you know, my real job. So I apologize to my regulars (if I have any at this point ;-). But hopefully this post will make up for the delay.

I've noticed lately (especially with LINQ coming soon), that lazy-loading of data from the database has become the de-facto method for many applications. While this works relatively well when you application has a low-latency connection to the database (i.e. webapps), it is not ideal for client-based applications, where data has to be sent over the Internet and cached on a local machine, until it is synchronized again with the server. Lazy loading also introduces a few issues even when connection latency is low, foremost being that since loading occurs unpredictably throughout your code execution; data may have changed during the load process.

Pre-loading data is arguably a cleaner approach, since it can always be born out into a simple set of steps:

Load the record you are after, and all its related records (ideally in one transaction)
Cache the data in memory while it is displayed and manipulated by the user
Persist the data back to the server, wrapped in a transaction, with optimistic concurrency checks

This three step process always ensures that data is read and written at the same time, reducing over-head, and ensuring that all data is from a particular snapshot in time. Unfortunately it can be extremely cumbersome to write the necessary SQL to load not only a record, but all of its parents, pertinent children, and type-table entries necessary for a given web page or windows form to work properly. This rather difficult step has lead the way to the domination of the lazy-loading approach -- but it doesn't have to be so. If there was a convent way (i.e. not involving hundreds of lines of SQL) to load all the records related to a particular target record (or records), then step #1 would become simple.

All of which are reasons why I wrote a class to perform automated related record loading. Its called FriendlyLoader, and it can take a simple SQL query, for a single record (or more), and parse the relationships in the DataSet, loading all parent records and (optionally) child records as well. This allows you to easily pull back an entire chunk of the database, bring it to the client-side, manipulate it as need be, then persist it back again. But why stop with making the retrieval of data convenient? FriendlyLoader also has a companion class, Persistor, which persists data in appropriate order, inserting parents first, deleting children first, etc. It's a one-stop shop for convenient loading and persistence of DataSets!

So, on to syntax; basically the FriendlyLoader class simply consists of one public method (with various overrides). The most basic of these just accepts a DataSet to fill, a string containing the SQL command to fill it, a SqlConnection to the DB, and the names of the tables (in order) that it will be filling.

So, if you’re select command is:

select * from Customers

select * from Employees

Then your TableNames parameter should be “Customers,Employees”.

No matter which method you call, your SQL command will be executed, then all parent rows will be loaded that are necessary to enable constraints. The various other methods simply include different ways to load optional child rows:

' Load only parent rows

Public Shared Sub ClientFill(ByVal Ds As DataSet, ByVal Sql As String, ByVal Con As SqlConnection, ByVal TableNames As String)

' Load all posible child rows

Public Shared Sub ClientFill(ByVal Ds As DataSet, ByVal Sql As String, ByVal Con As SqlConnection, ByVal TableNames As String, ByVal LoadAllChildren As Boolean)

' Load only child rows that reside in tables named in the ChildTableNames parameter

Public Shared Sub ClientFill(ByVal Ds As DataSet, ByVal Sql As String, ByVal Con As SqlConnection, ByVal TableNames As String, ByVal ChildTableNames As String)

' Load only child rows that reside in tables that are related to parent tables by a relation in the RelationNames parameter

Public Shared Sub ClientFillByRelation(ByVal Ds As DataSet, ByVal Sql As String, ByVal Con As SqlConnection, ByVal TableNames As String, ByVal RelationNames As String)

' Load only child rows that reside in tables named in the list of tables

Public Shared Sub ClientFill(ByVal Ds As DataSet, ByVal Sql As String, ByVal Con As SqlConnection, ByVal TableNames As String, ByVal ChildTables As List(Of DataTable))

' Load only child rows that reside in tables that are related to parent tables by a relation in the list of relations

Public Shared Sub ClientFill(ByVal Ds As DataSet, ByVal Sql As String, ByVal Con As SqlConnection, ByVal TableNames As String, ByVal ChildRelations As List(Of DataRelation))

If you are still paying attention at this point, you may wonder why the LoadAllChildren=True doesn’t just load the entire database. The answer lies in the execution order. First your SQL statement is run, after that all children are loaded, then children’s children, and so on. Lastly all necessary parent rows are loaded. So theoretically, if all the records in your DB are descended from one parent record, then yes, it could load your whole database, but if your query is fairly specific, then relatively few rows will be loaded.

This is definitely something to watch out for when loading tables that are high in the genealogy of the DB. “select * from Customers” with LoadAllChildren=True would load all Invoices and InvoiceItems as well. So when loading a table that is known to have hundreds of thousands of children, it is probably best to use one of the methods that lets you more specifically identify which children should be loaded.

One other thing to note: type tables are treated differently than all other tables (object tables, link tables, etc). If a Table has its IsTypeTable property set to True, than all of its contents will be loaded with every query. This is done to accommodate constraints, and also to provide appropriate data to fill DropDown boxes.

Ok, that about does it for this post. There is a demonstration project in a zip file below, feel free to download it and play around. You will need a copy of the Northwind DB and a SQL server of some kind. Also, as usual, this code is provided as-is and without warranty. Use it if you like, but be sure to test thoroughly first!

Until next time,

Brent

Combining the DataGrid and DetailsView

2007-04-21T23:51:00Z

Web pages in data-driven web applications can pretty much be reduced down to two types: list pages and edit pages. If you can view a list of objects, create a new object, and update and delete existing objects, then you have all the components of a CRUD (create, read, update, delete). If you are a web developer, you have probably spent countless hours creating and re-creating similar pages to do these four tasks. Some upcoming tools (like BLINQ) look to help in this regard, but for now we are more or less on our own.

The introduction of the DataGrid in ASP.NET 2.0 was an exciting event. It took care of three steps of the CRUD for us: It can list objects, edit them, and delete them. Unfortunately it lacks any kind of create. For create operations, you have to redirect to a page with a DetailsView. And, truth-be-told, the update on the DataGrid control is pretty un-friendly, since if you have more than a few fields, it becomes be wider than any user's screen. So we are pretty much stuck making two pages for each object: a list/delete page with a GridView, and an create/update page with a DetailsView.

Wouldn't it be nice to halve your work by only having one page per object? That sort of thinking led me to create a ComboGrid control which, as the name implies, consists principally of a GridView, but switches to a DetailsView on edit and insert. This sweet little guy has saved me a bunch of work, and reduced the amount of web pages I've had to create. So I figured I would post the code, so that it can help other people as well. If it isn't directly helpful, then perhaps at least it will be a good starting point for others, or a useful insight into how these controls work behind the scenes.

A few notes:

The ComboMaster is the primary control
It instantiates the ComboGrid and ComboView and necessary
It is bindable in the same ways as either of the controls it represents (DataSource, DataSourceId)
It uses the Columns collection for its properties, as opposed to the Fields collection
It only supports TemplateFields for the moment. (Why? Because I haven't gotten around to any others yet)
I've been using this one for a bit, so it is pretty stable, but as usual, this is intended primarily for example purposes, and comes with no warranty

Download the Sample Code!

- Brent

.NET Sid2User and User2Sid

2007-04-20T21:22:00Z

Every once in a while, I have to resolve a SID (Windows Security IDentifier) to its corresponding user. Throughout the years I've had to do this with the LookupAccountName API call, be it through C++, VB 6, etc. I was doing it that way in .NET, until Steve Schofield altered me to a post on forums.iis.net this morning. A bit of sample code from that site made me realize there was a much easier way to do Sid2User and User2Sid resolution in .NET:

Public Shared Function SidToUser(ByVal Sid As String) As String

Dim Si As New SecurityIdentifier(SID)

Dim Nta As NTAccount = CType(Si.Translate(GetType(NTAccount)), NTAccount)

Return Nta.Value

End Function

Public Shared Function UserToSid(ByVal Username As String) As String

Dim Terms() As String = Split(Username, "\")

If Terms.Length > 1 Then Return UserToSid(Terms(0), Terms(1))

Return UserToSid("", Username)

End Function

Public Shared Function UserToSid(ByVal DomainName As String, ByVal Username As String) As String

Dim Nta As New NTAccount(DomainName, Username)

Dim Si As SecurityIdentifier = CType(Nta.Translate(GetType(SecurityIdentifier)), SecurityIdentifier)

Return Si.Value

End Function

Cheers,

- Brent

Strip HTML from a String

2007-04-19T17:12:00Z

While not exactly earth-shattering, I thought I would post some handy code. Recently, I had to save some data from a FreeTextBox, in both HTML and plain text form (so that it could be full-text indexed). It looks like there are a number of components out there, but I knew the IE HTML DOM was very liberal in its interpretation of HTML and therefor a good choice, not to mention it would be hard to find a system without this component installed. (Although you need to make an interop reference to mshtml.tlb)

Without further ado, the code:

Protected Function StipHtml(ByVal Html As String) As String

Dim Doc As New mshtml.HTMLDocument()

Dim d1 As mshtml.IHTMLDocument4 = Doc

Dim d2 As mshtml.IHTMLDocument4 = Doc

d2.write(Html)

d2.close()

Return Doc.body.innerText

End Function

Happy coding,

- Brent

DataSet to Database Persistance, Examined

2007-04-19T14:47:00Z

If you already know allot about it, go on an skip this blog. I thought I would cover this topic because I think it is not fully understood by allot of developers, and it is an essential step on the road to developing a full-fledged Data Access Layer (can you guess where this series of posts is headed?).

Negative Indexes

One of the first steps to writing a clean DAL starts with the DataSets themselves. It is a good idea to set all your auto-increment columns to IncrementSeed=-1, IncrementStep=-1. This way, as you add rows to your tables, their PK's will count: -1, -2, -3. This prevents the rows you are adding in your DataSet from conflicting with the Primary Keys of any new rows added back on the Database server.

Concurrency Checks

The SqlDataAdapter class has a wonderful feature, which is enabled by default, where it examines the number of affected rows reported by SQL. If the number of rows actually updated (always zero or one), doesn't match the number the DataAdapter was expecting (always one), it throws an exception! This should prevent unexpected results when you update query fails. You can also use it to deliberately throw a ConcurrencyException if the data has changed by adding a where clause to your update query:

set @NewDate=getdate()

update Employees set

FirstName=@FirstName,

LastName=@LastName,

LastUpdatedDate=@NewDate

where EmployeeId=@EmployeeId

and LastUpdateDate=@LastUpdatedDate

In this case, if the Employee.LastUpdateDate in the DB doesn't equal your DataRow.LastUpdateDate, then the query will return zero rows, and the DataAdapter will throw an exception.

Transactions

It might be obvious by now, but since the DataAdapter might throw a ConcurrencyException half-way through your update process, you may want to wrap the process in a client-side transaction. That way if any of your updates (or inserts or deletes for that matter) fail, then everything will be rolled back. This is pretty simple in ADO.NET 2.0: Just create a new SqlTransaction object, and pass it to all of your SqlCommands.

Output Parameters

You may have noticed in the above example, that we are updating the DB with the @NewDate parameter, but how might we get that back into our DataSet? If we don't, then our next update will fail, but surely we don't want to manually keep a list of all updated rows, and select() them all back from the DB? Well, the DataAdapter makes this easy for us as well. When you add your SqlParameter object to the SqlCommand, you can set it update as an "output" parameter (or even InputOutput). Then you can define the parameter as "output" in your stored procedure, and whatever value it has will be inserted back into your DataRow when the sproc completes. (Same thing goes for non-sproc commands). This is especially handy on inserts. For your insert queries you can simply append: "set @OutputId = @@Identity", and your -12345 PK in the DataSet will be automatically updated to 3725 or whatever the auto-increment in the DB happens to be set to. Pretty cool huh? Now you see why we use negative keys on the client-side.

UpdateAction = Cascade

We've reviewed some pretty cool stuff, but this last bit is killer. Going back to one more DataSet based thing, on all your relationships, you'll want to set UpdateRule = Cascade. The reason for this, is that when your Employee.EmployeeId=-1 is inserted into the DB, and comes back as an output parameter as Employee.EmployeeId=3725, the UpdateRule=Cascade will go around to all related tables, and change the FK from -1 to 3725 on those as well. So you can insert an Employee into your DataSet, then insert a bunch of EmployeeRole records, then persist them to the DB all at the same time, and when you persist is finished, they will have all gone from fairy-land-style negative keys to real-world values auto-magically! And all your DataSet constraints will remain intact! How cool is that?

DataRowVersion

Ok, although maybe not as cool as the last one, the SqlParameter.DataRowVersion is sometimes handy. Lets say you don't have a handy Employee.LastUpdatedDate field in your table to check for concurrency, but you still want to make sure that no one has updated the record in the DB while you were looking at it. One interesting feature of DataRows is that they track not only what value they have now, but also what value they originally had when you loaded them from the DB. You can create SqlParameters with DataRowVersion=Original, and pass them into your sproc in the where clause. You will be checking many more fields than is probably necessary, but if any of them don't match, then you can still get the same optimistic concurrency as with UpdatedDate (or Timestamp), but without changing your DB schema.

Persistence Order

I might be going a little too far here, but for the sake of completeness, I will mention it. With typed DataSets, you don't even need to worry about persistence order (i.e. insert Employees first, then insert EmployeeRoles). Since all the relations should have been brought over from the database into your DataSet by the designer, you should have all the info you need to determine parent/child relations, and insert/update/delete in the correct order: Insert parent rows first, update parent rows second (* update, see below), then delete child rows last. As easy as this sounds, there is actually a good bit of work to it, which I won't get into here. Instead I will focus on both loading and persistence in my next post, as well as post sample code for a Data Acess Layer that should make both a piece of cake. What's that? You don't want to use proprietary code in your application's DAL? Well if you can sit tight for a little while, ADO.NET 3.5 should include a new feature where it can automatically loop through an entire DataSet persisting rows in the correct order!

Sorry, no code this time :-(

Until next time,

- Brent

Update 6/13/2007:

In the article above I said to "update parent rows second". This was how I had my code working until I ran into a situation where two rows were each other's parents. This threw everything into an infinite loop. If updating PrimaryKeys were possible in DataSets, then there would be problems, but since the ADO guys had the foresight not to allow this, modified rows should be able to be persisted in any order (and should be to avoid infinite loops). More correctly worded, the aforementioned sentence should omit the word "parent" to read: "update modified rows second".

Programmatically Generate Typed DataSets

2007-04-18T00:20:00Z

As perhaps you may have gathered from my DataSetSource post, I think typed DataSets are the bee's knees. As a general coding practice, I like to write the code-behind-the-code, rather than the code itself, and typed DataSet's allow me to do this, so I like them very much. That being said, they took a big hit with Visual Studio.NET 2005. I don't know why exactly, perhaps because of deadlines, the typed DataSet creator in VS 2005 has a few problems. I've had it fail to import relations, fail to find new columns, and even dragging a DataTable to a new location can take 15-20 seconds in a large dataset! All in all, VS 2005 was a step backward for this particular tool.

In order to continue using these wonderful classes, despite the idiosyncrasies of the new tool, I've created my own typed DataSet generation utility. It queries the schema of a SQL DB, allows you to select which tables you want to export, then creates the appropriate .vb and .xsd files. Best of all, having the source code, it can be modified to be scripted (perhaps as part of a build script?), run as often as necessary, and modified to suit particular tastes. For example, I have turned GetChildRows() into a property, and used Nullable(Of T) to represent null values, instead of the old "IsMyCollumnNull()" methods.

It's called the "Enhanced Generator of Advanced Data Sets", and it looks like this:

To use it, simply:

Enter the path you want it to create its classes under
Enter the connection string to your database
Enter the DataSetName you want your new dataset to be called
Click "Get Tables" - select the tables you want
Click "Generate"

Please Note:

Note that this code is for example purposes only, its meant as a base for you to develop off of, and an example of how to use the CodeDom
You may need to delete / rename / exclude the .xsd file, because Visual Studio tends to want to generate its own code, which will conflict with Egads

Download Egads!

ASP.NET Bindable DataSet DataSource

2007-04-13T03:01:00Z

O/R mappers have gotten allot of press lately, and for good reason. They take away much of the busy work of building a data access layer (DAL). They work fantastically for smaller projects, and as of Visual Studio 2007, they will receive true legitimacy with support from Microsoft (via LINQ-to-SQL). Occasionally I run into various limitations however, such as the inability to perform advanced operations like recursing relationships. Fortunately, Object Models have a more advanced, if not slightly trickier to implement cousin: Typed-Datasets.

Typed-Datasets have the features of a DataSet (run-time parsable schema, constraint enforcement, xml exportability), as well as the features that make O/R generated object models handy (compile-time parsable schema, i.e. intellisense). Unfortunately DataSets haven't received as much attention in databinding as have other object models. For one thing, they don't have an associated DataSource. The ObjectDataSource doesn't work with them for many reasons, least of which because DataRow's don't have a default constructor. The SqlDataSource is great, but it accesses the database directly, making a middle tier impossible.

Not wanting to see DataSets left feeling isolated and unwanted, I've created a bindable DataSource for them, thanks in part to example code in a blog post by Manuel Abadia. There are just a few things that bear explaining first. Unlike most O/R mapper code, the DsDataSource is designed for pre-loading (vs. lazy loading). The idea being to bind all DsDataSources on the page to different tables within a single DataSet, and to populate that DataSet completely and immediately upon page load:

Partial Class _Default

Inherits System.Web.UI.Page

' First, we create a new dataset attached to the page instance:

Protected pDs As New ExampleDs

' Right off the bat, we want to populate the DataSet from the DB:

Protected Sub Page_Load() Handles Me.Load

ExampleDs.EmployeeDataTable.LoadAll(Ds)

End Sub

' Lastly, we need to expose the DataSet as a property

’so that the DsDataSource controls can access it:

Public ReadOnly Property Ds() As ExampleDs

Get

Return pDs

End Get

End Property

End Class

The .aspx code looks like this:

<ow:DsDataSource ID="dsEmployee" runat="server"

<!—Property from .vb code -->

DataSetProperty="Ds" TableName="Employee"

TypeName="ExampleDs"

DataObjectTypeName="ExampleDs+Employee" />

<!—- Then, just set the DataSourceId on the GridView: -->

<asp:GridView runat="server" ID="gvEmployee" DataSourceID="dsEmployee"

AutoGenerateColumns="false" DataKeyNames="EmployeeId">

With no SelectMethod, as shown, the DsDataSource will display all rows in the target table. If necessary, you can specify a SelectMethod to return a subset of rows. The InsertMethod and UpdateMethod have been rolled into a common "Persist" method, and the obligatory DeleteMethod property is available as well. Aside from these minor differences, the DsDataSource functions much like the ObjectDataSource. It even attempts to use reflection to set the row properties if available, so that you can put business logic in your typed DataRow classes.

Hopefully that will be enough to get you coding, and I have an example available for download as well.

Happy coding!

- Brent

ASP.NET Custom ObjectDataSource

2007-04-13T02:36:00Z

I suppose it would be debatable, but I would venture to suggest that with the ObjectDataSource, ASP.NET has caught up with, and surpassed Windows Forms in terms of databinding. Unfortunately, it has one major problem that makes it incompatible with many O/R mappers: It only supports default (parameter-less) constructors.

The solution? Why make your own DataSource of course! While I looked into this briefly, the MSDN documentation is mostly from a usage perspective. It does little to describe how the base DataSource and DataSourceView classes work. Recently I found a fantastic blog post by Manuel Abadia that does a great job of explaining their interaction, and exactly what needs to be done to create your own ObjectDataSource. Using his advice, I was able to create a version of the ObjectDataSource that can call a constructor, and pass it pertinent parameters (like primary key information). This simple change allows the ObjectDataSource to work flawlessly with most code generated by O/R mappers!

If you like, you can download the custom ObjectDataSource, and example code.

Hopefully this code will save you as much time as it has saved me!

- Brent

Automatically Generating Audit Tables

2007-04-13T01:24:00Z

Developers,

Have you ever been in a situation where data mysteriously goes awry in production? Your boss comes running in, asking "Why are the totals off on the Johnson account? What's wrong with your code!". You look at the data. Indeed it is incorrect. You search the code, you can't find any bugs. You even try some common operations in your test environment. Still you can't figure out why the data is incorrect. Was it invalid user input? A bug in your code? A new DBA with an overzealous update query? One might never know....

Unless that is, you have audit tables; tables that look just like your regular tables, but log every change in your DB! If you had audit tables, in the example above, you would just tell your boss to hang on a minute, run "select * from audit_account where AccountName='Johnson'" and five seconds later you could tell him that the new girl in marketing entered invalid data on 4/11/2007 at 10:00am local time. That sure makes for a better answer than a shrug and an "I donno..."

But, if you've ever used audit tables, you know what a pain they are to create and maintain. Each time you add a new column to a production table, you need to remember to update your audit table, or else you will be missing valuable information. Worse yet, if you remove a column, you will get an exception when you try to insert new data :-S. All in all they are invaluable when debugging, but a pain in the butt to implement.

Unless that is you have an audit table generator. What? You don't? For shame. There are probably some good ones out there -- I wouldn't know, I've never used one, and the ones I saw that looked good were pricey and overly-complicated. Being a DIY kind of guy, I wrote my own, and now I figured I would share it with you :-)

If you are merely here for the code, I suggest you scroll to the download link now. If you are a coder, or are for some incomprehensible way interested in the generation of audit tables, by all means, continue.

Explanation of Code

I'll try to avoid a long and tedious line by line analysis, but basically it works like this:

The program connects to a database, and runs a query against the sysobjects table, and retrieves all the table names for a given DB. If you are not familiar, sysobjects is a very nifty "table of tables" and contains all the information about the structure of the database. Yes that's correct, T-SQL stores the schema of your database, *in* a database. Mind-bending huh?
The list of tables gets populated into a ListBox, allowing users to select which ones they want to audit.
The user hits "Generate" and the app loops through its list of tables, queries sysobjects for their schema, and writes a matching audit table.
The program adds triggers to the target table for insert, update, and delete.
Whenever a change to a target table is made, the trigger inserts a copy of the data into the audit table.

Usage Notes

Just paste in your connection string, select your tables, and hit Generate!
Compile with Visual Studio .NET 2005
This code is intended for example purposes only
If you do run it against a database, try it on a test DB first, and verify that it doesn't cause any unexpected results.

Screenshot:

Download Auditor

Happy coding!

- Brent

.NET and DFSR

2007-04-09T18:54:00Z

Recently I was tasked to create some automated tools that could configure DFSR. If you are not familiar with it, Microsoft has combined DFS (distributed file system) and FRS (file replication service); two fantastic technologies that give Windows Server users the ability to have redundant network drives. The combination of services is now called DFSR, and was a pretty logical and intelligent move. Unfortunately its seems that Microsoft didn't get around to creating an SDK for this technology before they sent it out the door, which pretty much leaves users with two options:

The DFS / FRS API's (http://msdn2.microsoft.com/en-us/library/aa379538.aspx). While these seem to work well enough, they are more or less legacy, and don't have the cool new additions of DFSR, and since they have no managed namespace equivalent, they are a little cumbersome to use from .NET.
The new DFSR WMI provider (http://msdn2.microsoft.com/en-us/library/aa380990.aspx). This is quite handy because it is easily accessible from managed code, and it contains all the latest DFSR stuff. The problem is that it seems to be read-only! (or at least I couldn't find any write methods, and calls to ManagementObject.Put() fail with a generic exception).

There is however a MMC snap-in included with Windows Server 2003, so there must be an API out there somewhere right? After some quick poking around its possible to see that the dfsmgnt.msc file loads DfsMgmt.dll, which in turn loads DfsObjectModel.dll. As the name implies, this .dll has a beautifully constructed object model for .NET. One look with Lutz Roeder's fantastic program Reflector (http://www.aisto.com/roeder/dotnet/) quickly reveals a logical class system neatly representing the underlying DFSR objects. The only problem being that all the classes are marked as "friend", so we can't simply import the assembly and use it ourselves!

Luckily what Microsoft taketh-away, they also giveth-back. Using reflection, it is possible to instantiate classes and call methods, even if they aren't exposed. Since there seems to be no clean alternative method for accessing DFS through .NET, I've created a some simple wrapper classes which re-expose the friend methods in this .dll.

The code listed below is by no means considered to be a complete recreation, but hopefully it will be a good starting point for others to build on. Bear in mind you will need to:

Create a DFS root to start with
Have a domain
Have at least one server with DFS installed (two would make more sense ;-)
Compile the small forms app with Visual Studio .NET 2005
Copy the DfsObjectModel.dll file from a Windows 2003 server into the \Bin\Debug folder prior to running this app
Update the textbox values to reflect the appropriate values for your environment
Bear in mind this is example code, not intended for production deployment!

Download DFS.zip

- Brent