Binding Problems EF4 POCO with GridView: “Object Does not match target type”

I just figured out that an exception may arise when I tried to bind IEnumerable<Something> from EF4 to GridView using traditional data binding syntax (Set DataSource property, then call DataBind method).

The problem is simple: The GridView cannot bind inconsistent collection. See this post to get full explanation.

The symptoms may arise if following code flow was executed:

  1. Create a new object of entity using “new” keyword
  2. Add it to EF ObjectContext using AddObject method
  3. Call SaveChanges method at EF ObjectContext
  4. Get collection of entity and bind it to GridView
  5. The GridView will rise exception “Object Does not match target type”

If you examine the returned collection, you may see that the collection will consist of “dynamic proxy objects” and the newly added entity object. The “dynamic proxy objects” is default behavior of EF4 to generate a collection of POCO object.

The solution is simple, we can construct a new object to tell the GridView that we can have consistent collection:

Instead of writing this:
GridView1.DataSource = repository.GetAll<Product>();
GridView1.DataBind();


we can change those lines to be:

var query = from p in repository.GetAll<Product>();
GridView1.DataSoure = query;
GridView1.DataBind();

The GridView will happy with the new “anonymous” consistent collection.

Other solutions are possible, such as using Prototype pattern to create a new instance of consistent items in a collection, or using TemplateField instead of BoundField, but the point of this problem is: the GridView must accept consistent items in a collection.

Share this post: | | | |
Posted by paulus with no comments

Mapping IDataReader to Generic List

I know we’re living in ORM decade, when IDataReader (or they friends: SqlDataReader, DbDataReader, etc.) is handled by the ORM and exposes entity classes instead, but sometimes we still need to face the data reader folks and sometimes it’s so painful to map columns in data reader object to properties of an entity class.

For example:

CREATE TABLE Customer
(
  CustomerID int not null primary key
  CustomerName varchar(50)
)

and simply want to obtain all customers:

Database db = DatabaseFactory.CreateDatabase();
List<Customer> customers = null;
using(DbCommand cmd = db.GetSqlStringCommand("select CustomerID, CustomerName from Customer"))
using(IDataReader reader = db.ExecuteReader(cmd))
{                                                
    while(reader.Read())
{
// iterate the data reader here,
// create the entity object, and add it to the generic list
}
}

The code above uses Data Access Application Block library. Then, we have the class declaration for Customer entity:

public class Customer
{
   public int CustomerID { get; set; }
   public string CustomerName { get; set; }
}

and we have to write code like this:
while(reader.Read())
{
   Customer cust = new Customer(); 
   cust.CustomerID = Convert.ToInt32(reader["CustomerID"]);
   cust.CustomerName = Convert.ToString(reader["CustomerName"]);
   list.Add(cust);
}

Imagine if we have so many properties in the customer class, and we have to map it one-by-one manually from the data reader to entity class. It’s absolutely so painful!!

After half day researching, I found this library in codeplex.

The AutoMap library can automagically map the data reader columns into the properties of entity class, and we can simply write this code to do that:

list = AutoMapper.Mapper.DynamicMap<IDataReader, List<Customer>>(reader);


The complete codes can be written as:

Database db = DatabaseFactory.CreateDatabase();
List<Customer> customers = null;
using(DbCommand cmd = db.GetSqlStringCommand("select CustomerID, CustomerName from Customer"))
using(IDataReader reader = db.ExecuteReader(cmd))
{                                                
    list = AutoMapper.Mapper.DynamicMap<IDataReader, List<Customer>>(reader);
}

foreach(var customer in list)
{
// iterate the customer list here
}
Thanks to the creator of AutoMapper!
Share this post: | | | |
Posted by paulus with 1 comment(s)

Dependency Injection dengan Unity Application Block


Unity Application Block adalah salah satu Dependency Injection (DI) framework yang dapat digunakan dalam dunia pemrograman .NET yang ditawarkan oleh Microsoft Patterns & Practices. Sebenarnya Unity sudah ada sejak Enterprise Library 4.x dan masih terus dikembangkan sampai Enterprise Library 5.0.

Ide awal dari DI adalah menghilangkan “dependency” antara object dengan sebuah class. Misalkan code C# berikut ini:

IDbConnection connection = new SqlConnection();

dari code di atas dapat dikatakan bahwa objek connection mempunyai “dependency” dengan SqlConnection. Dalam contoh ini, code di atas ingin mengatakan bahwa aplikasi ingin mengakses database SQL Server melalui objek connection.

Apabila suatu saat kita ingin mengubah connection ke database lain, misalnya ke database Microsoft Access, tentunya kita harus mengubah code di atas menjadi:

IDbConnection connection = new OleDbConnection();

dan kita harus mengkompilasi ulang kembali code tersebut….whew!

Salah satu blog yang menjelaskan istilah Dependency Injection dengan bahasa yang sangat dimengerti adalah di blog ini.

Untuk menggunakan Unity, tambahkan reference ke tiga assembly berikut:

  1. Microsoft.Practices.Unity
  2. Microsoft.Practices.Unity.Configuration
  3. System.Configuration

Assembly ketiga diperlukan jika kita menggunakan assembly yang kedua, yaitu menggunakan Unity dengan konfigurasi file.

