Identifying invalid resx files in web project

Recently I needed to do a bit of cleanup as in one project after many UI changes, where we moved or changed structure of pages, were left resource files which no longer had corresponding page or user control. 

Nobody likes doing such cleanup manually so I come up with short powershell script which went though individual resource files and try to identify if it is still needed or not.

Explanation:

1. Find all files in folder with local resources (e.g. workflow.aspx.en-US.resx)
2. For each file remove the suffix e.g. en-US.resx
3. Remove the subdirectory from name in order to get name of the page worflow.aspx
4. Get each file just once (there can be multiple language mutations)
5. Check if the file exists
6. Write out only those where the file was not found

$res_location = "./App_LocalResources/"
Get-ChildItem -Path $res_location -Name 
| ForEach-Object -Process {"$res_location$_" -ireplace "(.[a-z]{2}-[a-z]{2})?.resx",""} 
| ForEach-Object -Process {$_.Replace($res_location, "")} 
| Get-Unique 
| ForEach-Object -Process {Write-Output "$_ $(Test-Path $_ -PathType Leaf)"} 
| where { $_ -match "False" }

Jenkins service reinstall

I have not investigated more but it seems Jenkins does not like Java update. Sometimes the windows service fails to start. The simplest seems to remove it and install again...

sc delete jenkinsslave-D__Jenkins

Images used in web project

I am doing little clean-up in the project. Need to identify what images are still used and the rest I would like to remove. This PowerShell script helps to identify images which are used in any page, user control or c# code.

For each in the application is executed command which tries to find its name in all files matching given pattern.

@(Get-ChildItem 'D:\WebApp\images') | 

    ForEach-Object { 
        $_.name | Write-Host -NoNewline
        Write-Host ' ' -NoNewline
        
        dir 'D:\WebApp' -I *.aspx,*.ascx,*.cs,*.master -R | 
            Select-String $_.name -Quiet | where  {$_ -eq $true} | Write-Host -NoNewline
        
        Write-Host #writes a new line
    }

Moving SonarQube to other server

At first I had SonarQube installed together with Jenkins on the same server. SonarQube is quite resource demanding and it was causing slowness of the other jobs / tasks running on Jenkins.

Moving to other instance was simple as installing SonarQube on other server and configuring Jenkins to use new SonarQube instance.

Steps required

