Archive

Archive for September, 2012

Adding and accessing custom sections in your C# App.config

September 25th, 2012 33 comments

Update (Feb 10, 2016): I found a NuGet package called simple-config that allows you to dynamically bind a section in your web/app.config file to a strongly typed class without having to write all of the boiler-plate code that I show here. This may be an easier solution for you than going through the code I show below in this post.

So I recently thought I’d try using the app.config file to specify some data for my application (such as URLs) rather than hard-coding it into my app, which would require a recompile and redeploy of my app if one of our URLs changed.  By using the app.config it allows a user to just open up the .config file that sits beside their .exe file and edit the URLs right there and then re-run the app; no recompiling, no redeployment necessary.

I spent a good few hours fighting with the app.config and looking at examples on Google before I was able to get things to work properly.  Most of the examples I found showed you how to pull a value from the app.config if you knew the specific key of the element you wanted to retrieve, but it took me a while to find a way to simply loop through all elements in a section, so I thought I would share my solutions here.

Due to the popularity of this post, I have created a sample solution that shows the full implementation of both of the methods mentioned below.

Simple and Easy

The easiest way to use the app.config is to use the built-in types, such as NameValueSectionHandler.  For example, if we just wanted to add a list of database server urls to use in my app, we could do this in the app.config file like so:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <configSections>
        <section name="ConnectionManagerDatabaseServers" type="System.Configuration.NameValueSectionHandler" />
    </configSections>
    <startup>
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
    </startup>
    <ConnectionManagerDatabaseServers>
        <add key="localhost" value="localhost" />
        <add key="Dev" value="Dev.MyDomain.local" />
        <add key="Test" value="Test.MyDomain.local" />
        <add key="Live" value="Prod.MyDomain.com" />
    </ConnectionManagerDatabaseServers>
</configuration>

And then you can access these values in code like so:

string devUrl = string.Empty;
var connectionManagerDatabaseServers = ConfigurationManager.GetSection("ConnectionManagerDatabaseServers") as NameValueCollection;
if (connectionManagerDatabaseServers != null)
{
    devUrl = connectionManagerDatabaseServers["Dev"].ToString();
}

Sometimes though you don’t know what the keys are going to be and you just want to grab all of the values in that ConnectionManagerDatabaseServers section.  In that case you can get them all like this:

// Grab the Environments listed in the App.config and add them to our list.
var connectionManagerDatabaseServers = ConfigurationManager.GetSection("ConnectionManagerDatabaseServers") as NameValueCollection;
if (connectionManagerDatabaseServers != null)
{
    foreach (var serverKey in connectionManagerDatabaseServers.AllKeys)
    {
        string serverValue = connectionManagerDatabaseServers.GetValues(serverKey).FirstOrDefault();
        AddDatabaseServer(serverValue);
    }
}

And here we just assume that the AddDatabaseServer() function adds the given string to some list of strings.

One thing to note is that in the app.config file, <configSections> must be the first thing to appear in the <configuration> section, otherwise an error will be thrown at runtime. Also, the ConfigurationManager class is in the System.Configuration namespace, so be sure you have

using System.Configuration

at the top of your C# files, as well as the “System.Configuration” assembly included in your project’s references.

So this works great, but what about when we want to bring in more values than just a single string (or technically you could use this to bring in 2 strings, where the “key” could be the other string you want to store; for example, we could have stored the value of the Key as the user-friendly name of the url).

More Advanced (and more complicated)

So if you want to bring in more information than a string or two per object in the section, then you can no longer simply use the built-in System.Configuration.NameValueSectionHandler type provided for us.  Instead you have to build your own types.  Here let’s assume that we again want to configure a set of addresses (i.e. urls), but we want to specify some extra info with them, such as the user-friendly name, if they require SSL or not, and a list of security groups that are allowed to save changes made to these endpoints.

