BULK INSERT in MS SQL SERVER

In IT environment, it is always a necessary to import data from data files into database.

And recently, I have to deal with import a huge load of data (around 200 MB for 1 CSV input file) from CSV files into MS SQL SERVER. So what I had done to achieve this is by using BULK INSERT.

Firstly, prepare a CSV data file with the following content and save as test.csv.
July,Singapore,25
James,Australia,50
May,China,29

And then run scripts as following to load all the data from the CSV file into database.

--Temp table to store the data
CREATE TABLE CSVTest
(
   Name VARCHAR(500),
   Country VARCHAR(500),
   Age INT
)
GO

--Bulk insert into CSVTest
BULK INSERT CSVTest
FROM 'C:\test.csv'
WITH
(
   FIELDTERMINATOR=',',
   ROWTERMINATOR = '\n'
)
GO

--Get all data from CSVTest
SELECT *
FROM CSVTest
GO

--Drop the temp table
DROP TABLE CSVTest
GO

Kill Excel.exe Process in C# .NET

I am trying to get my C# .NET codes to generate reports into excel files. In the Export function, it creates the excel object, adds in the data and then saves the file.

All of this works fine. But for some reason, the excel.exe process is still running when I check from task manager.

I had tried to quit the excel application. I had tried to use InteropServices.Marshal.ReleaseComObject to get rid of them in .NET. But unfortunately, the excel.exe is still sitting in memory.

Finally, I found a way to kill the process in C# .NET. Take a look on the codes below:


using System.Diagnostics;
using System.Collections;
…
…
…

Hashtable myHashtable;
private void btnExport_Click(object sender, EventArgs e)
{
  // get process ids before running the excel codes
  CheckExcellProcesses();

  // export to excel
  ExportDataToExcel();

  // kill the right process after export completed
  KillExcel();
}

private void ExportDataToExcel()
{
  // your export process is here...
}

private void CheckExcellProcesses()
{
  Process[] AllProcesses = Process.GetProcessesByName("excel");
  myHashtable = new Hashtable();
  int iCount = 0;

  foreach ( Process ExcelProcess in AllProcesses) {
    myHashtable.Add(ExcelProcess.Id, iCount);
    iCount = iCount + 1;
  }
}

private void KillExcel()
{
  Process[] AllProcesses = Process.GetProcessesByName("excel");

   // check to kill the right process
  foreach ( Process ExcelProcess in AllProcesses) {
    if (myHashtable.ContainsKey(ExcelProcess.Id) == false)
       ExcelProcess.Kill();
  }

  AllProcesses = null;
}


Hope this helps.

ASP .NET Validators don’t work properly with Update Panel

Recently I am working on a project ASP .NET AJAX. Of course it is very common to have some validators in some forms to control the input values, and unfortunately I experienced problem with validators inside the Update Panel. The validators just don’t work properly as I am expected.

After google searching and here is the solution that I had found: ASP .NET AJAX Validators.

Firstly, downloads the validators.zip file and adds in the DLL into the /bin directory of your project. After that, include the following codes into the pages section of the web.config.

<tagMapping>
  <add tagType="System.Web.UI.WebControls.CompareValidator" mappedTagType="Sample.Web.UI.Compatibility.CompareValidator, Validators, Version=1.0.0.0"/>
  <add tagType="System.Web.UI.WebControls.CustomValidator" mappedTagType="Sample.Web.UI.Compatibility.CustomValidator, Validators, Version=1.0.0.0"/>
  <add tagType="System.Web.UI.WebControls.RangeValidator" mappedTagType="Sample.Web.UI.Compatibility.RangeValidator, Validators, Version=1.0.0.0"/>
  <add tagType="System.Web.UI.WebControls.RegularExpressionValidator" mappedTagType="Sample.Web.UI.Compatibility.RegularExpressionValidator, Validators, Version=1.0.0.0"/>
  <add tagType="System.Web.UI.WebControls.RequiredFieldValidator" mappedTagType="Sample.Web.UI.Compatibility.RequiredFieldValidator, Validators, Version=1.0.0.0"/>
  <add tagType="System.Web.UI.WebControls.ValidationSummary" mappedTagType="Sample.Web.UI.Compatibility.ValidationSummary, Validators, Version=1.0.0.0"/>
</tagMapping>

And the problem solved. Cheers!

OrElse and AndAlso operators in VB .NET

OrElse and AndAlso are two new logical operators in VB .NET and they have some properties that can enhance your codes in two general categories:

1. Avoid executing part of a logical expression.
2. Optimize code by not executing any more of a compound expression than required.

OrElse and AndAlso are quite similar with the And and Or except that VB .NET supports short-circuiting with the OrElse and AndAlso operators. This means that the expressions will only be executed when necessary. Anyway, the And and Or are still present in VB .NET.

For example:
// Assume that str1 = 5,
// x = 1 and y = 1

If x > str1 And y < str1 Then
' code
End If

When performing an And operator in VB .NET, it actually evaluates both of the expressions to get the final outcome. Even the first condition (x greater than str1) returns as FALSE, it still continues to look at the second argument even though it doesn’t need to.

Let’s see how AndAlso evaluates the codes below.

If x > str1 AndAlso y < str1 Then
' code
End If

When using AndAlso, VB .NET knows that the expression will not succeed once it is determined that the first condition (x greater than str1) is FALSE. So it stops evaluating the expression right away without checking the second condition.

The difference of Or and OrElse are also similar to And and AndAlso operator, which Or will check all the conditions and OrElse won’t checking the remaining expression, if it found any of the previous condition is TRUE.