• Java runtime environment
• SonarQube binaries
• MSSQL JDBC driver (make sure you get sqljdbc_auth.dll available on PATH)
• SonarQube configuration was copied from old server to new (sonarqube/conf/*)
• SonarQube instance was registered as service and started. 
• Once the service is running then try to access it in browser, if it is not accessible then check logs (sonarqube/logs) and resolve.
• Once the web is up and running there had to be installed language plugins again. 
• Jenkins configuration have to be updated with new SonarQube url (Manage Jenkins > Configure System)

SonarQube AD authentication setup

User in SonarQube can be validated against ActiveDirectory, once the user is validated it will be automatically created which is useful if there are a lot users who are required to use the tool.

1. LDAP plugin needs to be installed in SonarQube marketplace
2. sonar.properties needs to be updated with LDAP configuration details
3. SonarQube service needs to be restarted. 
4. Go to SonarQube web. 
• If there are issues with the configuration check the logs (SonarQube\logs\web.log)

# LDAP configuration
# General Configuration
sonar.security.realm=LDAP
sonar.authenticator.createUsers=true
ldap.url=ldap://ldapserver:389
ldap.bindDn=CN=username,CN=Users,DC=domain,DC=company,DC=com
ldap.bindPassword=password

# User Configuration
ldap.user.baseDn=DC=domain,DC=company,DC=com
ldap.user.request=(&(objectClass=user)(sAMAccountName={login}))
ldap.user.realNameAttribute=cn
ldap.user.emailAttribute=mail

ADExplorer is useful to confirm AD object properties and validate the server address and credentials.

In case it is not clear what are AD server details then use guidance on SO.

More details can be found in SonarQube LDAP plugin documentation.

SonarQube MSSQL backend setup

1. Create Database
• CREATE DATABASE "sonar" COLLATE Latin1_General_CS_AS 
• It needs to be case and accent sensitive
• Create user and add permissions to user so tables can be created with SonarQube start
2. Modify SonarQube configuration to point out to database created
• Go to SonarQube\conf\sonar.properties and follow instructions in the file
• Uncomment and change connection string e.g. sonar.jdbc.url=jdbc:sqlserver://localhost;databaseName=sonar;integratedSecurity=true
• In case there is not used integrated also specify sonar.jdbc.username and sonar.jdbc.password
3. In case there is used integrated connection to database used then download Microsoft JDBC driver, unzip and somewhere to the system PATH place sqljdbc_auth.dll (either 32 or 64 bit based on your operating system)
4. Restart SonarQube instance.

SonarQube installation quide 

Retrieving binary data from base64 node in xml variable

In case there is xml document with base64 encoded binary data e.g. PDF (below example has the binary data shortened)

declare @doc xml
set @doc = '<doc><PdfData>JVBERi0xLjQN</PdfData></doc>'

then there is very simple to get the binary data decoded as binary again

select CAST (@doc.value('(//PdfData/text())[1]', 'varbinary(max)') AS varchar(max))

https://stackoverflow.com/questions/5082345/base64-encoding-in-sql-server-2005-t-sql

How to find size of databases and last time they got accessed

Sometimes you may need to free some space on MSSQL server and idetify databases which are not used long time back. This is especially useful on development servers where people may restore database just for troubleshooting of some issues and then the database is not needed anymore!

with fs
as
(
    select database_id, type, size * 8.0 / 1024 size
    from sys.master_files
)

select db.*, last_user_seek = MAX(last_user_seek),
 last_user_scan = MAX(last_user_scan),
 last_user_lookup = MAX(last_user_lookup),
 last_user_update = MAX(last_user_update)
from (
 select 
  name,
  (select sum(size) from fs where type = 0 and fs.database_id = db.database_id) DataFileSizeMB,
  (select sum(size) from fs where type = 1 and fs.database_id = db.database_id) LogFileSizeMB 
 from sys.databases db
) as db
left join sys.dm_db_index_usage_stats stats on stats.database_id = db_id(db.name)
group by db.name, db.DataFileSizeMB, db.LogFileSizeMB
order by db.DataFileSizeMB desc

References:
Sql server 2008 howto query all databases sizes
How do you find the last time a database was accessed

Removing duplicate records from database table

If there is need to select or remove data from table which are duplicate by multiple fields (field1 and field2) in the below example, there can be used query like

WITH cte
     AS (SELECT ROW_NUMBER() OVER (PARTITION BY field1, field2
                                       ORDER BY ( SELECT 0)) RN
         FROM   mst_table)
DELETE FROM cte
WHERE  RN > 1;

MSSQL server - How to return values from multiple nodes in XML in single field

DECLARE @xml xml
SET @Xml = '<FIELD NAME="NAME" BASE="5179827" COUNT="4">
  <TOKEN TEXT="a">...</TOKEN>
  <TOKEN TEXT="b">...</TOKEN>
  <TOKEN TEXT="a">...</TOKEN>
  <TOKEN TEXT="b">...</TOKEN>
  <TOKEN TEXT="a">...</TOKEN>
  <TOKEN TEXT="c">...</TOKEN>
</FIELD>'

SELECT
  x.query('data(TOKEN/@TEXT)') AS List
 ,x.query('distinct-values(TOKEN/@TEXT)') AS DistinctList
 , x.query('<data>
    {
     for $x in distinct-values(TOKEN/@TEXT)
     return 
      (concat($x, ","))
    }
   </data>
 ').query('data/text()') AS CommaSeparatedList
FROM @xml.nodes('/FIELD') AS d(x)

References

http://www.olcot.co.uk/sql-blogs/using-xquery-to-remove-duplicate-values-or-duplicate-nodes-from-an-xml-instance

MSSQL server - how to read XML data - XPATH, sql:variable and local-name()

Example xml used for below queries

declare @xml xml
set @xml = '<Document>
 <Main>
  <Field_01>Field_01Value</Field_01>
  <Field_05>Field_05Value</Field_05>
  <TableFields>
   <Table_02>
    <Row>
     <Column_01>Table_02Row_01Col_01Value</Column_01>
     <Column_02>Table_02Row_01Col_01Value</Column_02>
    </Row>
    <Row>
     <Column_01>Table_02Row_02Col_01Value</Column_01>
     <Column_02>Table_02Row_02Col_02Value</Column_02>
    </Row>
   </Table_02>
  </TableFields>
 </Main>
</Document>'

Get the value of Field_01 tag

SELECT @xml.value('(//Field_01/text())[1]', 'nvarchar(max)')

Get the value of field in case you have the field name defined dynamically

declare @field varchar(10)
set @field = 'Field_05'
SELECT @xml.value('(//*[local-name()=sql:variable("@field")]/text())[1]', 'nvarchar(max)')

Similar in case you need value of value of second row of column_01 in table defined dynamically

set @field = 'Table_02'
SELECT @xml.value('(//*[local-name()=sql:variable("@field")]/*/Column_01/text())[2]', 'nvarchar(max)')

MSSQL server - how to read XML data and join with another table data

This code snippet shows how to work with data stored in xml variable (or column of type xml). The xml data are joined with table and there are joined some data to output.

-- input
set @xml = '<docs>
<doc> <DocId>1000052</DocId><InvoiceNumber>S37</InvoiceNumber></doc>
<doc> <DocId>1000053</DocId><InvoiceNumber>S74</InvoiceNumber></doc>
<doc> <DocId>1000054</DocId><InvoiceNumber>E85</InvoiceNumber></doc>
</docs>'


SELECT
   p.value('(DocId)[1]', 'VARCHAR(10)') AS DocId,
   p.value('(InvoiceNumber)[1]', 'VARCHAR(100)') AS InvoiceNumber,
   ISNULL(d.SupplierID, '') AS Supplier
FROM @xml.nodes('/docs/doc') doc(p)
INNER JOIN Documents d ON d.DocId = p.value('(DocId)[1]', 'VARCHAR(10)')
FOR XML AUTO, ROOT ('docs')

Output:

<docs>
  <doc DocId="1000052" InvoiceNumber="S37" Supplier="Microsoft" />
  <doc DocId="1000053" InvoiceNumber="S74" Supplier="Amazon" />
  <doc DocId="1000054" InvoiceNumber="E85" Supplier="Sony" />
</docs>

Resources:

http://stackoverflow.com/questions/899313/select-values-from-xml-field-in-sql-server-2008

https://www.simple-talk.com/sql/learn-sql-server/using-the-for-xml-clause-to-return-query-results-as-xml/

https://msdn.microsoft.com/en-us/library/ms178107.aspx

MSSQL server - how to generate XML 1

Xml can be generated in SQL server many different ways and it is not always straightforward

Requirement:

Generate elements with same names and some data as attributes and some as element inner text. 

Example:

<document>
  <fields>
    <field name="Field_1" type="Field1">Field1 Value</field>
    <field name="Field_2" type="Field2">Field2 Value</field>
  </fields>
</document>

Query:

select TOP 1
 'Field1' 'field/@type',
 'Field_1' 'field/@name',
 Field1Value AS 'field',
 '',
 'Field2' 'field/@type',
 'Field_2' 'field/@name',
 Field2Value AS 'field',
 ''
from Invoice as tbl1
for xml path('fields'), type, Root('Document')

Resources: 
http://stackoverflow.com/questions/25412429/sql-server-generating-xml-with-generic-field-elements

ASP.NET webforms URL routing

URL routing is very cool functionality available for ASP.NET applications. It allows us to make the application URLs more user friendly. That was not the root key why I decided to use it in our application.

Application renders the PDF files on output dynamically from images in database and show it to users. They can see it in browser in Adobe Reader. There is one functionality in Adobe Reader which allows to send PDF file by email, using email client on the computer. From some version the attachment started to have aspx extension which forces user to rename it before it can be actually opened.

Simple solution to this is to use URL routing functionality to render the file to output as if it is really PDF.

Below are few links which I found useful during implementation and deployment of the application.


Routing with ASP.NET Web Forms
URL routing is not working in IIS 6
Troubleshooting ASP.NET routing on IIS 7

Windows phone 8 emulator

I have come accross these problems when installing Windows phone 8 emulator in vmware player.

1. CPU
- needs to support virtualization and it needs to be enabled in BIOS

2. operating system
- 64 bit
- needs to be some which supports SLAT (Windows 8, Windows Server 2012 ...)
- needs to have Hyper-V enabled

3. VMware settings
- Edit vmx file and add this configuration hypervisor.cpuid.v0 = "FALSE",  mce.enable = "TRUE"
- Processors setting - enable Virtualize Intel VT-x/EPT or AMD-V/RVI
- Processors setting - setup at least two cores
- Memory settings - setup enough memory for virtual machine

4. Windows Mobile 8 SDK
- will download and install all required prerequisities, it is good to have prepared steps above


Sources:
Windows 8 Hyper-V will require SLAT (Second Level Address Translation)
MachineSLATStatusCheck
Windows Server 2012 and Hyper-V
Hyper-V in Workstation 8
Windows Phone SDK

MSSQL trace file analysis

Today I was looking for a way how to access MSSQL server trace data from TSQL. The solution is to import data to database via function fn_trace_gettable.

SELECT * INTO temp_trc
FROM fn_trace_gettable('c:\temp\trace.trc', default);

Issue to restore of replicated database solved

Restore of backuped database which used replications can be a problem. Database was restored successfully but it was still marked as part of publication (see is_published in sys.databases). There was not possible to truncate, alter structure of tables etc.

This issue can be resolved by running this command on database:

exec sp_removedbreplication 'database_name'

How to Export Image column to file on MSSQL II

Another way how to export binary data from database. It is possible to use bcp utility.

bcp "SELECT img FROM db_name.dbo.Images WHERE ID = 1" queryout "c:\001.jpg" -T -S server

Once you run it you will be asked for the file storage type (no change required), prefix-length (needs to be 0), length of field and field terminator (no change)

Update of XML data

Code snippet for updating specific data in xml column in mssql

declare @date datetime
set @date = GETDATE()

UPDATE XmlDocuments
SET    xmlmetadata.modify ('replace value of (/Header/Date/text())[1] with sql:variable("@date")') 
WHERE ID = '1000001'

Bulk update of Image data in MSSQL

This is just simple example how to do update of image data in Microsoft SQL database.

UPDATE Images SET Img = (SELECT BulkColumn AS Img FROM OPENROWSET(BULK N'C:\NoImage.TIF', SINGLE_BLOB) AS [Document])