So let’s start by looking at the app.config:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <configSections>
        <section name="ConnectionManagerDataSection" type="ConnectionManagerUpdater.Data.Configuration.ConnectionManagerDataSection, ConnectionManagerUpdater" />
    </configSections>
    <startup>
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
    </startup>
    <ConnectionManagerDataSection>
        <ConnectionManagerEndpoints>
            <add name="Development" address="Dev.MyDomain.local" useSSL="false" />
            <add name="Test" address="Test.MyDomain.local" useSSL="true" />
            <add name="Live" address="Prod.MyDomain.com" useSSL="true" securityGroupsAllowedToSaveChanges="ConnectionManagerUsers" />
        </ConnectionManagerEndpoints>
    </ConnectionManagerDataSection>
</configuration>

The first thing to notice here is that my section is now using the type “ConnectionManagerUpdater.Data.Configuration.ConnectionManagerDataSection” (the fully qualified path to my new class I created) “, ConnectionManagerUpdater” (the name of the assembly my new class is in).  Next, you will also notice an extra layer down in the <ConnectionManagerDataSection> which is the <ConnectionManagerEndpoints> element.  This is a new collection class that I created to hold each of the Endpoint entries that are defined.  Let’s look at that code now:

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ConnectionManagerUpdater.Data.Configuration
{
    public class ConnectionManagerDataSection : ConfigurationSection
    {
        /// <summary>
        /// The name of this section in the app.config.
        /// </summary>
        public const string SectionName = "ConnectionManagerDataSection";

        private const string EndpointCollectionName = "ConnectionManagerEndpoints";

        [ConfigurationProperty(EndpointCollectionName)]
        [ConfigurationCollection(typeof(ConnectionManagerEndpointsCollection), AddItemName = "add")]
        public ConnectionManagerEndpointsCollection ConnectionManagerEndpoints { get { return (ConnectionManagerEndpointsCollection)base[EndpointCollectionName]; } }
    }

    public class ConnectionManagerEndpointsCollection : ConfigurationElementCollection
    {
        protected override ConfigurationElement CreateNewElement()
        {
            return new ConnectionManagerEndpointElement();
        }

        protected override object GetElementKey(ConfigurationElement element)
        {
            return ((ConnectionManagerEndpointElement)element).Name;
        }
    }

    public class ConnectionManagerEndpointElement : ConfigurationElement
    {
        [ConfigurationProperty("name", IsRequired = true)]
        public string Name
        {
            get { return (string)this["name"]; }
            set { this["name"] = value; }
        }

        [ConfigurationProperty("address", IsRequired = true)]
        public string Address
        {
            get { return (string)this["address"]; }
            set { this["address"] = value; }
        }

        [ConfigurationProperty("useSSL", IsRequired = false, DefaultValue = false)]
        public bool UseSSL
        {
            get { return (bool)this["useSSL"]; }
            set { this["useSSL"] = value; }
        }

        [ConfigurationProperty("securityGroupsAllowedToSaveChanges", IsRequired = false)]
        public string SecurityGroupsAllowedToSaveChanges
        {
            get { return (string)this["securityGroupsAllowedToSaveChanges"]; }
            set { this["securityGroupsAllowedToSaveChanges"] = value; }
        }
    }
}

So here the first class we declare is the one that appears in the <configSections> element of the app.config.  It is ConnectionManagerDataSection and it inherits from the necessary System.Configuration.ConfigurationSection class.  This class just has one property (other than the expected section name), that basically just says I have a Collection property, which is actually a ConnectionManagerEndpointsCollection, which is the next class defined.

The ConnectionManagerEndpointsCollection class inherits from ConfigurationElementCollection and overrides the required fields.  The first tells it what type of Element to create when adding a new one (in our case a ConnectionManagerEndpointElement), and a function specifying what property on our ConnectionManagerEndpointElement class is the unique key, which I’ve specified to be the Name field.

The last class defined is the actual meat of our elements.  It inherits from ConfigurationElement and specifies the properties of the element (which can then be set in the xml of the App.config).  The “ConfigurationProperty” attribute on each of the properties tells what we expect the name of the property to correspond to in each element in the app.config, as well as some additional information such as if that property is required and what it’s default value should be.

Finally, the code to actually access these values would look like this:

