adsutil.vbs

This is a vb script to do changes in IIS. Sometimes you may need to set up an apppool or some other setting of a website or virtual directory. Below is how you can do it. At least I succeeded...

What I was trying to do was to set a property of IIS which reflects the Http Keep-Alives setting. I first dug into the metabase documentation - which is located here
Then tried to apply the adsutil.vbs
cscript x:\somefolder\adsutil.vbs SET W3SVC/SiteNumber/AllowKeepAlive true

And guess what it worked :)

So a couple of words on the SiteNumber ... It is either site number or site name. There are few ways to get it... but later on this.

Setting Windows Security in IIS 6.0 with ASP.NET 2.0...

One of the most laborious tasks in deploying intranet application is setting up security on the server. In particular when one tries to implement Windows Authentication in ASP.NET 2.0 and IIS 6.0 there is a number of tasks to do and things to check out …
Note that what I describe below is only applicable to a certain type of setup – which is impersonation of the application through the pool…

So steps are as following:

1. Set up an active directory (deny logon type) account X
2. Set identity of the pool to this account X
3. Set application to work with the pool
4. Comment out in the web.config of application <identity impersonate="true">
5. So far nothing complicated? Right… So now starts the tricky parts - set the security of the application to Integrated Windows (uncheck ALL other checkboxes)
6. Check the NTAuthenticationProviders of the IIS and the nodes (node may be designated by web site number). Set it to “NTLM,Negotiate” if not set. The syntax is as following in cmd: cscript adsutil.vbs set w3svc/NTAuthenticationProviders "NTLM,Negotiate". The article describing it is here: http://support.microsoft.com/kb/871179
7. Add the account X to belong to IIS_WSG group
8. Make sure that HTTP Keep-Alives is checked. If it is not you may be in trouble – check out this blog: http://www.west-wind.com/Weblog/posts/4375.aspx
9. Go to file system and add all the necessary permissions for the application folder. I mean ASP.NET account, group IIS_WPG, IWAM and IUSER and also – important – Authenticated Users and Domain Users (from Active Directory)….

The steps above are by no means guarantees that your intranet application will work from the first attempt. There is a number of other things to consider, such as network topology permissions which accounts you use may have or lack of such, version of the .NET Framework and other things…

iBatis - mapping generation - 2

iBatis requires mapping and generating xml for it can also be automated:

ALTER PROCEDURE [usp_TableToiBatisXML]
/*
Created by Michael Safro based on Cade Bryant's code.
*/
@table_name SYSNAME,
@model_name VARCHAR(200),
@prefix varchar(10)
AS
SET NOCOUNT ON
DECLARE @temp TABLE
(
sort INT,
code TEXT
)
INSERT INTO @temp SELECT 1,''
-- beginning of sqlMap
INSERT INTO @temp SELECT 2,''
INSERT INTO @temp SELECT 3,''
INSERT INTO @temp SELECT 4,''
INSERT INTO @temp SELECT 5,''
INSERT INTO @temp SELECT 6,'' -- start of the result maps
INSERT INTO @temp SELECT 7,'' -- start of map
INSERT INTO @temp SELECT 8,''
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table_name
ORDER BY ORDINAL_POSITION
INSERT INTO @temp SELECT 9,'' -- end of map
INSERT INTO @temp SELECT 10,'' -- end of maps...

INSERT INTO @temp SELECT 11,'' -- start of statements

-- start of select procedure
INSERT INTO @temp SELECT 12,''
INSERT INTO @temp SELECT 13, @prefix+'_'+@table_name+'_PR'
INSERT INTO @temp SELECT 14, ''
-- end of select procedure

-- start of update procedure --
INSERT INTO @temp SELECT 15,''
INSERT INTO @temp SELECT 16, @prefix+'_'+@table_name+'_PU'
INSERT INTO @temp SELECT 17, ''
-- end of update procedure --

-- start of insert procedure --
INSERT INTO @temp SELECT 18,''
INSERT INTO @temp SELECT 19, @prefix+'_'+@table_name+'_PI'
INSERT INTO @temp SELECT 20, ''
-- end of insert procedure --

-- start of delete procedure --
INSERT INTO @temp SELECT 21,''
INSERT INTO @temp SELECT 22, @prefix+'_'+@table_name+'_PD'
INSERT INTO @temp SELECT 23, ''
-- end of delete procedure --

INSERT INTO @temp SELECT 24, ''

-- start of parameter maps --
INSERT INTO @temp SELECT 25, ''

-- start of ParamSelect --
INSERT INTO @temp SELECT 26, ''
INSERT INTO @temp SELECT 27, ' INSERT NECESSARY KEYS '
INSERT INTO @temp SELECT 28, ''
-- end of ParamSelect

