I Heart Microsoft: February 2008

ASP.NET: Caching in a Web Service

I didn't know that caching worked with Web Services... but it does.

using System.Web.Caching;

...

HttpContext.Current.Cache[key] == "some value";
string value = HttpContext.Current.Cache[key].ToString();

SQL Server 2005: Restoring Data to a Table with an ID column

I deleted some data from a table today that I shouldn't have. With a few steps in SQL Server Management Studio, I was able to recover the data.

Restore Database Backup (of course you have automated backups)
1. Right Click Database -> Tasks -> Restore -> Database
2. Select From device:
3. Find the .bak file
4. Follow the prompts

Export data from source table to destination table
1. Right Click Database -> Tasks -> Export Data...
2. Fill in the source database / server. Next.
3. Fill in the destination database / server. Next.
4. Choose Write a query to specify data to transfer. Next.
5. Enter the query for data you want to copy. Next.
6. For Destination select the target table. Next.
7. Click the Edit button in the Mapping column. OK.
8. Check the Enable Identity Insert column. OK.
9. Preview, etc, and run (Next, Next, Finish, etc)

ADO.NET 2.x: DataTable.Select()

Converting a data type in a DataTable.Select() filter expression:

string filterExpression = "Convert(Year, 'System.String') LIKE '%{0}%'";
string sortExpression = "Year DESC";
DataRow[] rows = dataTable.select(filterExpression, sortExpression);

SQL Server 2005: Show Users / Disconnect Users

Show Users

USE MASTER
SELECT * FROM sysprocesses WHERE dbid = DB_ID('dbname')

Disconnect Users

USE master
GO
DECLARE @dbname varchar(30), @spid varchar(10), @start datetime
SELECT @start = current_timestamp, @dbname = 'dbname'
-- Timeout after 5 mts
while(exists(Select * FROM sysprocesses WHERE dbid = db_id(@dbname)) AND
 datediff(mi, @start, current_timestamp) < 5)
    begin
     DECLARE spids CURSOR FOR
      SELECT convert(varchar, spid) FROM sysprocesses
       WHERE dbid = db_id(@dbname)
     OPEN spids
     while(1=1)
     BEGIN
      FETCH spids INTO @spid
      IF @@fetch_status < 0 BREAK
      exec('kill ' + @spid)
     END
     DEALLOCATE spids
END
GO

Rant: Windows Live SSO Down?

I can't log into to Windows Live this morning. Timeouts for the last two hours. Is it capacity issues or what? Where do you go to check service status to find out if it is connectivity from your computer, your local network, your region, or just their shitty services in general? I wish everyone would just use Google Talk.

The connection has timed out
The server at login.live.com is taking too long to respond.

It's back up now. How annoying.

Firefox 2.x Memory Leak Fix

To free up memory in Firefox add the following setting. Once restarted, minimizing the browser will release resources.

Open FireFox

Enter about:config in the address bar

Right click somewhere in the body, click New -> Boolean

Enter config.trim_on_minimize for preference name, click ok, click true, click ok

Restart FireFox

.NET 2.0: Custom Configuration Section - In 3 Steps

This is a custom app.config / web.config configuration class that allows reading collections of custom objects. This allows a lot more flexibility and makes the configuration easier to read and update.

Create a new class called CustomConfiguration (add System.Configuration to your references if you get a compile error)

public class CustomConfiguration : ConfigurationSection
{
    [ConfigurationProperty("keys", IsRequired = false)]
    public KeyCollection Keys
    {
        get { return this["keys"] as KeyCollection; }
    }
}

public class Key : ConfigurationElement
{
    [ConfigurationProperty("name", IsRequired = false)]
    public string Name
    {
        get { return this["name"] as string; }
    }

    [ConfigurationProperty("value", IsRequired = false)]
    public string Value
    {
        get { return this["value"] as string; }
    }
}

public class KeyCollection : ConfigurationElementCollection
{
    public Key this[int index]
    {
        get
        {
            return base.BaseGet(index) as Key;
        }
        set
        {
            if (base.BaseGet(index) != null)
                base.BaseRemoveAt(index);
            this.BaseAdd(index, value);
        }
    }

    protected override ConfigurationElement CreateNewElement()
    {
        return new Key();
    }