Misalkan kita mempunyai interface IDocument dengan definisi sebagai berikut:

public interface IDocument
{
   string Text { get; set; }
   void Save();
}

Dan class WordDocument yang mengimplementasikan IDocument:

    public class WordDocument : IDocument
    {
        private string text;
        public string FileName { get; set; }  // auto property

        public WordDocument(string initialText) // parameterized constructor
        {
            this.text = initialText;
        }

        public string Text
        {
            get
            {
                return text;
            }
            set
            {
                this.text = value;
            }
        }

        public void Save() // implemented method
        {
            Console.WriteLine("Saving word document");
        }        
    }

File konfigurasi untuk unity pada app.config adalah sebagai berikut:

<configSections>
<section name="unity" type="Microsoft.Practices.Unity.Configuration.UnityConfigurationSection, Microsoft.Practices.Unity.Configuration"/>
</configSections>

<unity xmlns="
http://schemas.microsoft.com/practices/2010/unity">
    <alias alias="IDocument"
           type="LearnUnity.IDocument, LearnUnity" />
    <container> 
      <register type="IDocument" 
               
mapTo="LearnUnity.WordDocument, LearnUnity">
        <constructor>
          <param name="initialText"
               
value="this is the initial text, we can change this anytime" />
        </constructor>
      </register>

    </container>
</unity>

Konfigurasi Unity di atas ingin mengatakan bahwa ketika objek yang mengimplement IDocument diresolve, container harus meng-inject parameter di constructor dengan value tertentu.

Langkah pertama adalah membuat Unity container:

IUnityContainer container = new UnityContainer();
container.LoadConfiguration();

Baris kedua di atas menginstruksikan Unity untuk membaca file konfigurasi di app.config

Lalu kita dapat meresolve objek berdasarkan konfigurasi yang sudah kita buat:

IDocument doc = container.Resolve<IDocument>();
Console.WriteLine(doc.Text);

Jika code di atas dijalankan, terlihat bahwa value yang kita sebutkan di konfigurasi sudah di-inject melalui constructor saat objek doc dicreate melalui Unity container.

Kesimpulan:
Dengan Dependency Injection, kita dapat menurunkan coupling antara objek dengan class, karena kita tidak langsung membuat instance dari sebuah class, melainkan membuat objek dari interface yang mana interface ini diimplementasikan oleh class tersebut. Dependency Injection ini berguna ketika kita ingin membuat unit test, dengan membuat mock object, yaitu sebuah objek yang semua behaviornya menyerupai behavior konkritnya. Contoh code dapat diunduh di attachment dari post ini.

Share this post: | | | |
Posted by paulus with no comments
Filed under: ,

Creating Office Document using UNO

One of my clients want to “migrate” their software infrastructure from Microsoft Office to OpenOffice. But, the main problem for them is they have used Office library in their applications. Yes, they have used “Excel.Application” and “Word.Application” ActiveX library to instantiate office documents. The term “documents” here means Word document and Excel spreadsheet.

The first solution across my mind is we have to “migrate” their ActiveX implementation to other library that can create Microsoft Office documents, for example: NPOI. But wait a minute, their "document”-generator is scripted in JavaScript! Geez……they can compose a document using JavaScript, it’s generated on-the-fly at client side, definitely not at server side!

After googling and googling, I found UNO. In simple term, UNO is a component object model for OpenOffice. UNO can be used in various programming language, such as Java, Python, C#, and of course, JavaScript. UNO objects can be instantiated using JavaScript through COM Automation. In JavaScript, we can use new ActiveXObject(name of UNO class) to instantiate UNO objects.

For example:

var svcMgr = new ActiveXObject("com.sun.star.ServiceManager");

Code above means, we create an object svcMgr as an instantiate of com.sun.star.ServiceManager class.

We can read a complete reference of UNO in the OpenOffice SDK, which can be downloaded from this link. In the SDK, we can also read a complete documentation of UNO (or we can read it at  this site), including code samples.

In this post, I show you how to create a simple OpenOffice Calc spreadsheet, and save it as example.xls (which we can open it using Microsoft Excel later). But, before we start to code, we have to download the OpenOffice (I used OpenOffice 3.3) and the OpenOffice SDK.

Now, take a look at this JavaScript code:

function generateSpreadsheet() { 
var svcMgr = new ActiveXObject("com.sun.star.ServiceManager");    
var coreRef = svcMgr.createInstance("com.sun.star.reflection.CoreReflection");
var desktop = svcMgr.createInstance("com.sun.star.frame.Desktop");
var args = [];
var doc = desktop.loadComponentFromURL("private:factory/scalc", "_blank", 0, args);

var sheets = doc.Sheets
var sheet = sheets.getByIndex(0);

// fill data
sheet.getCellRangeByName("B2").String="ItemCode";
sheet.getCellRangeByName("C2").String="Qty";
sheet.getCellRangeByName("D2").String="Amount";
sheet.getCellRangeByName("E2").String="Total";

sheet.getCellRangeByName("B3").String="A01";
sheet.getCellRangeByName("C3").Value=2 ;
sheet.getCellRangeByName("D3").Value=100000;
sheet.getCellRangeByName("E3").Formula="=C3*D3";

sheet.getCellRangeByName("B4").String="A02";
sheet.getCellRangeByName("C4").Value=5 ;
sheet.getCellRangeByName("D4").Value=120000;
sheet.getCellRangeByName("E4").Formula="=C4*D4";

sheet.getCellRangeByName("B5").String="A01";
sheet.getCellRangeByName("C5").Value=3;
sheet.getCellRangeByName("D5").Value=200000;
sheet.getCellRangeByName("E5").Formula="=C5*D5";
    
sheet.getCellRangeByName("E6").Formula="=SUM(D3:D5)";
sheet.getCellRangeByName("E6").setPropertyValue("CharWeight", 150);    
sheet.getCellRangeByName("E6").setPropertyValue("CharColor", 16711680);
sheet.getCellRangeByName("E6").HoriJustify = 2;

sheet.getCellRangeByName("B6:D6").merge(true);
var url = "file:///C:/example.xls";
var arg = new Array();
arg[0] = MakePropertyValue(svcMgr, "FilterName", "MS Excel 97");
try
{
    doc.storeToURL(url, arg);
}
catch(e)
{
    alert(e.toString());
}
doc.close(true);
doc = null;
coreRef = null;
svcMgr = null;
}

You can copy-paste code above to Notepad, and save it as a JavaScript file, such as createcalc.js

Then create a new HTML document like this one:

<html>
<head>
    <script src="createcalc.js" type="text/javascript" language="javascript"></script>
</head> <body> <form> <input type="button" value="print" onclick="generateSpreadsheet();" /> </form> </body> </html>

Save it as CreateCalc.htm, then open it using Internet Explorer (we can use IE 6 or a newest one). The result of the script is:

image

The hardest part to write the script above is that we don’t know the constant value of a constant parameter. Take a look at this line:

sheet.getCellRangeByName("E6").setPropertyValue("CharWeight", 150); 

What is 150? Actually it is a bold constant value, in UNO, it is declared in class com.sun.star.awt.FontWeight, and the class consists some constants about font weight. Let’s take a look at the SDK folder: C:\Program Files\OpenOffice.org 3\Basis\sdk\docs\common\ref\com\sun\star\awt\FontWeight.html

To get the real constant value, take a look at the IDL folder: C:\Program Files\OpenOffice.org 3\Basis\sdk\idl\com\sun\star\awt\FontWeight.idl, it is clearly written that for the “bold” font style, the constant value must be set to 150.

Since I have to start this project immediately, if you have any experience developing office documents using UNO, please drop me an email or just comment this post. Thank you.

Share this post: | | | |
Posted by paulus with no comments

Calling WCF from SQLCLR

Sometimes I have to work with legacy database system, such as FoxPro, and I need to obtain data from FoxPro using WCF since I don’t want to directly query to FoxPro database.

But the main problem of this approach is I have to call the WCF from SQL Server in order to obtain data. For example, the customer data resides in FoxPro, and I have to obtain the customer name. I created WCF method GetCustomerName and I can successfully called it from my .NET application, but how can I call the method from SQL Server stored procedure?

After some research, I found a great blog which describes clearly how to solve this problem and I will summarize it in this post.

In order to properly call the WCF method from SQL Server, we need to create a CLR Stored Procedure. CLR Stored Procedure basically is a .NET assembly deployed in SQL Server. All you have to do is create a static method and decorate it with SqlMethodAttribute or SqlFunctionAttribute.

For example, we have created a WCF, hosted in localhost which have address such as: http://localhost/MyService/Customer.svc

As you may know, we have to create a proxy class using svcutil.exe:

svcutil http://localhost/MyService/Customer.svc /out:CustomerServiceClient.cs

The svcutil will produce auto-generated code in CustomerServiceClient.cs

