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
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
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
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
IBM DB2 Related Interview Questions
|IBM Websphere Interview Questions||IBM-REXX Interview Questions|
|IBM Cognos Interview Questions||IBM DB2 Interview Questions|
|J2EE Interview Questions||COBOL Interview Questions|
|IBM-JCL Interview Questions||DB2 Using SQL Interview Questions|
|IBM WAS Administration Interview Questions||IBM WebSphere Administration Interview Questions|
|Database Administration Interview Questions||DB2 SQL Programming Interview Questions|
|Mainframe DB2 Interview Questions|
IBM DB2 Related Practice Tests
|IBM Websphere Practice Tests||IBM Cognos Practice Tests|
|IBM DB2 Practice Tests||J2EE Practice Tests|
|COBOL Practice Tests||IBM-JCL Practice Tests|
|DB2 Using SQL Practice Tests||IBM WAS Administration Practice Tests|
|Database Administration Practice Tests||DB2 SQL Programming Practice Tests|
Ibm Db2 Tutorial
Db2 Application Development Overview
Application Development With Db2 Purexml
Application Development With Php
Application Development With C/c++
Application Development With Java
Application Development With .net
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.