chick.NET

This is a dumping ground of software development topics that I've run across and found interesting (mainly .NET development). Many topics are covered more thoroughly in other places, and much of the information is gleaned from other places, and I'll try to credit those sources when possible.

Friday, March 18, 2011

SQL: Sorting NULL Values

I was ordering a result set by a field that is nullable, but wanted any rows whose value for this field was null to be at the end of the set, not the beginning. Probably should have figured this out on my own, but you know how quick Mr. Google can provide the answer. This site had me up and running in a matter of seconds:

ORDER BY
   CASE
      WHEN Region IS NULL THEN 1
      ELSE 0
   END,
   Region

SQL Server: Restore cannot process database because it is in use by this session.

I tried to restore a SQL Server 2005 database, but kept getting the following error:

Restore cannot process database 'WTF' because it is in use by this session. It is recommended that the master database be used when performing this operation.

I opened Activity Monitor and killed a couple processes hanging on the database, but got the same error. When I opened the Activity Monitor again, one process had come back. I closed SSMS trying to get rid of this process, to no avail. I googled a bit and found various people going round and round about it, then found this simple solution from The DW Experience.

The problem was that my SQL login had its "Default database" set to the database I was attempting to restore, which keeps opening a session. By changing the default database to something else, I could finally restore the database. Then I promptly set the default database back, because that's the way I like it. Since I'll probably run into this problem again, I figured I should write it up here.

SQL: Many-to-Many Database Design

Here is a useful article on handling many-to-many relationships in your database design. I especially liked the advice on creating additional indexes on the table, which I promptly put to use on the 10 link tables I am currently working with:

http://www.singingeels.com/Articles/Understanding_SQL_Many_to_Many_Relationships.aspx

Wednesday, July 07, 2010

ICO File Creation

Here's a handy site for creating an ICO file:FavIcon from Pics

Thursday, January 14, 2010

C# .NET: Updating a DataGridView Cell's BackColor

Wow, this was harder than it should have been, maybe. As usual, I just didn't feel like digging into the dirty details, so maybe it makes plenty of sense. Anyway, I had a DataGridCheckBoxColumn that when checked, I wanted to "disable" some other cells (check indicates the user is an administrator and other settings do not apply). To disable, I set the ReadOnly property for each cell and the Style.BackColor to gray. However, the BackColor change just wasn't happening, and then it was happening, but not until the check cell lost focus, and so on. Finally, the oh-so-important EndEdit call did the trick.


/// <summary>
/// Event handler for when cell content is clicked. Specifically, we are checking if the Administrator
/// checkbox state is changed and refreshing the grid so the un-necessary cells are disabled and
/// grayed-out (via the subsequent CellFormatting event).
/// </summary>
private void OnCellContentClick(object sender, DataGridViewCellEventArgs e)
{
if (theGrid.Columns[e.ColumnIndex] != ColumnAdminCheckBox)
return;

theGrid.EndEdit(); // VERY important call or the repaint doesn't happen until the cell loses focus.
theGrid.Refresh();
}

/// <summary>
/// Event handler for when cell content is formatted. Specifically, we are checking the state of the
/// Administrator checkbox and setting the ReadOnly and BackColor of the cells that do not apply when
/// user is an administrator.
/// </summary>
void OnCellFormatting(object sender, System.Windows.Forms.DataGridViewCellFormattingEventArgs e)
{
// Only update certain columns
DataGridViewColumn column = theGrid.Columns[e.ColumnIndex];
if (column != ColumnA && column != ColumnB &&
column != ColumnC)
return;

object obj = theGrid[theGrid.Columns.IndexOf(ColumnAdminCheckBox), e.RowIndex].Value;
if (obj == null || obj.GetType() != typeof(bool))
return;

bool admin = (bool)obj;

theGrid[e.ColumnIndex, e.RowIndex].ReadOnly = admin;
e.CellStyle.BackColor = (admin ? theGrid.BackgroundColor : Color.White);
}