// Grab the Environments listed in the App.config and add them to our list.
var connectionManagerDataSection = ConfigurationManager.GetSection(ConnectionManagerDataSection.SectionName) as ConnectionManagerDataSection;
if (connectionManagerDataSection != null)
{
    foreach (ConnectionManagerEndpointElement endpointElement in connectionManagerDataSection.ConnectionManagerEndpoints)
    {
        var endpoint = new ConnectionManagerEndpoint() { Name = endpointElement.Name, ServerInfo = new ConnectionManagerServerInfo() { Address = endpointElement.Address, UseSSL = endpointElement.UseSSL, SecurityGroupsAllowedToSaveChanges = endpointElement.SecurityGroupsAllowedToSaveChanges.Split(',').Where(e => !string.IsNullOrWhiteSpace(e)).ToList() } };
        AddEndpoint(endpoint);
    }
}

This looks very similar to what we had before in the “simple” example.  The main points of interest are that we cast the section as ConnectionManagerDataSection (which is the class we defined for our section) and then iterate over the endpoints collection using the ConnectionManagerEndpoints property we created in the ConnectionManagerDataSection class.

Also, some other helpful resources around using app.config that I found (and for parts that I didn’t really explain in this article) are:

How do you use sections in C# 4.0 app.config? (Stack Overflow) <== Shows how to use Section Groups as well, which is something that I did not cover here, but might be of interest to you.

How to: Create Custom Configuration Sections Using Configuration Section (MSDN)

ConfigurationSection Class (MSDN)

ConfigurationCollectionAttribute Class (MSDN)

ConfigurationElementCollection Class (MSDN)

I hope you find this helpful.  Feel free to leave a comment.  Happy Coding!

Categories: .NET, C# Tags: , , , ,

SQL Server script commands to check if object exists and drop it

September 14th, 2012 6 comments

Over the past couple years I’ve been keeping track of common SQL Server script commands that I use so I don’t have to constantly Google them.  Most of them are how to check if a SQL Server object exists before dropping it.  I thought others might find these useful to have them all in one place, so here you go:

--===============================
-- Create a new table and add keys and constraints
--===============================
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'TableName' AND TABLE_SCHEMA='dbo')
BEGIN
	CREATE TABLE [dbo].[TableName] 
	(
		[ColumnName1] INT NOT NULL, -- To have a field auto-increment add IDENTITY(1,1)
		[ColumnName2] INT NULL,
		[ColumnName3] VARCHAR(30) NOT NULL DEFAULT('')
	)

	-- Add the table's primary key
	ALTER TABLE [dbo].[TableName] ADD CONSTRAINT [PK_TableName] PRIMARY KEY NONCLUSTERED
	(
		[ColumnName1], 
		[ColumnName2]
	)
	
	-- Add a foreign key constraint
	ALTER TABLE [dbo].[TableName] WITH CHECK ADD CONSTRAINT [FK_Name] FOREIGN KEY
	(
		[ColumnName1], 
		[ColumnName2]
	)
	REFERENCES [dbo].[Table2Name] 
	(
		[OtherColumnName1], 
		[OtherColumnName2]
	)
	
	-- Add indexes on columns that are often used for retrieval
	CREATE INDEX IN_ColumnNames ON [dbo].[TableName]
	(
		[ColumnName2],
		[ColumnName3]
	)
	
	-- Add a check constraint
	ALTER TABLE [dbo].[TableName] WITH CHECK ADD CONSTRAINT [CH_Name] CHECK (([ColumnName] >= 0.0000))
END

--===============================
-- Add a new column to an existing table
--===============================
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA='dbo'
	AND TABLE_NAME = 'TableName' AND COLUMN_NAME = 'ColumnName')
BEGIN
	ALTER TABLE [dbo].[TableName] ADD [ColumnName] INT NOT NULL DEFAULT(0)
	
	-- Add a description extended property to the column to specify what its purpose is.
	EXEC sys.sp_addextendedproperty @name=N'MS_Description', 
		@value = N'Add column comments here, describing what this column is for.' , 
		@level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',
		@level1name = N'TableName', @level2type=N'COLUMN',
		@level2name = N'ColumnName'
END

--===============================
-- Drop a table
--===============================
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'TableName' AND TABLE_SCHEMA='dbo')
BEGIN
	EXEC('DROP TABLE [dbo].[TableName]')
END