Then, we can add this file to our SQL CLR project in Visual Studio and we can write a static method to use the proxy class:

   1:          [SqlFunction(DataAccess=DataAccessKind.Read)]
   2:          public static string GetCustomerName(string custCode)
   3:          {
   4:              string address = GetEndPointAddress(http://localhost/MyService/Customer.svc);
   5:              EndpointAddress ep = new EndpointAddress(address);
   6:              CustomerServiceClient custSvc = new CustomerServiceClient(
   7:                  new BasicHttpBinding(),
   8:                  ep);
   9:   
  10:              string customerName = String.Empty;
  11:   
  12:              try
  13:              {
  14:                  customerName = custSvc.GetCustomerName(custCode.ToUpper());
  15:              }
  16:              catch
  17:              {
  18:              }
  19:   
  20:              return customerName;
  21:          }
.csharpcode, .csharpcode pre { font-size: small; color: black; font-family: consolas, "Courier New", courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; }

The second problem arises, the WCF URL is hard coded and we can’t deploy app.config in SQL CLR project. We have to query from database table if we don’t want to hard code the WCF URL. For now, let’s ignore this problem. Compile the SQL CLR project to produce an assembly (.dll).

Now, we have to deploy this assembly.

Before we can deploy the SQL CLR assembly, we have to deploy some .NET assemblies, they are:

  1. %SystemRoot%\Microsoft.NET\Framework\v3.0\Windows Communication Foundation\SMdiagnostics.dll
  2. %SystemRoot%\Microsoft.NET\Framework\v2.0.50727\System.Web.dll
  3. %SystemRoot%\Microsoft.NET\Framework\v2.0.50727\System.Messaging.dll
  4. %ProgramFiles%\Reference Assemblies\Microsoft\Framework\v3.0\System.ServiceModel.dll

Before deploying those, we have to make sure our database option for TRUSTWORTHY is enabled, just issue this command:

   1:  ALTER DATABASE [MyDatabaseName] SET TRUSTWORTHY ON;
.csharpcode, .csharpcode pre { font-size: small; color: black; font-family: consolas, "Courier New", courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; }

After that, we should able to deploy those .NET assemblies successfully, make sure we deploy it using UNSAFE permission set.

The next step is we have to deploy our SQL CLR assembly using UNSAFE permission set, just issue this command:

   1:  create assembly [AssemblyName]
   2:  from  'path_to_assembly'
   3:  with permission_set = unsafe
.csharpcode, .csharpcode pre { font-size: small; color: black; font-family: consolas, "Courier New", courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; } .csharpcode, .csharpcode pre { font-size: small; color: black; font-family: consolas, "Courier New", courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; }

Then we can create the T-SQL function as usual:

   1:  create function dbo.wcf_GetCustomerName(@CustCode nvarchar(20))
   2:  returns nvarchar(50)
   3:  external name MyAssembly.[MyCompany.Customer].GetCustomerName
.csharpcode, .csharpcode pre { font-size: small; color: black; font-family: consolas, "Courier New", courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; }

MyAssembly is the assembly filename.
MyCompany is C# the namespace
Customer is C# class name.
GetCustomerName is C# static method which returns string and ask single string parameter.

And then, we should able to call the WCF method using our T-SQL function as below:

select dbo.wcf_GetCustomerName('CA5290')
.csharpcode, .csharpcode pre { font-size: small; color: black; font-family: consolas, "Courier New", courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; }

An error may occur like this one:

A .NET Framework error occurred during execution of user-defined routine 
or aggregate <your T-SQL function>: System.Configuration.ConfigurationErrorsException: The type 'Microsoft.VisualStudio.Diagnostics.ServiceModelSink.Behavior, Microsoft.VisualStudio.Diagnostics.ServiceModelSink, Version=3.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' registered for extension 'Microsoft.VisualStudio.Diagnostics.ServiceModelSink.Behavior' could not be loaded. (C:\Windows\Microsoft.NET\Framework\v2.0.50727\Config\machine.config line 189)

Simply just disable the WCF debugging using this command in Visual Studio command prompt:

   1:  vsdiag_regwcf.exe -u
.csharpcode, .csharpcode pre { font-size: small; color: black; font-family: consolas, "Courier New", courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; }

re-issue the select statement like above, we suppose to able to call the WCF method successfully.

Share this post: | | | |
Posted by paulus with no comments
Filed under: , ,

Tricky Row by Row Processing

Seorang teman memberikan problem database seperti berikut ini:

Sebuah table dengan 1 field, berisi deretan angka sebagai berikut:

image

Lalu dia ingin mendapatkan hasil:

6 – 5 = 1
8 – 6 = 2
9 – 8 = 1
11 – 9 = 2

Sepintas problem ini hanya bisa dipecahkan dengan cursor, dengan menelusuri setiap baris pada table tersebut. Namun teman saya itu hanya meminta problem disolved hanya dengan satu query saja.

Saya mengajukan solusi menggunakan Common Table Expression:

;with temp as
(
    select row_number() over (order by angka) rownum,
            angka
    from #temp       
)
select (select angka
                from temp
                where temp.rownum = tmp.rownum + 1) – angka
from temp tmp

Dan hasilnya sebagai berikut:

image

Yup, hanya dengan 1 (satu) query saja.

Share this post: | | | |
Posted by paulus with 3 comment(s)
Filed under:

Trying VSTO, and….I love it

After poisoned by Andri Yadi in dotnet mailing list, I’m very curious to try VSTO. He said that VSTO can make old-veteran users happy because they can still work with their Microsoft Office documents and we can write busines logic “behind” it.

Then, I create a little program using VSTO. This program simply shows mapping information between items in a software (called Aphelion), and items in SAP. Actually, this is a real case, my work-mate in Finance department always work with this information when he wants to map an item in Aphelion software to an item in SAP, since an item in SAP can be breaked down to several items in Aphelion.

This is the excel worksheet, he used to work with.

image

Then I write some codes….

Creating a new tab in ribbon, contains only a button :

image

If “Select Club” clicked, it will show this form:

image

We can select a branch office from the combo box. Then, when “Select” button clicked. It will call a stored procedure in database and a complete list will be rendered in a worksheet:

image

var mappings = new List<ItemMappingInfo>();

Database db = DatabaseFactory.CreateDatabase("POS");
using (DbCommand cmd = db.GetStoredProcCommand("proc_GetAllMapItems"))
{
  db.AddInParameter(cmd, "ClubNumber", DbType.Int32, (comboBox1.SelectedItem as Club).ID);
  using (IDataReader reader = db.ExecuteReader(cmd))
  {
     while (reader.Read())
     {
       var item = new ItemMappingInfo()
       {
         ItemBarcodeAphelion = reader["ItemBarcodeAphelion"].ToString(),
         ItemNameAphelion = reader["ItemDescriptionAphelion"].ToString(),
         ItemBarcodeSAP = reader["ItemBarcodeSAP"].ToString(),
         ItemNameSAP = reader["ItemDescriptionSAP"].ToString(),
         ItemType = Convert.ToChar(reader["ItemType"])
       };
       mappings.Add(item);
     }
  }
}

In my custom tab ribbon, I write this event click handler for the button :

using (Form1 form = new Form1())
{
    form.ShowDialog();
    Sheet1 mainSheet = MyExcelWorkbook1.Globals.Sheet1;
    mainSheet.list1.DataSource = form.ItemMappings;
}

list1 is a ListObject control. I dragged it from VS toolbox. I can simply binding it to generic List<T> I created before.

image

Yeah, this OBA (Office Business Application) is very simple, but I’ve got the idea how to create OBA to fulfill my boss needs. Now I believe what Andri Yadi said about VSTO, thanks sir!

Share this post: | | | |
Posted by paulus with 2 comment(s)
Filed under: , ,

Menggunakan DynamicObject

Seperti halnya menggunakan class ExpandoObject, melalui class DynamicObject kita dapat mengubah behavior dari sebuah objek secara runtime, serta melakukan override default behavior dari object tersebut, misalnya overriding terhadap method, property, atau type conversion.

Pada contoh berikut, kita akan mengubah behavior dari sebuah class DataRow sehingga pembuatan DataColumn dalam DataRow tersebut dapat dilakukan secara runtime.

Misalkan pada contoh code berikut:

DataTable table = new DataTable();
DataRow row = table.NewRow();
row[“Name”] = “Paul”;

Jika code di atas dijalankan maka akan terjadi exception dengan pesan “Column ‘Name’ doest belong to table”.

Berikut ini kita akan membuat sebuah class baru yang behaviornya menyerupai class DataRow, namun di-inherit dari DynamicObject.

using System;
using System.Data;
using System.Dynamic;

public class DynamicDataRow : DynamicObject
{
  private DataRow dataRow = null;
  private DataTable dataTable = null;

  public DynamicDataRow(DataTable dataTable)
  {
    dataRow = dataTable.NewRow();
    this.dataTable = dataTable;
  }

  public DataRow GetDataRow()
  {
    return dataRow;
  }

  public override bool TryGetMember(
        GetMemberBinder binder,
        out object result)
  {
    if (dataRow.Table.Columns.Contains(binder.Name))
    {
      result = dataRow[binder.Name];
      return true;
    }

    result = null;
    return false;
  }

  public override bool TrySetMember(
        System.Dynamic.SetMemberBinder binder,
        object value)
  {
    if (!dataRow.Table.Columns.Contains(binder.Name))
    {
      dataRow.Table.Columns.Add(binder.Name, value.GetType());
    }
    dataRow[binder.Name] = value;

    if (dataTable.Rows.IndexOf(dataRow) < 0)
    {
       dataTable.Rows.Add(dataRow);
    }

    return true;
  }
}

dengan demikian kita bisa menggunakan class di atas seperti berikut ini

  DataTable table = new DataTable();

      dynamic row1 = new DynamicDataRow(table);
      row1.Name = "Paul";
      row1.City = "Jakarta";
      row1.Age = 30;
      row1.Active = true;

      dynamic row2 = new DynamicDataRow(table);
      row2.Name = "John";
      row2.City = "London";
      row2.Age = 50;

      dynamic row3 = new DynamicDataRow(table);
      row3.Name = "Jack";
      row3.City = "Jakarta";
      row3.Age = 28;

Perhatikan pada code di atas, sebuah DataRow dapat ditambahkan ke dalam object table (instance dari DataTable), tanpa melalui method table.Rows.Add(), karena pemanggilan method tersebut sudah kita lakukan dalam method TrySetMember.

Namun, jika kita ingin mengakses sebuah property di class DataRow, masih tidak dapat dilakukan, misalnya property ItemArray dari class DataRow. Pada method TryGetMember harus dilakukan pengubahan sebagai berikut:

        public override bool TryGetMember(
         System.Dynamic.GetMemberBinder binder, 
         out object result)
    {
       if (dataRow.Table.Columns.Contains(binder.Name))
       {
          result = dataRow[binder.Name];
          return true;
       }
       else
       {
          Type dataRowType = dataRow.GetType();
          result = dataRowType.InvokeMember(
                     binder.Name,
                     BindingFlags.GetProperty |
                     BindingFlags.Public |
                     BindingFlags.Instance,
                     null, dataRow, null);
                return true;
            }
         
result = null;
         
return false;
        }
     }

