Application example using ADO.NET - IBM DB2

In this section, we provide sample C# application codes, which utilize IBM DB2.NET Data provider to demonstrate inserting new customer data into the Connection Object customer table as well as updating and deleting existing customer information in the SAMPLE database. The CUSTOMER table in the SAMPLE database has three columns= as defined in Table. The primary key consists of CID (customer ID) column.

CUSTOMER table from sample database

CUSTOMER table from sample database

In the following set of samples, each task has been presented as a separate method.

Establishing the connection to the database
Establishing connection to the database is the essential first step. The connection code to IBM DB2 database using DB2 .NET data provider is implemented in ConnectDb() method. The ConnectDb() method will return DB2Connection Object once connection has been established using the three parameters (db alias, userid, and password) required for the connection string. If passed database alias is null, it will establish default connection to the SAMPLE database.

Connection is established by first creating DB2Connection object and passing the connection string to the constructor:

The namespaces shown in below are required for given samples:

Namespaces for IBM DB2 .NET Data Provider

Connecting to the DB2 database using .NET data provider

Selecting existing customer information
In order to read the customer data from CUSTOMER table, we select INFO column, which contains customer information using the WHERE Clause with specific CID. The sample INFO XML data from CUSTOMER table is shown below

Info XML data from the CUSTOMER table

The customer ID (CID) is also stored as an attribute in customerinfo element. The INFO column being returned from the SELECT statement is XMLdata type, which will be read using XmlReader object.

The XmlReader object is an event-based, read-only, forward-only XML pull parser. It provides functionality for reading in XML documents.

The XmlReader object is then loaded into XmlDocument object, which represents XML document as a node tree, where elements and attributes are stored as nodes that contain relational information.

Presence of XML namespace determines whether we need to add XmlNamespaceManager in order to successfully select nodes.

If the XPath expression in SelectNodes does not include a prefix, it is assumed that the namespace URI is the empty namespace. If XML document includes a default namespace, it must be added to a prefix and namespace URI to the XmlNamespaceManager or none of the nodes will get selected. The XmlNamespaceManager is required to resolve any prefixes in the XPath expression.

If XML namespace is not present in XML document (in absence of xmlns attribute), XmlNamespaceManager is not needed and XPath expression in SelectNodes will appear as following:

The double slashes (//) in XPath above refer to descendant-or-self Axis, meaning it contains the context node in addition to all the nodes contained in the descendant axis. Note that the use of (//) prefix will yield all instances of element name specified in the XML document.

If you want to differentiate elements of same name in different nodes, you can specify the specific XPath. For example, in the CUSTOMER table, we have two instances of <name> in the XML document for the “info” column. One is for name of the customer and the other is for the assistant’s name as shown below

Sample info XML data from the CUSTOMER table

Sample info XML data from the CUSTOMER table

The XPath shown below can be specified in the SelectNodes() to obtain all the names in the given XML document (customer’s and assistant’s).

Selecting all names

Obtain customer’s name and assistant’s name respectively

The value of the element is extracted using:

XmlElement.InnerXml(localname, XmlNamespaceManager).

The value of the attribute is extracted using:

XmlElement.GetAttribute(localname, XmlNamespaceManager).

The XmlNodeList implements IEnumerable interface, and thus it can be accessed using IEnumberator methods. The code, which selects INFO XML column from the customer table is implemented in SelectCustomer() method as shown below

Selecting customer information

Inserting new customer data
Inserting new Customer Information requires creation of data for CID (customer ID) and INFO (customer information). The HISTORY column information is not required. Since CID is the primary key and thus needs to be unique, we issue a simple query against the CUSTOMER table’s CID column to obtain the current MAX value, then add one to generate the new CID.

For the primary key CID generation, we used MAX()+1 but this is not ideal way to generate the key. You should utilize identity column, which provides a method for DB2 database to automatically generate a unique numeric value for each row in a table.

An identity column that is defined as generated always prevents the overriding of values in an SQL statement. An identity column that is defined as generated by default gives an application a way to explicitly provide a value for the identity column.

In this case, Sample CUSTOMER table was not created with identity column. The creation of INFO XML data is done by a separate CreateCustXML() method as shown below

The Example shows insert of new customer CID and INFO into the CUSTOMER table. Once XmlDcoument object containing customer info data has been created by CreateCustXML() method, we can now insert the data into the table.

The CID value can be concatenated to theCommandText string. However, the XmlDocument data needs to be first added using command.Parameters.Add() method after it is converted to String using InnerXml method. Note that @ prefix for the parameter is required:

cmd.Parameters.Add(new DB2Parameter(" @XMLData", XmlDocument.InnerXml));

Inserting new CID and info data into the CUSTOMER table

Creation of new customer information data for the CUSTOMER table requires creation of a new XML document.Below it demonstrates an XML document tree for the info XML data.

XML document tree for the customer info data

XML document tree for the customer info data

The XmlDocument can be created in memory using XmlDocument() constructor or by calling XmlImplementation.CreateDocument(). In our example, we create new XmlDocument using the constructor which will yield an empty document. Elements will be added to the XmlDocument object using CreateElement() and AppendChild() methods:

XmlDocument.CreateElement(localname). XmlElement.AppendChild(Elementname);

Attributes can be assigned using XmlElment.SetAttribute(localname, value) and Element values can be assigned using XmlElement.InnerXml = value.

Creating new info XML document

Updating existing customer information
Updating XML data in the CUSTOMER table will require use of the cmd.Parameters.Add() method to add XML data to the UPDATE statement. Customer’s info XML data will be recreated using new modified information, then UPDATE statement will be used to insert new data to existing CID (customer ID).A new XmlDocument is created using changed/ modified customer information through CreateCustXML() method. The DB2Connection object, CID, and XmlDocument object containing new info data will be passed to Update Customer() method for update.

Updating the Customer table

If we need to update customer information using the name of the customer, we need to first obtain the customer ID (CID). The CID is an attribute of customerinfo element in the info XML data, which needs to be queried using the customer name. The GetCustomerID() method shown below will demonstrate how XMLQuery can be used to obtain the CID attribute using the name element. The XMLQuery that will yield CID using the customer name is shown in below

XMLQuery yielding CID

GetCustomerID method which will retrieve CID using XMLQuery

Deleting customer entry
Deleting existing customer entry is done simply using DELETE statement with WHERE clause for CID as shown below

Deleting a row from the Customer table

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

IBM DB2 Topics