May 2008 - Posts
Microsoft Excel has been widely used in office and business application. When you are building enterprise business application that used to do something with excel, you need to integrate your application with excel.
One of the most simple step is reading data from excel. Actually there are some techniques to achieve it. But one of the most popular techniques is make use of ADO.NET. Let ADO.NET to treat the excel file as the data source. Here's the code sample:
string cs = @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + filename + @"; Extended Properties=""Excel 8.0; HDR=YES""";
using (OleDbConnection con = new
OleDbConnection(cs))
{
con.Open();
string query = "SELECT Number, Name FROM [Sheet1$]";
OleDbCommand cmd = new
OleDbCommand(query, con); OleDbDataAdapter adapter = new
OleDbDataAdapter(cmd);
DataTable db = new
DataTable();
adapter.Fill(dt);
return dt;
}
Firstly, you need specify the connection string. Treat it as the OleDB. Specify the data source property with the physical location of the file. Next you are ready to read the certain column from the certain Sheet using OleDbCommand. Now you are ready to store your data source to the datatable or collection.
The query
The above query "select Number, Name FROM [Sheet1$]" means to select Number and Name from the Sheet1 Sheet.
What if you want to select certain columns and row, you can do it like this "select Number, Name FROM [Sheet1$A3:G8]". Now the scope would be narrowed to the A3 till G8.
What if you want to select the condition like above BUT we don't know how many records exits (the above example stated G8, means that till the 8th row). You can do it like this "select Number, Name FROM [Sheet1$A3:G]". Now, it would be flexible, depending on how many records you have.
One important issue
I found this issue unintentionally when doing my project. Here's the story.
Firstly, here's my data:
Number | Name |
1 | Joe |
2 | Hansel |
3 | Dwight |
4 | Correy |
It seemed to working well.
Now when my data goes:
Number | Name |
1 | Joe |
2 | Hansel |
3 | Dwight |
4 | Correy |
e | Michael |
f | Roy |
We cannot read e and f. Hm… What's going on?
Then I modified my data:
Number | Name |
a | Joe |
b | Hansel |
c | Dwight |
d | Correy |
e | Michael |
f | Roy |
Now everything was doing well.
I changed my data again into:
Number | Name |
a | Joe |
b | Hansel |
3 | Dwight |
d | Correy |
5 | Michael |
f | Roy |
Now we could not read record 3 and 5.
The conclusion: ADO.NET tries to guess you data type of your column. Once it found some records that do not meet others records type, it will treat it as null, so it will be ignored.
To tell the ADO.NET "Hey, please don't guess my data type, just read what you see!", you need to modified your connection string to put IMEX=1;
string cs = @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + filename + @"; Extended Properties=""Excel 8.0;IMEX=1;HDR=YES""";
For more detail information, see http://support.microsoft.com/kb/194124
Now everything works well :D
In my previous post, I illustrated about ORM. In this post, I'll show you one of the great ORM tool, SubSonic. Actually, SubSonic is not just an ORM, it offers more than that. From its motto "All your database are belong to us", you can see that it is cool J.
Moreover, the good news is, SubSonic is not only free, but also open source.
Lately, I do some research on Subsonic and implement it on my project. I found that I really fall in love with this thing. Thanks very much to Rob Conery, the creator of Subsonic. You help so many people in this world with it :D.
Now, let me show you what the Subsonic is and its brief conceptual before we move on to the technical step / set-up / coding it (I'll try do it in my next post).
What is Subsonic?
Many people give their own definition on SubSonic.
For me, I agree with the illustration of this picture about SubSonic.
It's a multifunctional tool indeed.
From the SubSonic's website:
What is it?
A Super High-fidelity Batman Utility Belt. SubSonic works up your DAL for you, throws in some much-needed utility functions, and generally speeds along your dev cycle.
Why SubSonic?
Because you need to spend more time with your friends, family, dog, bird, cat... whatever. You work too much. Coding doesn't need to be complicated and time-consuming.
What do I need?
Something else to do - you're about to save a bunch of time. We support SQL Server 2000 or 2005, MySQL, or Oracle (with SQLLite, SQLCE, and PostGres coming soon!)
Let's see what it offers…
One of the essential parts of SubSonic is ORM; it helps up map from the relational tables to object oriented data classes.
To access it, you can simply write it like this:
Data Access Layer (DAL) Code Generator
Built on Active Record Pattern, SubSonic generates Data Access Layer for us, usually CRUD (Create, Retrieve, Update, and Delete). The generated file would be named xxxController where xxx refers to the table's name.
We don't have to care about the DAL because SubSonic has done these for us. To use it, just:
What data source /database does it support?
- SQL Server? Yes!
- MySQL? Yes!
- Oracle? Yes!
etc
Libraries
SubSonic provides us a huge numbers of libraries (classes, method, etc) to enhance the development process. For instance, we need query to Customer table where the City is Washington and the ContactName starts with "J". We could do it like this.
According to the query above, what does the return type is? DataReader? DataSet? Or CustomCollection?
The answer is all of them! Yeah, you can have it all.
Component
There are some handy components that are offered by SubSonic:
I will try to describe these more detail in next posts.
You can find more information on Rob Conery's blog and SubSonic official website.
I regret that at my first almost two years of software development, I still coded (sometimes copy and paste) my Data Access Layer manually. How many hours that I spent for that? It almost took 30% - 50% of my entire time on my development. Anyway, forget it. I'll show you something cool.
If you are still coding the plumbing Data Access Layer manually like me before, I suggest you to look what ORM is. In short, ORM is the mediator between the relational database and the object oriented application. We know relational is a popular technique in the database world, but in application / our code, usually Object Oriented rules!
So, what we have to do? Usually we build data class based on the table.
Column | Type |
StudentId | Nchar(10) |
StudentName | Nvarchar(200) |
Email | Nvarchar(200) |
DateOfBirth | smalldatetime |
For example:
We have MsStudent table in the database like this:
In our application, we try to build our data class. We would map the table in the database to a class, let's call it Student. All the columns in the table are supposed to be properties in that data class. Moreover, we could add some method to the Student class.
public
class
Student
{
public Student()
{
}
private
string _studentId;
public
string StudentId
{
get { return _studentId; }
set { _studentId = value; }
}
private
string _studentName;
public
string StudentName
{
get { return _studentName; }
set { _studentName = value; }
}
private
string _email;
public
string Email
{
get { return _email; }
set { _email = value; }
}
private
DateTime _dateOfBrith;
public
DateTime DateOfBirth
{
get { return _dateOfBrith; }
set { _dateOfBrith = value; }
}
}
If you have 10, 20, 50 of tables, you have to write it manually. Don't you feel that you are doing something that a machine / computer could do as well? These are plumbing works.
In fact, you generate these codes, based on the table structure using some code generator such as CodeSmith, etc. To do that, you have to build the template first, and then take the table and others properties as parameters, and then the code generator will generate the code for you. Well, but this is not my main point. I'll show you another approach.
ORM comes in to help you doing the mappings. By using ORM, you don't have build the template first. All you have to do is just set-up the configuration to point to the data source you want to map. The ORM tools would map it for you. You'll have the Student class like that as well.
ORM tools / product
There are some ORM tools in the market. Some of them are commercial, some of them are free to use, and some of them even are open source.
Most of them come from third party vendors. But, lately, in .NET Framework 3.5, Microsoft introduces the built-in ORM, it's LINQ to SQL (was DLINQ). Next few months later, you'll see another baby comes up; it's ADO.NET Entities Framework (ADO.NET EDM).
Let's see what they are:
Ahah! This is my favorite tools. I have fallen in love with it. In fact, Subsonic offers more than an ORM. SubSonic is a very useful and awesome tool. It is easy to use and configure.
In brief, SubSonic is open source, and it is created by Rob Conery. For information, Rob has been hired by Microsoft on ASP.NET team.
I'll post more details about SubSonic on next post.
In Java world, Hibernate is a very popular and mature ORM. Most of Java developer has used it in development.
NHibernate is .NET version of Hibernate. NHibernate makes use of XML as the metadata to map between tables in the database and classes in our application.
It is the first built-in ORM from Microsoft. You can find it in .NET Framework 3.5. LINQ to SQL is quite easy to configure and use. You can add LINQ to SQL class easily by adding new item, and choose Linq to Sql classes. Its extension is .dbml.
One big advantage of using LINQ to SQL is we can use LINQ, the built-in query expression in language (C# 3.0 and VB 9.0).
In contrast, you just only can use SQL Server (2000 and 2008) as the backend database.
You can find LINQ to SQL posts by Scott Gu here.
This product haven't released yet. But most people believe that ADO.NET EDM offers higher functionalities than LINQ to SQL does. At least, it supports any data source / database.
Some people believe that ADO.NET has the ability to reduce / get rid of impedance mismatch. Does it? Let's wait and see.
See Wikipedia's description.
- And so many more…
I bet you might have this question in your mind. "Which .NET ORM is the best?"
Well, it depends on many factors and point of view. You can take this discussion for your reference.
Yesterday, I joined CCNA Fundamental Community Class at Brainmatics. It was just a fundamental and introduction to Cisco Networking. Well, I still want to focus on software development career, attending this short workshop / training was only want to enrich my other knowledge. Since I almost have no idea about networking and CISCO, this one-day long class was an appropriate option to me.
Another strong point that attracted me to join this training is the instructor. The instructor is a famous guy in Indonesia's IT. The founder of www.ilmukomputer.com, the free computer studies community in Indonesia. I had not met him before yesterday. I bet most of you know him already. Yeah, he is Mr. Romi Satria Wahono. Indeed, he is an unordinary man. By looking at his profile, you can feel how superb he is. He got scholarship in Japan from Bachelor till Doctoral Degree. Moreover, he writes a lot of book and does a lot of research as well as delivers numerous of talk, seminars, and training. How about his competence? He comes from Software Engineer's world (in his studies), but he knows almost most areas outside of software's world. Can we say that he is a "superman"? :D (of course without looking at his body size hahaha…)
I am really impress with Mr Romi not only he is clever, but also his noble intention to improve Indonesia especially in computer studies area. In my opinion, if there are some more [Mr Romi]s exist, Indonesia would become more improve.
At the class yesterday, he was a good instructor. He could bring the ease feeling to the class. This friendly man also brought the material very well. Even though, the training only last for one day, but I felt I could learn a lot and these were very useful.
At last, I would like to say thanks to Mr Romi once again. Hope that we / I can become someone like Mr Romi later. Cheers!
At the end of last year, I considered to take the Master degree. I planned to take it in Singapore because of some reasons. I did survey some university there and I found that Nanyang Technological of University is not bad. It is one of the reputable universities in Asia.
What major would I take? These are programs that offered by NTU.
Information System? Ahah! Because I am quite interested in software development, software engineering, information system, etc, I think it suits me most.
After some of my friends saw the subjects, they said "The subjects are quite similar to the degree's ones, do you still want to take it? Don't you feel pity to study something that you maybe had studied before?"
My answer: Yes, they do similar with some subjects in degree's ones, but I like them. I treat them as "hobbies" :D. I wish to study them deeper and farther.
As the result, I have applied Master of Science in Information System in NTU on last February.
The selection takes few months. On last month, my application status on the web was still "Shortlisted", means that I still stood a chance to step in; I wasn't kicked up by them. As I knew that some of the applicant's status was "Application was not successful".
But last Saturday, I checked it again and I found that …
Yeah, I am officially accepted by the university.
The strategy design pattern offers "has a" relationship rather than "is a" / overriding method relationship.
Here's the story begins.
Common situation:
Let's say we have a Vehicle abstract class. It has a Go method.
abstract
class
Vehicle
{
public
virtual
void Go()
{
Console.WriteLine("I am driving");
}
}
Then we have Car class that inherits from Vehicle class.
class
Car : Vehicle
{
}
We don't have to modify the Go method since the implementation "I am driving" is appropriate for Car.
However, that doesn't make sense for Helicopter. As a consequence, we need to override Go method for Helicopter class.
class
Helicopter : Vehicle
{
public
override
void Go()
{
Console.WriteLine("I am flying");
}
}
The Ship class needs to override the Go method as well.
class
Ship : Vehicle
{
public
override
void Go()
{
Console.WriteLine("I am sailing");
}
}
Once we need a new class (Sport Car) that inherits from Car, we need to override the Go method again, because I want the Go method to write "I am driving very fast".
This overriding situation sometimes is quite boring and dull.
The strategy design pattern offers "has a" relationship. In this scenario, we extract the Go method to an abstract class called GoAlgorithm. Then there are some inherited class with different Go implementation such as GoByDriving, GoByFlying, and GoBySailing.
abstract
class
GoAlgorithm
{
public
virtual
void Go()
{
Console.WriteLine("I am driving");
}
}
class
GoByFlying : GoAlgorithm
{
public
override
void Go()
{
Console.WriteLine("I am flying");
}
}
class
GoBySailing : GoAlgorithm
{
public
override
void Go()
{
Console.WriteLine("I am sailing");
}
}
Now, let the Vehicle class to have a property of GoAlgorithem
abstract
class
Vehicle
{
private
GoAlgorithm _algo;
public
GoAlgorithm Algo
{
get { return _algo; }
set { _algo = value; }
}
public
virtual
void Go()
{
_algo.Go();
}
}
And here's Helicopter and Ship class
class
Helicopter : Vehicle
{
public Helicopter()
{
Algo = new
GoByFlying();
}
}
class
Ship : Vehicle
{
public Ship()
{
Algo = new
GoBySailing();
}
}
See! We set the Algo property with our suitable algorithm at the constructor.
Then we execute the client code.
class
Strategy
{
static
void Main(string[] args)
{
Helicopter h = new
Helicopter();
h.Go();
Ship s = new
Ship();
s.Go();
Console.ReadKey();
}
}
We can see the different style / pattern of designing a class. I hope this article is useful for the reader.
Thank you!