-- start of ParamUpdate --
INSERT INTO @temp SELECT 29, ''
INSERT INTO @temp SELECT 30, ''
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table_name
ORDER BY ORDINAL_POSITION
INSERT INTO @temp SELECT 31, ''
-- end of ParamUpdate

-- start of ParamInsert --
INSERT INTO @temp SELECT 32, ''
INSERT INTO @temp SELECT 33, ''
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table_name
ORDER BY ORDINAL_POSITION
INSERT INTO @temp SELECT 34, ''
-- end of ParamInsert

-- start of ParamSelect --
INSERT INTO @temp SELECT 35, ''
INSERT INTO @temp SELECT 36, ' INSERT NECESSARY KEYS '
INSERT INTO @temp SELECT 37, ''
-- end of ParamSelect

INSERT INTO @temp SELECT 38, ''

INSERT INTO @temp SELECT 39,'' -- end of sqlMap

DECLARE @code varchar(8000)
DECLARE TableCol Cursor FOR SELECT code FROM @temp ORDER BY sort
Open TableCol
FETCH NEXT FROM TableCol INTO @code
WHILE @@FETCH_STATUS = 0 BEGIN
PRINT @code + CHAR(13)
FETCH NEXT FROM TableCol INTO @code
END
-- ----------------
-- clean up cursor
-- ----------------
CLOSE TableCol
DEALLOCATE TableCol

iBatis - mapping generation

I am still new to iBatis, but find it an attractive ORM mapping technology. One of the most labor intensive tasks when mapping is to create actual maps, classes and UI based on the tables. While there are few good examples of how to do this for instance here:
I think that iBatis class/map/UI generation may have its own specifics... So I created a few scripts. Below is class generation:

ALTER PROCEDURE [usp_TableToClass]
/*
Created by Cade Bryant's.
Modified by Michael Safro to
generate VB.NET class code for a table
and fields/properties for each column.

*/

@table_name SYSNAME

AS

SET NOCOUNT ON

DECLARE @temp TABLE
(
sort INT,
code TEXT
)

INSERT INTO @temp
SELECT 1, 'public class _' + @table_name + CHAR(13) + CHAR(10) + 'Inherits Helper' + CHAR(13) + CHAR(10) + 'Implements IComparable(Of _' + @table_name + ')'

INSERT INTO @temp
SELECT 2, CHAR(13) + CHAR(10) + '#region'+ CHAR(34) + 'Constructors' + CHAR(34) + CHAR(13) + CHAR(10)
INSERT INTO @temp
SELECT 3, CHAR(9) + 'public sub new ()'
+ CHAR(13) + CHAR(10) + CHAR(9) + ''
+ CHAR(13) + CHAR(10) + CHAR(9) + 'end sub'

INSERT INTO @temp
SELECT 4, '#end region' + CHAR(13) + CHAR(10)

INSERT INTO @temp
SELECT 5, '#region' + CHAR(34) + 'Private Fields' + CHAR(34) + CHAR(13) + CHAR(10)
INSERT INTO @temp
SELECT 6, CHAR(9) + 'private ' +
+ '_' + COLUMN_NAME + ' as ' + CHAR(9) +
CASE
WHEN DATA_TYPE LIKE '%CHAR%' THEN 'string '
WHEN DATA_TYPE LIKE '%INT%' THEN 'Nullable (Of integer) = nothing '
WHEN DATA_TYPE LIKE '%DATETIME%' THEN 'Nullable(Of DateTime) = nothing '
WHEN DATA_TYPE LIKE '%BINARY%' THEN 'byte() '
WHEN DATA_TYPE = 'BIT' THEN 'Nullable (Of boolean) = nothing '
WHEN DATA_TYPE LIKE '%TEXT%' THEN 'string '
WHEN DATA_TYPE LIKE '%NUMERIC' THEN 'Nullable(Of decimal) = nothing '
ELSE 'object '
END
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table_name
ORDER BY ORDINAL_POSITION

INSERT INTO @temp
SELECT 7, '#end region' +
CHAR(13) + CHAR(10)