--===============================
-- Drop a view
--===============================
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'ViewName' AND TABLE_SCHEMA='dbo')
BEGIN
	EXEC('DROP VIEW [dbo].[ViewName]')
END

--===============================
-- Drop a column
--===============================
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA='dbo'
	AND TABLE_NAME = 'TableName' AND COLUMN_NAME = 'ColumnName')
BEGIN

	-- If the column has an extended property, drop it first.
	IF EXISTS (SELECT * FROM sys.fn_listExtendedProperty(N'MS_Description', N'SCHEMA', N'dbo', N'Table',
				N'TableName', N'COLUMN', N'ColumnName'))
	BEGIN
		EXEC sys.sp_dropextendedproperty @name=N'MS_Description', 
			@level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',
			@level1name = N'TableName', @level2type=N'COLUMN',
			@level2name = N'ColumnName'
	END

	EXEC('ALTER TABLE [dbo].[TableName] DROP COLUMN [ColumnName]')
END

--===============================
-- Drop Primary key constraint
--===============================
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE='PRIMARY KEY' AND TABLE_SCHEMA='dbo'
		AND TABLE_NAME = 'TableName' AND CONSTRAINT_NAME = 'PK_Name')
BEGIN
	EXEC('ALTER TABLE [dbo].[TableName] DROP CONSTRAINT [PK_Name]')
END

--===============================
-- Drop Foreign key constraint
--===============================
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE='FOREIGN KEY' AND TABLE_SCHEMA='dbo'
		AND TABLE_NAME = 'TableName' AND CONSTRAINT_NAME = 'FK_Name')
BEGIN
	EXEC('ALTER TABLE [dbo].[TableName] DROP CONSTRAINT [FK_Name]')
END

--===============================
-- Drop Unique key constraint
--===============================
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE='UNIQUE' AND TABLE_SCHEMA='dbo'
		AND TABLE_NAME = 'TableName' AND CONSTRAINT_NAME = 'UNI_Name')
BEGIN
	EXEC('ALTER TABLE [dbo].[TableNames] DROP CONSTRAINT [UNI_Name]')
END

--===============================
-- Drop Check constraint
--===============================
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE='CHECK' AND TABLE_SCHEMA='dbo'
		AND TABLE_NAME = 'TableName' AND CONSTRAINT_NAME = 'CH_Name')
BEGIN
	EXEC('ALTER TABLE [dbo].[TableName] DROP CONSTRAINT [CH_Name]')
END

--===============================
-- Drop a column's Default value constraint
--===============================
DECLARE @ConstraintName VARCHAR(100)
SET @ConstraintName = (SELECT TOP 1 s.name FROM sys.sysobjects s JOIN sys.syscolumns c ON s.parent_obj=c.id
						WHERE s.xtype='d' AND c.cdefault=s.id 
						AND parent_obj = OBJECT_ID('TableName') AND c.name ='ColumnName')

IF @ConstraintName IS NOT NULL
BEGIN
	EXEC('ALTER TABLE [dbo].[TableName] DROP CONSTRAINT ' + @ConstraintName)
END

--===============================
-- Example of how to drop dynamically named Unique constraint
--===============================
DECLARE @ConstraintName VARCHAR(100)
SET @ConstraintName = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
						WHERE CONSTRAINT_TYPE='UNIQUE' AND TABLE_SCHEMA='dbo'
						AND TABLE_NAME = 'TableName' AND CONSTRAINT_NAME LIKE 'FirstPartOfConstraintName%')

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE='UNIQUE' AND TABLE_SCHEMA='dbo'
		AND TABLE_NAME = 'TableName' AND CONSTRAINT_NAME = @ConstraintName)
BEGIN
	EXEC('ALTER TABLE [dbo].[TableName] DROP CONSTRAINT ' + @ConstraintName)
END

--===============================
-- Check for and drop a temp table
--===============================
IF OBJECT_ID('tempdb..#TableName') IS NOT NULL DROP TABLE #TableName

--===============================
-- Drop a stored procedure
--===============================
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE='PROCEDURE' AND ROUTINE_SCHEMA='dbo' AND
		ROUTINE_NAME = 'StoredProcedureName')
