Solved

Referencing two DLLs with same namespace in VB.NET

Posted on 2014-12-05
7
377 Views
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?
0
Comment
Question by:MasterWoodsman
  • 3
  • 2
  • 2
7 Comments
 
LVL 27

Accepted Solution

by:
Ark earned 350 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

0
 
LVL 40

Assisted Solution

by:Jacques Bourgeois (James Burger)
Jacques Bourgeois (James Burger) earned 150 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.
0
 
LVL 1

Author Comment

by:MasterWoodsman
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
MyConn.open()
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
       MyDR.read()
       MsgBox(MyDR.item("field name"))
Else
       MsgBox("Record NOT found")
End If
MyDR.close()
MyDR.dispose()
MyCmd.dispose()
MyConn.close()

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.
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 1

Author Comment

by:MasterWoodsman
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?
0
 
LVL 1

Author Closing Comment

by:MasterWoodsman
ID: 40486941
I gave both of you the solution but gave more points to Ark because it was more specific.  Thanks guys.
0
 
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
			Me.Add(project)
		End While

		projectData.Close()

	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)
			Else
				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.
0
 
LVL 27

Expert Comment

by:Ark
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.
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Replacing HTML tags in Textarea/Textbox 5 31
Crystal Report for VS2013 17 42
MySQL left join performance 4 15
Close word object 13 24
Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php (http://lmgtfy.com?q=how+easy+is+php)  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

746 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now