Tip of the Day: Creating a control that displays multiple types of messages using the IValidator interface

My first journey through expression trees and dynamic lambda expressions, and an explanation on how I got there. The end result is a case-insensitive search and a wildcard search function.

Tip of the Day: Extension method to HTML encode a string

Tip of the Day: T-SQL Script to rename bulk columns

Retrieve the column name which have uniqie constraints on them in a CodeSmith template.

Explore the INFORMATION_SCHEMA to get more useful metadata information on your database, much simpler than using sysobjects!

Retaining employees after this economy changes needs to start now!

Script to ferret out those procs you forgot to create permissions for!

Tip of the Day: Manipulating ASP.Net validators client side

How to register class based user controls in a web project

Extension method for providing dynamic sorting based on field names of a List collection.

Read more ...

Two useful menu items to help eliminate "using" clutter in VS2008.

Adding carriage returns to SSIS package annotations

More promising features of VS2010

Tip of the day: marking routines obsolete, and enforcing it!

Some of the great features of Visual Studio 2010 which are very appealing to me!

Using generics to simplify getting data from a SqlDataRow or DataRow object.

Launch another program, and optionally wait for it to complete.

A simple validation routine to validate a string meets the minimum and maximum length requirements.

WinForm: The base class % could not be loaded.

Using the MasterType directive to programatically access you Master Page.

Placing page trace information in a file instead of on the page

Interview question -- the virtual keyword

Fancy tooltips in WinForms!

Adding a WinForm tooltip without the control..

How to enumerate all the shortcut keys in the Visual Studio IDE

Debugging trick for placing a condition on a breakpoint to assist in speeding up debugging.

How to make the Visual Studio debugger break where the error actually occurs.

Tipf of the Day: A brief discussion of the new Extension Methods in .Net 3.5

Starting a new project, how to approach it?

I can't take credit for this, as I took it from the article How To Get Table Row Counts Quickly And Painlessly on SqlServerCentral.com by Kendal Van Dyke. However, it such a good tip, I had to change it a bit, as I tend to use this a LOT.

First, I made the results a little more "printable" but reducing the table name column. Next, I made it a system proc because when I go onto client sites, I am looking at many databases. I probably don't need to create a procedure for it, but there have been many cases where I trend things, and it would be nice to have this handy. Finally, I added a parameter to allow me to change the sorting. By default, it sorts by table name. I added an optional parameter to allow me to pass in an integer (could have done it a million ways, I know, but I chose to do this solely for my preferences -- feel free to change to yours).

Usage:  sp_retrieveRowCounts    --- sorts by table name

            sp_retrieveRowCounts 1  ---sorts by row count, then table name

Here's the proc:

USE master
GO

IF (SELECT OBJECT_ID('sp_retrieveRowCounts','P')) IS NOT NULL
BEGIN
    DROP PROC sp_retrieveRowCounts
END
GO


CREATE PROC sp_retrieveRowCounts (@OrderByCount int = null)
AS
BEGIN

    DECLARE @OrderByParm int
    DECLARE @SQLStatement varchar(700)
    DECLARE @OrderByClause varchar(35)
    
    SELECT @OrderByParm = ISNULL(@OrderByCount,0)

    IF (@OrderByParm != 0)
        SELECT @OrderByClause = ' ddps.row_count DESC, o.NAME ASC'
    ELSE
        SELECT @OrderByClause = ' o.NAME ASC'

    SELECT @SQLStatement = 'SELECT TableName = LEFT(o.name,50), TotalRows = ddps.row_count FROM sys.indexes AS i
     INNER JOIN sys.objects AS o ON i.OBJECT_ID = o.OBJECT_ID
     INNER JOIN sys.dm_db_partition_stats AS ddps ON i.OBJECT_ID = ddps.OBJECT_ID
     AND i.index_id = ddps.index_id
    WHERE i.index_id < 2
     AND o.is_ms_shipped = 0
    ORDER BY ' + @OrderByClause
    
    EXEC(@SQLStatement)

END

--Mark procedure as system object
EXEC sys.sp_MS_marksystemobject sp_retrieveRowCounts
GO


GRANT EXEC ON sp_retrieveRowCounts TO public
GO

 

When I go onto a client site, I usually don't have the luxury of designing everything from scratch. There are times (like on my current engagement) if I were given the choice, I'd say scrap it, but usually it doesn't happen that way. It is what it is... been saying that a lot lately!

So one of the tools I find useful is the ability to search for particular field names in the database. This is very similar to sp_findsp I use, which I have posted in the past off the net, but I have modified it so it gets registered as a system procedure.

So here's sp_findfields. It will return a fairly printable representation of the results of all fields which match the passed in string. It's registered as a system procedure in this script, so it is accessible to all databases.

Usage:

