Category Archives: Visual Basic .Net

Calling iSeries (AS/400) Commmand Using Visual Basic.NET

This blog post demonstrate on how to run iSeries Command using VB.Net. This is needed for example to change user ID password via an application.
Schedule Task also might be used it to perform some jobs that have conditional things or validation.

In order to run AS/400 Command via Visual Basic,you need a pre-requisite. You have to install IBM iSeries Client Access and make reference to IBM.Data.DB2.iSeries.dll in your application.
In my computer the library is located in C:\Program Files\IBM\Client Access folder.

Basically to call AS/400 Command, you will use ADO.Net objects and execute QSYS.QCMDEXEC syntax.
For example, if you have a program called MYPGM that you want to execute, you could send this SQL statement to the AS/400:
CALL QSYS.QCMDEXC('CALL PGM (MYLIB/MYPGM)', 0000000022.00000)
Note: The second parameter must contain exactly 15 digits: ten to the left of the decimal and five to the right.
You can write a program to count the length of the command and create the second parameter for you
Continue reading

Advertisements

Scheduling Excel Report With SSRS 2008 R2 And ASP.Net

As default SQL Server Reporting Service 2008 R2 has Scheduled Report Generation named Subscription. You can choose what report and when to generate it also delivery method.

However this feature is quite standard. We must supply report’s Parameter manually to Subscription. So that in this blog I will show you how to make more flexible to this kind of schedule using Visual Basic Asp.Net

The generated report will be on Excel format.

Here are step by step:

  1. Prepare the report you want to sent and deploy Report Server
  2. Browse the report via http://Your_serverip/ReportServer and get its exact Url for example http://Your_serverip/ReportServer/Pages/ReportViewer.aspx?%2fYour_Report_Name&rs:Command=Render
  3. Continue reading

Securing Your App – Making ‘Friend’ Class Available To Another Assembly With InternalsVisibleTo

Friend modifier makes a class and/or procedure only available within same assembly. It’s intended for securing your application so that your Friend class cannot be stealed by unsign assembly.

However, if we want to make your Friend class available to other assembly then we have to use InternalVisibleTo attribute.
InternalVisibleTo attribute sign another assembly so that Friend class could be acessed from that assembly.

I give you a simple example to do that.
Lets make library project FriendClassLib.vbproj. Project’s class source code:

Public Class FriendClass
    Friend Shared Sub SayHello()
        Console.WriteLine("Hello from FriendClass")
    End Sub

    Public Shared Sub PublicHello()
        Console.WriteLine("Public Hello")
    End Sub
End Class

The friend shared procedure, SayHello(), can’t be accessed by outside of this assembly as default. Here I want to make it can be accessed.

  1. Open AssemblyInfo.vb in FriendClassLib.vbproj
  2. Add this line <Assembly: InternalsVisibleTo(“FriendUsage”)>
    This intended to make all friend procedure available to FriendUsage.vbproj assembly

Create new FriendUsage.vbproj Console Project. Module source code:

Imports FriendClassLib
Module Module1

    Sub Main()
        FriendClass.SayHello()
        Console.ReadLine()
    End Sub

End Module

This console project has reference to FriendClassLib. With the use of InternalsVisibleTo attribute in FriendClassLib then the SayHello() can be run from other assembly, FriendUsage.

Regards,
Agung Gugiaji

Using VB.Net List Or Dictionary Class And Structure To Do Multidimensional Array

Multidimensional array sometimes hard to manage. One of its cause is array index must use number i.e integer in Visual Basic. Not like Php, We cannot put string as an array index in VB.
So sometimes to code with VB is not really easy that come said from Php programmer.

In fact to make code like Array easier we can use List or Dictionary Class With Structure as its value to compensate the multidimensional Array.
List is simpler than Dictionary Class but List has it weakness. So to do more complex task we should use Dictionary.

Lets see example of List in a first place. I want to make it like a multidimensional array. I have a list of email address info that has ID, Name, Email Address itself.
Also I have a structure to put those email information in one place.

Public Structure EmailInfo
        Public ID As Integer
        Public Name As String
        Public EmailAddress As String

        Public Sub New(ByVal _id As Integer, ByVal _name As String, ByVal _email As String)
            ID = _id
            Name = _name
            EmailAddress = _email
        End Sub
    End Structure

	 Sub Main()
		Dim emlinf As EmailInfo
        Dim list_emlinf As New List(Of EmailInfo)

        emlinf.ID = 1
        emlinf.Name = "Gugi"
        emlinf.EmailAddress = "g1@exmpl.com"

        list_emlinf.Add(emlinf)

        emlinf.ID = 2
        emlinf.Name = "Tulus"
        emlinf.EmailAddress = "g2@exmpl.com"

        list_emlinf.Add(emlinf)

        For Each ei As EmailInfo In list_emlinf
            Console.WriteLine("ID {0}, Name {1}, Email Address {2}", ei.ID, ei.Name, ei.EmailAddress)
        Next

        Dim ei2 As New EmailInfo(1, "Gugi", "g1@exmpl.com")

        Dim find As Boolean = list_emlinf.Contains(ei2)
        Console.WriteLine("Bool contains {0}", find)
		Console.Readline()
	end sub()

