Referencing two DLLs with same namespace in VB.NET

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 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?
Who is Participating?
ArkConnect With a Mentor Commented:
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

Jacques Bourgeois (James Burger)Connect With a Mentor PresidentCommented:
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.
MasterWoodsmanAuthor Commented:
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.
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

MasterWoodsmanAuthor Commented:
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?
MasterWoodsmanAuthor Commented:
I gave both of you the solution but gave more points to Ark because it was more specific.  Thanks guys.
Jacques Bourgeois (James Burger)PresidentCommented:
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.
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.