EncodingInfo Class

http://msdn.microsoft.com/en-us/library/system.text.encodinginfo.aspx

This class is primarily used by the Encoding class. The Encoding..::.GetEncodings method returns an array of this type.

This class is intended to provide minimal information about an encoding. To obtain additional information, the application should use the GetEncoding method to get an instance of the Encoding class, which contains more comprehensive information about the encoding it represents.

C#

Copy Code

using System;
using System.Text;

public class SamplesEncoding  {

   public static void Main()  {

      // Print the header.
      Console.Write( "Info.CodePage      " );
      Console.Write( "Info.Name                    " );
      Console.Write( "Info.DisplayName" );
      Console.WriteLine();

      // Display the EncodingInfo names for every encoding, and compare with the equivalent Encoding names.
      foreach( EncodingInfo ei in Encoding.GetEncodings() )  {
         Encoding e = ei.GetEncoding();

         Console.Write( "{0,-15}", ei.CodePage );
         if ( ei.CodePage == e.CodePage )
            Console.Write( "    " );
         else
            Console.Write( "*** " );

         Console.Write( "{0,-25}", ei.Name );
         if ( ei.CodePage == e.CodePage )
            Console.Write( "    " );
         else
            Console.Write( "*** " );

         Console.Write( "{0,-25}", ei.DisplayName );
         if ( ei.CodePage == e.CodePage )
            Console.Write( "    " );
         else
            Console.Write( "*** " );

         Console.WriteLine();
      }

   }

}

/*
This code produces the following output.

Info.CodePage      Info.Name                    Info.DisplayName
37                 IBM037                       IBM EBCDIC (US-Canada)
437                IBM437                       OEM United States
500                IBM500                       IBM EBCDIC (International)
708                ASMO-708                     Arabic (ASMO 708)
720                DOS-720                      Arabic (DOS)
737                ibm737                       Greek (DOS)
775                ibm775                       Baltic (DOS)
850                ibm850                       Western European (DOS)
852                ibm852                       Central European (DOS)
855                IBM855                       OEM Cyrillic
857                ibm857                       Turkish (DOS)
858                IBM00858                     OEM Multilingual Latin I
860                IBM860                       Portuguese (DOS)
861                ibm861                       Icelandic (DOS)
862                DOS-862                      Hebrew (DOS)
863                IBM863                       French Canadian (DOS)
864                IBM864                       Arabic (864)
865                IBM865                       Nordic (DOS)
866                cp866                        Cyrillic (DOS)
869                ibm869                       Greek, Modern (DOS)
870                IBM870                       IBM EBCDIC (Multilingual Latin-2)
874                windows-874                  Thai (Windows)
875                cp875                        IBM EBCDIC (Greek Modern)
932                shift_jis                    Japanese (Shift-JIS)
936                gb2312                       Chinese Simplified (GB2312)
949                ks_c_5601-1987               Korean
950                big5                         Chinese Traditional (Big5)
1026               IBM1026                      IBM EBCDIC (Turkish Latin-5)
1047               IBM01047                     IBM Latin-1
1140               IBM01140                     IBM EBCDIC (US-Canada-Euro)
1141               IBM01141                     IBM EBCDIC (Germany-Euro)
1142               IBM01142                     IBM EBCDIC (Denmark-Norway-Euro)
1143               IBM01143                     IBM EBCDIC (Finland-Sweden-Euro)
1144               IBM01144                     IBM EBCDIC (Italy-Euro)
1145               IBM01145                     IBM EBCDIC (Spain-Euro)
1146               IBM01146                     IBM EBCDIC (UK-Euro)
1147               IBM01147                     IBM EBCDIC (France-Euro)
1148               IBM01148                     IBM EBCDIC (International-Euro)
1149               IBM01149                     IBM EBCDIC (Icelandic-Euro)
1200               utf-16                       Unicode
1201               unicodeFFFE                  Unicode (Big-Endian)
1250               windows-1250                 Central European (Windows)
1251               windows-1251                 Cyrillic (Windows)
1252               Windows-1252                 Western European (Windows)
1253               windows-1253                 Greek (Windows)
1254               windows-1254                 Turkish (Windows)
1255               windows-1255                 Hebrew (Windows)
1256               windows-1256                 Arabic (Windows)
1257               windows-1257                 Baltic (Windows)
1258               windows-1258                 Vietnamese (Windows)
1361               Johab                        Korean (Johab)
10000              macintosh                    Western European (Mac)
10001              x-mac-japanese               Japanese (Mac)
10002              x-mac-chinesetrad            Chinese Traditional (Mac)
10003              x-mac-korean                 Korean (Mac)
10004              x-mac-arabic                 Arabic (Mac)
10005              x-mac-hebrew                 Hebrew (Mac)
10006              x-mac-greek                  Greek (Mac)
10007              x-mac-cyrillic               Cyrillic (Mac)
10008              x-mac-chinesesimp            Chinese Simplified (Mac)
10010              x-mac-romanian               Romanian (Mac)
10017              x-mac-ukrainian              Ukrainian (Mac)
10021              x-mac-thai                   Thai (Mac)
10029              x-mac-ce                     Central European (Mac)
10079              x-mac-icelandic              Icelandic (Mac)
10081              x-mac-turkish                Turkish (Mac)
10082              x-mac-croatian               Croatian (Mac)
12000              utf-32                       Unicode (UTF-32)
12001              utf-32BE                     Unicode (UTF-32 Big-Endian)
20000              x-Chinese-CNS                Chinese Traditional (CNS)
20001              x-cp20001                    TCA Taiwan
20002              x-Chinese-Eten               Chinese Traditional (Eten)
20003              x-cp20003                    IBM5550 Taiwan
20004              x-cp20004                    TeleText Taiwan
20005              x-cp20005                    Wang Taiwan
20105              x-IA5                        Western European (IA5)
20106              x-IA5-German                 German (IA5)
20107              x-IA5-Swedish                Swedish (IA5)
20108              x-IA5-Norwegian              Norwegian (IA5)
20127              us-ascii                     US-ASCII
20261              x-cp20261                    T.61
20269              x-cp20269                    ISO-6937
20273              IBM273                       IBM EBCDIC (Germany)
20277              IBM277                       IBM EBCDIC (Denmark-Norway)
20278              IBM278                       IBM EBCDIC (Finland-Sweden)
20280              IBM280                       IBM EBCDIC (Italy)
20284              IBM284                       IBM EBCDIC (Spain)
20285              IBM285                       IBM EBCDIC (UK)
20290              IBM290                       IBM EBCDIC (Japanese katakana)
20297              IBM297                       IBM EBCDIC (France)
20420              IBM420                       IBM EBCDIC (Arabic)
20423              IBM423                       IBM EBCDIC (Greek)
20424              IBM424                       IBM EBCDIC (Hebrew)
20833              x-EBCDIC-KoreanExtended      IBM EBCDIC (Korean Extended)
20838              IBM-Thai                     IBM EBCDIC (Thai)
20866              koi8-r                       Cyrillic (KOI8-R)
20871              IBM871                       IBM EBCDIC (Icelandic)
20880              IBM880                       IBM EBCDIC (Cyrillic Russian)
20905              IBM905                       IBM EBCDIC (Turkish)
20924              IBM00924                     IBM Latin-1
20932              EUC-JP                       Japanese (JIS 0208-1990 and 0212-1990)
20936              x-cp20936                    Chinese Simplified (GB2312-80)
20949              x-cp20949                    Korean Wansung
21025              cp1025                       IBM EBCDIC (Cyrillic Serbian-Bulgarian)
21866              koi8-u                       Cyrillic (KOI8-U)
28591              iso-8859-1                   Western European (ISO)
28592              iso-8859-2                   Central European (ISO)
28593              iso-8859-3                   Latin 3 (ISO)
28594              iso-8859-4                   Baltic (ISO)
28595              iso-8859-5                   Cyrillic (ISO)
28596              iso-8859-6                   Arabic (ISO)
28597              iso-8859-7                   Greek (ISO)
28598              iso-8859-8                   Hebrew (ISO-Visual)
28599              iso-8859-9                   Turkish (ISO)
28603              iso-8859-13                  Estonian (ISO)
28605              iso-8859-15                  Latin 9 (ISO)
29001              x-Europa                     Europa
38598              iso-8859-8-i                 Hebrew (ISO-Logical)
50220              iso-2022-jp                  Japanese (JIS)
50221              csISO2022JP                  Japanese (JIS-Allow 1 byte Kana)
50222              iso-2022-jp                  Japanese (JIS-Allow 1 byte Kana - SO/SI)
50225              iso-2022-kr                  Korean (ISO)
50227              x-cp50227                    Chinese Simplified (ISO-2022)
51932              euc-jp                       Japanese (EUC)
51936              EUC-CN                       Chinese Simplified (EUC)
51949              euc-kr                       Korean (EUC)
52936              hz-gb-2312                   Chinese Simplified (HZ)
54936              GB18030                      Chinese Simplified (GB18030)
57002              x-iscii-de                   ISCII Devanagari
57003              x-iscii-be                   ISCII Bengali
57004              x-iscii-ta                   ISCII Tamil
57005              x-iscii-te                   ISCII Telugu
57006              x-iscii-as                   ISCII Assamese
57007              x-iscii-or                   ISCII Oriya
57008              x-iscii-ka                   ISCII Kannada
57009              x-iscii-ma                   ISCII Malayalam
57010              x-iscii-gu                   ISCII Gujarati
57011              x-iscii-pa                   ISCII Punjabi
65000              utf-7                        Unicode (UTF-7)
65001              utf-8                        Unicode (UTF-8)  

*/

