Web-based Data Access Object for Unmanaged Application

• WebDAO for unmanaged code - 163.8 KB

Introduction

I described how Managed Code Application(Web-based Data Access Object) access data in a database via Web Services without Database Provider. Today, I will describe how Unmanaged Code Application access data in a database via Web Services. Unmanaged C++ can refer Web Services directly. But, it is not useful because Unmanaged C++ don't support the class [System.Data.DataTable] of .NET Framework. It is difficult to handle Record-Set form of data without the class [System.Data.DataTable]. So, I propose a method that Unmanaged Code call COM DLL(Dynamic Link Library) referring Web Services of ASP.NET for Web-DAO. When client request to query SQL, Web server query via Database Provider. And it send Record-Set form of result data to client. Managed Code DLL of client receive the data from web server and assign to class System.Data.DataTable type of variable. Unmanaged Code Application get each value of data from assigned variable of COM DLL. The following diagram describe how Unmanaged Code Application access data in a database via COM DLL.   

Background

First, we need to know why to access data in a database via Web Service and how to access. And we should know how to create COM DLL of managed Code and a method calling COM DLL in unmanaged Code. The following articles describe about this. 

• Web-based Data Access Object   
• Calling Managed .NET C# COM Objects from Unmanaged C++ Code

Using the code

Step 1. Create a Managed C# based COM DLL     

1. Make an interface [IWebDatabaseAccessObject] of external calling function.  

[Guid("db0eef1a-22e2-4cc2-9a9c-58d5de4614ae")]
public interface IWebDatabaseAccessObject
{
    [DispId(1)]void SetConnectionString(string strConnection);
    [DispId(2)]void SetConnection(string strIPAddress, int nPortNo, bool bSSL);
    [DispId(3)]int ExecuteNonQuery(string strSQL);
    [DispId(4)]bool Open(string strSQL);
    [DispId(5)]bool IsEOF();
    [DispId(6)]bool Close();
    [DispId(7)]string GetFieldStringByColumnNo(int nColumnNo);
    [DispId(8)]string GetFieldStringByColumnName(string strColumnName);
    [DispId(9)]byte[] GetBlobFieldValueByColumnNo(int nColumnNo);
    [DispId(10)]byte[] GetBlobFieldValueByColumnName(string strColumnName);
    [DispId(11)]bool MoveNext();
    [DispId(12)]int GetColumnSize();
    [DispId(13)]int GetRecordCount();
    [DispId(14)]string GetFieldNameByColumnNo(int nColumnNo);
    [DispId(15)]int ExecuteNonQueryBlob(string strSQL, string strParameter, byte[] arbBlobValue);
}  

2. Realize a class [WebDatabaseAccessObject] of interface [IWebDatabaseAccessObject]. 

[Guid("6ff59b6a-fb7e-4df6-9399-541ce0cd8632")]
[ClassInterface(ClassInterfaceType.None)]
[ProgId("WebDAO")]
public class WebDatabaseAccessObject : IWebDatabaseAccessObject

3. Make a function to assign information of web sever for Web Services of ASP.NET. 

public string ConnectionString
{
    get { return String.Format(@"{0}/{1}", 
            strConnection.TrimEnd('/'), strWebServiceFileName); }
}

// Set IP Address and Port No. of Web Server
public void SetConnectionString(string strConnection)
{
    this.strConnection = strConnection;
}

public void SetConnection(string strIPAddress, int nPortNo, bool bSSL)
{
    this.strConnection = String.Format("http{3}://{0}:{1}", 
          strIPAddress, nPortNo, (bSSL) ? "s" : null);
}

4. We make a code to request SQL Query to web server and assign Record-Set form of result data to the class [System.Data.DataTable] type of variable. The function [Open] connects to web server of Web Services, receive result data and assign the data to the variable unusually.   

// Get Data in a Database by the Parameter-SQL
public bool Open(string strSQL)
{
    DSWebDAO.WebServiceDAO wsdWebServiceDAO = new DSWebDAO.WebServiceDAO();
    wsdWebServiceDAO.Url = ConnectionString;
    try
    {
        dtRecodeSet = wsdWebServiceDAO.GetRecodeSet(strSQL);
        nRecodeIndex = 0;
    }
    catch
    {
    }
    return (dtRecodeSet != null);
}  

5. We should make member functions in COM DLL similar the structure of ADO or DAO. The function [MoveNext] moves to next Record-Row in Record-Set form of data and the function [GetFieldValue] can get each column data in selected Record-Row. 

// Move Next Row
public bool MoveNext()
{
    bool bReturnFlag = false;
    if (dtRecodeSet != null)
        if (bReturnFlag = (nRecodeIndex < dtRecodeSet.Rows.Count))
            nRecodeIndex++;
    return bReturnFlag;
}