Jangan lupa untuk menambahkan

           using System.Reflection;

Untuk lebih jelasnya, dapat mendownload attachment dari postingan ini.

Share this post: | | | |
Posted by paulus with no comments
Filed under: ,

var VS dynamic

iseng-iseng saya membuat sebuah program kecil untuk membandingkan performance code C# menggunakan keyword var dan dynamic.

Berikut code yang saya gunakan:

  static void Main(string[] args)
  {
      for (var testValue = 1000000; 
              
testValue <= 20000000;
               testValue += 1000000)
      {
         
Console.WriteLine("Test by " + testValue);
          var testVar = TestUsingVar(testValue);
          Console.WriteLine(testVar);
          var testDyn = TestUsingDynamic(testValue);
          Console.WriteLine(testDyn);

               Console.WriteLine();
           }

        }

        public static long TestUsingVar(long x)
        {
            Stopwatch sw = new Stopwatch();
            sw.Start();
            for (var i = 1; i <= x; i++)
            {
                EmptyMethod();
            }
            return sw.ElapsedMilliseconds;
        }

        public static long TestUsingDynamic(dynamic x)
        {
            Stopwatch sw = new Stopwatch();
            sw.Start();
            for (dynamic i = 1; i <= x; i++)
            {
                EmptyMethod();
            }

            return sw.ElapsedMilliseconds;
        }

        private static void EmptyMethod()
        {           
        }