Encoding..::.GetEncodings Method

http://msdn.microsoft.com/en-us/library/system.text.encoding.getencodings.aspx

C#

Copy Code

using System;
using System.Text;

public class SamplesEncoding  {

   public static void Main()  {

      // Print the header.
      Console.Write( "CodePage identifier and name     " );
      Console.Write( "BrDisp   BrSave   " );
      Console.Write( "MNDisp   MNSave   " );
      Console.WriteLine( "1-Byte   ReadOnly " );

      // For every encoding, get the property values.
      foreach( EncodingInfo ei in Encoding.GetEncodings() )  {
         Encoding e = ei.GetEncoding();

         Console.Write( "{0,-6} {1,-25} ", ei.CodePage, ei.Name );
         Console.Write( "{0,-8} {1,-8} ", e.IsBrowserDisplay, e.IsBrowserSave );
         Console.Write( "{0,-8} {1,-8} ", e.IsMailNewsDisplay, e.IsMailNewsSave );
         Console.WriteLine( "{0,-8} {1,-8} ", e.IsSingleByte, e.IsReadOnly );
      }

   }

}

/*
This code produces the following output.

CodePage identifier and name     BrDisp   BrSave   MNDisp   MNSave   1-Byte   ReadOnly
37     IBM037                    False    False    False    False    True     True
437    IBM437                    False    False    False    False    True     True
500    IBM500                    False    False    False    False    True     True
708    ASMO-708                  True     True     False    False    True     True
720    DOS-720                   True     True     False    False    True     True
737    ibm737                    False    False    False    False    True     True
775    ibm775                    False    False    False    False    True     True
850    ibm850                    False    False    False    False    True     True
852    ibm852                    True     True     False    False    True     True
855    IBM855                    False    False    False    False    True     True
857    ibm857                    False    False    False    False    True     True
858    IBM00858                  False    False    False    False    True     True
860    IBM860                    False    False    False    False    True     True
861    ibm861                    False    False    False    False    True     True
862    DOS-862                   True     True     False    False    True     True
863    IBM863                    False    False    False    False    True     True
864    IBM864                    False    False    False    False    True     True
865    IBM865                    False    False    False    False    True     True
866    cp866                     True     True     False    False    True     True
869    ibm869                    False    False    False    False    True     True
870    IBM870                    False    False    False    False    True     True
874    windows-874               True     True     True     True     True     True
875    cp875                     False    False    False    False    True     True
932    shift_jis                 True     True     True     True     False    True
936    gb2312                    True     True     True     True     False    True
949    ks_c_5601-1987            True     True     True     True     False    True
950    big5                      True     True     True     True     False    True
1026   IBM1026                   False    False    False    False    True     True
1047   IBM01047                  False    False    False    False    True     True
1140   IBM01140                  False    False    False    False    True     True
1141   IBM01141                  False    False    False    False    True     True
1142   IBM01142                  False    False    False    False    True     True
1143   IBM01143                  False    False    False    False    True     True
1144   IBM01144                  False    False    False    False    True     True
1145   IBM01145                  False    False    False    False    True     True
1146   IBM01146                  False    False    False    False    True     True
1147   IBM01147                  False    False    False    False    True     True
1148   IBM01148                  False    False    False    False    True     True
1149   IBM01149                  False    False    False    False    True     True
1200   utf-16                    False    True     False    False    False    True
1201   unicodeFFFE               False    False    False    False    False    True
1250   windows-1250              True     True     True     True     True     True
1251   windows-1251              True     True     True     True     True     True
1252   Windows-1252              True     True     True     True     True     True
1253   windows-1253              True     True     True     True     True     True
1254   windows-1254              True     True     True     True     True     True
1255   windows-1255              True     True     True     True     True     True
1256   windows-1256              True     True     True     True     True     True
1257   windows-1257              True     True     True     True     True     True
1258   windows-1258              True     True     True     True     True     True
1361   Johab                     False    False    False    False    False    True
10000  macintosh                 False    False    False    False    True     True
10001  x-mac-japanese            False    False    False    False    False    True
10002  x-mac-chinesetrad         False    False    False    False    False    True
10003  x-mac-korean              False    False    False    False    False    True
10004  x-mac-arabic              False    False    False    False    True     True
10005  x-mac-hebrew              False    False    False    False    True     True
10006  x-mac-greek               False    False    False    False    True     True
10007  x-mac-cyrillic            False    False    False    False    True     True
10008  x-mac-chinesesimp         False    False    False    False    False    True
10010  x-mac-romanian            False    False    False    False    True     True
10017  x-mac-ukrainian           False    False    False    False    True     True
10021  x-mac-thai                False    False    False    False    True     True
10029  x-mac-ce                  False    False    False    False    True     True
10079  x-mac-icelandic           False    False    False    False    True     True
10081  x-mac-turkish             False    False    False    False    True     True
10082  x-mac-croatian            False    False    False    False    True     True
12000  utf-32                    False    False    False    False    False    True
12001  utf-32BE                  False    False    False    False    False    True
20000  x-Chinese-CNS             False    False    False    False    False    True
20001  x-cp20001                 False    False    False    False    False    True
20002  x-Chinese-Eten            False    False    False    False    False    True
20003  x-cp20003                 False    False    False    False    False    True
20004  x-cp20004                 False    False    False    False    False    True
20005  x-cp20005                 False    False    False    False    False    True
20105  x-IA5                     False    False    False    False    True     True
20106  x-IA5-German              False    False    False    False    True     True
20107  x-IA5-Swedish             False    False    False    False    True     True
20108  x-IA5-Norwegian           False    False    False    False    True     True
20127  us-ascii                  False    False    True     True     True     True
20261  x-cp20261                 False    False    False    False    False    True
20269  x-cp20269                 False    False    False    False    True     True
20273  IBM273                    False    False    False    False    True     True
20277  IBM277                    False    False    False    False    True     True
20278  IBM278                    False    False    False    False    True     True
20280  IBM280                    False    False    False    False    True     True
20284  IBM284                    False    False    False    False    True     True
20285  IBM285                    False    False    False    False    True     True
20290  IBM290                    False    False    False    False    True     True
20297  IBM297                    False    False    False    False    True     True
20420  IBM420                    False    False    False    False    True     True
20423  IBM423                    False    False    False    False    True     True
20424  IBM424                    False    False    False    False    True     True
20833  x-EBCDIC-KoreanExtended   False    False    False    False    True     True
20838  IBM-Thai                  False    False    False    False    True     True
20866  koi8-r                    True     True     True     True     True     True
20871  IBM871                    False    False    False    False    True     True
20880  IBM880                    False    False    False    False    True     True
20905  IBM905                    False    False    False    False    True     True
20924  IBM00924                  False    False    False    False    True     True
20932  EUC-JP                    False    False    False    False    False    True
20936  x-cp20936                 False    False    False    False    False    True
20949  x-cp20949                 False    False    False    False    False    True
21025  cp1025                    False    False    False    False    True     True
21866  koi8-u                    True     True     True     True     True     True
28591  iso-8859-1                True     True     True     True     True     True
28592  iso-8859-2                True     True     True     True     True     True
28593  iso-8859-3                False    False    True     True     True     True
28594  iso-8859-4                True     True     True     True     True     True
28595  iso-8859-5                True     True     True     True     True     True
28596  iso-8859-6                True     True     True     True     True     True
28597  iso-8859-7                True     True     True     True     True     True
28598  iso-8859-8                True     True     False    False    True     True
28599  iso-8859-9                True     True     True     True     True     True
28603  iso-8859-13               False    False    True     True     True     True
28605  iso-8859-15               False    True     True     True     True     True
29001  x-Europa                  False    False    False    False    True     True
38598  iso-8859-8-i              True     True     True     True     True     True
50220  iso-2022-jp               False    False    True     True     False    True
50221  csISO2022JP               False    True     True     True     False    True
50222  iso-2022-jp               False    False    False    False    False    True
50225  iso-2022-kr               False    False    True     False    False    True
50227  x-cp50227                 False    False    False    False    False    True
51932  euc-jp                    True     True     True     True     False    True
51936  EUC-CN                    False    False    False    False    False    True
51949  euc-kr                    False    False    True     True     False    True
52936  hz-gb-2312                True     True     True     True     False    True
54936  GB18030                   True     True     True     True     False    True
57002  x-iscii-de                False    False    False    False    False    True
57003  x-iscii-be                False    False    False    False    False    True
57004  x-iscii-ta                False    False    False    False    False    True
57005  x-iscii-te                False    False    False    False    False    True
57006  x-iscii-as                False    False    False    False    False    True
57007  x-iscii-or                False    False    False    False    False    True
57008  x-iscii-ka                False    False    False    False    False    True
57009  x-iscii-ma                False    False    False    False    False    True
57010  x-iscii-gu                False    False    False    False    False    True
57011  x-iscii-pa                False    False    False    False    False    True
65000  utf-7                     False    False    True     True     False    True
65001  utf-8                     True     True     True     True     False    True     

*/

