In K2 workflow, we can create PDF file using PDF SmartObject, located in System node:

image

PDF Converter is a built-in SmartObject in K2 to create PDF and save it in PDF File SmartOject. The PDF is saved as BLOB. Actually it is a column contains XML and in the XML contains PDF as binary content.

The problem is, I have to extract the PDF and copy it to the file server using Windows File Sharing. K2 does not have built-in feature to extract PDF to physical file, thus I have to create it.

The following code utilizes SmartObject Client API to communicate with PDF File SmartObject in K2 Blackpearl server.

   1:  public void ExtractPDFWithSmartObject(int snapshotID, string fileName)
   2:  {
   3:      var smartObjServer = new SmartObjectClientServer();
   4:   
   5:      using (var connection = smartObjServer.CreateConnection())
   6:      {
   7:          connection.Open(k2WorkflowServer);
   8:          //smartObjServer.Connection.Open();
   9:   
  10:          var pdfSmartObj = smartObjServer.GetSmartObject("PDFFile");
  11:          pdfSmartObj.MethodToExecute = "Load";
  12:          pdfSmartObj.Properties["ID"].Value = snapshotID.ToString();
  13:   
  14:          var result = smartObjServer.ExecuteScalar(pdfSmartObj);
  15:          var file = (SmartFileProperty)pdfSmartObj.Properties["PDF"];
  16:          byte[] data = Convert.FromBase64String(file.Content);
  17:          File.WriteAllBytes(fileName, data);
  18:      }
  19:  }
.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; }

Add reference to SourceCode.HostClientAPI.dll and SourceCode.SmartObjects.Client.dll

Parameter snapshotID is an ID created by Create* method of PDF Converter and parameter fileName is a physical file name to be created in file system.

In line 7, we have to create string variable named k2WorkflowServer, this variable will be populated as connection string to K2 Blackpearl server.

The complete code can be downloaded from here, it is a .NET DLL file and can be referenced from K2 workflow.

 

Call this DLL from K2 Workflow

To use this DLL (PDFHelper.dll), open your K2 workflow (.kprx) using K2 Designer for Visual Studio, add Code Reference Event and add reference to PDFHelper.dll

In Code Reference Event, add call constructor and pass the K2 Connection String as parameter.
image

then call instance method ExtractPDFWithSmartObject and pass snapshot ID and filename as parametersOpen-mouthed smile:

image


Do I have to use Visual Studio to use this DLL in my K2 workflow?

If I use K2 Studio to add reference to this workflow, strangely I cannot call constructor and call instance method, it seems K2 Studio cannot load PDFHelper.dll. But if I use Visual Studio, I can add reference, call constructor, and call instance method smoothly.

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

I just noticed that in Windows 8.1 there’s different behavior between shutting down using Charm Bar and Power User menu.

As you know, Power User menu can be opened using Windows + X keystroke. There’s Shut Down option in “Shut Down or Sign Out” menu.

image

If I shut down using Charm Bar, Windows 8.1 will shut down using hybrid-mode. This behavior is also default shut down behavior in Windows 8.

But if I shut down using Power User menu, Windows 8.1 will perform “full shut down”, it acts like prior Windows 7 shut down behavior.

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

Starting from today, we can convert our “old-style” Hotmail user interface to brand new “Metro”-like user interface of Outlook. At right corner of your Hotmail inbox, you can choose “Upgrade to Outlook.com”

image

and here it is…

image

The look and feel of user interface is similar to Metro application in Windows 8.

 

Reading a mail in full width:

image

 

Compose a new mail, we can type the recipient name and their photos can appear

image

 

We can change the color theme by selecting a new color:

image

 

Come on, try it Smile

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

Setelah install SQL Server 2012, lalu buka SQL Server Management Studio, ada hal menarik yang saya temukan setelah saya membuat database baru, yaitu FileTables.

image

Karena tertarik dengan fitur baru ini, saya coba mencari tau apa itu FileTables di SQL Server 2012. Dengan FileTables, kita dapat membuat sebuah table yang berisikan banyak file dan file-file tersebut dapat diakses dengan standard API Windows (seperti copy file dan delete file). Untuk dapat menggunakan fitur ini, kita harus mengaktifkan dulu fitur FILESTREAM di instance SQL Server 2012 yang akan digunakan:

  1. Masuk ke SQL Server Configuration Manager, pilih SQL Server Services
  2. Pilih instance database engine yang ada, lalu klik kanan pilih Properties.
  3. Pilih tab FILESTREAM, dan pastikan fitur FILESTREAM diaktifkan.

image

 

Setelah itu buat sebuah database baru:

   1: CREATE DATABASE DocumentManagement

 

Kemudian ubah akses level FILESTREAM ke FULL:

   1: EXEC SP_CONFIGURE filestream_access_level, 2
   2: RECONFIGURE WITH OVERRIDE 

 

Tambahkan filegroup dan data file untuk FILESTREAM:

   1: ALTER DATABASE [DocumentManagement] ADD FILEGROUP [FILESTREAM01] CONTAINS FILESTREAM 
   2: go
   3: ALTER DATABASE [DocumentManagement] ADD FILE ( NAME = N'DocumentFileStream', FILENAME = N'D:\DB\MSSQL11.SQLEXPRESS\MSSQL\DATA\DocumentFileStream' ) TO FILEGROUP [FILESTREAM01]
   4: GO
   5: ALTER DATABASE [DocumentManagement] SET FILESTREAM( NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'Invoices' )
   6: GO

 