    protected override object GetElementKey(ConfigurationElement element)
    {
        return ((Key)element).Name;
    }
}

Add to web/app.config

<configSections>
    <section name="customConfiguration" type="CustomConfiguration" />
  </configSections>
  <customConfiguration>
    <keys>
      <add name="1" value="a"/>
      <add name="2" value="b"/>
    </keys>
</customConfiguration>

Test it (asp.net in this case):

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        foreach(Key key in config.Keys)
            Response.Write(String.Format("{0}:{1}<br/>", key.Name, key.Value));
    }

    public static CustomConfiguration config
    {
        get { return (CustomConfiguration) ConfigurationManager.GetSection("customConfiguration"); }
    }
}

Reference(s):
Creating Custom Configuration Sections in Web.config Using .NET 2.0's Configuration API

Visual Studio 2005: "The project type is not supported by this installation."

Problem:

The project type is not supported by this installation.

while opening an existing web application

Solution: Install Visual Studio 2005 Service Pack 1

SQL Server 2005: "Unable to cast COM object..."

If you get this:

Microsoft SQL Server Management Studio
Unable to cast COM object of type 'System.__ComObject' to interface type 'Microsoft.VisualStudio.OLE.Interop.IServiceProvider'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{6D5140C1-7436-11CE-8034-00AA006009FA}' failed due to the following error: No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE)). (Microsoft.VisualStudio.OLE.Interop)

Run this:

regsvr32 c:\windows\system32\actxprxy.dll

.NET 2.0: XML Serialization and Deserialization Example

Serialization for me is a pain in the butt. I think the ultimate goal is just to save typing but I'm not so sure that it's worth the aggravation. The following are two methods that do the work of serializing and deserializing objects. Following those are some sample classes with the right syntax and the resulting xml. If all the stars line up, you get interoperable xml and objects. You can even use the magical XSD.exe to generate your classes from well formed XML. As with all magical tools, you need to get pretty lucky to make it work sometimes.

Serialize

public static string SerializeObject(T obj)
{
   try
   {
      string xmlString = null;
      MemoryStream memoryStream = new MemoryStream();
      XmlSerializer xs = new XmlSerializer(typeof(T));
      XmlTextWriter xmlTextWriter = new XmlTextWriter(memoryStream, Encoding.ASCII);
      xs.Serialize(xmlTextWriter, obj);
      memoryStream = (MemoryStream)xmlTextWriter.BaseStream;

       ASCIIEncoding encoding = new ASCIIEncoding();
      xmlString = encoding.GetString(memoryStream.ToArray());

      return xmlString;
   }
   catch
   {
      return string.Empty;
   }
}

Deserialize

public static T DeserializeObject(string xml)
{
   XmlSerializer xs = new XmlSerializer(typeof(T));

   ASCIIEncoding encoding = new ASCIIEncoding();
   byte[] byteArray = encoding.GetBytes(xml);

   MemoryStream memoryStream = new MemoryStream(byteArray);
   XmlTextWriter xmlTextWriter = new XmlTextWriter(memoryStream, Encoding.ASCII);
   return (T)xs.Deserialize(memoryStream);
}

Object Classes

// the root element in the resulting xml, there can be only one
[XmlRoot("adf")]
public class ADF
{
    public ADF()
    {
         Prospect = new Prospect();
    }

    [XmlElement("prospect")]
    public Prospect Prospect;
}

// a wrapper element for all the other elements in the document, not the same as the root element
public class Prospect
{
    public Prospect()
    {
        Vehicles = new Vehicles();
    }

    [XmlElement("requestdate")]
    public string RequestDate;

    [XmlElement("vehicles")]
    public Vehicles Vehicles;

    [XmlElement("customer")]
    public Customer Customer;
}

public class Customer
{
    public Customer()
    {
        Name = new Name();
    }

    [XmlElement("name")]
    public Name Name;
}

// an element with text inside and an attribute called "part"
public class Name
{
    [XmlText]
    public string Text;

    [XmlAttribute("part")]
    public string Part;
}

// an element that contains a list of child elements
[Serializable] // notice this is necessary for using generic collections (using System.Collections.Generic)
public class Vehicles
{
    [XmlElement("vehicle")]
    public List<Vehicle> Vehicle;
}