Encoding..::.GetEncoding Method (String)

http://msdn.microsoft.com/en-us/library/t9a3kf7c.aspx

C#

Copy Code

using System;
using System.Text;

public class SamplesEncoding  {

   public static void Main()  {

      // Get a UTF-32 encoding by codepage.
      Encoding e1 = Encoding.GetEncoding( 12000 );

      // Get a UTF-32 encoding by name.
      Encoding e2 = Encoding.GetEncoding( "utf-32" );

      // Check their equality.
      Console.WriteLine( "e1 equals e2? {0}", e1.Equals( e2 ) );

   }

}

/*
This code produces the following output.

e1 equals e2? True

*/

MIME (Multipurpose Internet Mail Extensions)

http://www.w3schools.com/media/media_mimeref.asp

MIME Reference

MIME Types

content types.

MIME messages can contain text, images, audio, video, and other application-specific data.

Official MIME info is provided by the Internet Engineering Task Force (IETF) in the following documents:

• RFC-822 Standard for ARPA Internet text messages
• RFC-2045 MIME Part 1: Format of Internet Message Bodies
• RFC-2046 MIME Part 2: Media Types
• RFC-2047 MIME Part 3: Header Extensions for Non-ASCII Text
• RFC-2048 MIME Part 4: Registration Procedures
• RFC-2049 MIME Part 5: Conformance Criteria and Examples

Different applications support different MIME types.

The reference below is a list of MIME types supported by Microsoft Internet Information Server version 5.

---

MIME Types By Content Type