Berikut adalah contoh hasilnya:

image

Jika hasil di atas digambarkan dalam bentuk grafik:

image image

Interesting huh?

So, the moral of this experiment is: do not overuse dynamic variable and do not use it in long iterations. Stick out tongue

Share this post: | | | |
Posted by paulus with 4 comment(s)
Filed under: ,

Menggunakan ExpandoObject

Salah satu fitur menarik di .NET 4.0 adalah ExpandoObject. Dengan class ini, memungkinkan kita mengubah behavior dari sebuah objek dynamic secara runtime.

Misalkan kita akan membuat aplikasi sederhana dengan design form seperti ini:

image

  • Button “Car Info” untuk menampilkan informasi detail objek mobil.
  • Button “Run” untuk mulai menjalankan objek mobil.
  • Button “Previous Gear” untuk memindahkan posisi gear ke posisi sebelumnya
  • Button “Next Gear” untuk memindahkan posisi gear ke posisi berikutnya
  • Button “Stop” untuk menghentikan objek mobil.

Saat di-jalankan akan seperti berikut ini:

image

Pertama-tama, kita harus mendeklarasikan sebuah variable bertipe dynamic:

dynamic myCar;

Lalu objek tersebut harus dicreate dengan tipe ExpandoObject saat form di-load:

myCar = new System.Dynamic.ExpandoObject();

Lalu property objek tersebut dapat dibuat secara runtime, misalnya seperti ini:

myCar.Color = "Red";
myCar.Type = "Hatchback";
myCar.Doors = 4;
myCar.CurrentGear = 0;

Ini mengingatkan kita seperti membuat property pada sebuah objek di javascript.

Kita juga dapat membuat event secara run-time seperti ini:

myCar.GearsChanging = null;
myCar.GearsChanging += new EventHandler(
    delegate(object o, EventArgs args)
    {
        listBox1.Items.Add(
"Current gear: " + myCar.CurrentGear);
    });

Hmm……cukup menarik kan?

Berikutnya, kita dapat menambahkan method secara run-time, method ini dipanggil oleh masing-masing button dalam form tersebut.

myCar.GearsDown = (Action)(() => 
   { 
      myCar.CurrentGear—; 
      myCar.GearsChanging(myCar, new EventArgs()); 
   });

myCar.GearsUp = (Action)(() => 
   { 
      myCar.CurrentGear++; 
      myCar.GearsChanging(myCar, new EventArgs()); 
   });

myCar.Run = (Action)(() =>
   {
      myCar.CurrentGear = 1;
      listBox1.Items.Add("Now is running at gear: " + myCar.CurrentGear);
   });

myCar.Stop = (Action)(() =>
   {
      myCar.CurrentGear = 0;
      listBox1.Items.Add("Now is running at gear: " + myCar.CurrentGear + ". It's stopped.");
   });

Kita juga dapat melakukan method overriding secara run-time, kita dapat memanggil method ToString() yang telah di-override ini melalui button “Car Info”.

myCar.ToString = (Func<dynamic>)(() =>
    {
        return "Color: " + myCar.Color + "\n" +
               "Type: " + myCar.Type + "\n" +
               "Doors: " + myCar.Doors;
    });

Share this post: | | | |
Posted by paulus with 4 comment(s)
Filed under: ,

Cannot install SQL Server Express 2005 SP2 / SP3

I had a strange phenomenon while installing SQL Server 2005 Express SP2 at Windows XP with Service Pack 3 installed. The installation process stopped at installing MSXML 6 and the Database Service can't be installed. Thus, the installer said that the installation failed.

I thought this one may caused by the SP2, so I tried to install SQL Server 2005 Express SP3, but the installation also failed. Strangely, if I install either SQL Server 2005 Express SP2 or SP3 at Windows XP Service Pack 2, the installation can be completed successfully. The problem only occurred at Windows XP SP3.

I tried to search at Google, the first aid for my problem :D then I found that the problem was caused by the MSXML 6 which was installed by Windows XP SP3 package. And the most frustrating thing was: I can't remove MSXML 6 using Add/Remove Programs....GREAT!!!

To solve this problem, I had to download Windows Installer Clean Up from Microsoft website, remove MSXML 6 and then re-run setup for either SQL Server 2005 Express SP2 or SP3, the installer ran successfully and the Database Service can be installed...SOLVED!!!