INSERT INTO @temp
SELECT 8, '#region' + CHAR(34) + 'Public Properties' + CHAR(34) + CHAR(13) + CHAR(10)
INSERT INTO @temp
SELECT 9, CHAR(9) + 'public property ' +
+ COLUMN_NAME + ' as ' +
CASE
WHEN DATA_TYPE LIKE '%CHAR%' THEN 'string '
WHEN DATA_TYPE LIKE '%INT%' THEN ' Nullable (Of integer) '
WHEN DATA_TYPE LIKE '%DATETIME%' THEN 'Nullable (Of DateTime) '
WHEN DATA_TYPE LIKE '%BINARY%' THEN 'byte() '
WHEN DATA_TYPE = 'BIT' THEN 'Nullable (Of boolean) '
WHEN DATA_TYPE LIKE '%TEXT%' THEN 'string '
WHEN DATA_TYPE LIKE '%NUMERIC' THEN 'Nullable(Of decimal) '
ELSE 'object '
END +
CHAR(13) + CHAR(10) + CHAR(9) + '' +
CHAR(13) + CHAR(10) + CHAR(9) + CHAR(9) +
'get' + CHAR(13) + CHAR(10) + 'return _' + COLUMN_NAME + CHAR(13) + CHAR(10) + ' end get' +
CHAR(13) + CHAR(10) + CHAR(9) + CHAR(9) +
'set (byval value as ' +
CASE
WHEN DATA_TYPE LIKE '%CHAR%' THEN 'string '
WHEN DATA_TYPE LIKE '%INT%' THEN ' Nullable(Of integer)'
WHEN DATA_TYPE LIKE '%DATETIME%' THEN ' Nullable(Of DateTime) '
WHEN DATA_TYPE LIKE '%BINARY%' THEN 'byte() '
WHEN DATA_TYPE = 'BIT' THEN 'Nullable (Of boolean) '
WHEN DATA_TYPE LIKE '%TEXT%' THEN 'string '
WHEN DATA_TYPE LIKE '%NUMERIC' THEN 'Nullable (Of decimal) '
ELSE 'object '
END
+' )' + CHAR(13) + CHAR(10) + '_' + COLUMN_NAME + ' = value' + CHAR(13) + CHAR(10) + 'end set' +
CHAR(13) + CHAR(10) + CHAR(9) + ' end property' + CHAR(13) + CHAR(10)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table_name
ORDER BY ORDINAL_POSITION

INSERT INTO @temp
SELECT 10, '#end region'

INSERT INTO @temp
SELECT 11, '#Region "CRUD Procedures"'

INSERT INTO @temp
SELECT 13, 'Public Function Obtain(ByVal p'+ @table_name +' as Integer) as IList (Of _'+ @table_name +')' + CHAR(13)+CHAR(10)+
+ CHAR(32)+ CHAR(32) + 'return Mapper.QueryForList(Of _' + @table_name + ')("SelectFrom'+@table_name+'")'+CHAR(13)+CHAR(10)+
+ CHAR(39)+'INSERT NECESSARY KEYS' + +CHAR(13)+CHAR(10)+
+ 'End Function'

INSERT INTO @temp
SELECT 14, 'Public Sub Delete(ByVal p'+ @table_name +' as Integer)' + CHAR(13)+CHAR(10)+
+ CHAR(32)+ CHAR(32) + 'Mapper.Delete("Delete'+@table_name+'")'+CHAR(13)+CHAR(10)+
+ CHAR(39)+'INSERT NECESSARY KEYS' + +CHAR(13)+CHAR(10)+
+'End Sub'

-- start of update sub --
INSERT INTO @temp
SELECT 15, 'Public Sub Update()' + CHAR(13)+ CHAR(10)+
+ CHAR(32)+ CHAR(32) + 'Mapper.Update("Update'+@table_name+'",Me)'+ CHAR(13)+ CHAR(10)+
+'End Sub'
-- end of update sub --


-- start of Insert sub --
INSERT INTO @temp
SELECT 16, 'Public Sub Insert()' + CHAR(13)+ CHAR(10)+
+ CHAR(32)+ CHAR(32) + 'Mapper.Insert("Insert'+@table_name+'",Me)'+ CHAR(13)+ CHAR(10)+
+'End Sub'
-- end of Insert sub --

INSERT INTO @temp
SELECT 17, '#End Region'


-- start of Insert sub --
INSERT INTO @temp
SELECT 16, 'Public Function CompareTo(ByVal other as _'+@table_name+') as Integer Implements System.IComparable (Of _'+@table_name+').CompareTo' + CHAR(13)+ CHAR(10)+

+'End Function'
-- end of Insert sub --


INSERT INTO @temp SELECT 18, CHAR(13) + CHAR(10) + ' end class'
-- end of code

DECLARE @code varchar(8000)
DECLARE TableCol Cursor FOR SELECT code FROM @temp ORDER BY sort
Open TableCol
FETCH NEXT FROM TableCol INTO @code
WHILE @@FETCH_STATUS = 0 BEGIN
PRINT @code + CHAR(13)
FETCH NEXT FROM TableCol INTO @code
END
-- ----------------
-- clean up cursor
-- ----------------
CLOSE TableCol
DEALLOCATE TableCol

Start of my technology blogging

Tempted by the http://www.continuouslylearning.blogspot.com/, belonging to Perry McKenzie, whom I have an honor to work with, I decided to start my own little tech blog.