Type/sub-type	Extension
application/envoy	evy
application/fractals	fif
application/futuresplash	spl
application/hta	hta
application/internet-property-stream	acx
application/mac-binhex40	hqx
application/msword	doc
application/msword	dot
application/octet-stream	*
application/octet-stream	bin
application/octet-stream	class
application/octet-stream	dms
application/octet-stream	exe
application/octet-stream	lha
application/octet-stream	lzh
application/oda	oda
application/olescript	axs
application/pdf	pdf
application/pics-rules	prf
application/pkcs10	p10
application/pkix-crl	crl
application/postscript	ai
application/postscript	eps
application/postscript	ps
application/rtf	rtf
application/set-payment-initiation	setpay
application/set-registration-initiation	setreg
application/vnd.ms-excel	xla
application/vnd.ms-excel	xlc
application/vnd.ms-excel	xlm
application/vnd.ms-excel	xls
application/vnd.ms-excel	xlt
application/vnd.ms-excel	xlw
application/vnd.ms-outlook	msg
application/vnd.ms-pkicertstore	sst
application/vnd.ms-pkiseccat	cat
application/vnd.ms-pkistl	stl
application/vnd.ms-powerpoint	pot
application/vnd.ms-powerpoint	pps
application/vnd.ms-powerpoint	ppt
application/vnd.ms-project	mpp
application/vnd.ms-works	wcm
application/vnd.ms-works	wdb
application/vnd.ms-works	wks
application/vnd.ms-works	wps
application/winhlp	hlp
application/x-bcpio	bcpio
application/x-cdf	cdf
application/x-compress	z
application/x-compressed	tgz
application/x-cpio	cpio
application/x-csh	csh
application/x-director	dcr
application/x-director	dir
application/x-director	dxr
application/x-dvi	dvi
application/x-gtar	gtar
application/x-gzip	gz
application/x-hdf	hdf
application/x-internet-signup	ins
application/x-internet-signup	isp
application/x-iphone	iii
application/x-javascript	js
application/x-latex	latex
application/x-msaccess	mdb
application/x-mscardfile	crd
application/x-msclip	clp
application/x-msdownload	dll
application/x-msmediaview	m13
application/x-msmediaview	m14
application/x-msmediaview	mvb
application/x-msmetafile	wmf
application/x-msmoney	mny
application/x-mspublisher	pub
application/x-msschedule	scd
application/x-msterminal	trm
application/x-mswrite	wri
application/x-netcdf	cdf
application/x-netcdf	nc
application/x-perfmon	pma
application/x-perfmon	pmc
application/x-perfmon	pml
application/x-perfmon	pmr
application/x-perfmon	pmw
application/x-pkcs12	p12
application/x-pkcs12	pfx
application/x-pkcs7-certificates	p7b
application/x-pkcs7-certificates	spc
application/x-pkcs7-certreqresp	p7r
application/x-pkcs7-mime	p7c
application/x-pkcs7-mime	p7m
application/x-pkcs7-signature	p7s
application/x-sh	sh
application/x-shar	shar
application/x-shockwave-flash	swf
application/x-stuffit	sit
application/x-sv4cpio	sv4cpio
application/x-sv4crc	sv4crc
application/x-tar	tar
application/x-tcl	tcl
application/x-tex	tex
application/x-texinfo	texi
application/x-texinfo	texinfo
application/x-troff	roff
application/x-troff	t
application/x-troff	tr
application/x-troff-man	man
application/x-troff-me	me
application/x-troff-ms	ms
application/x-ustar	ustar
application/x-wais-source	src
application/x-x509-ca-cert	cer
application/x-x509-ca-cert	crt
application/x-x509-ca-cert	der
application/ynd.ms-pkipko	pko
application/zip	zip
audio/basic	au
audio/basic	snd
audio/mid	mid
audio/mid	rmi
audio/mpeg	mp3
audio/x-aiff	aif
audio/x-aiff	aifc
audio/x-aiff	aiff
audio/x-mpegurl	m3u
audio/x-pn-realaudio	ra
audio/x-pn-realaudio	ram
audio/x-wav	wav
image/bmp	bmp
image/cis-cod	cod
image/gif	gif
image/ief	ief
image/jpeg	jpe
image/jpeg	jpeg
image/jpeg	jpg
image/pipeg	jfif
image/svg+xml	svg
image/tiff	tif
image/tiff	tiff
image/x-cmu-raster	ras
image/x-cmx	cmx
image/x-icon	ico
image/x-portable-anymap	pnm
image/x-portable-bitmap	pbm
image/x-portable-graymap	pgm
image/x-portable-pixmap	ppm
image/x-rgb	rgb
image/x-xbitmap	xbm
image/x-xpixmap	xpm
image/x-xwindowdump	xwd
message/rfc822	mht
message/rfc822	mhtml
message/rfc822	nws
text/css	css
text/h323	323
text/html	htm
text/html	html
text/html	stm
text/iuls	uls
text/plain	bas
text/plain	c
text/plain	h
text/plain	txt
text/richtext	rtx
text/scriptlet	sct
text/tab-separated-values	tsv
text/webviewhtml	htt
text/x-component	htc
text/x-setext	etx
text/x-vcard	vcf
video/mpeg	mp2
video/mpeg	mpa
video/mpeg	mpe
video/mpeg	mpeg
video/mpeg	mpg
video/mpeg	mpv2
video/quicktime	mov
video/quicktime	qt
video/x-la-asf	lsf
video/x-la-asf	lsx
video/x-ms-asf	asf
video/x-ms-asf	asr
video/x-ms-asf	asx
video/x-msvideo	avi
video/x-sgi-movie	movie
x-world/x-vrml	flr
x-world/x-vrml	vrml
x-world/x-vrml	wrl
x-world/x-vrml	wrz
x-world/x-vrml	xaf
x-world/x-vrml	xof

---

Mime Types By File Extension

Extension	Type/sub-type
	application/octet-stream