// Get Selected Column Value of Selected Row
object GetFieldValue(object objColumn)
{
    object objReturn = null;
    try
    {
        if (dtRecodeSet != null)
            if (nRecodeIndex >= 0 && nRecodeIndex < dtRecodeSet.Rows.Count)
            {
                switch (Type.GetTypeCode(objColumn.GetType()))
                {
                    case TypeCode.Int16:
                    case TypeCode.Int32:
                    case TypeCode.Int64:
                        objReturn = dtRecodeSet.Rows[nRecodeIndex][Convert.ToInt32(objColumn)];
                        break;
                    default:
                        objReturn = dtRecodeSet.Rows[nRecodeIndex][(DataColumn)objColumn];
                        break;
                }
            }
    }
    catch
    {
    }
    return objReturn;
}
string GetFieldString(object objColumn)
{
    return Convert.ToString(GetFieldValue(objColumn));
}
public string GetFieldStringByColumnName(string strColumnName)
{
    return GetFieldString(dtRecodeSet.Columns[strColumnName]);
}
public string GetFieldStringByColumnNo(int strColumnNo)
{
    return GetFieldString(strColumnNo);
}  

Step 2. Create an Wrapping Class of Unmanaged C++ to Call Managed C# COM DLL  

Next, we would import it like the following code after generating Type-Library(tlb) file. 

#import "WebDAO.tlb" named_guids raw_interfaces_only  

We should create a wrapping class for Type-Library(tlb) file and can insert additional function in the wrapping class. For example, the function [GetNumberByColumnNo] transform the function [GetStringByColumnNo] to get number type of value.  

CWebDAO::CWebDAO(CString strConnection)
{
    bCOMInitailzed = Initialize();
    BSTR bstrTemp = strConnection.AllocSysString();
    m_pDotNetCOMPtr->SetConnectionString(bstrTemp);
    SysFreeString(bstrTemp);
}
 
BOOL CWebDAO::Initialize()
{
    HRESULT hRes = m_pDotNetCOMPtr.CreateInstance(WebDAO::CLSID_WebDatabaseAccessObject);
    return (hRes == S_OK);
}
 
BOOL CWebDAO::Open(CString strSQL, BOOL bOnlyTableName, BOOL bBlob, BOOL bUseClient)
{
    VARIANT_BOOL vbRetVal;
    if(bCOMInitailzed)
    {
        BSTR bstrTemp = strSQL.AllocSysString();
        m_pDotNetCOMPtr->Open(bstrTemp, &vbRetVal);
        SysFreeString(bstrTemp);
    }
    return (bCOMInitailzed &&(vbRetVal == -1));
} 
 
int CWebDAO::GetNumberByColumnNo(int nColumnNo)
{
    int nReturn = -99999;
    CString strReturn = _T("");
    try
    {
        if(bCOMInitailzed)
        {
            BSTR bstrReturn;
            m_pDotNetCOMPtr->GetFieldStringByColumnNo(nColumnNo, &bstrReturn);
            strReturn = (LPCTSTR)(_bstr_t)bstrReturn;
        }
        nReturn = atoi((LPSTR)(LPCTSTR)strReturn);
    }
    catch (CException* e)
    {
        e->Delete();
    }
 
    return nReturn;
}  

Step 3. Unmanaged C++ Application Call Managed C# COM DLL for the Wrapping Class 

Unmanaged C++ Application must call a function [CoInitialize] to initialize and deinitialize COM components. 

CoInitialize(NULL);

int CWebDAOTestApp::ExitInstance()
{
    // TODO: Add your specialized code here and/or call the base class
    CoUninitialize();
 
    return CWinApp::ExitInstance();
}

Run the unmanaged C++ application [WebDAOTest] after running IIS (Internet Information Services). Please input Root URL of ASP.NET Development Server in a TextBox [Web Address]. If you are running real web server for Web Services, Input real URL of Web-DAO. If the application connect to web server correctly, you can get result data. 

Points of Interest

Generally, the function [Open] of ADO or DAO connect to a database server and keep the connection. But the function [Open] of the class [WebDatabaseAccessObject] is different, because Web Services is discontinuous and request-response mode.  So, the function [Open] of the class [WebDatabaseAccessObject] connect to web server of Web Services, Furthermore it receive result data and assign to a variable of the class [System.Data.DataTable]. If you want to include BLOB (binary large object) in result data, you should use the function [GetBlobFieldValue] of CWebDAO instead of the function [GetFieldValue]. If you want wrapping class [CWebDAO] replace existing ADO Class or Wrapping, you should refer following code. If you modify function and variable name of wrapping class [CWebDAO], existing class would be replaced easily. 

#ifdef _WEB_
    CWebDAO recordSet(CDwUtility::GetWebDAOConnection());
#else
    CDwAdoRecord recordSet(CDwUtility::GetDBConnection());            
#endif
    CString strSQL;
    strSQL.Format("SELECT COUNT(*) AS EXAM_COUNT FROM T_CODE WHERE EXAM_KEY = %s", m_strExamKey);
    
    BOOL bExamCount = FALSE;
    if (recordSet.Open(strSQL))
    {
        while (recordSet.IsEOF() == FALSE)
        {
            int nCount;
            recordSet.GetFieldValue(_T("EXAM_COUNT"), nCount);
            bExamCount = (nCount > 0);
            recordSet.MoveNext();
        }
        recordSet.Close();
    }