How to get Windows’ Serial Number and Windows Logon User Name in .NET

By using the namespace System.Management, we can easily retrieve the serial number of your Windows OS and windows logon user name.

First of all, you need to add a reference of System.Management into your application. To add a reference into your .NET application, you can right-click on References in the solution explorer and select Add Reference.


From the Add References dialog box, select System.Management and click OK.


The sample codes below written in both C# and VB .NET show you how to read the Windows’ serial number and logon user name.

C# .NET
using System.Management;

public static string getSerialNumber() {
  string _serialNo = string.Empty;

  ManagementObjectSearcher objMOS = new ManagementObjectSearcher("Select * from Win32_OperatingSystem");
  ManagementObjectCollection objMOC;

  objMOC = objMOS.Get();

  foreach (ManagementObject objMO in objMOC) {
    _serialNo = objMO["SerialNumber"].ToString();
  }

  return _serialNo;
}

public static string getlogonUser(){
  string _user = string.Empty;

  ManagementObjectSearcher objMOS = new ManagementObjectSearcher("SELECT * FROM Win32_ComputerSystem");
  ManagementObjectCollection objMOC;

  objMOC = objMOS.Get();

  foreach (ManagementObject objMO in objMOC) {
    _user = objMO ["UserName"].ToString();
  }

  return _user;
}


VB .NET
Imports System.Management

Public Shared Function getSerialNumber() As String
  Dim _serialNo As String = String.Empty

  Dim objMOS As New ManagementObjectSearcher("Select * from Win32_OperatingSystem")
  Dim objMOC As ManagementObjectCollection

  objMOC = objMOS.Get

  For Each objMO As ManagementObject In objMOC
    _serialNo = objMO("SerialNumber").ToString()
  Next

  Return _serialNo
End Function

Public Shared Function getlogonUser() As String
  Dim _user As String = String.Empty

  Dim objMOS As New ManagementObjectSearcher("SELECT * FROM Win32_ComputerSystem")
  Dim objMOC As ManagementObjectCollection

  objMOC = objMOS.Get

  For Each objMO As ManagementObject In objMOC
    _user = objMO("UserName").ToString()
  Next

  Return _user
End Function

Cheers.

Export Data to Excel (Not HTML Table) in C# .NET

In my previous post - Export Data to Excel (Not HTML Tables), I had shown you how to export to excel in VB .NET.

In this post, I will use a C# .NET example instead.

Of cause first thing that you need to do is to add Excel.dll (Microsoft excel 11.0 Object Library) into your .NET project references.

Here is the codes:

private void ExportToExcelFile(System.Data.DataTable dt)
{
   Excel.Application xlsApp = new Excel.ApplicationClass();
   Excel.Workbook xlsWorkbook;
   Excel.Worksheet xlsWorksheet;
   string strhdr;
   int row;
   string strFile = "file1.xls";
   string filename = Server.MapPath(strFile);

   if (dt.Rows.Count > 0)
   {
      //Create new workbook
      xlsWorkbook = xlsApp.Workbooks.Add(true);

      //Get the first worksheet
      xlsWorksheet = (Excel.Worksheet)(xlsWorkbook.Worksheets[1]);

      //Activate current worksheet
      xlsWorksheet.Activate();

      //Set header row to row 1
      row = 1;

      //Add table headers to worksheet
      xlsWorksheet.Cells[row, 1] = "Name";
      xlsWorksheet.Cells[row, 2] = "Gender";
      xlsWorksheet.Cells[row, 3] = "Age";

      //Format header row (bold, extra row height, autofit width)
      xlsWorksheet.get_Range("A" + row.ToString(), "C" + row.ToString()).Font.Bold = true;
      xlsWorksheet.get_Range("A" + row.ToString(), "C" + row.ToString()).Rows.RowHeight = 1.5 * xlsWorksheet.StandardHeight;
      xlsWorksheet.get_Range("A" + row.ToString(), "C" + row.ToString()).EntireRow.AutoFit();

      //Freeze the columm headers
      xlsWorksheet.get_Range("A" + (row + 1).ToString(), "C" + (row + 1).ToString()).Select();
      xlsApp.ActiveWindow.FreezePanes = true;

      //Write data to Excel worksheet
      foreach (DataRow dr in dt.Rows)
      {
         row += 1;
         if (dr["Name"] != null)
            xlsWorksheet.Cells[row, 1] = dr["Name"];
         if (dr["Gender"] != null)
            xlsWorksheet.Cells[row, 2] = dr["Gender"];
         if (dr["Age"] != null)
            xlsWorksheet.Cells[row, 3] = dr["Age"];
      }

      //Format data rows (align to center and left, autofit width and height)
      xlsWorksheet.get_Range("A2", "C" + row.ToString()).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
      xlsWorksheet.get_Range("A2", "C" + row.ToString()).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
      xlsWorksheet.get_Range("A2", "c" + row.ToString()).EntireColumn.AutoFit();
      xlsWorksheet.get_Range("A2", "c" + row.ToString()).EntireRow.AutoFit();

      //Make excel workbook visible to user after all data has been added to worksheet.
      xlsApp.DisplayAlerts = false;
      xlsWorkbook.Close(true, filename, null);

      //Export data to client machine
      strhdr = "attachment;filename=" + strFile;
      Response.Clear();
      Response.ContentType = "application/vnd.ms-excel";
      Response.ContentEncoding = System.Text.Encoding.Default;
      Response.AppendHeader("Content-Disposition",strhdr);
      Response.WriteFile(filename);
      Response.Flush();
      Response.Clear();
      Response.Close();
   }
}

Cheers!