XML Explicit Serialization

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Xml;
using System.Xml.Serialization;
using System.Data.SqlTypes;
using DataAccessLayer;


namespace BusinessLayer
{
public class clsSalesHistory
{
public Messagers.Response TransferData(string strShop_ID,string strShop_Group_ID,string strSan_ID,string[] strISBN, double[] dUnit_Price, double[] dCost_Price,
double[] dSelling_Price,double[] dRR_Price,double[] dDiscount,double[] dSales_Tax,double[] dQuantity_Sold)
{
string strXML;
SqlParameter Param;
List ParamCollection = new List();
UTF8Encoding encoding = new UTF8Encoding();
MemoryStream mem = new MemoryStream();
XmlTextWriter xWriter = new XmlTextWriter(mem, Encoding.UTF8);

xWriter.Formatting = Formatting.Indented;
xWriter.WriteProcessingInstruction("xml", "version=\"1.0\" ");

GenerateInfo[] PassCollect = new GenerateInfo[strISBN.Length];

clsDbConnectivity objDataAccess = new clsDbConnectivity();

Messagers.Response objResponse = new BusinessLayer.Messagers.Response();

try
{
for (int i = 0; i <= strISBN.Length - 1; i++)
{
PassCollect[i].ISBN = strISBN[i];
PassCollect[i].Unit_Price = dUnit_Price[i];
PassCollect[i].Cost_Price = dCost_Price[i];
PassCollect[i].Selling_Price = dSelling_Price[i];
PassCollect[i].RR_Price = dRR_Price[i];
PassCollect[i].Discount = dDiscount[i];
PassCollect[i].Sales_Tax = dSales_Tax[i];
PassCollect[i].Quantity_Sold = dQuantity_Sold[i];

}

XmlSerializer xSerializer = new XmlSerializer(typeof(GenerateInfo[]));
XmlSerializerNamespaces xSeNameSpace = new XmlSerializerNamespaces();
xSeNameSpace.Add(string.Empty, string.Empty);

xSerializer.Serialize(xWriter, PassCollect,xSeNameSpace);
mem = (MemoryStream)xWriter.BaseStream;
strXML = encoding.GetString(mem.ToArray());
mem.Close();

Param = new SqlParameter("@vcShop_ID", SqlDbType.VarChar, 100);
Param.Value =strShop_ID;
ParamCollection.Add(Param);

Param = new SqlParameter("@vcShop_Group_ID", SqlDbType.VarChar, 100);
Param.Value =strShop_Group_ID;
ParamCollection.Add(Param);

Param = new SqlParameter("@vcSan_ID", SqlDbType.VarChar, 100);
Param.Value =strSan_ID;
ParamCollection.Add(Param);

Param = new SqlParameter("@xmlParam", SqlDbType.NText);
Param.Value = strXML;
ParamCollection.Add(Param);

objDataAccess.CommandName = "spSaveSalesBulkInfo";
objDataAccess.CommandParamCollection = ParamCollection;


if (objDataAccess.ExecuteSqlCommand())
{
objResponse.Acknowledgment = true;
}
else
{
objResponse.Acknowledgment = false;
}
}
catch
{
objResponse.Acknowledgment = false;
objResponse.AckMessage = objDataAccess.AcknowledgmentMsg;
}

return objResponse;

}


public struct GenerateInfo
{
public string ISBN;
public double Unit_Price;
public double Cost_Price;
public double Selling_Price;
public double RR_Price;
public double Discount;
public double Sales_Tax;
public double Quantity_Sold;

}


}

Calling SSL Enable URL using UTL_HTTP Package in Oracle

To access an SSL website using the utl_http package and PL/SQL is easy once you come to grips with the concept of a Wallet. The wallet stores all the encryption keys that the database needs to access the SSL site. But before looking at wallet config, lets review the PL/SQL code:

1. Declare some variables:req utl_http.req;resp utl_http.resp;rw varchar2(32767);

2. Define the location of the wallet:

UTL_HTTP.SET_WALLET ( 'file:' 'p:\wallet', 'mypassword' );

Where 'file:' is they keyword to state that the wallet is a file. 'p:\wallet' is the windows directory of the wallet. For unix, this might be '/u01/wallet'. 'mypassword' is the password required to open the wallet.

The disadvantage is that the password to the wallet is stored in clear text. Its recommended then that you create a wallet that only contains public keys from SSL sites, information which is publically available and will not pose a security risk.

You can store multiple wallets in different locations if you are paranoid about security.The disadvantage of wallets is that you need to know in advance each SSL site you will be accessing and extract its public key certificate. This isn't as flexible as a web browser, which does this for you.

3. Now access the SSL site. There are lots of ways of doing this, the following is just one:

req := utl_http.begin_request( 'https://www.amazon.com');
utl_http.set_header(req, 'User-Agent', 'Mozilla/4.0');
resp := utl_http.get_response(req);

4. And then you can process the data that comes back using a simple loop:
loopbegin rw := null;
utl_http.read_line(resp, rw, TRUE); -- process rwexception when others then exit;end;end loop;
utl_http.end_response(resp);

5. To configure the wallet go into IE and retrieve the certificate of the SSL site (haven't been able to do this in firefox yet, which shows you that IE still has some uses).You can either click on the certificate icon if it appears, or go to file-properties and retrieve it as follows.

And then choose the Base-64 encoded option to save it. Save it to a local directory.Keep in mind the SSL certificates can be hierarchical, in that the certification of a site might be dependent on its parent (authorising) site also approving it.So it might require extracting a couple of certificates to correctly authorise a site.Once saved, create an Oracle wallet. There are similar options to the ones shown below in Linux and even if you only have apache installed. The java program is called: owm.cl

as p:\wallet (or whatever directory is suitable for your database). Remember that the database has to be able to access the wallet, not your computer. So its best located on the same server as the database.

Then import the certificate you have just created

directory as the wallet, so I have a physical trail of all the certificates.Don't forget to save the wallet. You will find that the program creates an ewallet.p12 file in the directory. Leave it as is, this is an important file.

The certificate is now installed. Your PL/SQL program can now access the SSL site