I also read that this problem also found at SQL Server 2008 Express, can anyone confirm this?

Share this post: | | | |
Posted by paulus with 1 comment(s)
Filed under: ,

jQuery

Malam minggu ini saya mencoba sebuah framework di lingkungan client scripting yaitu jQuery. Tadinya saya menebak jQuery adalah framework atau tool di lingkungan Java karena ada embel-embel huruf J di depannya (walaupun bukan huruf J kapital), seperti JUnit, JPedal, JRoller, dan sebagainya. Ternyata jQuery adalah framework yang dapat digunakan di JavaScript. Dilihat dari fungsi teknisnya, jQuery mirip dengan ASP.NET AJAX Client Library yang dapat digunakan bukan hanya untuk membangun aplikasi dengan ASP.NET, yaitu memperluas fungsionalitas JavaScript sehingga developer dapat membangun code JavaScript dengan relatif lebih cepat karena code JavaScript yang perlu dituliskan lebih sedikit dan lebih memfokuskan pada fungsi apa yang ingin dilakukan oleh code JavaScript tersebut.

jQuery didistribusikan secara gratis dengan lisensi MIT dan GPL dan dapat di download di http://www.jquery.com

Sebenarnya jQuery hanyalah sebuah file JavaScript yang harus disertakan dalam sebuah halaman HTML (atau XHTML) melalui tag script seperti berikut :

<script language="JavaScript" type="text/javascript" src="path/to/jquery.js"></script>

Setelah itu kemudian saya bisa menggunakan semua kekuatan dari jQuery. Misalkan saya ingin membuat alert yang bertuliskan "Hello jQuery" menggunakan HTML button:

Code HTML:

<input type="button" id="btnHello" value="Hello jQuery" />


Code jQuery:

  $(function() {
    $("input#btnHello").click( function() { alert('Hello jQuery'); });
  });

 

Penggunaan tanda $ di sini sangat mengingatkan saya dengan ASP.NET AJAX Client Library, walaupun dengan jQuery saya bisa mengganti $ dengan token lain, misalnya dengan $jquery.

Contoh yang lebih rumit: Saya ingin membuat sebuah efek tulisan fade in dan fade out secara bergantian.

Misalnya untuk code HTML berikut ini:        

<h5>YOU'LL NEVER WALK ALONE</h5>
       
<p id="verse">
   When you walk through a storm,<br/>
   Hold your head up high,<br/>
   And don't be afraid of the dark.<br/>
   At the end of a storm,<br/>
   There's a golden sky,<br/>
   And the sweet silver song of a lark.<br/>
   Walk on through the wind, Walk on through the rain,<br/>
   Though your dreams be tossed and blown..
</p>
       
<a href="#" id="linkChorus">Hide Chorus</a>
<p id="chorus">
   Walk on, walk on, with hope in your heart,<br/>
   And you'll never walk alone.......<br/>
   You'll never walk alone. <br/><br/>
   Walk on, walk on, with hope in your heart,<br/>
   And you'll never walk alone.......<br/>
   You'll never walk alone.
</p>

Code jQuery:        

$(function() {
   $("a#linkChorus").click( function() {            
       if($("a#linkChorus").get(0).innerText == "Hide Chorus")
       {                   
          $("#chorus").hide("slow");
          $("a#linkChorus")[0].innerText = "Show Chorus";
       }
       else if($("a#linkChorus")[0].innerText == "Show Chorus")
       {                   
          $("#chorus").show("slow");
          $("a#linkChorus")[0].innerText = "Hide Chorus";                   
       }
   } );
});

Di sinilah kelebihan jQuery. Saya tidak perlu mendeteksi browser apa yang digunakan, karena framework jQuery yang nantinya akan berusaha mengenali browser dan menyesuaikan code JavaScript yang digunakan sesuai dengan fungsi yang saya inginkan. Code yang saya tulispun jauh lebih singkat daripada saya harus menuliskan code JavaScript secara keseluruhan.

Dan yang terpenting: jQuery menyediakan akses ke DOM yang lebih baik yaitu memungkinkan developer memfokuskan diri pada fungsionalitas yang ingin dibangun, persis seperti kalimat yang dituliskan di website jQuery: jQuery is designed to change the way that you write JavaScript.

jQuery: The Write Less, Do More, JavaScript Library

Share this post: | | | |
Posted by paulus with 4 comment(s)
Filed under:

Failed to install .NET Framework 2.0

Tadi siang asisten bos laptopnya minta diinstall .NET Framework 2.0 (hanya runtime bukan SDK) karena ada aplikasi yang menggunakan .NET Framework. Namun entah kenapa selalu saja failed saat installer baru mulai dan muncul pesan:

"Error opening installation log file. Verify that the specified log file location exists and is writable."

Hmmm.....mungkin hanya perlu restart Windows, tapi setelah restart pun hasilnya sama.

Coba install .NET Framework 3.5, gagal juga saat installer baru mulai.

Coba tanya uncle Google, ternyata ada beberapa kemungkinan:

  1. Space hard disk tinggal sedikit: Ini ga mungkin, free space hard disk masih di kisaran 70 GB.
  2. Temporary directory harus dibersihkan: Semua file di temporary sudah dihapus, tapi pesan error di atas tetap muncul.

