System.Web.Hosting.HostingEnvironmentException: Failed to access IIS metabase

I came across with this error yesterday and just had to share it.

CAUSE: You have installed Internet Information Services (IIS) after the installation of .NET Framework 2.0

RESOLUTION: Repair your .NET Framework 2.0 by running the following command line to reset the IIS registry settings for aspnet user.

C:\WINDWOS\Microsoft.NET\Framework\v2.0.50727>aspnet_regiis -i

How to Register DLLs in C#

The Regsvr32 tool is used to register or un-register DLL or OCX files.

Usage:
Regsvr32 [/u] [/s] [/n] [/i[:cmdline]] dll_name
/u – un-register server
/s – to run silently without display any message boxes
dll_name – indicates the path and file name of the DLL to register. More than one DLL is allowed in this command.

Example:
regsvr32 C:\example.ocx
regsvr32 /u C:\example.ocx


Sometimes we need to register DLLs programmatically, the codes below will show you how to invoke the regsvr32.exe and register a dll file in C#.

using System.Diagnostics;
…
public static void registerDLL(string dllPath)
{
  try {
    //'/s' : indicates regsvr32.exe to run silently.
    string fileinfo = "/s" + " " + "\"" + dllPath + "\"";

    Process reg = new Process();
    reg.StartInfo.FileName = "regsvr32.exe";
    reg.StartInfo.Arguments = fileinfo;
    reg.StartInfo.UseShellExecute = false;
    reg.StartInfo.CreateNoWindow = true;
    reg.StartInfo.RedirectStandardOutput = true;
    reg.Start();
    reg.WaitForExit();
    reg.Close();
  }
  catch(Exception ex) {
    MessageBox.Show(ex.Message);
  }
}

Hope this will help.

Error Message “No more new fonts may be applied in this workbook.” In Excel

I am currently working on a project, which I need to export an excel file with more than 100 charts need to be included. And guess what? Yeah…Microsoft Excel throws me this error when I tried to export data into the excel file:

No more new fonts may be applied in this workbook.

According to Microsoft Help and Support, this problem is being caused because of the enabled Auto Scale setting in my charts and textboxes (I am also adding textboxes in my excel file).

This problem occurs because of the Auto scale setting. When you add a chart to the workbook, the Auto scale setting is enabled by default. This setting causes charts to use two or more fonts instead of one. When you add multiple charts to a workbook with this setting enabled, the font limitation for a workbook may be reached.

For Microsoft Excel 2000 and later, the maximum number of fonts is 512. If you add charts manually or if you copy and paste existing charts, you can reach the font limitation for a workbook. The following is an example of copying existing charts:

§ You create a chart object in the worksheet.
§ You copy and paste the chart object on the same worksheet ten or more times.
§ You then copy the worksheet several times in the same workbook.

So what I do is unchecked all the Auto Scale checkbox in my charts and textboxes. And now I am able to export the data into excel file without any error. :)

To disable Auto Scale in charts or textboxes:
1. Select a chart or textbox.
2. For chart, right-click and select Format Chart Area. For textbox, right-click and select Format Text Box.
3. Under the Font tab, deselect the Auto Scale checkbox.
4. Click OK.

Cheers! :)

Write Data to SQL Server Tables Using SqlBulkCopy

SqlBulkCopy is a new feature in ADO .NET 2.0 that speed up a copy operation for a large amount of data from your .NET application to SQL Server tables.

The SqlBulkCopy class can be used to write data only to SQL Server tables from different types of data source, as long as the data can be loaded to a DataTable instance or read with an IDataReader instance.

SqlBulkCopy contains an instance method WriteToServer, which is used to transfer data from a source to the destination table. SqlBulkCopy uses a collection of SqlBulkCopyColumnMapping objects to map a column in a data source to a field in the destination table.

There are some essential properties of SqlBulkCopy that you should be aware of:
  § BatchSize: Number of rows SqlBulkCopy will copy from data source to the destination table.
  § BulkCopyTimeOut: The number of seconds that system should wait to complete the copy operation.
  § ColumnMappings: You can use its Add() method to add in a new SqlBulkCopyColumnMapping object to its collection.
  § DestinationTableName
  § NotifyAfter: SqlRowsCopied event handler will be triggered when the number of rows specified has been copied. This is very helpful if you want to be aware the progress of a copy operation, showing completed % in a progress bar, for example.


Sample Codes
Here, I will demonstrate how to import data from CSV source file into database.

…
using System.Data.SqlClient;
…
…
private void bulkCopy_SqlRowsCopied(object sender, SqlRowsCopiedEventArgs e) {
    MessageBox.Show(String.Format("{0} rows have been copied.", e.RowsCopied.ToString()));
}

private void CopyDataToDestinationTable()
{

    string strconn = "
server=localhost;Database=Transaction;User Id=sa;Password=123456;";
    OleDbConnection conn = new OleDbConnection();
    conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\\;Extended Properties=\"text;HDR=Yes;FMT=CSVDelimited\"";

    try {
        conn.Open();

        OleDbCommand myCommand = conn.CreateCommand();

        string commandString = "select * from source.csv";
        myCommand.CommandType = CommandType.Text;
        myCommand.CommandText = commandString;

        OleDbDataReader dataReader = myCommand.ExecuteReader();
        dataReader.Read();

        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(strconn)) {

            bulkCopy.DestinationTableName = "Products";
            bulkCopy.BulkCopyTimeout = 100000;
            bulkCopy.NotifyAfter = 1000;
            bulkCopy.SqlRowsCopied += new SqlRowsCopiedEventHandler(bulkCopy_SqlRowsCopied);
            bulkCopy.ColumnMappings.Add("Brand", "Brand");
            bulkCopy.ColumnMappings.Add("Model", "Model");
            bulkCopy.ColumnMappings.Add("ProductName", "ProductName");

            // call WriteToServer which starts import
            bulkCopy.WriteToServer(dataReader);
        }
    }
    catch (Exception ex) {
        MessageBox.Show(ex.Message);
    }
    finally {
        conn.Close();
    }
}

Well, that’s it. Feel free to ask if there is any problem. Cheers!

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.