Lalu buat sebuah FileTable di database yang baru saja kita konfigurasi:

   1: USE DocumentManagement
   2: GO
   3: CREATE TABLE PaidInvoices AS FILETABLE 
   4: GO

 

Sebuah FileTable baru sudah ada di database:

image

 

Untuk mengakses tabel ini dari aplikasi .NET, misalnya dengan code C#, kita dapat dengan mudah menggunakan method System.IO.File.Copy seperti berikut ini:

   1: System.IO.File.Copy(@"d:\docs\contoh invoice.docx", @\\Paulus-pc\sqlexpress\Invoices\PaidInvoices\invoice.docx);

 

Untuk melihat hasilnya, klik kanan pada table FileTable tersebut, lalu pilih Explore FileTable Directory:

image

image

Kita dapat melihat isi table FileTable ini seakan-akan seperti sharing folder di Windows.

 

Lalu coba lihat isi table FileTable ini:

   1: select * from PaidInvoices
image

 

Kita juga dapat memasukkan data ke FileTable ini dengan cara Copy-Paste seperti halnya kita menyalin file di Windows.

Atau kita juga dapat menggunakan T-SQL:

   1: INSERT INTO [dbo].[PaidInvoices]
   2: ([name],[file_stream])
   3: SELECT
   4: 'Invoice 2.docx', * FROM OPENROWSET(BULK N'd:\docs\Contoh2.docx', SINGLE_BLOB) AS FileData

image

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

Actually, Oracle Database 10g (64-bit version) cannot be installed on either Windows 7 x64 or Windows Server 2008R2, but we can “hack” the installer configuration to allow this installation scenario. However, Oracle said they didn’t recommend this “hacking” story. They said we have to use Oracle Database 11g instead. But, one of my big customer still use Oracle Database 10g, they don’t have license for 11g, and I have to install 10g in my development machine.

  1. First of all, copy the installation files to hard disk, let’s say we copy those files to c:\oracle10g
  2. Open folder install, open oraparam.ini using notepad, go to line 32, we will find this line:

    #Windows=5.0,5.1,5.2

    Change that line to:

    #Windows=5.0,5.1,5.2,6.0.6.1

    As you may know Windows 5.1 is well known as Windows XP, 5.2 is Windows Server 2003, 6.0 is Windows Vista/Windows Server 2008, and 6.1 is Windows 7/Windows Server 2008R2.
  3. Save oraparam.ini
  4. Open folder stage\prereq\db, open refhost.xml, at line 30, add this line:

    <OPERATING_SYSTEM>
          <VERSION VALUE="6.1"/>
    </OPERATING_SYSTEM>

  5. Save refhost.xml
  6. Back to the root folder, run setup.exe, Oracle Universal Installer should said that your operating system is passed the requirement.
  7. Install the Oracle Database as usual

While installing, we will be notified that Database Control (Enterprise Manager) service cannot be started. Don’t worry, we do not need this in our development machine. We still can use command line based, sqlplus, to control the database.

OK, so far so good, let’s try to build the database application using Visual Studio.

In my experiment, I used Visual Studio 2008, I tried to create simple ASP.NET 3.5 Web Site, and add reference to Microsoft Oracle Client library. This .NET library is bundled with .NET Framework (Version 2.0 or 4.0), you can find System.Data.OracleClient in GAC. Write this code:

var conn = new System.Data.OracleClient.OracleConnection();
conn.ConnectionString = "user id=hr;password=hr;data source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521))(CONNECT_DATA=(SID=ORCL)))";
conn.Open();

As you see, we have to use “long version” of data source value. It seems we can’t use only TNS name.

Then, I ran the web site…..and BOOM!!! ASP.NET threw an exception with message that Oracle Client 8.1.3 or higher is required.

It seems .NET Framework Oracle Client cannot use Oracle Database 10g x64 library we already installed.

Then….I tried to download and install Oracle Client 10g, the installer will add it’s bin folder to PATH environment variable. We have to move this folder to the last order of the PATH variable:

  1. Open up System Properties from Control Panel, go to Advanced tab, open Environment Variables window.
  2. At System Variables, find PATH variable, move Oracle Client’s bin folder to the last order of the PATH values.
  3. Click OK to save changes.
  4. Restart Windows to ensure our changes would be applied.

Re-open and re-run our ASP.NET Web Site code, the error message we saw before should be vanished and the connection object can be opened successfully.

You may have question, why do I not use ODP.NET instead?

Yes, I have tried it also. I have tried to use ODP.NET DLL, Oracle.DataAccess.dll (64 bit version), but I still can’t connect to the database from my .NET code. If you have any suggestion how to use ODP.NET 10g in .NET application, please let me know.

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

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

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 | 1 comment(s)


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: ,

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

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: , ,

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 | 3 comment(s)
Filed under:

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 | 2 comment(s)
Filed under: , ,

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: ,

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 | 4 comment(s)
Filed under: ,

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 | 4 comment(s)
Filed under: ,
More Posts Next page »