323	text/h323
acx	application/internet-property-stream
ai	application/postscript
aif	audio/x-aiff
aifc	audio/x-aiff
aiff	audio/x-aiff
asf	video/x-ms-asf
asr	video/x-ms-asf
asx	video/x-ms-asf
au	audio/basic
avi	video/x-msvideo
axs	application/olescript
bas	text/plain
bcpio	application/x-bcpio
bin	application/octet-stream
bmp	image/bmp
c	text/plain
cat	application/vnd.ms-pkiseccat
cdf	application/x-cdf
cer	application/x-x509-ca-cert
class	application/octet-stream
clp	application/x-msclip
cmx	image/x-cmx
cod	image/cis-cod
cpio	application/x-cpio
crd	application/x-mscardfile
crl	application/pkix-crl
crt	application/x-x509-ca-cert
csh	application/x-csh
css	text/css
dcr	application/x-director
der	application/x-x509-ca-cert
dir	application/x-director
dll	application/x-msdownload
dms	application/octet-stream
doc	application/msword
dot	application/msword
dvi	application/x-dvi
dxr	application/x-director
eps	application/postscript
etx	text/x-setext
evy	application/envoy
exe	application/octet-stream
fif	application/fractals
flr	x-world/x-vrml
gif	image/gif
gtar	application/x-gtar
gz	application/x-gzip
h	text/plain
hdf	application/x-hdf
hlp	application/winhlp
hqx	application/mac-binhex40
hta	application/hta
htc	text/x-component
htm	text/html
html	text/html
htt	text/webviewhtml
ico	image/x-icon
ief	image/ief
iii	application/x-iphone
ins	application/x-internet-signup
isp	application/x-internet-signup
jfif	image/pipeg
jpe	image/jpeg
jpeg	image/jpeg
jpg	image/jpeg
js	application/x-javascript
latex	application/x-latex
lha	application/octet-stream
lsf	video/x-la-asf
lsx	video/x-la-asf
lzh	application/octet-stream
m13	application/x-msmediaview
m14	application/x-msmediaview
m3u	audio/x-mpegurl
man	application/x-troff-man
mdb	application/x-msaccess
me	application/x-troff-me
mht	message/rfc822
mhtml	message/rfc822
mid	audio/mid
mny	application/x-msmoney
mov	video/quicktime
movie	video/x-sgi-movie
mp2	video/mpeg
mp3	audio/mpeg
mpa	video/mpeg
mpe	video/mpeg
mpeg	video/mpeg
mpg	video/mpeg
mpp	application/vnd.ms-project
mpv2	video/mpeg
ms	application/x-troff-ms
mvb	application/x-msmediaview
nws	message/rfc822
oda	application/oda
p10	application/pkcs10
p12	application/x-pkcs12
p7b	application/x-pkcs7-certificates
p7c	application/x-pkcs7-mime
p7m	application/x-pkcs7-mime
p7r	application/x-pkcs7-certreqresp
p7s	application/x-pkcs7-signature
pbm	image/x-portable-bitmap
pdf	application/pdf
pfx	application/x-pkcs12
pgm	image/x-portable-graymap
pko	application/ynd.ms-pkipko
pma	application/x-perfmon
pmc	application/x-perfmon
pml	application/x-perfmon
pmr	application/x-perfmon
pmw	application/x-perfmon
pnm	image/x-portable-anymap
pot,	application/vnd.ms-powerpoint
ppm	image/x-portable-pixmap
pps	application/vnd.ms-powerpoint
ppt	application/vnd.ms-powerpoint
prf	application/pics-rules
ps	application/postscript
pub	application/x-mspublisher
qt	video/quicktime
ra	audio/x-pn-realaudio
ram	audio/x-pn-realaudio
ras	image/x-cmu-raster
rgb	image/x-rgb
rmi	audio/mid
roff	application/x-troff
rtf	application/rtf
rtx	text/richtext
scd	application/x-msschedule
sct	text/scriptlet
setpay	application/set-payment-initiation
setreg	application/set-registration-initiation
sh	application/x-sh
shar	application/x-shar
sit	application/x-stuffit
snd	audio/basic
spc	application/x-pkcs7-certificates
spl	application/futuresplash
src	application/x-wais-source
sst	application/vnd.ms-pkicertstore
stl	application/vnd.ms-pkistl
stm	text/html
svg	image/svg+xml
sv4cpio	application/x-sv4cpio
sv4crc	application/x-sv4crc
swf	application/x-shockwave-flash
t	application/x-troff
tar	application/x-tar
tcl	application/x-tcl
tex	application/x-tex
texi	application/x-texinfo
texinfo	application/x-texinfo
tgz	application/x-compressed
tif	image/tiff
tiff	image/tiff
tr	application/x-troff
trm	application/x-msterminal
tsv	text/tab-separated-values
txt	text/plain
uls	text/iuls
ustar	application/x-ustar
vcf	text/x-vcard
vrml	x-world/x-vrml
wav	audio/x-wav
wcm	application/vnd.ms-works
wdb	application/vnd.ms-works
wks	application/vnd.ms-works
wmf	application/x-msmetafile
wps	application/vnd.ms-works
wri	application/x-mswrite
wrl	x-world/x-vrml
wrz	x-world/x-vrml
xaf	x-world/x-vrml
xbm	image/x-xbitmap
xla	application/vnd.ms-excel
xlc	application/vnd.ms-excel
xlm	application/vnd.ms-excel
xls	application/vnd.ms-excel
xlt	application/vnd.ms-excel
xlw	application/vnd.ms-excel
xof	x-world/x-vrml
xpm	image/x-xpixmap
xwd	image/x-xwindowdump
z	application/x-compress
zip	application/zip

Format Using FOR Xml Explicit Mode

http://codehighstreet.com/Snippets/using_for_xml_explicit.aspx

–STEP 1 CREATE A TABLE

DECLARE @AutoMobile AS TABLE (ID int IDENTITY(1,1) NOT NULL,Customer varchar(100),Make Varchar(100),Model Varchar(100),Year int)

–STEP2 INSERT VALUES

INSERT INTO @AutoMobile VALUES(‘Ron’,‘Audi’,‘Audi 100′,2005)

INSERT INTO @AutoMobile VALUES(‘Jhon’,‘Audi’,‘Audi Allroad’,2005)

INSERT INTO @AutoMobile VALUES(‘Chris’,‘Audi’,‘Audi Cabriolet’,2006)

INSERT INTO @AutoMobile VALUES(‘Bruce’,‘Chrysler’,‘Chrysler LHS’,2005)

INSERT INTO @AutoMobile VALUES(‘Lee’,‘Chrysler’,‘Chrysler New Yorker’,2006)

INSERT INTO @AutoMobile VALUES(‘Barry’,‘Chrysler’,‘Chrysler PT Cruiser’,2007)

INSERT INTO @AutoMobile VALUES(‘Bob’,‘Chrysler’,‘Chrysler Sebring’,2005)

INSERT INTO @AutoMobile VALUES(‘Tom’,‘Mercury’,‘Mercury Topaz’,2007)

INSERT INTO @AutoMobile VALUES(‘Poo’,‘Mercury’,‘Mercury Tracer’,2007)

INSERT INTO @AutoMobile VALUES(‘Nee’,‘Mercury’,‘Mercury Zephyr’,2006)

INSERT INTO @AutoMobile VALUES(‘Pops’,‘Mercury’,‘Mercury Villager’,2007)

–STEP 3

–SELECT 1 as tag,null as parent,ID FROM @AutoMobile FOR xml EXPLICIT

–You will get a SQL Error: FOR XML EXPLICIT query contains the invalid column name ‘ID’. Use the TAGNAME!TAGID!ATTRIBUTENAME[!..] format where TAGID is a positive integer.

–STEP 4: Create XML Nodes on column ID

–add tag name and its tag id

SELECT 1 as tag,null as parent,ID as [id!1] FROM @AutoMobile FOR xml EXPLICIT

–STEP 5: Create XML Nodes on column ID and column Model

SELECT 1 as tag,null as parent,ID as [id!1],NULL as [Model!2] FROM @AutoMobile

UNION ALL

SELECT 2 as tag,1 as parent,NULL as [id!1],Model as [Model!2] FROM @AutoMobile FOR xml EXPLICIT

–STEP 6: Now form a proper Xml Document with all details

SELECT 1 as tag,null as parent,null as [auto!1!element],NULL as [Model!2!name],null as [Model!2!Customer!element]

UNION ALL

SELECT 2 as tag,1 as parent,null as [auto!1!element],Model as [Model!2!name],Customer as [Model!2!Customer!element] FROM @AutoMobile WHERE Make=‘Mercury’

for xml explicit

codehighstreet.com

http://codehighstreet.com/

