[C#] Customize the delimiter of CSV when loading it by using Excel.Workbook.Open() (VBA)

I got an old program which can load a csv file and do something. The customer wants to change the delimiter from comma (,) to others like ";", "|", or "-"... etc. Sounds like an easy job. But no, it's not. The approach that uses on opening the csv file is

using Microsoft.Office;
....
xlsApp = new Excel.ApplicationClass();
xlsWBs = xlsApp.Workbooks;
xlsWB = xlsWBs.Open(TempFileName,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing);

Make Oracle Instant Client work

I installed Oracle Instant Client like others did, extract it at a folder, add the folder to path, change the folder's privilege, add some roles to that folder and have fully access... etc. None of them works.

So the ultimate way to make it work that I found is:
copy the following two files to your application folder "oci.dll" & "oraociei11.dll" and it will work. At least it works for me in my case.

PS: The "oraociei11.dll" has around 124MB of size, and this will make your application become a big monster. I hope you will never have to use this way.

Allowing only digitals in textbox

private void textBox_KeyPress(object sender, KeyPressEventArgs e)
{
  if (!Char.IsDigit(e.KeyChar) && !Char.IsControl(e.KeyChar))
    e.Handled = true;
}

Reference: stackoverflow - How do I make a textbox that only accepts numbers?

Delete a specific DataRow from the DataTable

DataTable dt = BusinessLogic.GetTable(sqlCmd);
dt.Rows.Remove(dt.Select("ColumnName1 = '12345' and ColumnName2 = '67890'")[0]);
dt.AcceptChanges();

Add parameters in oledb and odbc CommandText

OleDb:

using (OleDbCommand cmd = new OleDbCommand())
{
  cmd.CommandText = "Select * from VIP where ID = ? And RegDate >= ?";
  cmd.Parameters.AddWithValue("id", id);
  cmd.Parameters.AddWithValue("regDate", regDate);
  using (OleDbDataReader reader = cmd.ExecuteReader())
  {
    // ..........
  }
}

Odbc:

using (OdbcCommand cmd = new OdbcCommand())
{
  cmd.CommandText = "Select * from VIP where ID = ? And RegDate >= ?";
  cmd.Parameters.Add("id", OdbcType.Int).Value = id;
  cmd.Parameters.Add("regDate", OdbcType.DateTime).Value = regDate;
  using (OdbcDataReader reader = cmd.ExecuteReader())
  {
    // ..........
  }
}

1. The name of parameter doesn't matter, but the order of parameter does.
2. I didn't include full codes here so don't forget to fill in other components like OleDbConnection or OdbcConnection.

Trapping F9 in Winform

private void Form2_KeyDown(object sender, KeyEventArgs e)
{
  if (e.KeyCode == Keys.F9)
  {
    // your work here...
  }
}

1. Use the KeyDown event to trap it.
2. Set the Form's KeyPreview property to True.

Force to show Exception in English

using System.Threading;

Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
Thread.CurrentThread.CurrentUICulture = new System.Globalization.CultureInfo("en-US");

Reference: 如何強迫 .Net 應用程式輸出英文的例外訊息

MSSQL get current datetime

SELECT CURRENT_TIMESTAMP

or

SELECT {fn NOW()}

or

SELECT GETDATE()

byte[] convert to hexadecimal string (and vice versa)

byte[] to hex string:

static string ByteArrayToString1(byte[] source)
{
    StringBuilder sb = new StringBuilder();
    foreach (byte item in source)
    {
        sb.AppendFormat("{0:x2}", item);
    }
    return sb.ToString();
}

static string ByteArrayToString2(byte[] source)
{
    return BitConverter.ToString(source).Replace("-", "");
}

static string ByteArrayToString3(byte[] source)
{
    // namespace: System.Runtime.Remoting.Metadata.W3cXsd2001
    return new SoapHexBinary(source).ToString();
}

static string ByteArrayToString4(byte[] source)
{
    // works for .Net 4 only
    return String.Concat(Array.ConvertAll(source, x => x.ToString("X2")));
}

Loop through enum

enum Week
{
    Sunday,
    Monday,
    Tuesday,
    Wednesday,
    Thursday,
    Friday,
    Saturday
}

foreach (Week item in Enum.GetValues(typeof(Week)))
{
    //...
}

Reference: Can You Loop Through All Enum Values? c#

IPv4 Address convert to Integer (and reverse) (updated)

static Int64 IPv4StringToInt64(string ipv4String)
{
    Int64 ipInteger = 0;
    try
    {
        ipInteger = (long)(uint)IPAddress.NetworkToHostOrder(BitConverter.ToInt32(IPAddress.Parse(ipv4String).GetAddressBytes(), 0));
    }
    catch (Exception)
    { }

    return ipInteger;
}

static string IPv4Int64ToString(Int64 ipv4Int64)
{
    if (ipv4Int64 < 0)
        return "0.0.0.0";
    else if (ipv4Int64 < 4294967295)
        return IPAddress.Parse(ipv4Int64.ToString()).ToString();
    else
       return "255.255.255.255";
}

Usage:

Console.WriteLine("255.255.255.0 => " + IPv4StringToInt64("255.255.255.0"));
Console.WriteLine("4294967040 => " + IPv4Int64ToString(4294967040));

Reference: stackoverflow - How to convert an IPv4 address into a integer in C#?

Refresh WebBrowser in Winform

I put a WebBrowser control on my form and use it to load XML files. Here is the code:

OpenFileDialog FileDialog = new OpenFileDialog();
FileDialog.Title = "Open XML Document";
FileDialog.Filter = "XML file (*.xml)|*.xml";

if (FileDialog.ShowDialog() == DialogResult.OK)
{
 WebBrowser1.Navigate(FileDialog.FileName);
 WebBrowser1.Refresh();
}

I found that if I load the second XML, sometimes it will not refresh itself to show the new XML content. Instead, it still shows the previous one. I google the web to see if there is any other way to prevent the cache. Unfortunately, I found nothing. But this post inspires me and so I create a workaround.

DateTime: Get the first date of the week

1. Create an extension method.

public static class DateTimeExtensions
{
    public static DateTime StartOfWeek(this DateTime dt, DayOfWeek startOfWeek)
    {
        int diff = dt.DayOfWeek - startOfWeek;
        if (diff < 0)
        {
            diff += 7;
        }

        return dt.AddDays(-1 * diff).Date;
    }
}

Enum - simple example

class Program
{
  enum DateTypeEnum
  {
    Year = 1,
    Quarter = 2,
    Month = 4,
    Day = 8
  }

  static void Main(string[] args)
  {
    int DateType = 7;
    Console.WriteLine("Your available paying periods are: ");

    //check all available paying methods
    if ((DateType & (int)DateTypeEnum.Year) == (int)DateTypeEnum.Year)
      Console.WriteLine(DateTypeEnum.Year);

    if ((DateType & (int)DateTypeEnum.Quarter) == (int)DateTypeEnum.Quarter)
      Console.WriteLine(DateTypeEnum.Quarter);

    if ((DateType & (int)DateTypeEnum.Month) == (int)DateTypeEnum.Month)
      Console.WriteLine(DateTypeEnum.Month);

    if ((DateType & (int)DateTypeEnum.Day) == (int)DateTypeEnum.Day)
      Console.WriteLine(DateTypeEnum.Day);

    Console.WriteLine();

    int FavoritePayMethod = 4;
    //retrieve enum
    DateTypeEnum BillPeriod = (DateTypeEnum)Enum.Parse(typeof(DateTypeEnum), FavoritePayMethod.ToString());
    Console.WriteLine("Your favorite paying period is \"{0}\".", BillPeriod);
  }
}

Doing some work after Response.End() (11/14/2013 updated)

I need to do something that after users download a file from my website. Most file downloading approach is using something like this:

//data preparation at here...

Page.Response.ClearContent();
Page.Response.ClearHeaders();
Page.Response.Clear();
Page.Response.Buffer = true;
Page.Response.ExpiresAbsolute = DateTime.Now.Subtract(new TimeSpan(1, 0, 0, 0, 0));
Page.Response.Expires = 0;
Response.AddHeader("content-disposition", "attachment;filename=" + YourFileNameHere + ".xls";
//here I use excel file type
Response.ContentType = "application/vnd.ms-excel";
//FileContent is a string variable which stores your file content
Page.Response.AddHeader("content-length", FileContent.Length.ToString());
Response.Write(FileContent);
Response.End();

Tips: Prevent CheckBox from being changed by user

The easiest way to prevent your CheckBox from being changed by users is adding this to your CheckBox's html code:

OnClick="return false;"

For example:

<asp:CheckBox ID="CheckBox1" runat="server" Text="Want a salary increase?" Checked="False"
 OnClick="return false;" />

Distinct in DataTable or DataView

How to remove duplicated data? Suppose I have a DataTable (dtMembers) looks like this:

ID	Name	City	ZipCode
01	Kenny	LA	12345
02	Peter	CA	54321
03	John	NY	13125
04	Jimmy	NY	13125

Using the following two lines of codes if you want to get the non-duplicated city list:

string[] columnNames = new string[] { "City" };
DataTable dtCity = dtMembers.DefaultView.ToTable(true, columnNames);

The result will be "LA, CA, NY".

Take a look of the DataView.ToTable() method and this discussion.

Get .Net Color from Hex Color (Hex string)

It's easy but also easy to forget. I put it here as a note for myself. :)

Label1.BackColor = System.Drawing.ColorTranslator.FromHtml("#FFC0FF");

Tips: Prevent SSRS report caching

There are many ways to turn off the report cache, but many of them need you to have enough privilege to change the report or server settings. There is one approach that everyone can do without any privilege: comtrol the parameters in the url.

Append this commend to the end of the url:

&rc:emissionTime=633819525738564717

The number should be random, and you can do like this:

"http://.....&rc:emissionTime=" + DateTime.Now.Ticks;

The it's the same trick like this one.

Javascript: Get client's TimeZone

How to know client's time and time zone? The simplest way that can do this is using the javascript.

<script type="text/javascript">
function displayTime()
{
var localTime = new Date();
//this one will give you the GMT offset
var timezone = localTime.getTimezoneOffset()/60 * (-1);
var div = document.getElementById("div1");
div.innerText = "GMT" + timezone;
}
</script>

Or simply use

<script type="text/javascript">
function displayTime()
{
var div = document.getElementById("div1");
div.innerText = (new Date()).toString();
}
</script>