deleting or moving dynamically created controls


Monday, November 27, 2006

When adding controls dynamically to a page, it is important to do so on every page load or they no longer exist.  For example, the code below reads contact records from a database and creates LinkButtons for each contact.  It also encodes the LinkButtons with a CommandArgument set to the contact's unique identifier and wires it up to an event handler before adding to the page via a panel.

protected override void OnInit(EventArgs e)
{
    SetupContacts();
}

private void SetupContacts()
{
    .
    .
    .
    foreach (ContactsRow row in contactsTable.Rows)
    {
        LinkButton link = new LinkButton();
        link.Text = row.FirstName + " " + row.LastName;
        link.CommandArgument = row.ContactID.ToString();
        link.Click += new EventHandler(link_Click);
        panel1.Controls.Add(link);
}

A problem can occur when one of the dynamically created controls is deleted or moved on the page.  This is due to the fact that ViewState connects its information to a control by the control’s ID.  If you do not specify an ID, .Net creates the ID dynamically (e.g., ctl14, ctl19, etc.).  This will cause issues if you create a control on your page that deletes or moves the dynamically created control(s).  This is because it will change the ID of the control(s) after ViewState has been loaded and connected to the previous ID(s).  This is caused because child controls have their events fire after the ViewState is loaded for the page and tied to each controls’ ID.  To illustrate, suppose we have a Button control that accomplishes the following:

protected void Delete_Click(object sender, EventArgs e)
{
    //delete contact with unique identifier of 1
    DeleteContact(1);
    //refresh custom controls to eliminate deleted contact
    SetupControls();
}

After clicking the Delete button, the deleted contact will disappear but none of the LinkButtons will fire properly on the next click.  They will begin to work after you click one of the LinkButtons once, because the corresponding postback will reconnect the LinkButtons’ IDs to the ViewState successfully.  The key to getting around this issue is to be sure to add a unique identifier when you dynamically create the controls.  This will keep the ViewState info connected to the controls even if some of them are moved or deleted.  See the bolded code in the modified SetupContacts code below.

private void SetupContacts()
{
    foreach (ContactsRow row in contactsTable.Rows)
    {
        LinkButton link = new LinkButton();
        link.Text = row.FirstName + " " + row.LastName;
        link.CommandArgument = row.ContactID.ToString();
        link.Click += new EventHandler(link_Click);
        link.ID = "contactLink" + row.ContactID.ToString();
        panel1.Controls.Add(link);
    }
}

8:11:57 PM   | c# | asp.net |
 
transfering sql server logins from 7.0/2000 to 2005


Tuesday, November 21, 2006

Ran into an interesting issue when I attempted to restore a SQL 2000 database to a SQL 2005 database.  The user account restored into SQL Server, but it did not tie in with a corresponding login.  Creating a login would not help because the login's SID did not match the original login's SID.  I found a KB article that helped sort it out.  In a nut shell, there are a couple of stored procedures that can be run on the base 7.0/2000 server which generates a script that can be used on the upgraded server to create a login with the original SID and password.  Run the following TSQL on the source server to generate the two stored procedures:


USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
  DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
    @binvalue varbinary(256),
    @hexvalue varchar(256) OUTPUT
AS
DECLARE @charvalue varchar(256)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)
BEGIN
  DECLARE @tempint int
  DECLARE @firstint int
  DECLARE @secondint int
  SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
  SELECT @firstint = FLOOR(@tempint/16)
  SELECT @secondint = @tempint - (@firstint*16)
  SELECT @charvalue = @charvalue +
    SUBSTRING(@hexstring, @firstint+1, 1) +
    SUBSTRING(@hexstring, @secondint+1, 1)
  SELECT @i = @i + 1
END
SELECT @hexvalue = @charvalue
GO

IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
  DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
DECLARE @name    sysname
DECLARE @xstatus int
DECLARE @binpwd  varbinary (256)
DECLARE @txtpwd  sysname
DECLARE @tmpstr  varchar (256)
DECLARE @SID_varbinary varbinary(85)
DECLARE @SID_string varchar(256)

IF (@login_name IS NULL)
  DECLARE login_curs CURSOR FOR
    SELECT sid, name, xstatus, password FROM master..sysxlogins
    WHERE srvid IS NULL AND name <> 'sa'
ELSE
  DECLARE login_curs CURSOR FOR
    SELECT sid, name, xstatus, password FROM master..sysxlogins
    WHERE srvid IS NULL AND name = @login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
IF (@@fetch_status = -1)
BEGIN
  PRINT 'No login(s) found.'
  CLOSE login_curs
  DEALLOCATE login_curs
  RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated '
  + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
PRINT 'DECLARE @pwd sysname'
WHILE (@@fetch_status <> -1)
BEGIN
  IF (@@fetch_status <> -2)
  BEGIN
    PRINT ''
    SET @tmpstr = '-- Login: ' + @name
    PRINT @tmpstr
    IF (@xstatus & 4) = 4
    BEGIN -- NT authenticated account/group
      IF (@xstatus & 1) = 1
      BEGIN -- NT login is denied access
        SET @tmpstr = 'EXEC master..sp_denylogin ''' + @name + ''''
        PRINT @tmpstr
      END
      ELSE BEGIN -- NT login has access
        SET @tmpstr = 'EXEC master..sp_grantlogin ''' + @name + ''''
        PRINT @tmpstr
      END
    END
    ELSE BEGIN -- SQL Server authentication
      IF (@binpwd IS NOT NULL)
      BEGIN -- Non-null password
        EXEC sp_hexadecimal @binpwd, @txtpwd OUT
        IF (@xstatus & 2048) = 2048
          SET @tmpstr = 'SET @pwd = CONVERT (varchar(256), ' + @txtpwd + ')'
        ELSE
          SET @tmpstr = 'SET @pwd = CONVERT (varbinary(256), ' + @txtpwd + ')'
        PRINT @tmpstr
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
        SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
          + ''', @pwd, @sid = ' + @SID_string + ', @encryptopt = '
      END
      ELSE BEGIN
        -- Null password
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
        SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
          + ''', NULL, @sid = ' + @SID_string + ', @encryptopt = '
      END
      IF (@xstatus & 2048) = 2048
        -- login upgraded from 6.5
        SET @tmpstr = @tmpstr + '''skip_encryption_old'''
      ELSE
        SET @tmpstr = @tmpstr + '''skip_encryption'''
      PRINT @tmpstr
    END
  END
  FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
  END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO

The two stored procedures will be added in the source server's master database.  Next, run the sp_help_revlogin stored procedure on the source server.  The stored procedure will generate a script that needs to be run on the upgraded server to complete the login migration.
After utilizing this method of login migration, I was able to get my database user to associate to a newly created login account with the same SID and password.  This method works for login transfer between:
  • SQL Server 7.0 --> SQL Server 7.0
  • SQL Server 7.0 --> SQL Server 2000
  • SQL Server 7.0 --> SQL Server 2005
  • SQL Server 2000 --> SQL Server 2000
  • SQL Server 2000 --> SQL Server 2005
For more info, check out http://support.microsoft.com/kb/246133.

2:25:13 PM   | sql |