sp_findfields '%Contact%'

This will find all fields with the string 'Item' in it. Note you need to provide the wildcard characters.

USE master
GO

IF (SELECT OBJECT_ID('sp_findfields','P')) IS NOT NULL
    BEGIN
        DROP PROCEDURE sp_findfields
    END
GO

CREATE PROCEDURE sp_findfields
(@searchText varchar(50))
AS
BEGIN
    
    SELECT SchemaName = LEFT(TABLE_SCHEMA,10),
        TableName = LEFT(TABLE_NAME, 35),
        FieldName = LEFT(COLUMN_NAME, 40),
        DataType = LEFT(DATA_TYPE, 13),
        DataSize = CHARACTER_MAXIMUM_LENGTH,
        AlloysNulls = IS_NULLABLE
    FROM INFORMATION_SCHEMA.columns
    WHERE column_name like @searchText
    ORDER BY TableName, FieldName
END
GO

--Mark procedure as system object
EXEC sys.sp_MS_marksystemobject sp_findfields
GO


GRANT EXEC ON sp_findfields TO public
GO

The following code snippets make retrieving values from DataRows safely, and properly casts them to the desired values.

 

I am a huge fan of CodeSmith. If you are not familiar with it, and you do any amount of coding at all, you owe it to yourself to check it out.

Below is some template code which loads the foreign keys of a table into a Dictionary object. It stores the name of the table and foreign key field name. As you can see, it generates a notice when it comes across a recursive relationship.

 

public Dictionary<string,string> GetForeignKeyTables()
{
    Dictionary<string,string> dic = new Dictionary<string,string>( );

 for (int idx = 0; idx < SourceTable.ForeignKeys.Count; idx ++)
 {
        string primaryTable = String.Empty;
        string primaryKey = String.Empty;

  // To get the foreign key columns
  ColumnSchema col1 = SourceTable.ForeignKeys[idx].ForeignKeyMemberColumns[0];
        primaryKey = col1.Name.Trim();

  // To get the foreign key tables
        primaryTable = SourceTable.ForeignKeys[idx].PrimaryKeyTable.ToString().Replace("dbo.",String.Empty).Trim();
        if (!dic.ContainsKey(primaryKey))
            dic.Add(primaryKey, primaryTable);
        else
            MessageBox.Show("This table may contain recursive relationships. You must set those relationships up manually.");
    }

    return dic;
}

 

Interview question -- non-technical about how I handle release problems.

An example of reflection in coding!

Question: What's reflection, and how have you used it to solve a problem?

Answer: Reflection is a way to look at assembly metadata and look in and see what is contained. It also allows you to use late binding and call methods within those assembles.

For the second part, a simple explanation is all you need to show that you understand how it is used.

So what if you've enver used reflection? First, shame on you (kidding!). It DOES serve a very important purpose and allows for some great solutions, especially at an architectural level. If you've never used it, I recommend immediately trying it out, and you'll find many, many uses. Here's a tutorial I found that looks decent. That way, when asked this question (and I get this one a lot) you can answer how you *might* really use it. Honestly, you've probably used it and didn't realize it if you've added certain attributes to your code.

I have used reflection for everything from identifying certain classes and functions. For example, for a coding sample, I used LINQ for Reflection and attributes to identify classes which supported certain calculations. By doing this, I could add more functions dynamically and the UI would pick it without changing code. Since LINQ for Reflection is not discussed much, I'll post some about that later.

There are some costs associated with Reflection though. Speed! Late Binding is slower by it's nature, as well as all the processing looking for methods, so plan and use wisely!

Another interview question of the day -- Access Modifiers

Another interview question du jour!

I've been doing technical interviews for about 5 years now for .Net, and hired a lot of developers in that time. As a result, I have become proficient at quickly assessing a level of technical expertise as well as how a developer may fit within an organization. I will blog another time about what characteristics I look for in a candidate, but for now, I will go through some of the technical questions I ask, answer them, and explain why I ask the question.

Question: What's the difference between an abstract class and an interface

Answer: They have some similarities in that they both define abstract members which have to be implemented. An interface is really just a contract where you are stating "if I implement this interface, I will implement these methods". However, an abstract class can have an implementation, where an interface cannot. An abstract class can also have constructors and field data.

One advantage of an interface is that you can implement more than one interface per class, but you can only inherit from one class.

Note there's a lot more you could talk about with them both (and you don't want to overtalk any question), but the above will demonstrate you have a good knowledge of OOP design, which is what I was looking for. It's surprising how many developers cannot get this question right.

To learn more, here's a good article which discusses them in more detail (more than you'd need to answer the question for sure, but good knowledge to have!)

I highly recommend the Application Architecture guide from CodePlex.

I needed to use LINQ to XML to grab some RSS feed information into a BusinessObject I created to save to a database. Here's what I needed:

Tonight was a very enjoyable night of coding. I created the first phase of my "activity aggregator". I actually had a more clever name for it, but being tired, it slips my mind right now.

Currently, I post several places.. short updates for on Twitter. Longer discussions end up on here, my blog. I have some other sources I plan on integrating over time, such as links I found useful (similar to Digg, but also where I can place why I found it useful). That means growth, and when it comes to design, it's best to design change into it up front.

The first thing I needed to do was create my table and business object to collect these activities. In my table, I store ActivityDate, what type of activity it was (foreign key to another table -- ActivityType), a brief summary of the activity text, and finally the url someone interested could click on to get more information. The internal members of my activity class look like this:

Some tools I downloaded today which appear VERY useful, and touching on the ORM topic.

Tonight I didn't get as much time to work on the site as I would have liked, so I focused on getting the admin pages up on the production server so I could do some "behind-the-scenes" work remotely. This required some security, which I started last night, but the data migration for that was a bit more daunting, so I needed to finish it tonight. While the authentication providers and controls do handle a LOT, it has an interesting learning-curve, and deployment can be a challenge as well. All-in-all, it sure beats writing all that code by myself.

I tried to use a lot of caching and cached settings to help improve the performance on certain pages, especially my client list. On my admin pages, I made sure to create a means to reset that cache without having to do a complete server reset. Actually, the same approach I took would work well for high availability sites, as the method I used to complete this would not require a server reset to re-load systems. When I was int he banking industry, high-availability was a huge issue, and simple config changes meant waiting for a scheduled system outage or worse, an emergency outage. When I redesigned that site, that was one of the top priorities I had.

It occurred to me how much time it takes to set up the infrastructure of a site: master pages, common libraries, configuration files, CSS, etc. Once those are done, if done properly, the rest of the site should go fast. One thing I have found to speed that up was the use of Visual Studio Project Templates. Combined with code generation like CodeSmith, you can really generate web sites extremely fast, and get on to coding the "fun" items. I will write about both of those in the future (CodeSmith and Project Templates) once the site is stabilized.

Tomorrow night may not have a lot of development, as my parents will be in town. Later this week, I will be coding my next control: the activity feed. When posts and feeds are made or read, this control will snag it, and place a brief "teaser" on the home page.

A plug for my company, Rubicon Computing Solutions, LLC.

Using LINQ to XML to work with the Twitter response, and modifying the Yedda Twitter API wrapper.

Note: For visitors of your site, this entry is only displayed for users with the preselected language English (United States)/English (United States) (en-US)

How the VS2008 IDE is very helpful to conditional compilation, and also how the site is coming along

New Data property of the Exception class is quite useful!

Get the name of the current db in t-sql

A new blog...

Is it just my installation, or is WinCV missing from the latest version of .Net? Granted, I can still use the old WinCV by changing the config file, but why was such a useful tool pulled from VS2005?

If you've never used WinCV, check it out for VS2003 and prior version. It essentially lets you search the framework for a certain keyword. For example, if you want to find out which namespace "Registry" is in, type it in, and see the entire implementation. By using the configuration file, you can add additional assemblies for WinCV, to search your own libraries or those of third party vendors.

 

Just a little tip, as I didn't read the error message generated as closely as I should have. I was working this weekend on the Microsoft Code Template for Building a Provider Based Feature and was migrating some existing libraries I had for VS2003 to take advantage of the features in VS2005.

First, let me say I love things in VS2005 like Generics, and the implementation of the Provider Model. I was migrating my data access libraries and error handling libraries, and was setting up some tests in nUnit as part of ongoing testing. Although I haven't changed my data and error handling models in a long time, I figured it was a great design idea to be able to change them dynamically using the Provider Model.

To make a long story short, I was running my tests for the data library, and when I invoked my Data Access classes, I was getting a "TypeInitializationException" error. I checked and double-checked my configuration file, and it appeared to all be correct. I looked at the InnerException, and it didn't seem to be much more help. However, I went further to look at the InnerException of the InnerException, and there's where I found the solution. The Rosetta Stone?

• Only one <configSections> element allowed per config file and if present must be the first child of the root <configuration> element.

A quick check indicated that I hadn't made the configSection the first element. Doh!

So there are a few lessons to learn here. 1) Make sure your configSections is first (normally not a problem, just a cut and paste issue here) and 2) Make sure you dig REAL deep into the InnerExceptions for the details and 3) the new Visualizers in vs2005 were too cool and very helpful.

Hats off to the VS2005 IDE team! It's spawned a few visualizers I can think of, so if time permits in the future, I will be publishing those right here!

..or why MS has once again dropped the ball on it's core developer community.

"Internal Query Processor Error: The query processor could not produce a query plan. Contact your primary support provider for more information" when passing in C# DateTime value...

