Convert xml to table in SQL 2005

This is how I convert XML string into a Table in SQL 2005:

DECLARE @tempTable TABLE (
userId INT,
userName NVARCHAR(50),
password NVARCHAR(50)
)
DECLARE @xml XML
SET @xml='
<row userId="67" userName="Kenny1" password="1234" />
<row userId="80" userName="Kenny2" password="5678" />'

INSERT INTO @tempTable
SELECT Tbl.Col.value('@userId', 'INT'),
       Tbl.Col.value('@userName', 'NVARCHAR(50)'),
       Tbl.Col.value('@password', 'NVARCHAR(50)')
FROM   @xml.nodes('//row') Tbl(Col)

--See the table
SELECT * FROM @tempTable 

Web page search and SQL

Here is a way to search many columns in a table:

CREATE PROCEDURE [dbo].[SearchExample] (
@userId UNIQUEIDENTIFIER = NULL,   --set all parameters' default value to null
@firstName NVARCHAR(50) = NULL,
@lastName NVARCHAR(50) = NULL,
@address NVARCHAR(100) = NULL,
@zipcode INT = NULL
)

SELECT ...  --omit
FROM   dbo.UserInfo u
WHERE  u.userId = COALESCE(@userId, u.userId)
AND    u.firstName LIKE ('%'+ COALESCE(@firstName, u.firstName)+'%')
AND    ...  --omit
...    --omit

Set the default value to null for all the input parameters is because if we don't have any filter to search (the value is null), then COALESCE() will use the second parameter as the output, otherwise it will output the first parameter.
For example, if @userId = 1001, the

WHERE  u.userId = COALESCE(@userId, u.userId)

will become like

WHERE  u.userId = 1001

Otherwise, it will become like

WHERE  u.userId = u.userId

Brainstorming: Prefix and Postfix Operators

Another interested code segment talks about prefix and postfix operators:

class IncrementExample
{
 public static void Main()
 {
   int x = 1;

   Console.WriteLine("{0}, {1}", x++, x++);
   Console.WriteLine("{0}, {1}", ++x, ++x);
 }
}

The result is

1, 2
4, 5

Since x++ has a postfix operator, that means x will give its value to Console.WriteLine first, then perform the ++ operation. In contrast, prefix operator will perform ++ operation first, then give the value to Console.WriteLine.

Brainstorming: An interested interview question

I saw this code segment on a blog:

public class A
{
  void PrintA()
  {
    Console.WriteLine("Print A");
  }

  class B : Program
  {
    new void PrintA()
    {
      Console.WriteLine("Print B");
    }
  }

  public static void Main()
  {
    A Atemp = new A();
    B Btemp = new B();

    Atemp.PrintA();
    Btemp.PrintA();
  }
}

What do you think the result will be? Don't forget this is a Interview question, that means you have to answer no more than few minutes.

The answer is:

Print A
Print A

Why not and how to get the following result?

Print A
Print B

The key point is the inner/nested class. We have to declare it is a public class so it's visible to everyone.

Photo and Archive Merger (link updated)

