Oracle Database Acess in .NET

I wanted to import a Oracle database backup to a test environment and used .NET to talk with it.

First import the database backup:
C:> imp system/orcl@odbdev file= dbbackup.dmp fromuser=orms touser=orms

Configure .NET Data Provider for Oracle:
Data Source: Oracle Database (Oracle Client);
Data Provider: .NET Framework Data Provider for Oracle;
Server Name: DBServer (This is the service name configured in tnsname.ora which is set using oracle client tools)
User Name:[User]
Password: [Password]

Connection String:

<add key="OracleDBConnString" value="user ID=[USER];Password=[PASSWORD];data source=ODBDEV;" />

Both Oracle and Microsoft make their Oracle data providers available for free. Microsoft Oracle data provider is available in .NET 1.1 and .NET 2.0 framework, but it still requires Oracle client software installed; Oracle Data Provider for .NET (ODP.NET) is included with the Oracle database installation. The recommendation is use ODP.NET since it's optimized for Oracle by Oracle, and we did find ODP.NET running faster than Microsoft's in our test.

To use ODP.NET, you need to add reference of Oracle.DataAccess.dll from GAC, import the name space, and the rest is identical to the regular ADO.NET with SQL Server:

using System.Configuration;
using Oracle.DataAccess.Client;

public class DAL
{
    public static DataTable GetAllUsers()
    {
         DataTable dtUser = new DataTable();
         string connString= ConfigurationSettings.AppSettings("OracleDBConnString");
         string sqlText = "SELECT * FROM Users";
         try
         {      
             using (OracleConnection conn = New OracleConnection(connString))
             {
                  OracleAdapter adapter= new OracleDataAdapter(sqlText, conn);
                  adapter.Fill(dtUser);
             }
         }
         catch (Exception ex)
         {
             ErrorLog.Write(ex);
         }

         return dtUser;
    }
}

Concatenate Generic String List To A String

How to convert .NET 2.0 Generic List<string> to a string like "str1, str2,..."? Of course we can loop through each items inside the generic list, and add each to a string builder. But that doesn't look very elegant. The easier way is use string.Join static method and Generic ToArray method:

using System;
using System.Collections.Generic;

class Program
{
    static void Main(string[] args)
    {
        List<string> Names = new List<string>();
        Names.Add("Bob");
        Names.Add("Rob");
        string strNames = string.Join(", ", Names.ToArray());
        Console.WriteLine(strNames);
        Console.Read();
    }
}

The result is:

Bob, Rob

.NET DateTime Format String

You can display a DateTime with custom format using DateTime.ToString() or String.Format() methods in .NET. The custom format string follows a set of naming convention such as y for year, M for month, d for day, h for hour 12, m for minute, s for second, and z for time zone. Let's create a simple ASP.NET page to exam the DateTime format string:

    public partial class WebForm1 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            string dtString = string.Empty;
            DateTime dt = DateTime.Now;
            StringBuilder sb = new StringBuilder();
            sb.Append("y yy yyy yyyy \t--- (Year) "  + dt.ToString("y yy yyy yyyy") + "<br>");
            sb.Append("M MM MMM MMMM \t--- (Month) "  + dt.ToString("M MM MMM MMMM") + "<br>");
            sb.Append("d dd ddd dddd \t--- (Day) "  + dt.ToString("d dd ddd dddd") + "<br>");
            sb.Append("h hh H HH \t--- (Hour) "  + dt.ToString("h hh H HH") + "<br>");
            sb.Append("m mm \t\t--- (Minute) "  + dt.ToString("m mm") + "<br>");
            sb.Append("z zz zzz \t--- (Zone) "  + dt.ToString("z zz zzz") + "<br>");

            sb.Append("d \t--- " + dt.ToString("d") + "<br>");
            sb.Append("D \t--- " + dt.ToString("D") + "<br>");
            sb.Append("f \t--- " + dt.ToString("f") + "<br>");
            sb.Append("F \t--- " + dt.ToString("F") + "<br>");
            sb.Append("g \t--- " + dt.ToString("g") + "<br>");
            sb.Append("G \t--- " + dt.ToString("G") + "<br>");
            sb.Append("m \t--- " + dt.ToString("m") + "<br>");
            sb.Append("r \t--- " + dt.ToString("r") + "<br>");
            sb.Append("s \t--- " + dt.ToString("s") + "<br>");
            sb.Append("u \t--- " + dt.ToString("u") + "<br>");
            sb.Append("U \t--- " + dt.ToString("U") + "<br>");
            sb.Append("y \t--- " + dt.ToString("y") + "<br>");

            sb.Append("yyyy-MM-dd \t\t--- " + dt.ToString("yyyy-MM-dd") + "<br>");
            sb.Append("yyyy-MM-dd HH:mm \t--- " + dt.ToString("yyyy-MM-dd HH:mm") + "<br>");
            sb.Append("yyyy-MM-dd HH:mm:ss \t--- " + dt.ToString("yyyy-MM-dd HH:mm:ss") + "<br>");
            sb.Append("yyyy-MM-dd HH:mm tt \t--- " + dt.ToString("yyyy-MM-dd HH:mm tt") + "<br>");
            sb.Append("yyyy-MM-dd H:mm \t--- " + dt.ToString("yyyy-MM-dd H:mm") + "<br>");
            sb.Append("yyyy-MM-dd h:mm \t--- " + dt.ToString("yyyy-MM-dd h:mm") + "<br>");
            sb.Append("ddd, dd MMM yyyy HH:mm \t--- " + dt.ToString("ddd, dd MMM yyyy HH:mm") + "<br>");
            sb.Append("dddd, dd MMMM yyyy HH:mm \t--- " + dt.ToString("dddd, dd MMMM yyyy HH:mm") + "<br>");

            lblDateTimeFormat.Text = sb.ToString();
        }
    }

