Text truncated to 255 characters when reading from Excel using OLEDB
Today my friend Jacob got a strange problem while importing data from EXCEL using OLEDB; he was not able to read actual values of cells, which contains more than 255 characters. The text was truncated if the cell contains more than 255. The code was like this
OleDbConnection con = new OleDbConnection(
@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
+ _xlsFilePath + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\";");
OleDbDataAdapter da = new OleDbDataAdapter
("select * from [" + sheetName + "$]", con);
DataTable dt = new DataTable();
da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
da.FillSchema(dt, SchemaType.Source);
da.Fill(dt);
return dt;
We tried various options in the connection strings but nothing worked. Later we found this MS Support article, which helps to resolve this error using Registry Editing. This issue is because the Jet Engine reads the first 8 cells of each column and check it’s data type.
You can fix this problem by setting a value between 0 to 16 for the TypeGuessRows key in HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel location. If you put 0 as the value, Jet engine will read the first 16000 rows, which may affect the performance.
You can resolve this problem with code.
RegistryKey key = Registry.LocalMachine.OpenSubKey(
@"SOFTWARE\Microsoft\Jet\4.0\Engines\Excel\", true);
if (null != key)
{
key.SetValue("TypeGuessRows", 0, RegistryValueKind.DWord);
}
How to return null from a generic method
You can’t return a null from a generic method normally, if you try to return a null it will throw a compile time error. – Cannot convert null to type parameter ‘T’ because it could be a non-nullable value type. Consider using ‘default(T)’ instead.
public T GenericMethod<T>(object t)
{
return null;
}
But you can resolve this error, by putting a class constraint.
public T GenericMethod<T>(object t) where T : class
{
return null;
}
Setting an application on top other windows using C#
Yesterday while watching video on Youtube.com I found one problem I can’t view YouTube video as well as write code. 
on same time. We can find always on top options in most video players, but I don’t think this option available in browsers or editors. So I thought about writing an application, which helps to set any application on top of other windows / applications. This application is SetWindowPos win32 api function. And I am enumerating all the windows using Process.GetProcess() method.
[DllImport("user32.dll", SetLastError = true)]
[return: MarshalAs(UnmanagedType.Bool)]
private static extern bool SetWindowPos(IntPtr hWnd,
int hWndInsertAfter, int x, int y, int cx, int cy, int uFlags);
private const int HWND_TOPMOST = -1;
private const int SWP_NOMOVE = 0x0002;
private const int SWP_NOSIZE = 0x0001;
Method used to load all the process
private void LoadProcesses()
{
//Getting all the process
Process[] processes = Process.GetProcesses();
this.lstApps.DisplayMember = "MainWindowTitle";
this.lstApps.ValueMember = "MainWindowHandle";
foreach (Process process in processes)
{
//Adding only process which has got Window Title.
if (process.MainWindowTitle.Length >= 1)
{
this.lstApps.Items.Add(process);
}
}
}
And you can set the Window on top by calling SetWindowPos() method like this.
//Checks wheather an item selected on not in the listbox
if (this.lstApps.SelectedIndex != -1)
{
Process process = this.lstApps.SelectedItem as Process;
SetWindowPos(process.MainWindowHandle,
HWND_TOPMOST, 0, 0, 0, 0, SWP_NOMOVE | SWP_NOSIZE);
}
Happy Programming 
How to do code coverage with NUnit tests
In this post I am talking about using Visual Studio code coverage tools to find code coverage with NUnit tests. In my current project we were using NUnit to write unit test cases. Now only we are started moving the NUnit tests to MS Tests for unit testing. It is very difficult and tedious job; also some changes required in the testing logic too. I faced the main issue with the code coverage; it is very easy to do with MS Test, because everything is integrated to Visual Studio. Just run the unit tests, right click on the results, select the code coverage results from the context menu, it will display the code coverage. But if you are using NUnit, you wont get this much flexibility, I don’t think that won’t be much issue compare to re-writing the NUnit test cases to MS tests for code coverage.
Hope you wrote the library and unit test cases.
- First we need to inject the code coverage instrumentation code to the assembly, to do this, go to the command line and type
“C:\Program Files\Microsoft Visual Studio 10.0\Team Tools\Performance Tools\ vsinstr.exe” [assembly] / COVERAGE
This will inject instrumentation code to the assembly. If the assembly is successfully instrumented, it will display a message like this
Successfully instrumented file [assembly]
- Next start the coverage monitor, to do this again type
“C:\Program Files\Microsoft Visual Studio 10.0\Team Tools\Performance Tools\ VSPerfMon.exe” /COVERAGE /OUTPUT:[assembly.coverage]
It will start coverage monitor (profile) server, and it will display a message like
Started in Stand Alone Mode
Filename: [filename.coverage] - Now using NUnit you can run the Unit Test cases.
- After executing all the test cases you can close the NUnit application.
- To view the coverage, you need to stop the coverage monitor(profiler), you can do this by executing the following command in another command line.
“C:\Program Files\Microsoft Visual Studio 9.0\Team Tools\Performance Tools\VSPerfCmd.exe” /SHUTDOWN
This command will shutdown the profile monitor.
Now open the coverage file in Visual Studio to see the coverage. If you have pdbs, you can even see source highlighting as per the coverage.
An example screenshot of code coverage results source code.
Code coverage results
Here is the color codes
- RED : uncovered
- GREEN : covered
- YELLOW : partly covered
Thanks to Pushkaraj
Note: You need to un-sign the assemblies first, before you start converage.
How to enable code coverage in Visual Studio 2010
If you are writing frameworks and creating unit tests for the framework, you may require to find the code coverage of your code. And if you were using VS 2008 and moved to VS 2010, its slightly different.
- Open the local.testsettings which you can access from Test -> Edit Test Settings -> Local (local.testsettings)
- Select Data and Diagnostics from the list
- Select the Enabled checkbox on the Code Coverage row.
- Double-click the Code Coverage row or Select the row and click on the configure button on the top
- Select the assemblies you want to instrument
- Specify a re-signing key file if your assemblies are strong-named
- Click OK
- Click Apply and Close.
Test Settings
Code coverage Details