Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Referencing two DLLs with same namespace in VB.NET

Posted on 2014-12-05
Medium Priority
Last Modified: 2014-12-08
Hello Experts,

I'm rewriting an old program I originally wrote in VB6 with VB.Net.  My old program referenced a MySQL 4.0.3  database.  I am in the process of migrating everything to MySQL 5.5.  The program is quite large (for me) and there are many tables to restructure.  I will be working on this migration for months to come. (I cannot simply do a one-time upgrade of the MySQL tables from 4 to 5.5 for multiple reasons; that would be beautiful but not possible)

So for now I need to be able to access BOTH versions of MySQL from the same VB.NET application.

Here comes the problem.  The current vb.net mysql connector cannot access any version of MySQL below 5.0.  The older one is too old and incompatible with many of the automation features of VB.NET.  So I am forced to use an older one alongside the current one.  My goal is to simply have two references to the DLLs.  But this is proving impossible as they both have the same namespace and I get ambiguity problems.

They also had the same filename so I created a copy of the old one and renamed it.

I scoured the net and found that a system exists to resolve this problem in C# using "extern alias" but that no equivalent solution exists for VB.  I also saw several people suggest using a class wrapper for the old one, but I'm unsure how to do this without recreating all the classes.

Any solutions?
Question by:MasterWoodsman
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
LVL 28

Accepted Solution

Ark earned 1400 total points
ID: 40484317
You can try reflection (MS Word example)
Dim word12 = System.Reflection.Assembly.LoadFile("c:\Program Files (x86)\Microsoft Visual Studio 10.0\Visual Studio Tools for Office\PIA\Office12\Microsoft.Office.Interop.Word.dll")
Dim type12 = word12.GetType("Microsoft.Office.Interop.Word.ApplicationClass")
Dim app12 = Activator.CreateInstance(type12)
Dim doc12 = app12.Documents.Add
app12.Visible = True

Open in new window

LVL 40

Assisted Solution

by:Jacques Bourgeois (James Burger)
Jacques Bourgeois (James Burger) earned 600 total points
ID: 40484725
Create 2 dlls of your own, with different namespaces, one for each of the MySQL versions.

Do all your database access in functions in these dlls that return the results.

Reference these 2 dlls from your application and call these functions when you need to retrieve.

This is a good thing to do anyway even when you do not have your problem. By putting all your data access in a dll, if you ever change you database backend, you only not to change make changes to the dll. And the same dll can be used by many applications, so you can save a lot of time if you write a second application that deals with the same database. When come times to change databases or increase performance, you simply do the job in the dll without having to touch the applications.

Author Comment

ID: 40486910
Thanks Ark,

I tried your solution as follows and it works:
Dim MySQL4Assembly As System.Reflection.Assembly = System.Reflection.Assembly.LoadFile("Path To Mysql dll")
Dim TypeConn As System.Type = MySQL4Assembly.GetType("MySql.Data.MySqlClient.MySqlConnection")
Dim MyConn = Activator.CreateInstance(TypeConn)
Dim MyCmd As Object
Dim MyDR As Object
Dim SQL As String = ""

MyConn.connectionstring = OldConnectionString
SQL = "SELECT * FROM mytable WHERE somecriteria"
MyCmd = MyConn.createcommand()
MyCmd.commandtype = System.Data.CommandType.Text
MyCmd.commandtext = SQL
MyDR = MyCmd.executereader()
If MyDR.hasrows = True Then
       MsgBox(MyDR.item("field name"))
       MsgBox("Record NOT found")
End If

Open in new window

The only negative with this system is that Intellisense won't work since this is late binding.  This can be a pain depending on how much I will need it.
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.


Author Comment

ID: 40486934
Thanks for you answer Jacques,

Your suggestion makes sense, although it would take some work to get it done.  I will give it some thought.  Are there any examples on the web that I can follow for this?

Author Closing Comment

ID: 40486941
I gave both of you the solution but gave more points to Ark because it was more specific.  Thanks guys.
LVL 40
ID: 40487651
Your search for code would be as good as mine.

Here is a short sample from a woodworking application I am currently working on.

The application works with 2 dlls. One deals with the different classes I use when writing in a woodworking application. I can then reuse that in all my woodworking applications. The second one deals only with database access. If I change the database, or have the application run on different databases depending on the location or the customer, I simply design different versions of the same dll, making sure that they have the same classes, with the same properties and methods. I can then switch from one database to another simply by changing a reference in my project.