Result:

y yy yyy yyyy  --- (Year) 6 06 2006 2006
M MM MMM MMMM  --- (Month) 1 01 Jan January
d dd ddd dddd  --- (Day) 18 18 Wed Wednesday
h hh H HH      --- (Hour) 9 09 21 21
m mm           --- (Minute) 12 12
z zz zzz       --- (Zone) -5 -05 -05:00
d  --- 1/18/2006
D  --- Wednesday, January 18, 2006
f  --- Wednesday, January 18, 2006 9:12 PM
F  --- Wednesday, January 18, 2006 9:12:36 PM
g  --- 1/18/2006 9:12 PM
G  --- 1/18/2006 9:12:36 PM
m  --- January 18
r  --- Wed, 18 Jan 2006 21:12:36 GMT
s  --- 2006-01-18T21:12:36
u  --- 2006-01-18 21:12:36Z
U  --- Thursday, January 19, 2006 2:12:36 AM
y  --- January, 2006
yyyy-MM-dd           --- 2006-01-18
yyyy-MM-dd HH:mm     --- 2006-01-18 21:12
yyyy-MM-dd HH:mm:ss  --- 2006-01-18 21:12:36
yyyy-MM-dd HH:mm tt  --- 2006-01-18 21:12 PM
yyyy-MM-dd H:mm      --- 2006-01-18 21:12
yyyy-MM-dd h:mm      --- 2006-01-18 9:12
ddd, dd MMM yyyy HH:mm    --- Wed, 18 Jan 2006 21:12
dddd, dd MMMM yyyy HH:mm  --- Wednesday, 18 January 2006 21:12

In reality a DateTime value is often passed through with a formatted string, and is converted back to DateTime at some point. The safest way for parsing a DateTime string is using the TryParseExact method:

string dtString = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
DateTime curDate;
DateTime.TryParseExact(dtString, "yyyy-MM-dd HH:mm:ss", DateTimeFormatInfo.InvariantInfo, 
                 DateTimeStyles.None, out curDate); 

.NET 2.0 Yield Usage

The .NET equivalent of Java Iterator is called Enumerators. Enumerators are a collection of objects which provide "cursor" behavior moving through an ordered list of items one at a time. Enumerator objects can be easily looped through by using "foreach" statement in .NET.

Actually .NET framework provides two interfaces relating to Enumerators: IEnumerator and IEnumerable. IEnumerator classes implement three interfaces, and IEnumerable classes simply provide enumerators when a request is made to their GetEnumerator method:

namespace System.Collections
{
    public interface IEnumerable
    {
        IEnumerator GetEnumerator();
    }

    public interface IEnumerator
    {
        object Current { get; }
        bool MoveNext();
        void Reset();
    }
}

.NET 2.0 introduces "yield" keyword to simplify the implementation of Enumerators. With yield keyword defined, compiler will generate the plumbing code on the fly to facilitate the Enumerators functions. Following example demos how to use "yield" in C#:

using System;
using System.Collections;
using System.Collections.Generic;

namespace ConsoleApplication1
{
    public class OddNumberEnumerator : IEnumerable
    {
        int _maximum;
        public OddNumberEnumerator(int max)
        {
            _maximum = max;
        }

        public IEnumerator GetEnumerator()
        {
            Console.WriteLine("Start Enumeration...");
            for (int number = 1; number < _maximum; number++)
            {
                if (number % 2 != 0)
                    yield return number;
            }
            Console.WriteLine("End Enumeration...");
        }