(and maybe why Extreme Programming (XP) is also bad?)

Need a good developer?

Tired of cutting and pasting? Try CodeSmith...

What the $*#(U$(? Where did the wse version go?

I am joining the fray....

How to get them to reappear...

Let's talk resume's for a minute. You want that interview don't you? Well, sometimes all you have to get you there is your resume (although personal reference is the best way if you can get it, an area where networking and doing quality work can help).

There's a million places on the internet to help you decide how to structure your resume. I have only gotten one criticizm from my resume, and I am not sure if it's the content or the structure which works best. But here's a couple of pointers which I think have helped out my resume.

1. Place a logo of any certifications you have if available (e.g. MCSD). These are real eye catchers. Make the images small though..
2. Place your specific skills separate from your experience, preferably near the top. This makes it easy to survive the first pass from the recruiter/manager.
3. Sell yourself! Research “power“ words and make any experience descriptions short and chock full of those words.

Now, there's a few easy tips of what to do. I have been helping staff for years, and here's some of the gems I have seen.

• Horrible writing and spelling.
• Obviously exagerated skills and experiences (4 years experience, 10 years at a certain skill)
• Never say you are an expert at anything, even if you are. Humility goes a long way, and if the interviewer knows more, you'll be humbled by them, as I witnessed in the past week.
• Use nice paper if submitting a hardcopy (I received one handwritten on a partial piece of paper -- I kid you not)

It's not tough... just use some common sense!

OK, so now you know wht might be asked.. how not to blow it.

WTF? Poor documentation...

wow... what was that? Some observations from helping with some technical interviews.

When you need to uniquely identify a file using the time and date stamp, use this!

Someone asked me today if I knew that you couldn't use GETDATE() in functions in SQL Server. Well, I did, so I dug out this solution I had found a while back, and decided to post it, since I am sure others have come across the same issue.

Basically, you use a view to do the work for you... here you go!

CREATE VIEW Function_Assist_GETDATE

/********************************************************
*
* A view to return one row, with one column, the current
* date/time from the built-in function GETDATE().  This
* view allows a UDF to bypass the restriction on access to
* the non-deterministic getdate() function.
*
* Attribution: Based on a newsgroup posting in by Mikhail
*   Berlyant in microsoft.public.sqlserver.programming
*
* Common Usage:
DECLARE @dtVar datetime
select @dtVAr = [GetDate] from Function_Assist_GETDATE
**********************************************************/

AS
    SELECT getdate() as [GetDate]

GO

GRANT SELECT on Function_Assist_GETDATE to PUBLIC
GO

Exception handling analyzed...

Clearing up the confusion when you need the return value from an insert.

Finding all tables where a field exists in SQL Server

How to format a date to a particular format...

How to quickly find out which control caused the postback

How to step through your client side code

The vb function in C#

Just a tip to remind myself

A brief look into the System.Diagnostics namespace...

A costly mistake to forget...

How to cast to a specific type if you have the string value of the type

Quick fix for a "catastrophic" problem!

Well lookie what else it can do!

Let's work with an enum today!

A useful port from something in the old VB6 days...

Save the day!

Hot keys and aliases, oh my!

A quick function to convert an integer to a Roman numeral

Follow me as I configure a client in automated builds

How to configure log4net to save entries into the database

How to make an ASP.Net page expire

How to break on an error in ASP.Net

Changing the look of thinks client side dynamically...

How to search all those assemblies and find just what you need...

CodeLib from fish

Allows you to capture the click, dblclick, etc to make a normal form control read only.

How to dynamically change the EntType of an HTML form...

When you can't create a SQL diagram, use this to print usable field information

How to add client-side javascript to individual listItems in a RadioButtonList

How to grab a random record which is very useful for testing...

DBA Must Haves (IMHO)

First, a little about myself. I have been working with databases since SQL Server 6, and a little back when it was Sybase. Here are two procs I use so much, I have to link them here.

First, there's Narayana Vyas Kondreddi's sp_generete_inserts. It's a great utility/sproc for extracting data from existing tables. I have used it from everything from migrating data to making impromptu backups to text files, all the way to bailing out some hapless developers who run against production and wipe out data they should not be touching. Here's where you can get it:

http://vyaskn.tripod.com/code/generate_inserts.txt

Next, there's sp_findsp. Not the prettiest thing, but if I want to find out where a string (such as a table or proc) is used, I can run this baby and it will return to me a list where I can start looking. It's also very useful to me when I start a new project to do some “mining“ to see what's going on. Here's where this one can be found:

http://databasejournal.com/features/mssql/article.php/1458871

These two follow me everywhere I go!

 

Note: For visitors of your site, this entry is only displayed for users with the preselected language English/English (en)

Want a good tip?