How to Create COM DLL (.tlb) in .NET 4.x for reference in VB6

Recently, I've been working on a Helper (COM DLL (.tlb) in .NET 4.x for reference in VB6) to assist with the parallel migration from VB6 to .NET Framework. The issue that might arise is having to maintain the Business Logic in two places. So I design & implement a parallel approach, by default, VB6 will call the Business Logic in .NET Framework. If there are any problems, I can turn off the Feature Flag and use the VB logic instead.

Add Reference to ADODB

In this step, I want to use a recordset asa Data Structure for VB6 Side, such as converting JSON (1 Level Deep) from WebAPI and converting it into a recordset format that VB6 can access. the Add Reference to ADODB

  1. Right-click your project in Visual Studio > Add > Reference.
  2. Go to the COM tab.
  3. Find and select Microsoft ActiveX Data Objects 2.x Library (usually 2.8 is available).
  4. Click OK.

Sample usage ADODB Namespace

  • Connect DB, but I not user for this scenario I prefer use ADODB.NET with EF Or Dapper for best performance
using ADODB;

Connection conn = new Connection();
Recordset rs = new Recordset();

conn.Open("Provider=SQLOLEDB;Data Source=SERVER;Initial Catalog=DB;User Id=USER;Password=PWD;", "", "", 0);
rs.Open("SELECT * FROM MyTable", conn, CursorTypeEnum.adOpenStatic, LockTypeEnum.adLockReadOnly, -1);

while (!rs.EOF)
{
    Console.WriteLine(rs.Fields["ColumnName"].Value);
    rs.MoveNext();
}

rs.Close();
conn.Close();

My Scenario: Use this COM DLL as a VB6 connector to a web service

In my scenario, I want to connect VB6 / NET Framework Client to Process Something On Web Service this design can add-on many thing such as jwt / oauth flow

I will focus on VB6Connector - create a request by storing value in 2 format String / json

  • String
DATE | 2018-07-31;
LIST<INT> | 1147,1148,1149,1150,1151,1152,1153,1154,1155,1156;
INT| 18;
INT| 1;
BOOL| true;"
  • json
{
  "invsConfig": {
    "GATEWAY_WEBROOT": "192.168.0.180:10600/",
    "GATEWAY_PREFIX": null,
    "GATEWAY_HTTPS": false,
    "VBSERVERPATH": "C:\\invs_data\\"
  },
  "targetService": {
    "ObjectId": "1209900",
    "TargetServiceName": "RebalanceEqProcessService",
    "TargetMethodName": "ProcessRebalanceAPI",
    "TargetParam": [
      {
        "DATE": "2018-07-31"
      },
      {
        "LIST<INT>": [1147, 1148, 1149, 1150, 1151, 1152, 1153, 1154, 1155, 1156]
      },
      {
        "INT": 18
      },
      {
        "INT": 1
      },
      {
        "BOOL": true
      }
    ]
  }
}