CodeHighStreet.com is a cluster of articles and code snippets related to ASP.NET,C#,VB.NET,Java Script, PL/SQL, SQL. Browse through the code snippets section to take a look at handy pieces of ASP.NET related tips and other useful web designing stuff.

How To Merge Two Arrays in ASP.NET?

http://codehighstreet.com/Snippets/how_to_merge_two_arrays.aspx

With Duplicates

private string[] MergeArray(string[] DestinationArray, string[] SourceArray)

{

int iSourceIndex;

int iDestinationLength = DestinationArray.Length;

if (SourceArray.Length > 0)

Array.Resize(ref DestinationArray, DestinationArray.Length + SourceArray.Length);

for (iSourceIndex = 0; iSourceIndex < SourceArray.Length ; iSourceIndex++)

{

DestinationArray[iDestinationLength + iSourceIndex] = SourceArray[iSourceIndex];

}

return DestinationArray;

}

Without Duplicates

private string[] MergeArray(string[] DestinationArray, string[] SourceArray)

{

int iSourceIndex;

int iDestinationLength = DestinationArray.Length;

if (SourceArray.Length > 0)

Array.Resize(ref DestinationArray, DestinationArray.Length + SourceArray.Length);

for (iSourceIndex = 0; iSourceIndex < SourceArray.Length ; iSourceIndex++)

{

if (Array.IndexOf(arrDestination, arrSource[iSourceLength]) == -1)

DestinationArray[iDestinationLength + iSourceIndex] = SourceArray[iSourceIndex];

}

return DestinationArray;

}

}

Array.IndexOf will do a search for the specified object on array and returns the index of the first occurrence. it will return -1 if it is not find anything on the array

Hierarchical Tree Represented by Modified Preorder Tree Traversal Technique using C# 3.0 and SQL 2005.

http://www.codeproject.com/KB/tree/Tree_Traversal.aspx

• Download demo project – 47.54 KB

Introduction

During my work in a (CMS) Content Management System, and in the module of Content-mapping (which is a .NET desktop application) I needed to classify my contents into a chain of categories like hierarchical tree, but I also needed to represent that tree into more than a format, such as Textual, Tabular, Nodes and Database.

I searched the web about which .NET library is going to represent my tree into the SQL2005 database. I couldn’t found that library, but I found an article (Gijs Van Tulder) is titled Storing Hierarchical Data in a Database that explains the concept of the MPTT (Modified Preorder Tree Traversal). Unfortunately, it was written for PHP developers, so, I translated the concept into C# and SQL2005 code. Moreover, I wrote some algorithms to represent trees into textual, tabular, tree view nodes and graphical format. I also used provider model techniques as a step to support various databases such as SQLite.

Finally, I gathered all these staff into one project, but, the entire classes could be easily reused and separated into class libraries, even the innovative algorithms (that I wrote) could be reused individually like the one that is written to render the graphical representation of the tree.

Now I think that I have an efficient way to add, insert, delete and retrieve tree-elements using many ways including a way for SQL2005 database with a very effective schema, which is MPTT that minimizes the number of database queries by having just one query for each activity.

What is Hierarchical Tree?

Simply, it is a structure of data that looks like a real tree, even though the hierarchical-tree is generally shown upside down compared with a real tree; that is to say with the root at the top and the leaves at the bottom, elements of that structure are related with each other by a parent-child relationship, those relationships are represented by a connecting-lines between elements and they are called “Branches”, moreover, elements that have no superior are called “ROOTs” and elements that have no children are called “Leaves”.

The following example demonstrates a textual representation for the Animal-kingdom tree:

Collapse Copy Code

Animal Kingdom
#Backbones
##Mammal
##Lungs
##Reptile
##Bird
##Gills
###Fish
###Amphibian
#No Backbones
##Starfish
##Mollusk
###Snail
###Clam
##Jointed Legs
###Insect
###Spider
###Crustacean

Collapse Copy Code

Figure 1: Hierarchical tree, textual representation.

