Sunday, February 21, 2010

.NET: VB6 to VB.NET Migration TIPS

  1. First thing you must do is to Convert earlier version of VB code to VB6 .
  2. Add "Option Explicit" to all VB code behind forms. Because VB.NET does not support a variable declaration without a type declaration. So do not leave any variable declaration types as default variant type.
  3. Just keep in mind that all the controls in VB.NET are objects.
  4. Clean the multiple variable declarations per line. All variable declarations should be in deferent lines.
  5. Do not use (0 – Zero) to disable the timer, instead set Timer enabled = false.
  6. Make sure the code compile and runs before the conversion.
  7. Convert all arrays to Zero based.
  8. Migrate all your Data access code from DAO and RDO to ADO.
  9. Do not use defaults when accessing control properties. Change code that is using Text1 to read the text box value because Text1 in VB.NET represents textbox object.
  10. Rename any VB.NET keywords in the VB6 code.
  11. Form.Print doesn't exist in VB.NET
  12. Another feature missing from VB.NET is control arrays.
  13. VB6 is a 16-bit integer; in VB.NET, it is a 32-bit integer. The 16-bit equivalent in .NET is a Short,
  14. VB.NET requires a managed RCW class to wrap every ActiveX control or COM component it needs to use. Thankfully, the Upgrade Wizard creates these assemblies automatically, and modifies the imported code to use them.
  15. The ZOrder propertyis missing in VB.NET , the wizard will convert it, but add a message that the behavior has changed.
  16. An Integer in VB6 is a 16-bit integer; in VB.NET, it is a 32-bit integer. The 16-bit equivalent in .NET is a Short

  17. In .NET, control arrays aren't needed to handle multiple events. Instead, you can use the handles clause or the AddHandler statement to link up as many controls as you want to a single event handler. You can then use the sender parameter to interact with the control that fires the event.
    VB.NET code:
    Private Sub Highlight(sender As _ Object, e As MouseEventArgs) _
    Handles lblLine1.MouseMove, _ lblLine2.MouseMove
    Dim lbl As Label = _ CType(sender, Label)
    lbl.ForeColor = _ Color.RoyalBlue
    End Sub.

  18. Big change in Popup Menu's
    In classic VB you usually creates munu object and use built-in PopupMenu command to activate it:

    VB 6 code:
    Private Sub Form_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
    If Button = 2 Then
    PopupMenu mnuOrderOptions
    End If
    End Sub

  19. In VB.NET, you can't use the same menu for a main menu and a context menu. Instead, you'll need to create a new ContextMenu object, and copy the ordinary menu information into it with the CloneMenu() method:
    VB .NET code:
    Dim mnuPopUp As New ContextMenu()
    Dim mnuItem As MenuItem
    Private Sub Form1_MouseDown (ByVal sender As System.Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles MyBase.MouseDown
    If e.Button = Mouse _ Buttons.Right Then
    Me.ContextMenu.Show(Me, _ New Point(e.X, e.Y))
    End If
    End Sub
  20. An Internet project using Web Classes, ActiveX Documents, or DHTML: None of these development technologies is supported in .NET.
  21. A database project that uses data binding to other controls: These features can be upgraded under some circumstances, but will definitely fail with the DAO and RDO data access technologies.
  22. An ActiveX control or ActiveX DLL project: While you can create .NET equivalents of these COM-based types of programs, you will lose all of their existing COM features. If you have controls or components that are still being shared among numerous applications, it will probably be easier to use them in .NET or to make additional .NET versions, rather than trying to migrate them and replace the originals.
  23. ADO and ADO.NET objects models are totally different. Although it is certainly possible to use ADO within your Visual Basic .NET application, your mileage will vary as far as performance is concern. It is possible to preserve your existing investment in ADO while still taking advantage of .NET controls and performing such tasks as data binding and marshaling Recordsets through XML Web services.

  24. The classic ADO Recordset functionality has been split into smaller and more specialized objects: the DataSet and DataReader classes. Forward-only "firehose" Recordsets map directly to DataReaders, while disconnected Recordsets map best to DataSets. In essence, a DataSet contains collections of DataTables and DataRelations. The DataTable class conceptually corresponds closely to a database table, while the DataRelation class is used to specify relations between the DataTables.
  25. Following is the simple to convert your ADO RecordSet object to VB.NET DataTable object using OleDBDataAdaptor.
    Dim olead As New Data.OleDb.OleDbDataAdapter()
    Dim dt As New Data.DataTable()
    'We use the DataAdapter to fill a DataTable
    olead.Fill(dt, rs)
    'Now we bind the DataTable to the DataGrid
    Me.DataGrid1.DataSource = dt
    'This is cleanup. Always close your objects as soon as you are done with them.
  26. Calling classic VB COM components (unmanaged code libraries) from .NET managed code is known as a Runtime-Callable Wrapper. See the corresponding posting for more details. 
  27. Notice the changes happened to ADO Command object METHODS.
  • ExecuteReader : Execute a query and return a DataReader object.
  • ExecuteScalar : Execute a query and return a single value.
  • ExecuteXmlReader : Execute an XML query and return an Xml- Reader object.
  • ExecuteNonQuery : Execute a query with no results

Friday, February 19, 2010

.NET: Accessing JDEdwards Data

Recently I have developed an integration module between JDEdwards (IBM DB2) database and Oracle Database using .NET Web service. Core objective of the module is to read the Payments data from JDEdwards Database and update to Oracle Database.
It was quite challenging and interesting task to work with. Being a beginner to JDEdwards Database, I had come across with some hurdles especially while reading JDEdwards Data during my implementation process. So I just would like to share this article with a hope that it could really be useful to someone who are on the same boat .

Download Integrating DB2 Universal Database for iSeries with Microsoft ADO.NET Book :

 PC setup:Before we begin, the following software components must be installed onto your development PC:

  • Microsoft .NET Framework and Microsoft Visual Studio .NET: Visu al Studio .NET will install a version of the .NET Framework if a required version is not already installed.
  • iSeries Access for Windows (5722-XE1), V5R3M0 or later

  • Most import thing to remember during the installation of IBM iSeries Access for Windows is to check .NET Data Provider Data Access component.

Install available Service Packs (Fixes) from the iSeries Access for Windows Web site at:

Note: The IBM DB2 UDB for iSeries .NET provider is also referred to by the namespace it defines, IBM.Data.DB2.iSeries. The two terms are used interchangeably.

IBM.Data.DB2.iSeries provider object model:

Adding an assembly reference to the provider:

  1. Solution Explorer (Figure 4-8), right-click References and select Add Reference
  2. Select IBM DB2 UDB for iSeries .NET provider and click Select.
IBM.Data.DB2.iSeries Namespace will allow applications using Microsoft's .NET framework to access iSeries databases using ADO.NET.

Imports IBM.Data.DB2.iSeries

Accessing JDEdwards Data using .NET:
Configure your JDEdwards connection parameters in Web.Config file:
You could always use Encryption and Decryption of this connection strings in Web.Config file.

-- Development Server configuration -->
<add key="JDE_IP" value="" />
<add key="JDE_UserName" value="username" />
<add key="JDE_Password" value="password" />

Protected  Friend Function JDEConnection() As iDB2Connection
'Open JDE Connection
    Dim JDEConn As iDB2Connection = New iDB2Connection
    Dim JDEConnString As String = String.Empty
    Dim JDE_Ip As String = ConfigurationSettings.AppSettings("JDE_Ip")
    Dim JDE_UserName As String = ConfigurationSettings.AppSettings("JDE_UserName")
    Dim JDE_Password As String = ConfigurationSettings.AppSettings("JDE_Password ")

    JDEConnString = "DataSource=" & JDE_Ip & ";UserID=" & JDE_UserName &
                                ";Password=" & JDE_Password & ";EnablePreFetch=false;"
    JDEConn.ConnectionString = JDEConnString
    Return JDEConn
Catch ex As Exception
       'Handle the exceptions
End Try

End Function

Using Kerberos authentication
Before opening a connection using Kerberos, you must use iSeries Navigator to define the connection and configure the connection for Kerberos. In your ConnectionString, do not specify either the
UserID or the Password property; the connection will use the configured default of Kerberos.

For more information about using Kerberos with iSeries Access for Windows, see the IBM Redbook iSeries Access for Windows V5R2 Hot Topics.

Connection pooling
The IBM.Data.DB2.iSeries .NET provider supports connection pooling. This support is on by default.
Use the Pooling property to turn connection pooling on or off.

Example 4-12 Specifying the Pooling property in the ConnectionString:
iDB2Connection cn = new iDB2Connection();
cn.ConnectionString = "DataSource=myiSeries; Pooling=false;";

Some Important Notes:
DataAdapter with the iDB2CharBitData and iDB2VarCharBitData data type of column results array of bytes to Dataset.

So, Alter your SQL SELECT statement to explicitly cast the data.
Example :CAST(COL1 AS VARCHAR(10) CCSID 037)

sSQL = "SELECT CAST(COL1 AS VARCHAR(10) CCSID 037) AS COL1, COL2, COL2 FROM <Table> <Where clause>"

All numeric columns in JDE always hold a negative value, so you must convert the data to a proper numeric value. Following ConvertAmount function  will do the job for you.

Private Function ConvertAmount(ByVal Amount As Double) As Double
    Dim dReturn As
    Double = Amount

   If dReturn < 0 Then

      dReturn = CDbl((Amount * -1) / 100)
  End If
Return dReturn
End Function

JDEdwards stores the date values in Julian date format which is a numeric value.
0 --> represents no date.
So you must convert the Julian date from to Oracle Date format (Or human readable format). Use the following function to Convert Julian Date to Normal Date format.

Private Function Julian2Date(ByVal pDate As String) As String
'Julian date in JDE database is in number format. 0 represents no date
     Dim sReturn As String = String.Empty
     Dim jDate As Integer
    Dim dtIn As Date

   If Not IsDBNull(pDate.Trim) AndAlso pDate.Trim.Length > 0 Then
    jDate = CInt(pDate)
   'jDate = 0 means the invoice
        If jDate > 0 Then
              dtIn = DateSerial(CInt(1900 + jDate / 1000), 1, CInt(jDate Mod 1000))
               sReturn = dtIn.ToString("yyyyMMdd")
       End  If

End If
Return sReturn
Catch ex As Exception
      'Handle the exceptions
End Try
End Function

Public Function Date2Julian(ByVal lstrDate As String) As Long

Dim lstrDt As Date
Dim lintYear As Integer
Dim lintDays As Integer
lstrDt = System.DateTime.ParseExact(lstrDate, "yyyyMMdd", Nothing)
lintDays = lstrDt.DayOfYear
lintYear = CInt(Mid(lstrDate, 1, 4))
Date2Julian = (lintYear - 1900) * 1000 + lintDays
End Function

Injucting JDEdwards Data into a DataSet:
Public Function GetJDEPaymentInfo As DataSet
Dim _JDEConnection As iDB2Connection
Dim daJDEPayments As New iDB2DataAdapter
Dim dsJDEPayments As DataSet = Nothing 'traditional ADO.NET Dataset Object   
_JDEConnection = JDEConnection() 'Open JDE Connection
If Not _JDEConnection Is  Nothing Then
    dsJDEPayments = New DataSet("dsJDEPayments")
    daJDEPayments = New iDB2DataAdapter(<SQLString>, _JDEConnection)
          daJDEPayments.Fill(dsJDEPayments, "tbJDEData")
      End If
Catch ex As Exception
    Throw ex
If Not _JDEConnection Is Nothing AndAlso _JDEConnection.State <> onnectionState.Closed Then
End If
End Try
Return dsJDEPayments
End Function

.NET: View State & Control State Overview

View State:

The ViewState property provides a dictionary object for retaining values between multiple requests for the same page postbacks.
ASP.NET page framework uses by default to preserve page and control property values between round trips.
When the HTML for the page is rendered, the current state of the page and values that need to be retained during postbacks are serialized into XML and then encoded using base64 encoding strings and output in the view state hidden field or fields.
EnableViewState is always set to "TRUE" in ASP.NET.

<input type="hidden" name="__VIEWSTATE" value="dDwtMTI4MDAxOTkwMjs7bDxJbWFnZUJ1dHRvbjE7Pj69/Bmk435QA9XEXIwU10D6d8QwoQ==" />

Amount of data in a __VIEWSTATE hidden field is significantly becomes large depends on your page controls objects, some proxies and firewalls will prevent access to the page that contains them. The best practice to set EnableViewState = false at Controls level or Page level depending on the requirement which could potentially increase your page access time.

For instance if you have place a DataGrid control on your page that's bind to the result of a simple query , check the view source of running instance of page and you will probably be shocked to see the amount of __VIEWSTATE hidden field data. So deciding when to disable view state in your controls is obviously important factor.

MaxPageStateFieldLength : (This property is new in the .NET Framework version 2.0.)
When the MaxPageStateFieldLength property is set to a positive number, the view state sent to the client browser is broken into multiple hidden fields, and each field's value is less than the size specified in the MaxPageStateFieldLength property.

You could always set MaxPageStateFieldLength property in the pages element of the Web.config file.
ASP.NET 2.0 enables to split the ViewState's single hidden field into several using the MaxPageStateFieldLength property in the web.config <pages> section. This indicates the maximum bytes size allowed for one viewstate hidden field. If the real size exceeds the value then viewstate is splitted in multiple fields. By default, the attribute is set to -1 which means that no maximum size is defined.

Sample ViewState before:

Then set in web.config:
Sample ViewState After :
<input type="hidden" name="__VIEWSTATE1" id="__VIEWSTATE1" value="KwANAGQYAQUJR3JpZFZpZXcxD2dk4sjERFfnDXV/" />
<input type="hidden" name="__VIEWSTATE2" id="__VIEWSTATE2" value="hMFGAL10HQUnZbk="
Data Types You Can Store in View State:
You can store objects of the following types in view state:
  • Strings
  • Integers
  • Boolean values
  • Array objects
  • ArrayList objects
  • Hash tables

Control State:

View state is a property inherited from the control class and is inherited by all controls. It is independent of the Control's state.
Control state is the control's private view state. Control state is designed for storing a control's essential data (such as the page number of a pager control) that must be available on postback when view state is disabled.

View state can be enabled or disabled for an entire page or for a specific control but Control state cannot be disabled for a control.
The control state is stored in the same hidden variable as the view state. Even when view state is disabled control state travels to the client and back to the server in the page. On postback the hidden variable is deserialized and the control state is loaded into each control that is registered for the control state mechanism.

 For example, if you have written a custom control that has different tabs that show different information, in order for that control to work as expected, the control needs to know which tab is selected between round trips. The ViewState property can be used for this purpose, but view state can be turned off at a page level by developers, effectively breaking your control. To solve this, the ASP.NET page framework exposes a feature in ASP.NET called control state.

Sunday, February 14, 2010

Oracle Database Architecture

The Oracle database has a logical layer and a physical layer.The physical layer consists of the files that reside on the disk and logical layer map the data to these files of physical layer

Oracle physical Structure
The physical database structure comprises of datafiles, redo log files and control files

Datafiles contain database's data. The data of logical data structures such as tables and indexes is stored in datafiles of the database. One or more datafiles form a logical unit of database storage called a tablespace.

Redo log files:
The purpose of these files is to record all changes made to data. These files protect database against failures.

Control files:
Control files contain entries such as database name, name and location of datafiles and redo log files and time stamp of database creation.

Oracle Logical Database Structure
Logical structures include tablespaces, schema objects, data blocks, extents and segments.

Database is logically divided into one or more tablespaces. Each tablespace creates one or more datafiles to physically store data.

SYSTEM tablespace is automatically created when the database is created. It contains data dictionary for the entire database.

Schema objects:
Schema objects are the structure that represents database's data. Schema objects include structures such as tables, views, sequences, stored procedures, indexes, synonyms, clusters and database links.
These schema objects consist of structures such as:
  • tables
  • clusters
  • indexes
  • views
  • stored procedures
  • database triggers
  • sequences
Data Blocks:
Data block represents specific number of bytes of physical database space on disk.

An extent represents continuous data blocks that are used to store specific data information.

A segment is a set of extents allocated for a certain logical structure.

Define Data-Dictionary Cache:
Data-Dictionary Cache keeps information about the logical and physical structure of the database.
The data dictionary contains information such as the following:
User information, such as user privileges Integrity constraints defined for tables Names and data types of all columns in database tables Information on space allocated and used for schema objects.

SGA (System Global Area)

Shared Pool:
This memory structure is divided into two sub-structures which are Library Cache and Data Dictionary Cache for storing recently used PL/SQL statements and the recent data definitions. The maximum size of the Shared Pool depends on the SHARED_POOL_SIZE parameter.

Database Buffer Cache:
This memory structure improves the performance while fetching or updating the recently used data as it stores the recently used datafiles. The size of this block is decided by DB_BLOCK_SIZE.

Redo Log Buffer:
This memory structure is used to store all the changes made to the database and it's primarily used for the data recovery purposes. The size of this block is decided by LOG_BUFFER.

Java Pool:
This memory structure is used when Java is installed on the Oracle server. Size that can be used is stored in parameter named JAVA_POOL_SIZE.

Large Pool:
This memory structure is used to reduce the burden of the Shared Pool, as the Session memory for the Shared Server, as the temporary storage for the I/O and for the backup and restore operations or RMAN. Parameter that stores the maximum size is LARGE_POOL_SIZE.

Program Global Area (PGA)

The PGA is a memory area that contains data and control information for the Oracle server processes. This area consists of the following components:
Stack space--This holds the session's variables and arrays.
Session information--If you are not running the multithreaded server, the session information is stored in the PGA. If you are running the multithreaded server, the session Private SQL area--This area keeps information about binding variables and runtime buffers.

Types of Segments

Data Segments:
There is a single data segment to hold all the data of every non clustered table in an oracle database. This data segment is created when you create an object with the CREATE TABLE/SNAPSHOT/SNAPSHOT LOG command. Also, a data segment is created for a cluster when a CREATE CLUSTER command is issued.
The storage parameters control the way that its data segment's extents are allocated. These affect the efficiency of data retrieval and storage for the data segment associated with the object.

Index Segments:
Every index in an Oracle database has a single index segment to hold all of its data. Oracle creates the index segment for the index when you issue the CREATE INDEX command. Setting the storage parameters directly affects the efficiency of data retrieval and storage.

Rollback Segments:
Rollbacks are required when the transactions that affect the database need to be undone. Rollbacks are also needed during the time of system failures. The way the roll-backed data is saved in rollback segment, the data can also be redone which is held in redo segment.
A rollback segment is a portion of the database that records the actions of transactions if the transaction should be rolled back. Each database contains one or more rollback segments. Rollback segments are used to provide read consistency, to rollback transactions, and to recover the database.
Types of rollbacks:
- statement level rollback
- rollback to a savepoint
- rollback of a transaction due to user request
- rollback of a transaction due to abnormal process termination
- rollback of all outstanding transactions when an instance terminates abnormally
- rollback of incomplete transactions during recovery.

Temporary Segments:
The SELECT statements need a temporary storage. When queries are fired, oracle needs area to do sorting and other operation due to which temporary storages are useful.

Locking in oracle
Locking protect table when several users are accessing the same table. Locking is a concurrency control technique in oracle. It helps in data integrity while allowing maximum concurrency access to data. Oracle offers automatic locking whenever situation requires. This is called implicit locking.

Shared Lock
This type is placed on a record when the record is being viewed.

Exclusive lock
This is placed when Insert, Update or Delete command is performed. There can be only one exclusive lock on a record at a time.
What are background processes in oracle?

Saturday, February 13, 2010

Oracle: Materialized Views

 Materialized Views


Materialized views are a data warehousing/decision support system tool that can increase by many orders of magnitude the speed of queries that access a large number of records.

A materialized view is a database object that contains the results of a query. They are local copies of data located remotely, or are used to create summary tables based on aggregations of a table's data. Materialized views, which store data based on remote tables are also, know as snapshots.

Types of Materialized Views:

Read-Only Materialized Views : No FOR UPDATE clause
Updatable Materialized Views : Having FOR UPDATE clause
Writeable materialized view :

Setup of Materialized Views:

Set the following mandatory INIT.ORA parameter :
  • COMPATIBLE >= 8.1.0
    • ENFORCED - Queries will be rewritten using only constraints and rules that are enforced and guaranteed by Oracle.
    • TRUSTED - Queries will be rewritten using the constraints that are enforced by Oracle.
    • STALE TOLERATED - Queries will be rewritten to use materialized views even if Oracle knows the data contained in the materialized view is ' stale ' (out-of-sync with the details).
The needed privileges are as follows:
grant query rewrite to scott;
alter session set query_rewrite_enabled=true;
alter session set query_rewrite_integrity=enforced;

create materialized view mv_emp
  build immediate
  refresh on commit
  enable query rewrite
select depno, count(*)
  from emp
  group by deptno;

    [refresh [fast|complete|force]
    [on demand | commit]
    [start with date] [next date]
    [with {primary key|rowid}]]

The following statement creates the primary-key materialized view.


Uses of Materialized Views
 - Biggeset performance improvement .
 - Greatly reduces the load on the machine.
 - Less physical reads - There is less data to scan through.
 - Less writes -  Sorting/aggregating is not done frequently witch could potentially decreas CPU
- Consumption  - Calculating aggregates and functions are not done on the original data which could markedly faster response times

Oracle: Difference between Truncate & Delete

  • DELETE is a DML command and deleted data
    gets copied to Rollback Tablespace which can be rolled back.
  • DELETE is a logged operation on a per row basis. This means that the deletion of each row gets logged into Redo log file and physically deleted.
  • You can DELETE any row that will not violate a constraint, while leaving the foreign key or any other constraint in place.
  • DELETE will not reset the identity column.
  • DELETE: You can use WHERE clause with DELETE Statement.
  • DELETE: Fires Triggers
  • TRUNCATE is a DDL command and cannot be rolled back (delete + commit) . All of the memory space is released back to the server.
  • TRUNCATE is also a logged operation, but in a different way. TRUNCATE logs the deallocation of the data pages in which the data exists. The deallocation of data pages means that your data rows still actually exist in the data pages, but the extents have been marked as empty for reuse. This is what makes TRUNCATE a faster operation to perform over DELETE.
  • You cannot TRUNCATE a table that has any foreign key constraints. You will have to remove the constraints, TRUNCATE the table, and reapply the constraints.
  • TRUNCATE will reset any identity columns to the default seed value.
  • TRUNCATE You can't use WHERE clause.
  • TRUNCATE does not write any date in redo log files.
  • TRUNCATE does not fire any triggers. DELETE Fires Triggers.

Oracle: What's new in UPDATE Statement

Update the results of a SELECT statement

SET column_name = value
WHERE column_name condition value;

SET sal = sal + (sal * 10/100)
WHERE grade = 'A';

Wednesday, February 10, 2010

Oralce: Whats new in INSERT statment


emprec emp%ROWTYPE;
--Add the new row to emprec
emprec.empno = 100; = 'James';
emprec.deptno = 20 ;
emprec.sal = 5000 ;

--INSERT the row into emp table
VALUES emprec ;


Using Insert All you can insert into multiple tables unconditionally with a single SQL statement.

INTO (table_name) VALUES (column_name_list)
INTO (table_name) VALUES (column_name_list)
(SELECT Statement);

INTO emp_10(empno, ename, deptno, sal)
VALUES (empno, ename, deptno, sal)
INTO emp_10_BONUS(empno, bonus)
VALUES (empno, sal+(sal* 10/100))
SELECT empno, ename, deptno, sal
FROM emp
WHERE dept_no = 10 ;

Using INSERT ALL WHEN, you can insert into multiple tables “conditionally” with a single SQL statement

WHEN (condition) THEN
INTO table_name(column_list)
VALUES (values_list)
WHEN (condition) THEN
INTO table_name(column_list)
VALUES (values_list)
INTO table_name()
VALUES (values_list)
SELECT column_list FROM table_name;

WHEN (deptno = 10) THEN
INTO emp_10(empno, ename, deptno, sal)
VALUES (empno, ename, deptno, sal)
WHEN (deptno = 20) THEN
INTO emp_20(empno, ename, deptno, sal)
VALUES (empno, ename, deptno, sal)
SELECT empno, ename, deptno, sal
FROM emp
WHERE dept_no in (10,20) ;

INSERT With Returning Row

INSERT INTO (table_name)(column_list)
VALUES (values_list)
RETURNING (value_name)
INTO (variable_name);

vempno emp.empno%TYPE;
INSERT INTO emp(empno, ename,deptno, sal)
VALUES(1,'james',10, 5000)
INTO vempno ;