The application calls the first dll, that calls the second one, that returns data to the first, that transforms it to return it to the application.

Here is a piece of code in the first dll. It gets the content of a table containing a list of Projects and fills a collection of Project objects with the data in the database:

	Private Sub New(ByVal active As Boolean, all As Boolean)

		'Builds the collection from data in the database
		'				 with optional filter between the active and inactive projects
		'Params: active	If True, only the active projects are returned
		'								If False, only the inactive projects are returned
		'				 all		All the projects are returned

		Dim projectData As Common.DbDataReader = WoodworkData.GetProjects(active, all)

		While projectData.Read
			Dim project As New Project(projectData.GetString(1))
			With project
				.ID = projectData.GetInt32(0)
				.SubTitle = projectData.GetString(2)
				.Active = projectData.GetBoolean(3)
				.DateStart = projectData.GetDateTime(4)
				.DateEnd = projectData.GetDateTime(5)
				.DesignTime = TimeSpan.FromMinutes(projectData.GetInt32(6))
				.PrototypeTime = TimeSpan.FromMinutes(projectData.GetInt32(7))
				.CutTime = TimeSpan.FromMinutes(projectData.GetInt32(8))
				.AssemblyTime = TimeSpan.FromMinutes(projectData.GetInt32(9))
				.FinishingTime = TimeSpan.FromMinutes(projectData.GetInt32(10))
				.ExtraTime = TimeSpan.FromMinutes(projectData.GetInt32(11))
				.Comments = projectData.GetString(12)
				.Photo = projectData.GetString(13)
				.ProjectDir = projectData.GetString(14)
				.Height = projectData.GetFloat(15)
				.Width = projectData.GetFloat(16)
				.Depth = projectData.GetFloat(17)
				.LastChanged = projectData.GetDateTime(18)
				.Finish = projectData.GetString(19)
				.Coats = projectData.GetInt32(20)
				.FinishingNotes = projectData.GetString(21)
				.Units = projectData.GetByte(22)
				.HasChanged = False
			End With	'Project
		End While


	End Sub

Open in new window

Note that this code does not call the database. This job is done in the second dll, the one that has many versions, one for each type of database, through the WoodworkData.GetProjects(active, all) methods. No matter wich database I have in the background, each version of my data access dll has that method and returns a Common.DbDataReader, a database insensitive object from the framework that can read the content of a table or the result of a request line by line. The method that does the job in the SQL Server version of my second dll is :

	Friend Shared Function GetProjects(active As Boolean, all As Boolean) As DbDataReader

		'Returns a DataReader that reads the projects from the database, 
		'				 with optional filter between the active and inactive projects
		'Params: active	If True, only the active projects are returned
		'								If Flase, only the inactive projects are returned
		'				 all		All the projects are returned
		'Do not forget to close the DataReader when done

		Using cmd As New SqlCommand("ProjectGet", CreateConnection)

			cmd.CommandType = Data.CommandType.StoredProcedure
			If all Then
				cmd.Parameters.AddWithValue("@Active", DBNull.Value)
				cmd.Parameters.AddWithValue("@Active", active)
			End If
			Return cmd.ExecuteReader(CommandBehavior.CloseConnection)

		End Using	'cmd

	End Function

Open in new window

Making a version of that for the Access, Oracle or DB2 would not be very difficult, I would simply use a OleDBCommand object instead of a SqlSommand object. As long as the database have the same structure and both have the ProjectGet stored procedure/query, I can switch the dlls. It simply requires good design from the start.

From the application, all I have to do is

Dim projects As New ProjectCollection(True, False) 'Or any other combination of required parameters

and then work with the collection

MessageBox.Show("You have selected project number " & projects(x) & " titled: " & projects(x).Name

The code in the application and the code in the first dll stays the same, no matter what type of database I work with. Only the second dll changes. But you could also reference 2 similar dlls by giving them different namespaces, just as Microsoft did by creating SqlClient, OracleClient, ODBC and OleDB namespaces for different types of database access with similar if not exactly the same code.

Sorry, I do not have time to revise my post. I hope it is clear, does not have too many typos, and that it helps you understand the concept.
LVL 28

Expert Comment

ID: 40488300
You can write your code using reference to an appropriate MySql dll. In this case intellisense will work OK. When need just remove reference - error window will show errors. Replace early binding declarations with reflection where need.

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question