BUG: Memory leak occurs when you query an open Excel worksheet by using ActiveX Data Objects (ADO) when Excel is opened

For Excel Connection
'HDR=NO; = HEADER
'IMEX = 1; = type= text
'IMEX = 2; = type= Mixed
'change the registry...
'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel\TypeGuessRows = 0 - it will check the data type for every field
'but at this momment we can not change using MAXSCANROWS=0 - it doesn't work
Dim excelConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & fileFullName & ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1;'"

When you retrieve a Microsoft ActiveX Data Objects (ADO) Recordset from an Excel worksheet that is open in Excel, a memory leak occurs in the Excel process. Repeated queries may eventually cause Excel to run out of memory and raise an error, or cause Excel to stop responding.

The memory used by the ADO queries cannot be reclaimed by closing and releasing the ADO objects. The only way to release the memory is to quit Excel.

If possible, query the Excel worksheet only while the file is not open in Excel.

If the worksheet must remain open (for example, to allow dynamic recalculation of worksheet values on an ongoing basis) use one of the following methods to work around the behavior:

Method 1

•

Use the SELECT INTO syntax of the Jet OLE DB Provider to export the Excel data to a new worksheet. For additional information about using the SELECT INTO syntax to export data, click the following article number to view the article in the Microsoft Knowledge Base: 295646 (http://support.microsoft.com/kb/295646/) How To Transfer Data from ADO Data Source to Excel with ADO

Method 2

•	Use the SaveCopyAs method of the Workbook object in the Excel object model to programmatically save the open Excel file under a new name. You can then query the copy of the file that you previously saved under a new name from the ADO application.

Method 3

save as Excel using Excel.Application . SaveAs to Excel CSV file and Read that CSV file using OLEDB Connection

Provider=Microsoft.Jet.OLEDB.4.0;Data Source='FOLDER PATH';Extended Properties='text;HDR=Yes;IMEX=1;FMT=Delimited'


and using select * from fileName.

Adding Permission to Excel COM Object

If you run the service using Network Service Account then you need to add permission to Microsoft Excel COM Object because by default Microsoft Excel as a COM object can only activated by the following accounts:

• Administrator
• System
• Interactive

Configure DCOM

• Go to the Start-Run menu item.
• Type in "DCOMCNFG" and hit enter.
• This should load the "Component Services" MMC (you can also load from Administrative Tools - Component Services"
• Expand "Component Services"
• Expand "Computers"
• Expand "My Computer"
• Select the "DCOM Config" item
• Select the "Microsoft Excel Application" item.
• Right click and select Properties
• Add Permission to network service or ASP Net account
  

EventLog.WriteEvent in Windows Service or ASPNET

If you get this error in writting EventLog.WriteEvent in Windows Service or ASP.Net.
"The source was not found, but some or all event logs could not be searched. Inaccessible logs: Security."

You need to create your source manually. because the process can not created for u.

To add your source manually,
Open Regedit and add your source inside.
"HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Eventlog\Application".

After that you will be able to Write event log freely.

How to debug your Javascript in .net 1.1 / 2.0

To debug your javascript code...

* Using Internet Explorer
Make sure that u untick Disable Script Debuging options.

* for Firefox.. There is extension plugin

*Configure your web config .. to enable debug...

* Press F5 for build with debugging instead of Shift + F5.


* You may need to separate your javascript into other file... It can not be embedded into your .aspx file because at the runtime.. your *.aspx content will dynamically converted into html...

However in VS 2005.. You can debug your embeded javascript...
But you can not directly put a break point into that line of code...
You need to Run first (F5) and go to DEBUG > WINDOWS > Script Explorer... You will see.. the merge javascript with your aspx which has been converted into html... and you can put the break point in there.


* USE ASP.NET AJax Extender...

You can use
Sys.Debug.trace("Whatever you want to trace - will appear in output window");
and
Sys.Debug.fail("It will stop in this point.. and you may start debugging");

Using AjaxPro2.dll from Michael Schwarz

*Add web Config
inside configuration/configSections

inside configuration/system.web/httpHandlers



*add reference AjaxPro2.dll into the bin folder
using AjaxPro;

* in page load (postback and not) - add Utility.RegisterTypeForAjax(typeof(_Default));

*On client ajax method add attribute
[AjaxMethod(HttpSessionStateRequirement.ReadWrite)] public string MyAjaxMethod(string username)
{
return "Halo" + UserName;
}


*call in client javascript
_Default.MyAjaxMethod("Kurniawan").value

Thread and Delegate Issue

Run Thread with Parameters
----------------------------------------------
*** But it is only limited to 1 object...
Thread t = new Thread(new ParameterizedThreadStart(Run));
t.Start("an Object");

or using Thread Pool
-----
ThreadPool.QueueUserWorkItem(new WaitCallback(Run), "an Object");
-----

----------------------------------------------------------
*** Use Class method...
public class UrlFetcher

{
  string url

  public UrlFetcher (string url)
  {
      this.url = url;
  }

  public void Fetch()
  {
      // use Any Attribute here
  }
}

UrlFetcher fetcher = new UrlFetcher (myUrl);// Set All the input
new Thread (new ThreadStart (fetcher.Fetch)).Start();

-------------------------------
or Call Delegate Asyn

delegate void MyDel(object a, object b);

          MyDel d = new MyDel(Fetch);
          d.BeginInvoke(a,b,new AsyncCallback(AfterRun),"this is state");

====================================

===================================================
CALL DELEGATE Dynamically

void Force(Delegate method, object[] args)
{
method.DynamicInvoke(args)
}

Coding Issue

Finalize VS Descructor


Finalize and (~) Desctructor is the same

in VB - use Finalize
but
in C# - u can not use Finalize - must be desctructor (~)

Protected Overrides Sub Finalize()
' Desctructor code to free unmanaged resources
MyBase.Finalize()
End Sub

~ ClassName(){}


***Desctructor will be called by the garbage collector - if you call
GC.Collect();





-------------------------------------
GC.Collect VS GC.SuppressFinalize(Object)
Collect - will run Garbage Collector - which run your Desctructor / finalize
- However I realize It will run before you create another object.

SuppressFinalize - will not run.. your desctructor on the object



-------------------------------------
Dispose() Vs Descructor

Desctructor - will guarantee run by Garbage Collector - But It may time Lag even you put GC.Collect(); - it Collect.. after you create another object

Dispose - If you need to explicitly clean up without waiting GC...
- But It will not be run by GC - You need to call
YourObject.Dispose();

or using using Keyword in CS
Using(Object obj = new Object);

Note you call the Dispose method yourself and in Desctructor there is .Dispose as well...
, you should suppress garbage collection on the object once it's gone, as we'll do here.

*** My preference is use Desctructor... to eliminate the risk - and called GC.Collect();


-------------------------------------
ROUNDING Decimal 2 Digit
Math.round(123.23123 * 100) / 100
ROUNDING Decimal 3 Digit
Math.round(123.23123 * 1000) / 1000

-------------------------------------
REF and OUT in C#
Ref to use in and out..
out is for out only... you can not parse put input in it... you need to init inside the method...
--------------------------------------