Web-based Data Access Object for Unmanaged Application

using System;
using System.Data;
using System.Web;
using System.Collections;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.ComponentModel;
using System.Data.OracleClient;

namespace WSWebDAO
{
    /// <summary>
    /// Summary description for WebServiceDAO
    /// </summary>
    [WebService(Namespace = "http://tempuri.org/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [ToolboxItem(false)]
    public class WebServiceDAO : System.Web.Services.WebService
    {
        static string strDataSource = "DataSource";
        static string strUserID = "UserID";
        static string strPassword = "Password";
        static OracleConnection oraConn = new OracleConnection(String.Format("Data Source={0};Persist Security Info=True;User ID={1};Password={2};Unicode=True", strDataSource, strUserID, strPassword));
        
        [WebMethod]
        public DataTable GetRecodeSet(string strSQL) 
        {
            DataTable dtDataTable = new DataTable("WebDAO");
            DataSet dsDataSet = new DataSet();
            try
            {
                OracleCommand oraCommand = new OracleCommand(strSQL, oraConn);
                OracleDataAdapter oraDataAdapter = new OracleDataAdapter(oraCommand);
                oraDataAdapter.Fill(dsDataSet);
            }
            catch (OracleException e)
            {
                DataColumn dcCode = new DataColumn();
                dcCode.DataType = System.Type.GetType("System.Int32");
                dcCode.ColumnName = "CODE";
                dtDataTable.Columns.Add(dcCode);

                // Create second column.
                DataColumn dcMessage = new DataColumn();
                dcMessage.DataType = Type.GetType("System.String");
                dcMessage.ColumnName = "MESSAGE";
                dtDataTable.Columns.Add(dcMessage);

                DataRow drException = dtDataTable.NewRow();
                drException["CODE"] = e.Code;
                drException["MESSAGE"] = e.Message;
                dtDataTable.Rows.Add(drException);
            }
            
            if (dsDataSet.Tables.Count > 0)
                dtDataTable = dsDataSet.Tables[0];
            return dtDataTable;
        }

        [WebMethod]
        public int ExecuteStoredProcedure(string strProcedureName, string[] arstrParameterName, string[] arstrParameterValue)
        {
            int nResultCode = -1;
            try
            {
                oraConn.Open();
                OracleCommand oraCommand = new OracleCommand(strProcedureName, oraConn);
                oraCommand.CommandType = CommandType.StoredProcedure;
                for (int i = 0; i < arstrParameterName.Length; i++)
                {
                    oraCommand.Parameters.Add(arstrParameterName[i], OracleType.VarChar);
                    oraCommand.Parameters[arstrParameterName[i]].Value = arstrParameterValue[i];
                }
                oraCommand.ExecuteNonQuery();
            }
            catch (OracleException exOracleException)
            {
                nResultCode = exOracleException.Code;
            }
            oraConn.Close();
            return nResultCode;
        }

        [WebMethod]
        public int ExecuteNonQuery(string strSQL)
        {
            int nResultCode = 0;
            try
            {
                OracleString ostrResult;
                oraConn.Open();
                OracleCommand oraCommand = new OracleCommand(strSQL, oraConn);
                oraCommand.CommandType = CommandType.Text;
                oraCommand.ExecuteOracleNonQuery(out ostrResult);
            }
            catch (OracleException exOracleException)
            {
                nResultCode = exOracleException.Code;
            }
            oraConn.Close();
            return nResultCode;
        }

        [WebMethod]
        public int ExecuteNonQueryBlob(string strSQL, string strParameter, byte[] arbBlobValue)
        {
            int nResultCode = 0;
            try
            {
                OracleString ostrResult;
                oraConn.Open();
                OracleCommand oraCommand = new OracleCommand(strSQL, oraConn);
                OracleParameter opOracleParameter = new OracleParameter();
                opOracleParameter = oraCommand.Parameters.Add(strParameter, OracleType.LongRaw);
                opOracleParameter.Direction = ParameterDirection.Input;
                opOracleParameter.Value = arbBlobValue;

                oraCommand.ExecuteOracleNonQuery(out ostrResult);
            }
            catch (OracleException exOracleException)
            {
                nResultCode = exOracleException.Code;
            }
            oraConn.Close();
            return nResultCode;
        }
    }
}