So we have 1 root (Animal Kingdom) and 12 leaves (Mammal, Lungs, Reptile, Bird, Fish, Amphibian, Starfish, Snail, Clam, Insect, Spider, Crustacean), we have 18 lines where each line contains one tree element/node, the count of (#)s in front of the node-title represents the depth level of the element in the tree, and elements that have the same level and have the same parent are called “SIBLINGs”.

A graphical representation of such trees exist in Figure 2, where a rectangle is a symbolic representation of a node, and also a tabular representation for such trees exist in Figure 3.

Figure 2: Hierarchical tree graphical representation.

ID	Parent ID	Title	Left	Right
1	0	Animal Kingdom	1	36
2	1	Backbones	2	17
3	2	Mammal	3	4
4	2	Lungs	5	6
5	2	Reptile	7	8
6	2	Bird	9	10
7	2	Gills	11	16
8	7	Fish	12	13
9	7	Amphibian	14	15
10	1	No Backbones	18	35
11	10	Starfish	19	20
12	10	Mollusk	21	26
13	12	Snail	22	23
14	12	Clam	24	25
15	10	Jointed Legs	27	34
16	15	Insect	28	29
17	15	Spider	30	31
18	15	Crustacean	32	33

Figure 3: Hierarchical tree tabular representation.

Using the Demo Project

Back to the previous section, copy the text of Figure 1, and paste it into the TextBox of the right hand side on the interface of demo project, then, press the button that is titled “<<” to convert the text into the traditional tree representation in the TreeView control in the left hand side.

Show a tabular representation of the tree by clicking the radio button that is called “Tabular,” and toggle between textual and tabular using the the other radio button that is called “Textual”.

To save the tree you just converted into SQL2005 database you should first select an existing database, but you don’t have to create any tables, just construct a working connection-string by clicking the button that is called “Connection String,” after composing the connection string, simply click the button that is called “Save” to save the tree into a table called “tblTree,” the table will be created automatically if it does not exist or is truncated if it is existing.

Of course, you could load this tree any time again from the same connection-string if you provided it correctly, just compose the connection string and click the “Load” button. However, you don’t have to enter the connection string each time you open the demo project, it saves the connection string into a file named constring.txt automatically.

Now you can enjoy showing the graphical representation of the tree by clicking the “Draw” button.

Using the Code

You simply have to create an object from the MpttCoreEngine class and call any method from its interface.

Collapse Copy Code

MpttCoreEngine engine = new MpttCoreEngine();

This class contains all the algorithms that were written to represent hierarchical trees in various formats, the following list contains the most important methods in this class.

SetConnectionString	Use this function to provide a connection string to the engine
ConvertMpttIntoTree	Use this function to load tree nodes from the database
ConvertTreeIntoMptt	Use this function to save tree nodes to database
ConvertTextIntoTree	Converts textual tree into TreeNode suitable to TreeView
ConvertTextTableIntoTree	Converts tabular tree into TreeNode suitable for TreeView
ConvertTreeIntoText	Converts TreeNode back to textual representation
ConvertTreeIntoTable	Converts TreeNode back to tabular representation
DrawTree	Draws TreeNode on an Image and returns the Image for later use

The following code is a snapshot from the demo project which demonstrates how to use those methods.

Collapse Copy Code

	private void LoadDb()
	{
	    try
	    {
	        treeView.Nodes.Clear();
	        TreeNode node = null;
	        engine.SetConnectionString(_connectionString);
	        node = engine.ConvertMpttIntoTree();
	        if (node != null)
	        {
	            treeView.Nodes.Add(node);
	            treeView.ExpandAll();
	        }
	    }
	    catch (Exception e)
	    {
	        MessageBox.Show(e.Message +
                     "\r\nThe Connection string may not be correct");
	    }
	}

	private void SaveDb()
	{
	    if (treeView.Nodes.Count <= 0)
	        return;
	    try
	    {
	        engine.SetConnectionString(_connectionString);
	        engine.ConvertTreeIntoMptt(treeView.Nodes[0]);
	    }
	    catch (Exception e)
	    {
	        MessageBox.Show(e.Message +
                     "\r\nThe Connection string may not be correct");
	    }
	}

	private void TreeReflection()
	{
	    char t = (delimiter.Text.Length > 0 ? delimiter.Text[0] : '#');
	    engine.LevelDelimiter = t;
	    //
	    treeView.Nodes.Clear();
	    TreeNode node = null;
	    if (textual.Checked)
	        node = engine.ConvertTextIntoTree(textView.Text, _delimiter, '*');
	    else//tabular
	        node = engine.ConvertTextTableIntoTree(textView.Text, true);
	    if (node != null)
	    {
	        treeView.Nodes.Add(node);
	        treeView.ExpandAll();
	    }
	}

	private void TextReflection()
	{
	    if (treeView.Nodes.Count <= 0)
	        return;
	    char t = (delimiter.Text.Length > 0 ? delimiter.Text[0] : '#');
	    engine.LevelDelimiter = t;
	    if (textual.Checked)
	    {
	        textView.Text = engine.ConvertTreeIntoText(treeView.Nodes[0], false);
	    }
	    else
	    {
	        textView.Text = engine.ConvertTreeIntoTable(treeView.Nodes[0], false);
	    }
	}

	private void btnDraw_Click(object sender, EventArgs e)
	{
	    if (treeView.Nodes.Count <= 0)
	        return;
	    Bitmap bmp = engine.DrawTree(treeView.Nodes[0]);
	    TreePreview preview = new TreePreview(bmp);
	    preview.Show();
	}

Points of Interest

The code is full of interest points, where most of them may need individual articles to explain. Here are the most important:

1. 1- The algorithm of drawing the tree nodes is so complex, I used anonymous functions, recursion and callbacks to calculate accurate locations for the nodes. You can find that algorithm on the function DrawTree.
2. The MPTT concept is explained in several places on the web, though, I composed that concept with a design pattern known by “provider model“, where, an abstract class called TreeProvider is created to be implemented and supports any database type like SQL2005, MySQL or SQLite.
3. You could simply separate the core of the demo projects into a class library by copying only the folder named Core to any project you want.

History

Version 1.0:

1. TreeView representation
2. Textual representation
3. Tabular representation
4. MPTT representation
5. Graphical representation
6. SQL2005 support

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Wael Alghool

Programming since 1990. I’m Experience in VC++, COM, MFC, API, ISAPI, ASP.NET, C#, jQuery, SQLite, SQL2005, Fatwire-CMS and J2EE. Since 2004, I’m SME (Subject matter expert) in requirements gathering. Since 2005, I’m PSP (Personal Software Process) certified. Since 2005, I Lead teams in between 8 to 30 members in several companies. Since 1999, I Worked for international companies like Harf-IT, ISlamweb, Qatar-awqaf ministry and Islamonline. Occupation: Architect Company: Government Location: Qatar

Tree structures in ASP.NET and SQL Server

http://www.developerfusion.com/article/4633/tree-structures-in-aspnet-and-sql-server/

Library tutorials & articles

Tree structures in ASP.NET and SQL Server

By James Crowley, published on 28 Dec 2004

 James first started this website when learning Visual Basic back in 1999 whilst studying his GCSEs. The site grew steadily over the years while being run as a hobby – to a regular monthly audience …

• Download.zip

Page 1 of 8

1. Introduction
2. Storing Trees in SQL Server
3. Self-maintaining Trees Using Triggers
4. Basic Operations over Trees in SQL Server
5. Displaying our Nodes
6. Displaying Breadcrumbs
7. Previewing Sub-levels
8. Populating the TreeNode Children

Introduction

Trees can be an intuitively simply way of organising large amounts of information. We’re exposed to them everywhere – from directories in file systems and categories in a web directory to hierarchies in organisations and family trees! Something like XML handles hierarchical data well, but if you’ve got a database full of data we want to associate with the tree – for example, a table full of articles – splitting our data store between XML and something like SQL Server isn’t a particularly elegant option. Unfortunately, relational SQL doesn’t make it especially easy to store these structures in such a way that we can perform useful (and efficient) operations over the trees.

This article looks at one way to represent trees in .NET – and how to map them to a table in SQL Server and back again – which should hopefully take the pain out of storing trees and manipulating them from .NET. Our aim will be to create an ASP.NET page that provides some standard “web directory” features including

• Breadcrumbs
• Listing sub-nodes in the current section
• Indicating some of the children of these child nodes

whilst ensuring that we have zero limitations on the number of items in the tree, or its depth.

I’ve referenced many articles on the subject in order to implement the SQL Server portion of this – in particular, Maintaining Hierarchies and More Trees & Hierarchies in SQL – so many thanks to those authors. I hope this proves to be a useful extension to their discussions, rather than just a rehash of old material! Naturally, any mistakes in the implementation I show here are mine alone – but please do point them out to me.

Representing trees in .NET

To start with, its probably useful to remind ourselves what exactly a tree is (!). Each element in a tree is known as a node – each node has zero or more child nodes, and the one at the top – with no parent – is the root of the tree. .NET doesn’t currently have a built-in datatype for representing trees, but it’s fairly straightforward to to create our own. We’ll simply create a class to represent each “node” in the tree – which will consist of the following.

Property Name	Data Type	Description
UniqueID	int	A unique identifier for the node in this tree. As we’re looking to store the tree in a relational database, this maps nicely to a primary/identity key, so we’ll use an integer here. If we are creating a new TreeNode object that has not yet been associated with a unique identifier, this value will be zero.
ParentID	int	Used to identify the parent node of this object by storing the unique id of the parent. A parent ID of zero indicates that the node has no parent (ie it is a root node).
Name	string	A text value (not necessarily unique) to be associated with this node.
Children	ArrayList<TreeNode>	A collection of TreeNode objects that are children of this node. This will not necessarily contain all children that are in the original tree stored in our relational database – but will be populated appropriately depending on which queries we run.

The simple class shown below encapsulates this.

[Serializable]
public class TreeNode
{
    private int _uniqueID;
    private string _name;
    private int _parentID;
    private int _depth;
    private ArrayList _children;
    public TreeNode() { }
    public TreeNode(string name, int parentID) : this(0,name,parentID,-1)
    {
    }
    public TreeNode(int uniqueID, string name, int parentID, int depth)
    {
        _uniqueID = uniqueID;
        _name = name;
        _parentID = parentID;
        _depth = depth;
    }
    /// <summary>
    /// Gets or sets the unique ID associated with this category
    /// </summary>
    /// <remarks>Once a non-zero ID has been set, it may not be modified.</remarks>
    public int UniqueID
    {
        get { return _uniqueID; }
        set
        {
            if (_uniqueID == 0)
                _uniqueID = value;
            else
                throw new Exception("The UniqueID property cannot be modified once it has a non-zero value");
        }
    }
    public int Depth
    {
        get { return _depth; }
    }
    /// <summary>
    /// Gets or sets the label for this node
    /// </summary>
    public string Name
    {
        get { return _name; }
        set { _name = value; }
    }
    /// <summary>
    /// The ID of the parent node
    /// </summary>
    public int ParentID
    {
        get { return _parentID; }
        set { _parentID = value; }
    }
    /// <summary>
    /// Gets the children TreeNode objects for this category
    /// </summary>
    /// <remarks>In .NET 2.0, this can be modified to use generics, and have type ArrayList&lt;TreeNode></remarks>
    public ArrayList Children
    {
        get { return _children; }
        set { _children = value; }
    }
}

We’re not going to actually use this class to create standalone tree structures along the lines we would if were were adding elements to, say, a TreeView control. The TreeNode’s will be used to represent what we have stored in the database as a tree. However, if we want to add a TreeNode to the structure, the request is going to be made directly to the database – and the change will be reflected in a future request on the state of the tree structure. Likewise, if we modify the Name or ParentId of a TreeNode object, we’ll need to tell the database that we’ve made this change.

• Storing Trees in SQ

Storing Hierarchical Data in a Database

http://www.sitepoint.com/article/hierarchical-data-database/

Whether you want to build your own forum, publish the messages from a mailing list on your Website, or write your own cms: there will be a moment that you’ll want to store hierarchical data in a database. And, unless you’re using a XML-like database, tables aren’t hierarchical; they’re just a flat list. You’ll have to find a way to translate the hierarchy in a flat file.

Storing trees is a common problem, with multiple solutions. There are two major approaches: the adjacency list model, and the modified preorder tree traversal algorithm.

In this article, we’ll explore these two methods of saving hierarchical data. I’ll use the tree from a fictional online food store as an example. This food store organizes its food by category, by colour and by type. The tree looks like this:

 

1105_tree (click to view image)

 

This article contains a number of code examples that show how to save and retrieve data. Because I use that language myself, and many other people use or know that language too, I chose to write the examples in PHP. You can probably easily translate them to your own language of choice.

The Adjacency List Model

The first, and most elegant, approach we’ll try is called the ‘adjacency list model’ or the ‘recursion method’. It’s an elegant approach because you’ll need just one, simple function to iterate through your tree. In our food store, the table for an adjacency list looks like this:

 

1105_table1 (click to view image)

 

As you can see, in the adjacency list method, you save the ‘parent’ of each node. We can see that ‘Pear‘ is a child of ‘Green’, which is a child of ‘Fruit’ and so on. The root node, ‘Food’, doesn’t have a parent value. For simplicity, I’ve used the ‘title’ value to identify each node. Of course, in a real database, you’d use the numerical id of each node.

Give Me the Tree

Now that we’ve inserted our tree in the database, it’s time to write a display function. This function will have to start at the root node — the node with no parent — and should then display all children of that node. For each of these children, the function should retrieve and display all the child nodes of that child. For these children, the function should again display all children, and so on.

As you might have noticed, there’s a regular pattern in the description of this function. We can simply write one function, which retrieves the children of a certain parent node. That function should then start another instance of itself for each of these children, to display all their children. This is the recursive mechanism that gives the ‘recursion method’ its name.

<?php
// $parent is the parent of the children we want to see
// $level is increased when we go deeper into the tree,
//        used to display a nice indented tree
function display_children($parent, $level) {
   // retrieve all children of $parent
   $result = mysql_query('SELECT title FROM tree '.
                          'WHERE parent="'.$parent.'";');

   // display each child
   while ($row = mysql_fetch_array($result)) {
       // indent and display the title of this child
       echo str_repeat('  ',$level).$row['title']."\n";

       // call this function again to display this
       // child's children
       display_children($row['title'], $level+1);
   }
}
?>

To display our whole tree, we’ll run the function with an empty string as $parent and $level = 0: display_children('',0); For our food store tree, the function returns:

Food
Fruit
Red
Cherry
Yellow
Banana
Meat
Beef
Pork

Note that if you just want to see a subtree, you can tell the function to start with another node. For example, to display the ‘Fruit’ subtree, you would run display_children('Fruit',0);

The Path to a Node

With almost the same function, it’s possible to look up the path to a node if you only know the name or id of that node. For instance, the path to ‘Cherry’ is ‘Food’ > ‘Fruit’ > ‘Red’. To get this path, our function will have to start at the deepest level: ‘Cherry’. It then looks up the parent of this node and adds this to the path. In our example, this would be ‘Red’. If we know that ‘Red’ is the parent of ‘Cherry’, we can calculate the path to ‘Cherry’ by using the path to ‘Red’. And that’s given by the function we’ve just used: by recursively looking up parents, we’ll get the path to any node in the tree.

<?php
// $node is the name of the node we want the path of
function get_path($node) {
   // look up the parent of this node
   $result = mysql_query('SELECT parent FROM tree '.
                          'WHERE title="'.$node.'";');
   $row = mysql_fetch_array($result);

   // save the path in this array
   $path = array();

   // only continue if this $node isn't the root node
   // (that's the node with no parent)
   if ($row['parent']!='') {
       // the last part of the path to $node, is the name
       // of the parent of $node
       $path[] = $row['parent'];

       // we should add the path to the parent of this node
       // to the path
       $path = array_merge(get_path($row['parent']), $path);
   }

   // return the path
   return $path;
}
?>

This function now returns the path to a given node. It returns that path as an array, so to display the path we can use print_r(get_path('Cherry')); If you do this for ‘Cherry’, you’ll see:

Array
(
   [0] => Food
   [1] => Fruit
   [2] => Red
)

Disadvantages

As we’ve just seen, this is a great method. It’s easy to understand, and the code we need is simple, too. What then, are the downsides of the adjacency list model? In most programming languages, it’s slow and inefficient. This is mainly caused by the recursion. We need one database query for each node in the tree.

As each query takes some time, this makes the function very slow when dealing with large trees.

The second reason this method isn’t that fast, is the programming language you’ll probably use. Unlike languages such as Lisp, most languages aren’t designed for recursive functions. For each node, the function starts another instance of itself. So, for a tree with four levels, you’ll be running four instances of the function at the same time. As each function occupies a slice of memory and takes some time to initiate, recursion is very slow when applied to large trees.

Go to page: 1 2 3 Next