00000011 - Apostrophe fix for ExcelPackage library

This may be a no-brainer for people already familiar with the Excel Package library, if so, please disregard.

ExcelPackage is a free .NET library that can be found here .

While checking for ways to create and read excel files for an ASP .NET application, I came up with several options on the web:

- Microsoft.Office.Interop.Excel

- Generating an excel file from a DataTable

- Excel Package / Excel Package Plus

After further checking this options, there were several cons:

Microsoft.Office.Interop.Excel in ASP .NET requires the server to have Microsoft Office installed, may throw unexplained exceptions and negatively impact performance. I have not yet used this library, however, for what I've read, it is only recommended for Windows Form Apps.

I did test generating an excel file from a DataTable, however, the file generated is not a 'true' excel file, which both complicates the file managing afterwards if reading is required and shows an "are you sure you want to open this file" every time the user opens the file with Excel (really annoying actually).

Most people recommend using either Excel Package or Excel Package Plus (which I've not yet tested due to time constraints). Excel Package Plus is actually an extension of Excel Package and is advertised to allow for easier excel data manipulation for graphs generation and content handling, however at this time, I did not require any of this stuff.

So in order to use Excel Package, just proceed to download the binary package and reference it in your project. The "Where to Start Guide" provided by the site is really well explained, and the library is really simple to use.

This is all perfect, unless like me, you come across the need of generating an excel file in which the apostrophe (') character is pretty much a standard. If this happens, the current library will throw an invalid character exception every time you load a cell which contains any value with the character.

You can avoid this replacing the character with some other character, however this doesn't sound much like a solution. I tried escaping the character with some string handling, but this required previous knowledge of which cells would require this "special handling" and to be honest, both messed up the code and didn't really work.

Luckily, the solution was already documented in the excelpackage.codeplex discussion forums.

But in order to save some of your time, the solution is as follows (provided in the forum by user b_o_o_n)

Download and build the source code (as a class library) with the below change. If your project can't find System.IO.Packaging add a reference to WindowsBase.

If the project wont load from the .sln or .csproj make a new project and copy in the .cs files.

In ExcelCell.cs find the string
* XmlNode stringNode = _xlWorksheet.xlPackage.Workbook.SharedStringsXml.SelectSingleNode(string.Format("//d:si[d:t='{0}']", Value), _xlWorksheet.NameSpaceManager);
* replace with
* XmlNode stringNode = _xlWorksheet.xlPackage.Workbook.SharedStringsXml.SelectSingleNode(string.Format("//d:si[d:t='{0}']", System.Security.SecurityElement.Escape(Value)), _xlWorksheet.NameSpaceManager);

Reference the built .dll in your project.

Although this appears in their discussion panel, it is weird that no official solution to this issue has been addressed or implemented by the team in any of their releases. Maybe this was fixed in the Plus release, but at the time of this post, I've not verified that yet.

Good thing is this solution has worked fine for me... at least for now (will keep you posted if not), hope this is somewhat useful. There are several more options to handling Excel files, if you have tried any other solutions, please do share :)

With nothing more to say for now, happy coding!

Entry dismissed.

00000010 - Random SQL Server Databases "Facts"

Remember your database courses in which the professor explained how databases can hold large amounts of data so the design should make several considerations to enable proper querying, maintenance, and handling of data?

Well, welcome to the real world.

Back in the university practices, courses, heck even investigations, the concept or handling of a large database was distant. Even then large was a rather ambiguous concept.

Here in the real world I've had some curious experiences with large databases... say.. tables with more than 20 million registers?

Here are some considerations you must take and facts you probably had not heard of before... hey, at least I know I hadn't prior to this.

**Please note the considerations below apply to C# and SQL Server implementations

---

Error:

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

At first you may not run into this problem as probably you may not start working in a already highly populated database.

However, time will only enable database growth and it's just a matter of time for your SQL queries to present this issue everywhere, as long as the database growth is exponential or completely random.

Luckily, this error is well documented on the web. The solution I've found to be the easiest to implement is as follows.

If you, like me are using SqlCommand to perform SQL operations from C#, just modify the SqlCommand.timeout attribute.

As indicated in the link this refers to "the time in seconds to wait for the command to execute, the default is 30 seconds", which in the example explained above (20 million registers and increasing...) just doesn't cut it.

---

There is a max size for SQL Server Primary Keys

This may be a non-brainer if thought through, as it's a well known best practice to keep primary keys and database indexing as simple as possible.

But it is worth mentioning as a curiosity as... oh well, it happens.

---

SQLType smalldatetime and C# DateTime are not friends?

To be honest I'm not really sure on this one yet, but somehow it has happened. An explanation is still pending, and even though this is not really a large database issue, I'll mention it in case it happens.

It's a common scenario, there is a table with some date stored as a smalldate, you retrieve the smalldate value from the database and store it in a C# DateTime variable. Maybe you later use the DateTime variable for another query... and BAM!

Error: smalldatetime time type can not be transferred to string

Curious as it is, this error appeared in queries created by appending the parameters to create the string. If this explanation is not clear, instead of using sqlCommand, picture:

DateTime randomDate = "22/10/2012 00:00:00"
String query = "SELECT * FROM TABLEA WHERE DATE = '" + randomDate + "';";

For whatever reason, suddenly the date you just picked up from the database is no longer a valid value for the same database. And no standard "google results" solution solves the issue.

The most common suggestion was to use either a SQL CONVERT(...) or CAST(...), but this ended up with the same error.

A working solution is to change your query implementation to sqlCommand, however this may prove rather tedious if the application is not constructing the queries in this format already.

The solution I found to be both working and simple was to create a method to perform the DateTime to String conversion to a SQL date recognized format. The method is not required, however using a single method through which perform the formatting ensures consistency.

public String dateTimeToString( DateTime date) {
  return String.Format("{0:M/d/yyyy HH:mm:ss}", date);
}

DateTime randomDate = "22/10/2012 00:00:00"
String query = "SELECT * FROM TABLEA WHERE DATE = '" + dateTimeToString(randomDate) + "';";

That's all I wanted to share with you for today.

Entry dismissed.

00000001 - It all began here

Hello and welcome to my blog.

This idea started following the advice of my friend and colleague Pablo Castro, who recently started a blog on his own which you can visit here.

I honestly was not very enthusiastic about starting a blog, however after reading the aforementioned blog I kinda changed my mind and decided this to be worth a shot.

As a software developer in Costa Rica, I'll try to share topics regarding general issues I've found in my day to day, lessons I've learned, and some random or interesting facts related to the field.

My hope is for this blog to become somewhat useful both to me and to any fellow developer desperately searching the web for the solution on a problem I've already faced.

As for myself, I have a major in Computer Science from the University of Costa Rica, at the time of this entry I've been working in the IT industry for 1 year and 6 months.

My career started with a job as a SAP support specialist, but I'm now finally working as a Software Developer.

I do apologize in advance if the topics handled in this blog are too simple or not properly explained... hopefully I'll improve with time.

With this short introduction, let's declare this blog started...