C# Tips and Tricks: Setting default DateTime values

AmyLeave a comment

Here is the standard way that I set default values for a DateTime field. I like to set defaults for my DateTime fields whenever it makes sense. In this example I am setting the values so that it will return all the data for the last month, including the current day.

public bool StandardMethodName(DateTime? startDate, DateTime? endDate)
{
     startDate = startDate.GetValueOrDefault(DateTime.Today.AddMonths(-1));
     endDate = endDate.GetValueOrDefault(DateTime.Now);

     return ProcessInfo(startDate, endDate);
}

Filter DDL items using javascript and a Dictionary<string, string> object in the ViewBag

AmyLeave a comment

I had a need to modify the options on a DDL based on different selections the user had made on the page.  I did this at runtime by passing in a list of menu items in the ViewBag and using javascript to add and remove items based on the users selections.

Step 1: Create a ViewBag Dictionary <string, string> object that contains the full list of possible menu items.

ViewBag.MenuItems = new Dictionary<string, string> {
     {"option1", "Option One"},
     {"option2", "Option Two"}, 
     {"option3", "Option Three"}, 
     {"optionShowMe", "Option Show Only Me"}, 
};

Step 2: Create a javascript function that will insert an item into the DDL

function AddItem(Text,Value)
{
    // Create an Option object
    var opt = document.createElement("option");
    
    // Add an Option object to Drop Down/List Box
    document.getElementById("DropDownList").options.add(opt);

    // Assign text and value to Option object
    opt.text = Text;
    opt.value = Value;
}

Step 3: Create a method that will filter the options based on the users selection

function FilterDropDownListItems() {
    var array = @(Html.Raw(Json.Encode(ViewBag.MenuItems)));
    var userSelectedOption = $('#UserDDL').val();
    $("#DropDownList").empty();

    if (userSelectedOption === '140') {
        for (var val in array) {
            if(val === 'optionShowMe')
                AddRequestTypeItem(array[val], val);
        }
        $('#DropDownList').val('optionShowMe');
    } else {
        AddRequestTypeItem('Please Select', '');
        for (var val in array) {
            if(val != 'optionShowMe')
                AddRequestTypeItem(array[val], val);
        }
        $('#DropDownList').val('');
    }
}

SQL Tool: Search all table columns for specific value.

AmyLeave a comment

There are times when I am doing some research or refactoring and I need to find all instances of a value in a database. Things like “remove all instances of a person with id 123” or fix all instances of “The Misspelled Cumpany Name”. To do this I use the following SQL tool that searches all columns in all tables of a database for matching instances of the value passed in.  It takes a while but will find all instances you are looking for. 

IF OBJECT_ID('tempdb..#Results') IS NOT NULL
BEGIN
 DROP TABLE #Results
END
/**** Customize ***************************************************/
-- The string you are searching for
DECLARE @SearchStr NVARCHAR(100) SET @SearchStr = '3330'  
-- <''>  Use equals for exact matches or like for finding it in a string.
DECLARE @Operator NVARCHAR(100) SET @Operator = 'equals' 
-- <1> If 1 it searches only columns that are of type 'char', 'varchar', 'nchar', 'nvarchar' 
DECLARE @SearchStringsOnly INT SET @SearchStringsOnly = 0 
-- Search only columns that contain this text in their name.  if blank it searches all columns
DECLARE @InColumnName NVARCHAR(100) SET @InColumnName = 'ID' 
/*****************************************************************/

DECLARE @TableName nvarchar(256) SET @TableName = '' 
DECLARE @ColumnName nvarchar(128) SET @ColumnName = ''

CREATE TABLE #Results (ColumnName NVARCHAR(370), ColumnValue NVARCHAR(3630))

SET NOCOUNT ON

SET @SearchStr  = CASE WHEN @Operator = 'equals' 
                       THEN QUOTENAME('' + @SearchStr + '','''') 
                       ELSE QUOTENAME('%' + @SearchStr + '%','''') END 

WHILE @TableName IS NOT NULL 
BEGIN
 SET @ColumnName = ''
 SET @TableName = 
 (
  SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
  FROM  INFORMATION_SCHEMA.TABLES
  WHERE  TABLE_TYPE = 'BASE TABLE'
   AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
   AND OBJECTPROPERTY(
     OBJECT_ID(
      QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
       ), 'IsMSShipped'
            ) = 0
   --AND QUOTENAME(TABLE_NAME) NOT LIKE '%%'
   
 )

 WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
 BEGIN
  SET @ColumnName =
  (
   SELECT MIN(QUOTENAME(COLUMN_NAME))
   FROM  INFORMATION_SCHEMA.COLUMNS
   WHERE   TABLE_SCHEMA = PARSENAME(@TableName, 2)
    AND TABLE_NAME = PARSENAME(@TableName, 1)
    AND ((@SearchStringsOnly = 1 and DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar'))
         or @SearchStringsOnly = 0)
    AND QUOTENAME(COLUMN_NAME) > @ColumnName
    AND COLUMN_NAME like '%' + @InColumnName + '%'
  )

  IF @ColumnName IS NOT NULL
  BEGIN
   INSERT INTO #Results
   EXEC
   (
    'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
    FROM ' + @TableName + ' (NOLOCK) ' +
    ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr
   )
   PRINT N'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
    FROM ' + @TableName + ' (NOLOCK) ' +
    ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr
  END
 END 
END

SELECT ColumnName, ColumnValue FROM #Results

DROP TABLE #Results

ReportViewer: Displaying an empty row on a table

AmyLeave a comment

We use Microsoft’s ReportViewer for our reporting needs.  It isn’t the most elegant but it does the job.

Recently I needed to display a table on a report with an empty row if there was no data in the data source.  This is a visual indicator on the report that there is no data for that item.  Here are the instructions on the way that I found to do this.

1.  Right click on the data row in the table
2.  Add a new row below it, outside of the group.
3.  Right click on the row and select row visibility
4.  Click the radio button for “Show or hide based on expression”
5.  Enter code similar to the following as the expression:

=IIF(Sum(Fields!Id.Value, "DataSetName") &gt; 0, true, false)

This will show the row empty if there is no data and hide it if there is data.  Not very elegant but will do the job.

SQL Tool: Find all columns, by name, in database.

Amy2 Comments

We have some pretty large databases in our system and at times I need to find all columns in a database that have the same name. 

DECLARE @ColName VARCHAR(200) = '%cert%'

SELECT Obj.Name AS [TableName], Col.name AS [ColumnName]
FROM DBO.SysObjects  Obj
INNER JOIN DBO.SysColumns Col ON Col.Id = Obj.Id
WHERE Col.Name LIKE @ColName

Here are my results for all columns that have “cert” in the name.

Visual Studio 2012

AmyLeave a comment

I love working on teams that use MVC.  They always seem to be the ones willing to grab the latest technology and start using it.  I have worked in the past for places that say “We use the latest and greatest…” but when I have been there a while I find out that they really aren’t willing to upgrade anything.  Today I got to install Visual Studio 2012 and start using it.  Here are a few links to sites that had useful information:

Scott Hanselman is awesome.  Here is his article on Visual Studio.

Microsoft has a product guide for 2012.