Generating C# const declarations from table data

In my previous post I showed how to generate INSERT and UPDATE statements from table data stored in an SQL Server database using dbscript. Now we create a Value Script definition to generate C# classes with const int declarations, one class relating to one database table.

Setting up a Project in dbscript has been explained in the mentioned post.

Now create a Value Script entry with type “C# public consts”. On the Tables page, you find 3 different Script Modes, namely

• public static const
• public enum
• public const

“public const” is to be used with C# 1.x, as it does not support static classes.

After adding tables to the script definition, select each table to define the scripting options:

Class Name

By default, the class is named after the table. If you want to change the class name, enter the desired name.

C# Identifier

The varchar column containing the const identifiers.

Value

The int column containing the values of the C# constants.

Description

Optional column containing a description for each C# constant. Being generated as /// summary comments, the values show up in Intellisense in Visual Studio.

Finally, click Generate in the Value Script’s Action menu. Select the project version the script is based on (a script may contain tables or columns that need not be present in every project version).

Press Generate. The Generated Script textbox will now contain T-SQL statements that generate C# classes and const declarations according to your definition. If you defined a name for a Stored Procedure in the Value Script properties, the script will also contain the CREATE PROCEDURE header.

Alternatively, select a database from the dropdown to retrieve values directly from that database. This is a sample based on the ObjectType table used by dbscript:

//
// dbscript2dev Value Script
// automatically generated by script
// 2008-12-29 22:07:16
// script generator by dbscript 0.93 29.12.2008 22:07:16
//

public static class ObjectType
{
    /// <summary>
    /// Table
    /// </summary>
    public const int Table = 1;
    /// <summary>
    /// View
    /// </summary>
    public const int View = 2;
    ...
}

Copy the generated script or C# code directly from the textbox, or click the download link to save it.

dbscript is available for download here.

Generating INSERT and UPDATE statements from table data

In dbscript, a Project contains the various versions of a database schema as well as database information related to the project, and definitions of Value Scripts, which allow you to generate T-SQL code that represents data stored in your (development) database.

This post deals with generating SQL statements from data of selected tables.

First we need to define which tables are contained in the project (or in the current project version). Create a Database entry, which is either just a name (if you upload CREATE statements later on), or the connection string information to a database that is directly available to dbscript.

Create a Project Version entry, and select from the Actions menu Upload if you want to upload an SQL file containing CREATE TABLE statements, or Import if you have entered a valid connection string for one of the project’s databases.

Finally, create a Value Script entry, selecting “T-SQL Value Script” as Script Type. Behind the Tables link, we can now define the contents of the value script.

Select one of the tables from the dropdown, and choose a Script Mode:

Insert+Update	IF (NOT EXISTS …) INSERT … ELSE UPDATE …
Insert Only	INSERT …
Insert with Check	IF (NOT EXISTS …) INSERT
Update Only	UPDATE …
Delete Before Insert	DELETE … INSERT …

Press Insert to add the table to your script.

From the list of tables, select the newly created entry. Select a column name as the primary key column from the dropdown.

If a table contains foreign key references to another table, you may control the sequence of tables in the script by setting the Sequence field.

Go to the Columns page of your currently selected table. Press “Insert All” if you want your script to contain all columns of the table, or select individual columns.

Finally, click Generate in the Value Script’s Action menu. Select the project version the script is based on (a script may contain tables or columns that need not be present in every project version).

Press Generate. The Generated Script textbox will now contain T-SQL statements that generate Insert and Update statements according to your definition. If you defined a name for a Stored Procedure in the Value Script properties, the script will also contain the CREATE PROCEDURE header.

Alternatively, select a database from the dropdown to retrieve values directly from that database. This is a sample based on the ObjectType table used by dbscript:

--
-- table dbo.ObjectType
--

IF (SELECT COUNT(*) FROM dbo.ObjectType
    WHERE    OID = 1
    ) = 0
    INSERT INTO dbo.ObjectType (OID, ID, OTT_OID, ProgID, SysobjectType,
                                Seq2000, Seq2005, WikiTitleFormat)
    VALUES    (1, 'Table', 1, 'Table', 'U', 100, 100, N'_(table)')
ELSE
    UPDATE    dbo.ObjectType
    SET ID = 'Table', ProgID = 'Table', OTT_OID = 1, SysobjectType = 'U',
        Seq2000 = 100, Seq2005 = 100, WikiTitleFormat = N'_(table)'
    WHERE    OID = 1

Copy the generated script directly from the textbox, or click the download link to save the generated script.

dbscript is available for download here.

Generating XML from Database Schema and Table Data

As described in my previous posts on dbscript, version 0.92 supports generation of XML files based on schema information imported from an SQL Server database or uploaded via SQL files containing CREATE statements for all supported object types.