// a child element with many properties
public class Vehicle
{
    [XmlElement("year")]
    public string Year;

    [XmlElement("make")]
    public string Make;

    [XmlElement("model")]
    public string Model;
}

Resulting XML (or Source XML)

<adf>
 <prospect>
    <customer>
      <name part="full">Patrick Lewis</name>
    </customer>
    <vehicles>
      <vehicle>
        <year>2005</year>
        <make>Toyota</make>
        <model>Tacoma</model>
      </vehicle>
      <vehicle>
        <year>2007</year>
        <make>Ducati</make>
        <model>Monster</model>
      </vehicle>
    </vehicles>
  </prospect>
</adf>

Working with the object

public void serialize()
{
    // create an object, (I didn't use constructors so this is a pita)
    ADF adf = new ADF();
    adf.Prospect.Customer.Name.Text = "Patrick Lewis";
    adf.Prospect.Customer.Name.Part = "full";

    Vehicle v1 = new Vehicle();
    v1.Make = "Toyota";
    v1.Year = "2005";
    v1.Model = "Tacoma";
    adf.Prospect.Vehicles.Vehicle.Add(v1);
    
    Vehicle v2 = new Vehicle();
    v2.Make = "Ducati";
    v2.Model = "Monster";
    v2.Year = "2007";
    adf.Prospect.Vehicles.Vehicle.Add(v2);
    
    // serialize
    string xml = SerializeObject(adf);

    // use the xml for something
    XmlDocument xDoc = new XmlDocument();
    xDoc.LoadXml(xml);

    // deserialize
    ADF adf2 = DeserializeObject(xDoc.OuterXml);

    // use the object data for something
    string name = adf2.Prospect.Customer.Name.Text;
}

.NET 2.0: Creating Windows Services

Creating, Installing, and Debugging:
Creating a Windows Service in .NET

Reinstall Service (put this in a .cmd):

cd C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\
installutil /u path\to\your.exe
installutil -i path\to\your.exe

ASP.NET: Creating Web Services

Professional ASP.NET Web Services : Asynchronous Programming

SQL Server 2005: Paging with ROW_NUMBER()

SELECT someColumn
FROM
(
SELECT ROW_NUMBER()
OVER
(
ORDER BY someColumn ASC
)
AS Row, someColumn FROM someTable
)
AS someTable
WHERE Row >= 0 AND Row <= 100 and someColumn = someValue

SQL Server 2005: Resize/Truncate Log Files

Resize - no backup, just resize down to original

BACKUP LOG tempdb WITH TRUNCATE_ONLY

Resize - with backup

BACKUP LOG tempdb TO tempdb_backup

And when that doesn't work use the brute force method:

USE [master]
GO
ALTER DATABASE [YourDatabase] SET RECOVERY SIMPLE WITH NO_WAIT
GO
USE [YourDatabase]
GO
DBCC SHRINKFILE (YourDatabase_log, 10)
GO

Note: I don't know anything about recovery modes, you'll probably want to do a full backup before doing this. I was just pissed off enough to go ahead and do this anyway when the two methods that should have worked didn't.

SQL Server 2005: Select Columns from Table

Select columns along with their data types from a SQL Database.

Use [DatabaseName]
SELECT OBJECT_name(c.object_id) TableName
 , c.name ColumnName
 , c.column_id Sequence
 , c.max_length MaxLength
 , c.is_identity as PrimaryKey
 , t.name as DataType
FROM sys.columns AS c
 JOIN sys.types AS t ON c.user_type_id=t.user_type_id
WHERE OBJECT_name(c.object_id) = [TableName]
ORDER BY c.column_id;

SQL Server 2005: Select All Tables in a Database

Use [DatabaseName]
SELECT SCHEMA_NAME(schema_id) AS SchemaName,name AS TableName
FROM sys.tables

SQL Server 2005: Kill a Process

Kill a process, blocking or otherwise:

SELECT * FROM master..sysprocesses WHERE dbid=db_id('dbname')
KILL #;
KILL # WITH STATUSONLY;

Visual Studio: Debug Syntax

#if DEBUG
    // debug code
#else
    // production code
#endif

Mootools: Cancel an event

element.addEvent('click', function(e){
  e = new Event(e);
  e.stopPropagation();
  // do stuff
});

I Heart Microsoft