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.

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

ASP.NET Panel with empty GroupingText

Using the GroupingText property of an asp:Panel will cause a pretty box to surround a group of controls, but sometimes I want to have that box without the text. I set GroupingText=" " hoping it would be interpreted as no text, but instead you can see the space in the box, which is annoying. Checking out the client side HTML (and MSDN documentation), I found the panel is represented as FIELDSET and LEGEND elements. By replacing the asp:Panel elements with fieldset, and not specifying a legend, I get the caption-free box I want. Note that you may have to put your fieldset in a div to get the style you want, like width. Simple example below:

<asp:Panel runat="server" GroupingText="Group of controls" Width="300px">
A panel using GroupingText="Group of controls"
</asp:Panel>
<br />

<asp:Panel runat="server" GroupingText=" " Width="300px">
A panel using GroupingText=" " (see gap in box above?)
</asp:Panel>
<br />

<div style="width: 300px;">
<fieldset runat="server">
A fieldset to get a solid box with no text.
</fieldset>
</div>

Followers