Udah hampir nyerah, tapi masih ada waktu, si pemilik laptop mau meeting pukul 2 siang, sekarang masih pukul 13.30.

Saya coba masuk ke Command Prompt, lalu semua isi environment ditampilkan dengan perintah set, ternyata ada keanehan. Value dari variabel TEMP dan TMP isinya aneh.

TEMP=%USERPROFILE%\User\LOCALS~1\Temp
TMP=%USERPROFILE%\User\LOCALS~1\Temp

Lalu saya coba lakukan hal yang sama di komputer saya, hasilnya:

TEMP=C:\DOCUME~1\Paul\LOCALS~1\Temp
TMP=C:\DOCUME~1\Paul\LOCALS~1\Temp

Tampak variabel %USERPROFILE% diterjemahkan menjadi value variabel. Tapi yang di laptop ini kenapa variabel %USERPROFILE% tidak diterjemahkan menjadi value variabelnya? Padahal beberapa baris di bawahnya saya lihat variabel %USERPROFILE% juga ada:

USERPROFILE=C:\Documents and Settings\User

Wah, kayaknya ga sempat lagi untuk mencari tahu kenapa ini bisa terjadi, saya coba hapus variabel TMP dan TEMP dari environment di System Properties - Advanced - User Variables, lalu coba install lagi .NET Framework 2.0. Ternyata, pesan error tidak muncul lagi, instalasi berhasil. Variabel TMP dan TEMP saya kembalikan ke kondisi semula dan laptop harus segera dikembalikan ke pemiliknya karena sudah pukul 13.50.

Whew....Koq bisa begitu ya?

Share this post: | | | |
Posted by paulus with 2 comment(s)
Filed under: ,

Silverlight dan Nokia

Nokia akan mengimplementasikan Silverlight pada ponselnya. Baca selengkapnya di sini.

Share this post: | | | |
Posted by paulus with no comments
Filed under:

Embedded web resource

Ini post saya yang pertama membahas tentang .NET, mohon maaf jika ada kesalahan (maklum langsung code di sini, ga pakai Visual Studio).

Ketika membuat aplikasi ASP.NET kadang saya membuat project Web Control Library yang isinya user controls yang tidak ingin saya expose code nya ke developer lain. User control tersebut membutuhkan code javascript yang ada pada file terpisah, atau file-file image. Jika user control ini digunakan dalam web form yang ada pada project terpisah atau pada solution yang lain, file-file javascript atau image tersebut harus diikutsertakan juga. Hal ini sepertinya agak merepotkan (setidaknya untuk saya).

Supaya yang dideploy hanya single file assembly, file javascript dan image tersebut dapat di-embed sebagai embedded resource ke dalam assembly.  Caranya adalah:

Untuk setiap file yang akan di-embed sebagai embedded resource, di Visual Studio ubah property Build Action dari file tersebut menjadi Embedded Resource.

Kemudian daftarkan file tersebut pada AssemblyInfo.cs supaya HTTP handler dapat mengaksesnya melalui WebResource.axd, dengan syntax:

[assembly:WebResource("namespace.filename", "content-type")]

Misalnya untuk namespace MyWebControl.Validator dan nama file javascript yang akan di-embed adalah LengthValidator.js, maka penulisannya menjadi:

[assembly:WebResource("MyWebControl.Validator.LengthValidator.js", "text/javascript")]

Sampai di sini, jika project web control library di-build akan menghasilkan satu file DLL yang di dalamnya sudah di-embed file javascript yang disebutkan tadi. Misalkan nama file DLL yang dihasilkan adalah LengthValidator.dll

Untuk meng-consume resource, dilakukan dengan cara seperti halnya meng-inject sebuah file javascript ke dalam web form dengan memanggil method Page.ClientScript.RegisterClientScriptInclude:

Page.ClientScript.RegisterClientScriptInclude(
     "LengthValidator",
     Page.ClientScript.GetWebResourceUrl(typeof(LengthValidator),
                                         "MyWebControl.Validator.LengthValidator.js"));

Kalau diperhatikan di atas, perbedaan antara meng-inject sebuah file javascript biasa dengan file javascript sebagai embedded resource adalah pada parameter kedua dari method RegisterClientScriptInclude. Biasanya untuk meng-inject sebuah file javascript, parameter ini diisi dengan nama file javascript, tetapi karena yang di-inject adalah embedded resource, maka gunakan method GetWebResourceUrl.

 

Jika file javascript yang akan di-embed berada dalam folder lain (bukan folder root project), sebutkan nama folder tersebut ketika file tersebut didaftarkan di AssemblyInfo.cs. Misalkan file javascript LengthValidator.js berada dalam folder bernama scripts:

[assembly: WebResource("LengthValidator.scripts.LengthValidator.js", "text/javascript")] 

Serupa dengan di atas, untuk mengambil javascript yang sudah di-embed tersebut dengan menyebutkan nama folder tadi:

Page.ClientScript.RegisterClientScriptInclude(
                "LengthValidator",
                Page.ClientScript.GetWebResourceUrl(this.GetType(),
                        "LengthValidator.scripts.LengthValidator.js"));


Share this post: | | | |
Posted by paulus with no comments
Filed under:
More Posts Next page »