Thursday, October 22, 2009

Disabling ASP.NET Session

Interesting tidbit from this MSDN page.

"You should be aware that even when not in use, sessions carry some overhead for an application. You can squeeze a little bit more performance out of your pages if you turn off sessions on pages that do not use it. Also, setting session state to read-only can also optimize pages that read but do not write data to sessions. Configure sessions in this fashion by adding an attribute to the @Page directive in one of these two ways:"

<%@ Page EnableSessionState="false" %>
<%@ Page EnableSessionState="readonly" %>

Wednesday, August 05, 2009

SQL Server - Scripting table data

Scripting data is handy for lookup tables and I found just what I needed in this stored procedure and discussion. I copied the enhanced procedure below, just in case the link ever dies.


CREATE PROCEDURE dbo.spScriptInsertStatements (
@TableName varchar(100),
@WhereClause varchar(1000) = NULL,
@OrderBy varchar(1000) = NULL
)
AS

DECLARE @sql nvarchar(MAX)
DECLARE @sqlColumns varchar(MAX)
DECLARE @sqlColumnValues varchar(MAX)
DECLARE @hasIdentity bit
DECLARE @tbl TABLE (
SortOrder int IDENTITY(1, 1) NOT NULL,
SQLText varchar(MAX) NULL
)

SET @hasIdentity = 0

SELECT @sqlColumns = COALESCE(@SqlColumns + ', ','') + COLUMN_NAME,
@hasIdentity = CASE @hasIdentity
WHEN 0 THEN COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity')
ELSE 1 END
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName
AND (@OrderBy IS NULL OR COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 0)

SELECT @SqlColumnValues = COALESCE(@SqlColumnValues + '+ '', '' + ','') + CASE
WHEN DATA_TYPE IN ('varchar','nvarchar', 'char', 'nchar', 'datetime' )
THEN 'COALESCE(QuoteName(' + Column_Name + ', ''''''''), ''NULL'')'
ELSE 'COALESCE(CAST(' + Column_Name + ' AS varchar(MAX)), ''NULL'')' END
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName
AND (@OrderBy IS NULL OR COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 0)

SELECT @sql = 'SELECT ''INSERT INTO ' + @TableName + ' (' + @SqlColumns + ') ' +
'VALUES ('' + ' + @SqlColumnValues +' + '')'' AS SQLText FROM ' + @TableName +
COALESCE(' WHERE ' + @WhereClause, '') + COALESCE(' ORDER BY ' + @OrderBy, '')

-- If table contains an Identity column and sorting was not specified
IF @hasIdentity = 1 AND @OrderBy IS NULL
BEGIN
INSERT INTO @tbl (SQLText) VALUES ('SET IDENTITY_INSERT ' + @TableName + ' ON ')
INSERT INTO @tbl (SQLText) VALUES ('GO ')
END

INSERT INTO @tbl (SQLText)
EXEC sp_executesql N'EXEC sp_executesql @statement = @sql', N'@sql nvarchar(MAX)', @sql

-- If table contains an Identity column and sorting was not specified
IF @hasIdentity = 1 AND @OrderBy IS NULL
BEGIN
INSERT INTO @tbl (SQLText) VALUES ('GO ')
INSERT INTO @tbl (SQLText) VALUES ('SET IDENTITY_INSERT ' + @TableName + ' OFF ')
INSERT INTO @tbl (SQLText) VALUES ('GO ')
END

SELECT SQLText FROM @tbl ORDER BY SortOrder

-- Unit Test
--
-- EXEC spScriptInsertStatements @TableName = 'glsBatchType'
-- EXEC spScriptInsertStatements @TableName = 'glsBatchType', @WhereClause = 'glsTypeSeq < 3'
-- EXEC spScriptInsertStatements @TableName = 'glsBatchType', @OrderBy = 'glsTypeCode DESC'
--
GO

Followers