I have a Structure to store email address info and it has more than one member to make it multidimensional. The ID member type in this structure is integer. Off course, you can set it to string type if you like.
The signature of List class is List(of Value_type). As you can see above in Sub Main() procedure the ‘List’ Class Value type can be anything from basic type i.e string, integer etc to a Structure or Class type.
We can use For Each iterator or another to list of ‘List’ Class value.
Continue reading

SSRS 2008 R2 Programmatically List Running Reports/Jobs & Cancel Examples

SSRS 2008 R2 does not have pre built List Running Reports in Web format that comes from installation. Unlike the older version: SSRS 2005, we have to use SQL Server Management Studio (SSMS) to see SSRS 2008 R2 running reports / jobs .

The lack of this pre built web format is kind of missing in my day to day monitoring because of SSRS 2008 R2 List running jobs view in SSMS shows only the report name in the first look.
I have to look at each jobs properties to see more details which means more clicks are required.

The below screen shot shows the older version: SSRS 2005 list running Reports:

Steps to show SSRS 2008 R2 list running Reports with help of SSMS are:

  1. Open SSMS 2008 and connect to Reporting Service server.
    Set Server Type field to Reporting Service. Server Name set to Url http://<myserveripaddress>/reportserver. Replace the <myserveripaddress> to your report server ip address.
    Authentication, UserName and Passwd depend to your environment. If your localhost is Reporting Service server then you can use Window Auth.
    Otherwise if the server is a remote server then use basic/form auth. Anyway just try it.
  2. Open Jobs folder to see running Reports. Refresh it to see current jobs.
  3. Double click to see more details at each job
  4. Cancel Job(s) by right click job and click ‘Cancel Job(s)’

Alternatively, we can build our own list running reports/jobs tool. SSRS has a built in class to show running Reports programmatically. The class is ReportingService2010 for SSRS 2008 R2. This class is accessed via web service. The Url path of this webservice is http://<myserveripaddress>/ReportServer/ReportService2010.asmx.
In this blog post, I give you examples of programmatic SSRS 2008 R2 List running jobs.
Continue reading

Connect to IBM DB2 & Read Image Column Using VB.Net

IBM DB2 LUW or DB2 Linux Unix Windows is one of a recommended RDBMS software used by many organizations.
By its name this DB2 LUW is a platform independent because it can be run on Linux, Windows, Solaris, Mac OS.

IBM has free version of DB2 LUW which is DB2 Express-C. Unlike another RDBMS, DB2 Express-C does not have any time restrictions or database size limitations.
So this free version can be used in production environment.

Because of platform independent we can install DB2 server on Linux as database server to avoid virus infection. Since MS Windows is more widely used (at least in my country) then we also can use MS Windows to run client application (VB.Net) that connect to DB2.
I will show you example using VB.Net to work with DB2 and also to get image data.

DB2 .Net Data Provider

IBM provides .Net Data Provider for DB2. It is located on installation of DB2 folder like C:\Program Files\IBM\SQLLib\Bin\netf20\IBM.Data.DB2.dll. I added this library file to project’s reference.
I use DB2Connection class to establish connection to DB2. DB2DataAdapter to retrieve recordset and DB2Command to execute sql query.

Example

I use SAMPLE database and populate DataGridView with Employee data. If employee has a photo then it will be showed on picture box.
Please refer to this pic below:


Continue reading

Cancelling ADO.NET DataAdapter.Fill Method On The Fly By Throwing An Error

ADO.NET DataAdapter is a bridge between data source and DataSet. The implementation of DataAdapter can be SQLDataAdapter, OLEDBDataAdapter, iDB2DataAdapter etc. DataAdapter.fill method fills data source recordset to a DataSet or DataTable returned by a ‘select’ query.
After populating to a DataSet and DataTable, database connection can be closed but DataTable data still able to access. This is known as Disconnected ADO.Net operation.
We do not have to re-connect to database if we want to retrieve same data with previously DataAdapter.Fill work. We can access the DataTable’s data like an array.
Because of that performance of disconnected operation is better compared with connected one in some cases.

If the ‘select’ query has result of thousands or millions record DataAdapter.Fill method will populate them all thus long run might occur, DataAdapter.Fill looks like slow.
However there is no default method to cancel DataAdapter.Fill. So to terminate long Fill process we have to kill it manually and the application will close.
We need to know how to cancel DataAdapter.Fill on the fly for particular reason.

Continue reading