Data Organizer

The Data Organizer application was created to save a user’s time by providing a hierarchical scratch-pad that allows the creation and organization of textual data, without the need for repeating the steps of creating folders and files or editing and saving those files in a text editor window. It automatically saves the content of the text window when switching nodes (control-S can also be used to save the current content). The standard “edit” key combinations are also supported, and always apply to the text window (rather than the tree control): Select All (control-A), Copy (control-C), and Paste (control-V). Selecting a word with a double-click will automatically disregard a trailing space character, so that passwords or usernames can be pasted from the clipboard without errors.

The Data Organizer application uses a folder named “data” to store all data (created in the folder where the EXE is installed), arranging it in a tree of folders containing text files:

This design makes it possible to safeguard the contents of the “data” folder tree using a source control system.

The Data Organizer application supports the renaming of nodes, which changes the underlying folder names:

Feel free to Download and try Data Organizer out.

The Source Code is also available if you prefer reviewing the source before building and using Data Organizer.

Happy organizing!

 

Purify That XML

Processing XML using XPATH or XSLT is much more difficult when the XML contains namespaces; sometimes the namespaces are a very necessary part of the data structure and should be left intact; it is when namespaces are used arbitrarily (and contribute nothing to the underlying data structure) that they become a problem. For the latter case, I have developed this XML purifier routine, which processes an XML string prior to parsing, translating, or even viewing the content. Even the presence of only the “default” namespace is enough to break MSXML XPATH parsing as well as MSXML XSLT transformations or using the .NET XslCompiledTransform class.

using System;
using System.Text.RegularExpressions;
namespace Utilities
{
    public class XmlPurifier
    {
        public static string Purify(string input)
        {
            string output = input;
             
            //
            // remove all "xmlns" attribute assignments:
            //
 
            string xmlns = "(?i)xmlns\\s*=\\s*\\\"[^\\s]*\\\"";
             
            Regex regex = new Regex(xmlns);
 
            MatchCollection nsCollection = regex.Matches(output);
             
            foreach (Match match in nsCollection)
            {
                output = output.Replace(match.Value, String.Empty); 
            }                      
             
            //
            // find and remove all namespace references:
            //
 
            string nsr = "(?i)xmlns:(([a-z0-9_]+))\\s*=\\s*\\\"[^\\s]*\\\"";
             
            Regex nsrx = new Regex(nsr);
 
            MatchCollection nsrCollection = nsrx.Matches(output);
 
            foreach (Match match in nsrCollection)
            {
                // remove the namespace reference:
                output = output.Replace(match.Value, String.Empty);
 
                // remove all namespace prefixes:
                output = output.Replace(match.Groups[1].ToString() + ":", String.Empty);
            }
             
            return output;
        }
    }
}

Stored Procedure Design

When creating stored procedures, I like to use in and out parameters that report possible internal problems. I also like to design a simple “create record if it does not exist and return ID” procedure:

USE [THE_DATABASE]
 
GO
 
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ReadOrAddUser]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[ReadOrAddUser]
GO
 
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE PROCEDURE [dbo].[ReadOrAddUser]
(
 @in_GroupID integer,
 @in_UserName nvarchar(50),
 @in_UserData nvarchar(MAX),
 @out_UserID integer OUTPUT,
 @out_error_code integer OUTPUT,
 @out_error_message nvarchar(2000) OUTPUT
)
AS
BEGIN
 
 SET NOCOUNT ON
 
 -- populate standard variables
 SELECT @out_error_code = 0,
 @out_error_message = NULL
  
 Select @out_UserID = [ID]
 from [dbo].[Users]
 where [UserName] = @in_UserName AND [GroupID] = @in_GroupID
  
 BEGIN TRANSACTION
 
 BEGIN TRY
 
 -- if Group User is not in table, insert. Return ID in all cases.
 IF ( @out_UserID is null )
 BEGIN
 
 -- insert into table
 INSERT INTO dbo.Users
 (
 GroupID,
 UserName,
 UserData
 )
 VALUES
 (
 @in_GroupID,
 @in_UserName,
 @in_UserData
 )
 
 -- set parameter to value of new record
 SET @out_UserID = SCOPE_IDENTITY()
 
 END
 
 -- else update the data and return the ID.
 ELSE
 BEGIN
 UPDATE [dbo].[Users]
 SET [UserData] = @in_UserData
 WHERE [ID] = @out_UserID
 END
 
 END TRY
 BEGIN CATCH
 
 SET @out_UserID = NULL
 SET @out_error_code = 5000
 SET @out_error_message = ERROR_PROCEDURE() + ': ' + CAST(ERROR_NUMBER() AS VARCHAR(10)) + ' (line ' + CAST(ERROR_LINE() AS VARCHAR(10)) + ') ' + ERROR_MESSAGE()
  
 ROLLBACK TRANSACTION
 RETURN 1
 
 END CATCH
 
 COMMIT TRANSACTION
 
 RETURN 0
 
END
 
GO

C# code for calling the above procedure:

private const string OUT_ERROR_CODE = "@out_error_code";
private const string OUT_ERROR_MESSAGE = "@out_error_message";
 
public int ReadOrAddUser(
    string userName,
    string userData,
    int GroupID)
{
    SqlConnection con = new SqlConnection(_connString);
    SqlCommand cmd = new SqlCommand("ReadOrAddUser", con);
 
    addInParam(ref cmd, "@in_UserName", userName);
 
    addInParam(ref cmd, "@in_UserData", userData);
 
    addInParam(ref cmd, "@in_GroupID", GroupID);
 
    return ExecuteProcedure(ref con, ref cmd, "@out_UserID");
}
 
 
// supporting code:
 
public void addInParam(
    ref SqlCommand cmd,
    string name,
    string value)
{
    cmd.Parameters.Add(name, SqlDbType.Text);
    cmd.Parameters[name].Value = value;
}
 
public void addInParam(
    ref SqlCommand cmd,
    string name,
    int value)
{
    cmd.Parameters.Add(name, SqlDbType.Int);
    cmd.Parameters[name].Value = value;
}
 
public void addOutIntParam(
    ref SqlCommand cmd,
    string name)
{
    cmd.Parameters.Add(new SqlParameter(name, SqlDbType.Int));
    cmd.Parameters[name].Direction = ParameterDirection.Output;
}
 
public int ExecuteProcedure(
        ref SqlConnection con,
        ref SqlCommand cmd,
        string outVarName = "")
{
    bool getResult = (outVarName.Length > 0);
 
    int result = 0;
 
    cmd.CommandType = CommandType.StoredProcedure;
 
    if (getResult)
    {
        addOutIntParam(ref cmd, outVarName);
    }
 
    addOutIntParam(ref cmd, OUT_ERROR_CODE);
 
    cmd.Parameters.Add(new SqlParameter(OUT_ERROR_MESSAGE, SqlDbType.NChar));
    cmd.Parameters[OUT_ERROR_MESSAGE].Direction = ParameterDirection.Output;
    cmd.Parameters[OUT_ERROR_MESSAGE].Size = 2000;
 
    con.Open();
 
    cmd.ExecuteNonQuery();
 
    int code = (int)cmd.Parameters[OUT_ERROR_CODE].Value;
 
    if (code != 0)
    {
        string message = (string)cmd.Parameters[OUT_ERROR_MESSAGE].Value;
 
        throw new Exception(message);
    }
    else if (getResult)
    {
        result = (int)cmd.Parameters[outVarName].Value;
    }
 
    con.Close();
 
    return result;
}