BEGIN
	EXEC('DROP PROCEDURE [dbo].[StoredProcedureName]')
END

--===============================
-- Drop a UDF
--===============================
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE='FUNCTION' AND ROUTINE_SCHEMA='dbo' AND 
		ROUTINE_NAME = 'UDFName')
BEGIN
	EXEC('DROP FUNCTION [dbo].[UDFName]')
END

--===============================
-- Drop an Index
--===============================
IF EXISTS (SELECT * FROM SYS.INDEXES WHERE name = 'IndexName')
BEGIN
	EXEC('DROP INDEX TableName.IndexName')
END

--===============================
-- Drop a Schema
--===============================
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'SchemaName')
BEGIN
	EXEC('DROP SCHEMA SchemaName')
END

--===============================
-- Drop a Trigger
--===============================
IF EXISTS (SELECT * FROM SYS.TRIGGERS WHERE NAME = 'TriggerName')
BEGIN
	EXEC('DROP TRIGGER TriggerName')
END

You may have noticed that I wrap the actual DROP statements in an EXEC statement.  This is because if you run the script once and it drops the schema object, if you try to run the script a second time SQL may complain that the schema object does not exist, and won’t allow you to run the script; sort of like failing a compile-time check.  This seems stupid though since we check if the object exists before dropping it, but the “SQL compiler” doesn’t know that.  So to avoid this we convert the drop statement to a string and put it in an EXEC, so that it is not evaluated until “run-time”, and since the IF EXISTS checks prevent that code from being executed if the schema object does not exist, everything works fine.

Happy coding!

Categories: SQL Tags: , , , , ,

AutoHotkey cannot interact with Windows 8 Windows…or can it!

September 10th, 2012 No comments

<Update>Before you go running your script as an admin, see if this less obtrusive fix will solve your problems.</Update>

If you’ve installed Windows 8 and are trying to use AutoHotkey (AHK) to interact with some of the Winodws 8 Windows (such as the Control Panel for example), or with apps that need to be Ran As Administrator, then you’ve likely become very frustrated as I did to discover that AHK can not send any commands (keyboard or mouse input) to these windows.  This was a huge concern as I often need to run Visual Studio as an administrator and wanted my hotkeys and hotstrings to work in Visual Studio.  After a day of fighting I finally realized the answer (and it’s pretty obvious once you think about it).  If you want AHK to be able to interact with Windows 8 Windows or apps running as administrator, then you also need to have your AHK script Run As Administrator.

If you are like me then you probably have your AHK scripts set to run automatically at login, which means you don’t have the opportunity to right-click on the script and manually tell it to Run As Administrator.  Luckily the work around is simple.

First, if you want to have your AHK script (or any program for that matter) run when you log in, create a shortcut to the application and place the shortcut in:

C:\Users\[User Name]\AppData\Roaming\Microsoft\Windows\Start Menu\Programs\Startup

Note that you will need to replace “[User Name]” with your username, and that “AppData” is a hidden folder so you’ll need to turn on viewing hidden folders to see it (you can also type “shell:startup” in the Windows Explorer path to jump directly to this folder).  So by placing that shortcut there Windows will automatically run your script when you log on.  Now, to get it to run as an administrator by default, right-click on the shortcut and go to Properties.  Under the Shortcut tab, click on the “Advanced…” button and check off “Run as administrator”.  That’s it.  Now when you log onto Windows your script will automatically start up, running as an administrator; allowing it to interact with any application and window like you had expected it to in the first place.

==< EDIT >==

This method works for running AHK scripts that don’t require admin privileges at startup.  It only works for running AHK scripts as admin at Windows startup if you have disabled UAC in the registry in Windows 8, which you likely do not want to do (and I had done at the time of writing this article, but have since switched it back on).  For a better, UAC-friendly solution to running your AHK scripts as admin at startup, see my newer post to actually get your AHK script to run as admin at startup.

If you do need your AHK script to run as admin and plan on manually double-clicking your AHK script to launch it though, then you can still use this trick of create a shortcut and setting it to Run As Admin in order to avoid having to right-click the AHK script and choose Run As Admin.

==</ EDIT >==

image

Happy coding!