And then call Web Service (Spring Boot / ASP WebAPI to process from client request. You can see my sample code below

  • this helper will convert json result to ADODB.Recordset for VB6
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace invs.vb6connector.common
{
    public class ConvertNetTypetoVB6TypeHelper
    {
        public static DataTable CreateDataTable<T>(IEnumerable<T> list)
        {
            Type type = typeof(T);
            var properties = type.GetProperties();

            DataTable dataTable = new DataTable();
            foreach (PropertyInfo info in properties)
            {
                dataTable.Columns.Add(new DataColumn(info.Name, Nullable.GetUnderlyingType(info.PropertyType) ?? info.PropertyType));
            }

            foreach (T entity in list)
            {
                object[] values = new object[properties.Length];
                for (int i = 0; i < properties.Length; i++)
                {
                    values[i] = properties[i].GetValue(entity);
                }

                dataTable.Rows.Add(values);
            }

            return dataTable;
        }

        public static ADODB.Recordset ConvertToRecordset(DataTable inTable)
        {
            ADODB.Recordset result = new ADODB.Recordset();
            if (inTable.Rows.Count != 0)
            {
                result.CursorLocation = ADODB.CursorLocationEnum.adUseClient;

                ADODB.Fields resultFields = result.Fields;
                System.Data.DataColumnCollection inColumns = inTable.Columns;

                foreach (DataColumn inColumn in inColumns)
                {
                    resultFields.Append(inColumn.ColumnName
                        , TranslateType(inColumn.DataType)
                        , inColumn.MaxLength
                        , inColumn.AllowDBNull ? ADODB.FieldAttributeEnum.adFldIsNullable :
                                                 ADODB.FieldAttributeEnum.adFldUnspecified
                        , null);                   
                }

                result.Open(System.Reflection.Missing.Value
                        , System.Reflection.Missing.Value
                        , ADODB.CursorTypeEnum.adOpenStatic
                        , ADODB.LockTypeEnum.adLockOptimistic, 0);

                foreach (DataRow dr in inTable.Rows)
                {
                    result.AddNew(System.Reflection.Missing.Value,
                                  System.Reflection.Missing.Value);

                    for (int columnIndex = 0; columnIndex < inColumns.Count; columnIndex++)
                    {
                        resultFields[columnIndex].Value = dr[columnIndex];                 
                    }
                }
            }

            return result;
        }

        private static ADODB.DataTypeEnum TranslateType(Type columnType)
        {
            switch (columnType.UnderlyingSystemType.ToString())
            {
                case "System.Boolean":
                    return ADODB.DataTypeEnum.adBoolean;
                case "System.Byte":
                    return ADODB.DataTypeEnum.adUnsignedTinyInt;
                case "System.Char":
                    return ADODB.DataTypeEnum.adChar;
                case "System.DateTime":
                    return ADODB.DataTypeEnum.adDate;
                case "System.Decimal":
                    return ADODB.DataTypeEnum.adCurrency;
                case "System.Double":
                    return ADODB.DataTypeEnum.adDouble;
                case "System.Int16":
                    return ADODB.DataTypeEnum.adSmallInt;
                case "System.Int32":
                    return ADODB.DataTypeEnum.adInteger;
                case "System.Int64":
                    return ADODB.DataTypeEnum.adBigInt;
                case "System.SByte":
                    return ADODB.DataTypeEnum.adTinyInt;
                case "System.Single":
                    return ADODB.DataTypeEnum.adSingle;
                case "System.UInt16":
                    return ADODB.DataTypeEnum.adUnsignedSmallInt;
                case "System.UInt32":
                    return ADODB.DataTypeEnum.adUnsignedInt;
                case "System.UInt64":
                    return ADODB.DataTypeEnum.adUnsignedBigInt;
                case "System.String":
                    return ADODB.DataTypeEnum.adVarChar;                    
                default:
                    return ADODB.DataTypeEnum.adVarChar;                    
            }
        }

        public static DataTable ConvertToDataTable(ADODB.Recordset pRecordset)
        {
            System.Data.OleDb.OleDbDataAdapter adapter = new System.Data.OleDb.OleDbDataAdapter();
            DataTable dt = new DataTable();
            adapter.Fill(dt, pRecordset);
            return dt;
        }

        public static Dictionary<string, string> GetDict(DataTable dt)
        {

            Dictionary<string, string> dicConfig = new Dictionary<string, string>();
            foreach (DataRow row in dt.Rows)
            {
                dicConfig = Enumerable.Range(0, dt.Columns.Count).ToDictionary(i => dt.Columns[i].ColumnName, i => row.ItemArray[i].ToString());
            }
            return dicConfig;
        }

        //http://www.nullskull.com/q/10057748/hi-all.aspx

        #region Converting ObjectArray to Datatable
        public static DataTable ConvertToDataTable(Object[] array)
        {
            PropertyInfo[] properties = array.GetType().GetElementType().GetProperties();
            DataTable dt = CreateDataTable(properties);
            if (array.Length != 0)
            {
                foreach (object o in array)
                    FillData(properties, dt, o);
            }
            return dt;
        }

        private static DataTable CreateDataTable(PropertyInfo[] properties)

        {
            DataTable dt = new DataTable();
            DataColumn dc = null;
            foreach (PropertyInfo pi in properties)
            {
                dc = new DataColumn();
                dc.ColumnName = pi.Name;
                dc.DataType = pi.PropertyType;
                dt.Columns.Add(dc);
            }
            return dt;
        }

        private static void FillData(PropertyInfo[] properties, DataTable dt, Object o)
        {
            DataRow dr = dt.NewRow();
            foreach (PropertyInfo pi in properties)
            {
                dr[pi.Name] = pi.GetValue(o, null);
            }
            dt.Rows.Add(dr);
        }
        #endregion
    }
}
  • Sample Code /dowork endpoint
public ADODB.Recordset Execute(int pHeadMemberId
                             , String pHeadMemberName
                             , String pHeadIdentifier
                             , int pServiceId
                             , string pTargetServiceName
                             , string pTargetMethodName
                             , string pTemplateRequest = null)
{
	try
	{
		InitializeConfig(this._LogPath, "Execute");
		
		//Gettoken
		object[] RequestParam = pTemplateRequest == null ? new object[] { } : CreateObjectRequestforTemplate(pTemplateRequest);
		invsTaskRequest request = ReformatHelper.CreateMessageWithHeader(pHeadMemberId, pHeadMemberName, pHeadIdentifier, pServiceId, pTargetServiceName, pTargetMethodName, RequestParam, invs.Common.Const.BODYTYPE_CONST.JSON);
		
		if (request.ParamClz == null)
		{
			request.ParamClz = "";
			request.ParamObj = new string[] { };
		}
		
		string functionurl = (this._TargetURL.Contains("invs-gateway-web-app") ? "invsserv/dowork" : "/gw/invsserv/dowork");
		
		Dictionary<string, object> headers = new Dictionary<string, object>();
		string password = (_PassWord == "" || _PassWord == null) ? pHeadMemberName : _PassWord;
		string accessToken = GetToken(pHeadMemberName, password);
		
		if (accessToken != null) 
			headers.Add("Authorization", "Bearer " + accessToken);
		
		headers.Add(RestHelper.HEADER_CONTENTTYPE, "application/json");
		_logger.Info("Execute Dowork : " + this._TargetURL + functionurl + " " + pTargetServiceName + "." + pTargetMethodName);
		_logger.Debug("Template Request : " + pTemplateRequest);
		
		invsTaskResponse response = invsJsonHelper.DeserializeObject<invsTaskResponse>(RestHelper.Restful(this._TargetURL, functionurl, "POST", request, this._IsHttps, headers));
		
		if (!String.IsNullOrWhiteSpace(response.Error))
		{
			_logger.Error(response.Error);
			throw new VB6ConnectorException(-1234567890, response.Error);
		}
		
		DataTable ds = GetDataTableFromJsonString(response.ResultObj.ToString());
		_logger.Debug("Dowork : Complete");
		return ConvertNetTypetoVB6TypeHelper.ConvertToRecordset(ds);
	}
	catch (Exception Ex)
	{
		_logger.Error(Ex);
		throw new VB6ConnectorException(Ex);
	}
}
  • Sample Code /doworkasync endpoint
public ADODB.Recordset ExecuteAsync(int pHeadMemberId
                                  , String pHeadMemberName
                                  , String pHeadIdentifier
                                  , int pServiceId
                                  , string pTargetServiceName
                                  , string pTargetMethodName
                                  , string pTemplateRequest = null)
{
	try
	{
		InitializeConfig(this._LogPath, "Executeasync");

		object[] RequestParam = CreateObjectRequestforTemplate(pTemplateRequest);
		invsTaskRequest request = ReformatHelper.CreateMessageWithHeader(pHeadMemberId, pHeadMemberName, pHeadIdentifier, pServiceId, pTargetServiceName, pTargetMethodName, RequestParam, invs.Common.Const.BODYTYPE_CONST.JSON);

		string functionurl = (this._TargetURL.Contains("invs-gateway-web-app") ? "invsserv/doworkasync" : "/gw/invsserv/doworkasync");
                
		Dictionary<string, object> headers = new Dictionary<string, object>();
		string password = (_PassWord == "" || _PassWord == null) ? pHeadMemberName : _PassWord;
		string accessToken = GetToken(pHeadMemberName, password); //Password Not Encrypt
		
		if (accessToken != null) headers.Add("Authorization", "Bearer " + accessToken);
			headers.Add(RestHelper.HEADER_CONTENTTYPE, "application/json");
			
			_logger.Info("Execute Doworkasync : " + this._TargetURL + functionurl + " " + pTargetServiceName + "." + pTargetMethodName);
			_logger.Debug("Template Request : " + pTemplateRequest);
			
			invsTaskResponse response = invsJsonHelper.DeserializeObject<invsTaskResponse>(RestHelper.Restful(this._TargetURL, functionurl, "POST", request, this._IsHttps, headers));
                
			if (!String.IsNullOrWhiteSpace(response.Error))
            {
				_logger.Error(response.Error);
				throw new VB6ConnectorException(-1234567890, response.Error);
			}
			
			DataTable ds = GetDataTableFromJsonString(response.ResultObj.ToString());			
			_logger.Debug("doworkasync : Compleate");
			return ConvertNetTypetoVB6TypeHelper.ConvertToRecordset(ds);
	}
	catch (Exception Ex)
	{
		_logger.Error(Ex);
		throw new VB6ConnectorException(Ex);
	}
}
  • Sample Code that receive json parameter
public ADODB.Recordset ExecutebyJSON(String pJSONRequest)
{
    VB6RequestObjectDTO JSONRequest = JsonConvert.DeserializeObject<VB6RequestObjectDTO>(pJSONRequest);
    try
    {
        InitializeConfig(GetLogFileNamewithPath(JSONRequest.invsConfig.VBSERVERPATH), "ExecutebyJSON");

        var param = ConvertDynamicRequest(JSONRequest.targetService.TargetParam);
        object[] RequestParam = CreateObjectRequestforJSONTemplate(param);
        invsTaskRequest request = ReformatHelper.CreateMessageWithHeader(JSONRequest.targetService.MemberId																   , JSONRequest.targetService.MemberName																   , JSONRequest.targetService.Identifier															   , JSONRequest.targetService.ObjectId																   , JSONRequest.targetService.TargetServiceName																   , JSONRequest.targetService.TargetMethodName																   , RequestParam																   , invs.Common.Const.BODYTYPE_CONST.JSON);
       
        string functionurl = (this._TargetURL.Contains("invs-gateway-web-app") ? "invsserv/dowork" : "/gw/invsserv/dowork");

        Dictionary<string, object> headers = new Dictionary<string, object>();
        string accessToken = GetToken(JSONRequest.targetService.MemberName, JSONRequest.targetService.MemberName); 
        if (accessToken != null) headers.Add("Authorization", "Bearer " + accessToken);
        headers.Add(RestHelper.HEADER_CONTENTTYPE, "application/json");
        invsTaskResponse response = invsJsonHelper.DeserializeObject<invsTaskResponse>(RestHelper.Restful(JSONRequest.invsConfig.GATEWAY_WEBROOT																									, functionurl																									, "POST"																									, request																									, JSONRequest.invsConfig.GATEWAY_HTTPS, headers));
																										
        if (!String.IsNullOrWhiteSpace(response.Error))
        {
            throw new VB6ConnectorException(-1234567890, response.Error);
        }

        DataTable ds = GetDataTableFromJsonString(response.ResultObj.ToString());

        return ConvertNetTypetoVB6TypeHelper.ConvertToRecordset(ds);
    }
    catch (Exception Ex)
    {
        throw new VB6ConnectorException(Ex);
    }
}
  • invsTaskRequest / invsTaskResponse are intermediate classes used to standardize the sending and receiving of data for all APIs to have the same format, where:
    - invsTaskRequest specifies which Service to execute with which method and what parameters
    - invsTaskResponse stores the results obtained from that Service, or stores an Id to retrieve from Cache
  • Covert JSON to DataTable
public DataTable GetDataTableFromJsonString(string json)
{
    JToken token = JToken.Parse(json);

    if (token is JArray)
    {
        var jArrray = JArray.Parse(json);

        // Find the first array using Linq
        //var srcArray = jsonLinq.Descendants().Where(d => d is JArray).First();
        var trgArray = new JArray();
        foreach (JObject row in jArrray.Children<JObject>())
        {
            var cleanRow = new JObject();
            foreach (JProperty column in row.Properties())
            {
                // Only include JValue types
                //พวกที่เป็น Array จะถูกเอาออกไปได้
                if (column.Value is JValue)
                {
                    cleanRow.Add(column.Name, CheckDate((string)column.Value));
                }
            }

            trgArray.Add(cleanRow);
        }

        return (DataTable)JsonConvert.DeserializeObject(trgArray.ToString(), typeof(DataTable));
    }
    else if (token is JObject)
    {
        var jObject = JObject.Parse(json);
        var cleanRow = new JObject();
        var trgArray = new JArray();
        foreach (JProperty column in jObject.Properties())
        {
            // Only include JValue types
            //พวกที่เป็น Array จะถูกเอาออกไปได้
            if (column.Value is JValue)
            {

                cleanRow.Add(column.Name, column.Value);
            }
        }
        trgArray.Add(cleanRow);
        return (DataTable)JsonConvert.DeserializeObject(trgArray.ToString(), typeof(DataTable));

    }
    else if (token.Type == JTokenType.Integer)
    {
        //Convert to int
        DataTable temp = new DataTable();
        temp.Columns.Add("Result", typeof(string));
        temp.Rows.Add(token.Values());
        return temp;
    }
    else
    {
        throw new NotImplementedException("JSON not support");
    }

}
  • Next Convert DataTable to ADODB.RecordSet in ConvertNetTypetoVB6TypeHelper

Note: I cannot share all the information because this is my real work.

Project Setting

  • Add RegisterForComInterop in csproj file
<RegisterForComInterop>true</RegisterForComInterop>
  • In AssemblyInfo.cs add ComVisible and check Guid has generated
// COM, set the ComVisible attribute to true on that type.
[assembly: ComVisible(true)]

// The following GUID is for the ID of the typelib if this project is exposed to COM
[assembly: Guid("57b6cf0c-81bc-48bd-9c81-2846eea09a2a")]

Create .tlb file for VB6 to Register

- Register
  1. CMD Run As Administrator
  2. Change Directory to Path C:\Windows\Microsoft.NET\Framework\v4.0.30319
  3. Run command RegAsm.exe [PATH TO DLL]\[YOUR_DOTNET_COM].dll /register /tlb /codebase
RegAsm.exe C:\Users\chatri.ngam\Desktop\VB6TestConnector\DLL\invs.vb6connector.dll /register /tlb /codebase
  1. For VB6 Project when register process form (3) is complete, you can use file .tlb such as invs.vb6connector.dll for Reference in your VB6 code base
- Unregister
  1. CMD Run As Administrator
  2. Change Directory to Path C:\Windows\Microsoft.NET\Framework\v4.0.30319
  3. Run command RegAsm.exe [PATH TO DLL]\[YOUR_DOTNET_COM].dll /unregister

Reference


Discover more from naiwaen@DebuggingSoft

Subscribe to get the latest posts sent to your email.