Name: Kenny's Photo and Archive Merger
Version: 1.0
OS: Windows 2000/XP with .Net Framework 2.0 installed
Introduce:
In some circumstances you will need to hide your archive. For example, some free space only allow you to upload and store photos/images, and this program can help you to hide your archive in a photo/image. I believe you can have more ways to use this tool depend on your imaginations... :)
How to use:
1.Select a photo/image (to be a cloak)
2.Select an archive (to be cloaked)
3.Select a destination (don't forget to append the extension name, e.g. ".jpg")
4.Click Merge icon
That's it!
How to split:
Either 1.change the extension name from image type (.jpg) to original archive type (.rar or .zip) or 2.use the corresponding archiver (WinRAR or WinZIP) to open the photo/image directly.

Closing a windows form

How to close a windows form? Many people will create a click event and call the Form class' Close() method like this:

private void btnExit_Click(object sender, EventArgs e)
{
  this.Close();  //or this.Dispose();
}

Since you created the Click event just for closing the form, why don't we try other simpler approach? We can set the button's DialogResult property to a non-default value (any value other than None), so that .Net framework will close the form for us automatically when user click that button. This way we can handle button clicks without an event handler. Actually the DialogResult property will return a value depends on how the form is closed, and we can use that value to do many things.

What if we also have an event handler for the same button's Click event? Well, the Click event will still be executed, and it will be done first. After the event finishes and returns control to framework, the form will be closed.

By the way, this cannot replace the Application.Exit(). Don't waste your time on using the button's DialogResult property on the main form's Exit button, it won't work.

If you want to know more details about what's underlying, I'll suggest you to read this book: .NET Windows Forms in a Nutshell.

MultiLine TextBox (7/9/2009 updated)

In many cases, we want the TextBox accepts multi-line input. If we want to read the whole data, we can just fetch it from TextBox's "Text" property. But how to read the input line by line? Here is how:

string strLineData;
using (StringReader sr = new StringReader())
{
    //fetch line data
    strLineData = sr.ReadLine();

    while (!String.IsNullOrEmpty(strLineData))
    {
        //do something here

        //fetch next line of data
        strLineData = sr.ReadLine();
    }
}

Round off in C# (updated)

How to round off in C#? Heres are what I know:

1.Create a method and perform some logics
Ex:
N = 24.12731 I want to show "24.13" only.
Step a. N*100 + 0.5 = 2413.231
Step b. Divide System.Math.Floor(2413.231) by 100
Step c. The result will be 24.13

2.Use ToString()

double d = 3.445;
d.ToString("0.00");  //3.45
d.ToString("0.0");  //3.4
d.ToString("0");  //3

3.Use System.Math.Round()

System.Math.Round(3.445, 2);  //3.44
System.Math.Round(3.445, 2, MidpointRounding.AwayFromZero)  //3.45

One thing needs to be cared of:
In some cases, the result from System.Math.Round() will be different from ToString(). For example:

double d = 0.2975;
System.Math.Round(d, 3)  //You will get 0.297
d.ToString("0.000")  //You will get 0.298

Convert to/from ASCII code

Sometimes I need to convert char to/from ASCII code, and here I list all the ways I know to do this.

1.Cast

int i = (int)'a';  //i = 97

2.System.Text.Encoding

/* Get the corresponding ASCII codes */
byte[] ByteArray = Encoding.ASCII.GetBytes(string)

/* Get the corresponding chars */
char[] CharArray = Encoding.ASCII.GetChars(byte[])

3.Convert.ToXXX()

char c = 'a';
int i = Convert.ToByte(c);  //i = 97
int i = 98;
char c = Convert.ToChar(i);  //c = 'b'

Kenny's CD/DVD-Rom Ejector

Name: Kenny's CD/DVD-Rom Ejector
Version: 1.0
OS: Any Windows platform with .Net Framework 2.0 installed.
Introduce:
For some reason I cannot eject or close my CD/DVD-Rom's tray by clicking the its button, so I wrote this small tool to control the tray by sending the control signal to it.

Kenny's ZIP Code Finder

Name: Kenny's Zip Code Finder
Version: 1.0
OS: Any Windows platform with .Net Framework 2.0 installed
Introduce:
Ever got a strange call and wants to find out where does it come from? Has a zip code and would like to know more from it? Wants to know what zip codes or phone area codes belong to your state? That's it! That's why I wrote this small tool.

Kenny's Blog Code Converter (KBCC)

Name: Kenny's Blog Code Converter (KBCC)
Version: 3.1
OS: Any Windows platform with .Net Framework 2.0 installed
Download: Here.
Introduce:
When you pasted some codes on your post or blog, you found that the format of the original code become very ugly. For example, if your code looks like this

class MyClass
{
  ....

  if (condition)
  {
    ....
  }
  else
  {
    ....
  }
}

and when you copied/pasted it to your blog, it will become like this

class MyClass
{
....

if (condition)
{
....
}
else
{
....
}
}

Then you will have to add "＆ｎｂｓｐ;" to your code manually. If your code is very large or complex... God bless you!

So I wrote a very small tool to convert it for you/me. It's very easy to use, just copy the original codes to the left and then click the convert button. Any feedback is welcome!


Change logs:

For 3.0:
+ New design
+ New engine
+ Check new version automatically
- No more click on "Clean" button before the next convert.

For 2.7:
+Supports more special characters.
-Simplify the converter engine. (faster replacing, more stable)

For 2.6:
+Supports more special characters: ＜, ＞, "(double quoted) and ＆.
-Improve the converter engine.
-Improve the performance.

For 2.5:
+Add convert HTML special characters function

Note: Please press "Clear" before converting the next code block.

Brainstorm: Reference type, Value type, and Immutable type in C#

Please read the following codes carefully:
Code 1:

public class Form1
{
     int i = 0;
     string s = "Empty";

     private void button1_Click(object sender, EventArgs e)
     {
          M1(i, s);
          MessageBox.Show("i = " + i.ToString() + ", " + "s = " + s);
     }

     public void M1(int inputI, string inputS)
     {
          inputI = 5;
          inputS = "Something";
     }
}

Code 2:

public class Form1
{
     int i = 0;
     string s = "Empty";

     private void button1_Click(object sender, EventArgs e)
     {
          M1();
          MessageBox.Show("i = " + i.ToString() + ", " + "s = " + s);
     }

     public void M1()
     {
          i = 5;
          s = "Something";
     }
}

What will the MessageBox show of Code 1 and Code 2 separately? Don't copy and paste those codes to your IDE (Visual Studio 2003/2005 or SharpDevelop) now, try to compile and run it in your brain first. This small test can examine your concepts of Reference type, Value type, and Parameter passing in C#. As everyone knows, "int" is a value type, and "string" is a reference type. Let me explain the Code 1 first.

In Code 1, we pass two variables i and s to the method M1 and change their value. Since Value type is passed by value and Reference type is passed by reference, the result should be "i = 0, s = Something". Unfortunately, it's incorrect. The result will be "i = 0, s = Empty". Why? Don't forget "string" is an immutable type. We pass the string s to the variable inputS, so inputS has the same reference (address) of the memory location, which contains the value "Empty", with the variable s. Until now, inputS and s are pointing to the same memory location. When we change the value of inputS, CLR actually create a new memory block which contains the new value "Something" and assign the new reference to inputS. Now inputS and s are pointing to the different memory location: inputS points to "Something", and s still points to "Empty". Now you know why the result is "i = 0, s = Empty".

How about Code 2? To cut a long story short, the correct answer is "i = 5, s = Something". Why? Because we didn't pass i and s to M1, we modified them directly in M1. Although s is immutable, but it will still get the new reference of "Something".

Confused? Don't worry, there is an excellent article talking about this topic: Parameter passing in C#. However, if you are too busy (or too lazy) to read that article, you can go to see this. It's a very good and an easy understanding article, and it also recommended by Jon Skeet, the author of Parameter passing in C#.

PS: When boxing a value type, it will become a reference type plus immutable type, just like string.

C# Coding Style

I believe most of the programmers know how important coding style is, and what benefits that it will bring to us. However, I haven't found any official .Net coding style document, and that's why there are many versions of coding style sheets/documents around us. Some companies even have their own coding style.

Most people will recommend this one: http://www.icsharpcode.net/TechNotes/

But if you really follow the coding style when you are coding in Visual Studio 2005 environment, you will find that VS 2005 IDE will automatically format your code which is different from the coding style document that I mentioned above. For example, your original code looks like this

try {
 ...
} catch (Exception e) {
 ...
}

it will be changed to

try 
{
 ...
} 
catch (Exception e) 
{
 ...
}

This is just an example, and I believe you can find many others. (However, you can change this settings in VS2005 by selecting Tools -> Options -> Text Editor -> C# -> Formatting -> New Lines)

I think MS already has a standard style (built in Visual Studio 2005) for how codes should look like, and he only left naming convention for us to play.

PS1: You can also press Ctrl-K + D to format your code manually.
PS2: I found that Microsoft actually has its own Naming Guidelines. Someone already organized well for us, see here.
PS3: There is another good C# Coding Standards wrote by Lance Hunt at here.

New Open !

This blog is just opened. I'll start to post in a short time. Don't forget to come back once in a while.