I provided two examples of how these XML files can be used by XSL transformations integrated into dbscript to generate wiki content.

In this post, I will introduce the capability to generate a Stored Procedure which in turn generates an XML representation of data stored in a database table.

Currently, the XML script generator supports 3 modes, called:

• Field Elements
• Column Name Elements
• Column Name Attributes

to reflect the various ways data can be mapped onto XML.

The root element for all XML data files is named <data>, its child elements <table> have the attributes name and schema. Each record within a table is marked by the element <record>, its attributes being the names of the defined primary key columns.

All examples are constructed from tables of the dbscript database.

Field Elements

Field Elements mode have <field name=”column name”> child elements, with the record field values as node texts:

<table name="ProjectVersionType" schema="dbo">
  <record OID="1">
    <field name="OID" datatype="int">1</field>
    <field name="ID" datatype="nvarchar">Major</field>
    <field name="DispSeq" datatype="int">100</field>
  </record>
</table>

Column Name Elements

The child elements of the <record> element are named according to the column names of the table:

<table name="ObjectType" schema="dbo">
  <record OID="1" >
    <OID datatype="int">1</OID>
    <ID datatype="nvarchar">Table</ID>
    <ProgID datatype="varchar">Table</ProgID>
    <SysobjectType datatype="varchar">U</SysobjectType>
    <Seq2000 datatype="int">100</Seq2000>
    <Seq2005 datatype="int">100</Seq2005>
    <WikiTitleFormat datatype="nvarchar">_(table)</WikiTitleFormat>
  </record>
</table>

Column Name Attributes

The <record> element contains attributes according to the column names of the table:

<table name="ScriptType" schema="dbo">
  <record
    OID="1"
    ID="T-SQL Value Script"
    HasSCols="1"
    ProgID="TSqlValues"
  >
  </record>
</table>

The latest version of dbscript (0.92) is available for download here.

Finding SELECT Commands in .aspx Files

During tests of my most recent versions of dbscript I ran into problems with a SELECT statement that works fine on SQL Server 2000, but raises an error on SQL Server 2005.

The statement is in the form of

SELECT NULL AS OID, NULL AS ID
UNION ALL
SELECT MyTable.OID, MyTable.ID
FROM MyTable
ORDER BY MyTable.ID

The statement retrieves all records of MyTable, and adds an empty record at the beginning. The problem seems to be that while SQL2000 only considers the column name of the ORDER BY clause, SQL2005 cannot find the column MyTable.ID in the result set, as it is only defined as ID in the first SELECT.

After fixing the statement, the obvious question was where else in the source code such statements occurred.

A single line on the cmd prompt invoking my graspx tool showed the occurrences:

graspx l SelectCommand *.aspx | find "UNION" | find "ORDER"

Get Network Card Bandwidth Info using PowerShell

There are a lot of tools around to monitor your network and NICs, but I found a single line of PowerShell code can list your network cards (local and remote as well) and their bandwidths:

Get-WmiObject -class Win32_PerfFormattedData_Tcpip_NetworkInterface |
    Select Name, CurrentBandWidth |
    Format-Table
        @{Expression={$_.Name}; Label="NIC" },
        @{Expression={$_.CurrentBandWidth/ 1000000 }; Label="MBit/s"}

Wiki Documentation from MSSQL Database Schema

The upcoming version 0.92 of my database tool dbscript provides the functionality to generate XML documents on single objects as well as on the whole database schema. Using XSLT, this XML document can then be transformed into any XML-based or text-based representation of the schema information.

In a recent post, I demoed the function on a table out of dbscript’s data model. The result of an XSLT applied to the XML representing AdventureWorks’ Person.Contact can be viewed here.

New functionality allows an XSL to be applied on the XML result of the database schema (called ProjectVersion in dbscript), and the result is an overview of the whole database catalog in MediaWiki syntax. This list shows all objects in AdventureWorks (MSSQL 2005) and is a direct and unmodified output of dbscript.

Since you are free to add and edit XSL style sheets in dbscript, users can adapt the XSL to reflect any kind of syntax their wikis use.

Selenium NUnit crawler speed-up

I improved the speed of my Selenium link crawling algorithm by directly extracting the href URLs of all hyperlinks, instead of retrieving the hyperlinks by ID and querying their href attributes:

string sLinks = selenium.Eval(@"
var s = '', i = 0;
for(i = 0; i < window.document.getElementsByTagName('a').length; i++) {
    s = s + ' ' + window.document.getElementsByTagName('a')[i].href;
}
s;");

string[] rgsLinks = sLinks.Split(' ');

The string array now contains all URLs found in the current page.

As each call to the Selenium API is passed to the Selenium server, then to the browser, which evaluates it, and returns the result to the server, which passes it to the client, this approach is way faster than querying individual a.href attributes.