Saturday, December 29, 2007

Getting SQL 2005 Column information for field validation

Form fields should be validated to ensure that they are within the maximum allowable length. For database applications, ultimately that length is determined by the field size in the database. Any complex database application should build an keep of the database schema in memory and use that information to validate fields on a form. The following SQL 2005 query returns a single table of the most relevant information for a database, assuming the SQL login has permission to view this information. The information can be kept in memory in a dataset, or can be loaded into a dictionary for quick retrieval. Replace 'AdventureWorks' with the name of your database.


USE AdventureWorks
GO

SELECT C.Table_Name, C.Column_Name, C.Ordinal_Position, C.Column_Default, C.Is_Nullable, C.Data_Type, C.Character_Maximum_Length, C.Numeric_Precision, C.Numeric_Scale, C.DateTime_Precision

FROM information_schema.columns C

JOIN Information_Schema.Tables T ON C.Table_Name=T.Table_Name

WHERE Table_Type = 'Base Table'

ORDER BY C.table_name, C.Ordinal_Position



Hope that helps.
Joe Kunk
Okemos, MI USA

Tuesday, December 11, 2007

Use a CheckBox instead of a ToggleButton for .Net windows form

Sometimes it is the little things that can drive you nuts. In VB6 I was used to having a ToggleButton control but I just went to use the equivalent in a .Net form for the first time and it was nowhere to be found.

Turns out that if you need this effect on a .Net winform, use the CheckBox control and set the Appearance property to "Button". The button appears "down" when Checked is true. Simple question, simple answer (once you find it).

Hope that helps.

Joe Kunk
Okemos, MI
USA

Saturday, December 08, 2007

Post-build event command line

I am working on building a plugin dll to a commercial software product. That dll must be placed in a particular sub-folder within the application's Program Files folder in order to be loaded at application startup.

I want to keep the freshly built plugin.dll and plugin.pdb files in the standard Visual Studio project build folder to be consistent with other projects, but it is a pain to manually copy those two files to the application's reserved folder after each build.

As typical in Visual Studio, when you think "there must be a better way", there usually is.

Go to the project properties, Build Events, and specify a post-build event command line that copies the freshly built assembly to the desired location. Thus it will just automatically be placed there after each successful build. You have the option to execute the post-build event command line even if the build is not successful.

The event command line can be any command that will execute properly in the operating system's command line interface (File, Run, Cmd {enter}). For more complex tasks, you can call a batch file or even a console project executable in your solution.

If the build event fails, your build will fail as well. This is a good thing since you want to be sure that your post-build event completed successfully. A pre-build event command line is available too.

The single line command that i used is below. The /y parameter overwrites any existing file of the same name without prompting. It uses pre-defined project constants that are listed for you when you are in the editor box of that property page. Visual Studio refers to them as "macros".

{commercial software reserved folder location} refers to the specific reserved plugin folder of the software. Note the use of quotes since the build path and destination path may both contain spaces. Note that the macros will expand properly even within quoted strings.

copy /y "$(TargetName).*" "C:\Program Files\{commercial software reserved folder location}\"


Hope that helps.

Joe Kunk
Okemos, MI
USA

Tuesday, December 04, 2007

VistaDB Table Index Names May Repeat Across Tables

A common database storage requirement is for the items contained in a drop-down list on the user entry/update form. I've seen these items referred to as a PickList or LOV for List-Of-Values. While all PickList values across the application could be combined into a single table, I've usually seen separate tables for each of the application's major PickList sets.

I'm currently working on an application with almost two dozen PickList tables. To make working with them easier, the table schemas are identical except for the first field, the identity seed primary key field.

The data import routine needs to determine if an incoming field is already contained within its corresponding PickList table or not. To make searching easier, I created an index for the display-text field on each of the PickList tables.

To make coding easier, I wanted the index name to be identical on each of the tables, but I was concerned that might cause a problem. Turns out that it was fine, VistaDB had no problem allowing me to create identically named indexes across all the PickList tables. Good news! :<)


Hope that helps.

Joe Kunk
Okemos, MI

VistaDB is a commercial product of VistaDB Software, Inc. All rights reserved.

Sunday, December 02, 2007

Visual Studio 2008 Multi-Targeting feature does not apply to Visual Studio 2003 (.Net Framework 1.1 and 1.0)

Visual Studio 2008 has a feature called multi-targeting that allows the visual environment to enforce or target development for a specific framework. The advantage is that you no longer need to maintain multiple copies of Visual Studio on your computer in order to support applications written in prior versions.

This feature supports .Net framework versions 2.0, 3.0, and 3.5. As a result, this feature does not apply to the .Net framework version 1.1 or version 1.0, used by Visual Studio 2003. If you have older applications that require those versions, you will need to keep Visual Studio 2003 on your computer.

Hope that helps.

Joe Kunk
Okemos, MI