        public static IEnumerable<int> GetOddNumbers(IEnumerable<int> numbers)
        {
            Console.WriteLine("Start GetOddNumbers Method...");
            foreach (int number in numbers)
            {
                if (number % 2 != 0)
                    yield return number;
            }
            Console.WriteLine("End GetOddNumbers Method...");
        }
    }

    class Program
    {
        public static void Main(string[] args)
        {
            OddNumberEnumerator oddNumbers = new OddNumberEnumerator(10);
            Console.WriteLine("Loop through odd number under 10:");
            foreach (int number in oddNumbers)
            {
                Console.WriteLine(number);
            }

            Console.WriteLine();
            int[] numbers = { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 };
            Console.WriteLine("Using method with yield return:");

            foreach (int number in OddNumberEnumerator.GetOddNumbers(numbers))
            {
                Console.WriteLine(number);
            }
            Console.ReadLine();
        }
    }
}

Result:


Notice we are using two approaches to loop through odd numbers. In the first approach where "yield return" is used in IEnumerable.GetEnumerator, there's no space allocated to hold the odd numbers, and they are all generated on the fly, which is very efficient when dealing with a large list of items (think about if we want to print out or save all 32-bit odd numbers in our example).

Using CrystalReport to Export DataTable With Different Format

We can export data inside a DataTable to an Excel spreadsheet by defining the Response.ContentType:

Response.ContentType = "application/vnd.ms-excel";
string separator = "\t";
foreach (DataColumn dc in dataTable.Columns)
{
       Response.Write(separator + dc.ColumnName );
}
Response.Write("\n");
foreach (DataRow dr in dataTable.Rows)
{
       for (int i = 0; i < dataTable.Columns.Count; i++)
       {
              Response.Write(separator + dr[i].ToString());
       }
       Response.Write("\n"); 
}

What if we need more format such as PDF? You can export data to a few other popular formats by using Crystal's ReportDocument if you have Crystal Reports available:

using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Shared;
using System.IO;

private void ExportReport()
{
    MemoryStream oStream = new MemoryStream();
    string filename = string.Empty;
    try
    {
        ReportDocument crReport = new ReportDocument();
        string reportFile = "CrystalReport1.rpt";
        string reportPath = string.Format("{0}/{1}", Server.MapPath("."), reportFile);
        crReport.Load(reportPath);

        ReportDataSetTableAdapters.UtilityTableAdapter ad = new ReportDataSetTableAdapters.UtilityTableAdapter();
        ReportDataSet.UtilityDataTable ut = ad.GetAllData();
        crReport.SetDataSource((DataTable)ut);

        switch (SelectedType)
        {
         case "1": //Rich Text (RTF)
             oStream = (MemoryStream)crReport.ExportToStream(ExportFormatType.RichText);
             Response.ContentType = "application/rtf";
             filename = "data.rtf";
             break;
         case "2": //PDF 
             oStream = (MemoryStream)crReport.ExportToStream(ExportFormatType.PortableDocFormat);
             Response.ContentType = "application/pdf";
             filename = "data.pdf";
             //in case you want to export it as an attachment use the line below
             //crReport.ExportToHttpResponse(ExportFormatType.PortableDocFormat, Response, true, "Your Exported File Name"); 
             break;
         case "3": //MS Word (DOC)
             oStream = (MemoryStream)crReport.ExportToStream(ExportFormatType.WordForWindows);
             Response.ContentType = "application/doc";
             filename = "data.doc";
             break;
         case "4": //MS Excel (XLS)
             oStream = (MemoryStream)crReport.ExportToStream(ExportFormatType.Excel);
             Response.ContentType = "application/vnd.ms-excel";
             filename = "data.xls";
             break;
         default: //PDF 
             oStream = (MemoryStream)crReport.ExportToStream(ExportFormatType.PortableDocFormat);
             Response.ContentType = "application/pdf";
             filename = "data.pdf";
             break;
        }
        //write report to the Response stream
        byte[] dataByte = oStream.ToArray();
        Response.Charset = "";
        Response.AddHeader("content-disposition", "fileattachment;filename=" + filename);
        Response.ContentType = "application/octet-stream";
        Response.BinaryWrite(dataByte);
        Response.Flush();
        Response.Clear();
        Response.End();
    }
    catch (Exception ex)
    {
        ErrorLog.Write(ex);
    }
    finally
    {
        //clear stream
        oStream.Flush();
        oStream.Close();
        oStream.Dispose();
    }
}

Just a side note if you can not import Crystal merge module into your VS2003, then you need to register the dll again:
regsvr32 "C:\Program Files\Common Files\Microsoft Shared\MSI Tools\mergemod.dll"

Check Windows Group Users & Clean-up Network Logon

Often used commands inside Windows domain:

Show users in a local group:
>net localgroup administrators

Show users in a domain group:
>net group All_IT_Staff /domain

Delete credential for a network share
>net use \\shareServerName /del

Delete all credentials for all shares:
>net use * /del

Open Credential manager:
>control keymgr.dll
>rundll32.exe keymgr.dll, KRShowKeyMgr

.NET Volatile Field

The MSDN description for the volatile modifier:
"For non-volatile fields, optimization techniques that reorder instructions can lead to unexpected and unpredictable results in multi-threaded programs that access fields without synchronization such as that provided by the lock-statement."

What does it mean? In a multi-CPU and multi-threaded environment, a value could be temporarily stored in registers and multiple levels of cache, and the value change by a thread in one CPU may not be seen immediately by another thread run in another CPU. This could lead to some unpredictable issues.

We can guarantee to get a refresh value by declaring a volatile field:

using System;
using System.Threading;
class Test
{
    public static bool finished = false;
    private static object syncLock = new object();
    static void Main()
    {
        new Thread(new ThreadStart(Thread1)).Start();
        new Thread(new ThreadStart(Thread2)).Start();
        Console.Read();
    }
    static void Thread1()
    {
        while (true)
        {
            if (finished)
            {
                Console.WriteLine("Finished.");
                break;
            }
        }
    }
    static void Thread2()
    {
        lock (syncLock)
        {
            finished = true;
        }
    }
}

In above code snippet, Thread1 will immediately see the latest "finished" value updated by a Thread2, and guarantee to finish and break the infinite loop. Without volatile declared, Thread1 could (not always) never be ended in some systems.

One important note is that volatile field doesn't mean it's thread-safe. Locking mechanism is still required in some scenarios. With a locked field, the value of that field is guarantee to be the latest one, and volatile is unnecessary in such case.

(Updated 2006/9):
My original understanding may not be correct. Some people argue that volatile only means the sequence of accessing a volatile member won't reordered, it doesn't mean that a value assignment will be immediately visible to all processors. However I could not verify this without a real environment.

Visual Studio 2005 & SQL Server 2005 Launch Event in Toronto

Microsoft was launching VS2005, SQL Server 2005, BizTalk Sever 2006 Server as well as .NET 2.0 in Toronto Congress center yesterday. We attended the developer sessions and saw some really good stuff from the presenters. Even better, every attendee was given complimentary software. That includes full version of VS 2005 professional edition, SQL server 2005 standard edition and BizTalk Server 2006 standard edition. Microsoft is quite generous this time.

Web-based Real-time Information Tracking

I am working on a project required web-based real-time status tracking on a big automobile plant. Supervisors need to check if some events such as “incidents happen using a browser. The browser needs to update the information in a few seconds based on the setting. The status information such as “incidents” are stored in a database with information of “start time”, “end time”, “duration”, etc. We decided to use Dundas Charts to generate the information images, and use JavaScript to populate the real-time images. We thought about two approaches for dynamic loading Dundas images on the fly.

1. Image generated in the server

• Advantage: browser friendly without any client side requirement
• Disadvantage: increase the workload in server and more traffic in the network if images are big

2. Image generated in the client

• Advantage: less overhead in server and network
• Disadvantage: require ActiveX enabled in the browser and more overhead in the client

The first method uses JavaScript to update the images regularly. To avoid the page flicker, and the page only reloads the image instead of the whole page (change the image source inside JavaScript):

Consider the image generated in the server may take a bit time (retrieving data from database and building chart image), we could use image buffering in the server: the page image at client only loaded when it’s ready at server. We can buffer image in memory or in files:

The second method also uses JavaScript, but the JavaScript gets the real-time data from server instead of binary images. To do this, we create XMLDOM object inside JavaScript. The data is retrieved from server by XMLHTTP object, and filled into ActiveX object (Dundas chart), then we redraw the chart, as shown in following figure:

There is a sampling rate issue for plotting data in a chart when the unit of X-value in chart is different from that of the data from database. A simple solution for that is to plot the data when there is an incident inside that time slot. As depicted in below figure, three incidents are all plotted between the time shot 2 and shot 3:


(Nov. 2005 update) The first approach (generating images from the server) has been implemented with memory buffering mechanism. The client is quite satisfied what we delivered.

Autonomus System

We are working on a pilot project called BEAT (Best-Effect Autonomous Technology) that will be used in buildings' remote management. The project is quite challenging. We face so many concerns. The complexity of the application would grow significantly if every concern needs to be well addressed. It seems very hard to implement a practical and effective autonomous system in the real world. But hold on a second, is autonomous system well set in every automobile?:-)

Three of us in our team spent almost a whole day on discussing a module of our autonomous system, and the whiteboard pictures of our discussion: