My Notebook: Development

Mapping SQL Server integer types to .NET types

BigInt = Int64 (Long)
Int = Int32 (Integer)
SmallInt = Int16 (Short)
TinyInt = Byte

e.g. byte recurrenceType = reader.GetByte(8);

Posted 30 April 2010 and tagged as , ,

Setting AM & PM Designators

This is a useful little tip. By default, AM and PM time designators are output in uppercase (in the 1033 language set atleast) but there are times (in this case, enforced by a coporate style guide) when they should be lowercase. There are two ways to achieve this – one is…

DateTime now = DateTime.Now;
String nowString = now.ToString("dddd, d MMMM yyyy a\\t h.mm");
nowString = nowString + now.ToString("tt").ToLower();

… which is okay if you’re just using it the once. More than once, you’re better off using a DateTimeFormatInfo to set the AM and PM designators. Like so…

DateTimeFormatInfo dtfi = new DateTimeFormatInfo();
dtfi.AMDesignator = "am";
dtfi.PMDesignator = "pm";

String nowString = now.ToString("dddd, d MMMM yyyy a\\t h.mmtt", dtfi);

Posted 16 April 2010 and tagged as ,

Shrinking MSSQL Log Files Automatically

The MSSQL server for the SharePoint farm I manage always seems to be running out of room. After a little rummaging, I found the culprit – the database log files. For databases that ranged from 10MB to 45MB, the log files were as large as 450MB – which, when you calculate the space used by 50+ databases, all adds up.

To solve the issue I created a new scheduled task on the server which triggers the following script (found at the end of this post) once a week.

DECLARE @DBName varchar(255)
DECLARE @DBLogicalFileName varchar(255)
DECLARE @DATABASES_Fetch int
DECLARE DATABASES_CURSOR CURSOR FOR

SELECT DATABASE_NAME = db_name(MaTableMasterFiles.database_id), MaTableMasterFiles.Name
FROM sys.master_files MaTableMasterFiles
WHERE

-- ONLINE
MaTableMasterFiles.State = 0

-- Only look at databases to which we have access
AND has_dbaccess(db_name(MaTableMasterFiles.database_id)) = 1

-- Not master, tempdb or model
AND db_name(MaTableMasterFiles.database_id) NOT IN ('Master','tempdb','model')

AND type_desc LIKE 'log'
GROUP BY MaTableMasterFiles.database_id, MaTableMasterFiles.name
ORDER BY 1

OPEN DATABASES_CURSOR
FETCH NEXT FROM DATABASES_CURSOR INTO @DBName, @DBLogicalFileName
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC ('BACKUP LOG [' + @DBName + '] WITH TRUNCATE_ONLY')
EXEC ('Use [' + @DBName + '] DBCC SHRINKFILE ("' + @DBLogicalFileName + '")')
FETCH NEXT FROM DATABASES_CURSOR INTO @DBName, @DBLogicalFileName
END

CLOSE DATABASES_CURSOR
DEALLOCATE DATABASES_CURSOR

Posted 15 April 2010 and tagged as

Detecting Mobile Devices with ASP.NET

I’ve no imediate use for this code, but I know wthat it will be useful in the future – especially as the number of user’s accessing websites via mobile phones and PDAs grow.

Taken from a post by truelove.

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
If Not IsPostBack Then
Dim Browser_obj As System.Web.Mobile.MobileCapabilities = CType(Request.Browser, System.Web.Mobile.MobileCapabilities)
If Browser_obj.Browser = "Pocket IE" Then
Label1.Text = "the is Pocket PC"
ElseIf Browser_obj.Browser = "IE" Then
Label1.Text = "Microsoft Internet Explorer"
ElseIf Browser_obj.Browser = "Phone.com" Then
Label1.Text = "the is Openwave"
End If
End If
End Sub

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
If Not IsPostBack Then
Dim browser As System.Web.Mobile.MobileCapabilities = CType(Request.Browser, System.Web.Mobile.MobileCapabilities)
If browser.ScreenCharactersWidth < 20 Then
Label1.Text = "short text message"
Else
Label1.Text = "long text message"
End If
End If
End Sub

Posted 19 November 2009 and tagged as

Copying data to the clipboard using C#

Clipboard.SetText( “Hello World!”);

Short and sweet! :)

Posted 4 November 2009 and tagged as

Row numbers within MySQL

Sometime you will need a query that will return a resultset of ranked records. The following MySQL query will do the trick:

SELECT @rownum:=@rownum+1 AS rank, companies.* FROM conpanies p, (SELECT @rownum:=0) r ORDER BY profit DESC LIMIT 20;

Posted 10 April 2009 and tagged as

Skype buttons

I had used this service before and spent a while trying to track down the URL again. Here it is for future reference.

http://www.skype.com/intl/en/share/buttons/wizard.html

Posted 13 February 2009 and tagged as

A minimal XHTML document

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" lang="en" xml:lang="en">
 <head>
  <meta http-equiv="content-type" content="text/html; charset=utf-8"/>
  <title>title</title>
  <link rel="stylesheet" type="text/css" href="style.css"/>
  <script type="text/javascript" src="script.js"></script>
 </head>
 <body>

</body>
</html>

Posted 14 January 2009 and tagged as

Strip HTML Tags from a String

Private Function ConvertHtmlToPlainText(ByVal htmlText As String) As String
Return Regex.Replace(htmlText, "<[^>]*>", String.Empty)
End Function

Posted 14 January 2009 and tagged as

ROW_NUMBER()

To simply returning a limited number of records while using SubSonic I added a position field to the View.

SELECT ROWID= ROW_NUMBER() OVER ( ORDER BY ProductID ASC), ProductID, [Name]
FROM Production.Product

SQL Server 2005 ROW_NUMBER()

Posted 30 September 2008 and tagged as

« Older Entries