Required reading:
Read this MSDN article: Best Practices for Assembly Loading
In short:
It looks like you're assuming the System.Data.SqlClient.SqlConnection class always exists inside System.Data.SqlClient.dll.
This is an incorrect assumption:
- A NuGet package is not a .NET assembly.
- A NuGet package does not map 1:1 with a .NET assembly nor namespaces.
- A NuGet package can contain multiple assemblies.
- A NuGet package can contain zero assemblies.
- A NuGet package can contain assemblies that don't have any types defined in them at all!
- They could be assemblies that only contain Resources or other embedded items
- They could be assemblies that use Type-Forwarding to redirect types that previously existed in this assembly other assemblies. Only the JIT uses this feature, however, not reflection.
- And those "forwarded-to" assemblies don't have to exist in NuGet packages either: they can be "in-box" assemblies built-in to the runtime like
mscorlib.dll and System.Data.dll).
- They could be stub assemblies that don't provide any types when those types are already provided by the Base Class Library - the NuGet package only exists to provide those types for other platforms.
- This is the situation you're dealing with.
- A NuGet package can have very different effects based on the project's target (.NET Framework, .NET Standard, .NET Core, etc)
Your code cannot assume that a specific class is located in a specific assembly file - this breaks .NET's notion of backwards-compatibility through type-forwarding.
In your case...
In your case, your code assumes System.Data.SqlClient.SqlConnection exists inside an assembly file named System.Data.SqlClient. This assumption is false in many cases, but true in some cases.
Here is the top-level directory structure of the System.Data.SqlClient NuGet package:
Observe how inside the package there are subdirectories for each supported target (in this case, MonoAndroid10, MonoTouch10, net46, net451, net461, netcoreapp2.1, netstandard1.2, etc). For each of these targets the package provides different assemblies:
When targeting .NET Framework 4.5.1, .NET Framework 4.6 or .NET Framework 4.6.1 the files from the net451, net46 and net461 directories (respectively) will be used. These folders contain a single file named System.Data.SqlClient.dll which does not contain any classes. This is because when you target the .NET Framework 4.x, the System.Data.SqlClient (namespace) types are already provided by the Base Class Library inside System.Data.dll, so there is no need for any additional types. (So if you're building only for .NET Framework 4.x then you don't need the System.Data.SqlClient NuGet package at all.
Here's a screenshot of the insides of that assembly file using the .NET Reflector tool (a tool which lets you see inside and decompile .NET assemblies) if you don't believe me:
When targeting other platforms via .NET Standard (i.e. where System.Data.dll isn't included by default, or when System.Data.dll does not include SqlClient) then the NuGet package will use the netstandard1.2, netstandard1.3, netstandard2.0 directories, which does contain a System.Data.SqlClient.dll that does contain the System.Data.SqlClient namespace with the types that you're after. Here's a screenshot of that assembly:
And other platforms like MonoAndroid, MonoTouch, xamarinios, xamarintvos, etc also have their own specific version of the assembly file (or files!).
But even if you know your program will only run on a single specific platform where a specific NuGet package contains an assembly DLL that contains a specific type - it's still "wrong" because of type-forwarding: https://learn.microsoft.com/en-us/dotnet/framework/app-domains/type-forwarding-in-the-common-language-runtime
While Type-Forwarding means that most programs that reference types in certain assemblies will continue to work fine, it does not apply to reflection-based assembly-loading and type-loading, which is what your code does. Consider this scenario:
- A new version of the
System.Data.SqlClient NuGet package comes out that now has two assemblies:
System.Data.SqlClient.dll (which is the same as before, except SqlConnection is removed but has a [TypeForwardedTo] attribute set that cites System.Data.SqlClient.SqlConnection.dll).System.Data.SqlClient.SqlConnection.dll (the SqlConnection class now lives in this assembly).
- Your code will now break because it explicitly loads only
System.Data.SqlClient.dll and not System.Data.SqlClient.SqlConnection.dll and enumerates those types.
Here be dragons...
Now, assuming you're prepared to disregard all of that advice and still write programs that assume a specific type exists in a specific assembly, then the process is straightforward:
// Persistent state:
Dictionary<String,Assembly> loadedAssemblies = new Dictionary<String,Assembly>();
Dictionary<(String assembly, String typeName),Type> typesByAssemblyAndName = new Dictionary<(String assembly, String typeName),Type>();
// The function:
static Type GetExpectedTypeFromAssemblyFile( String assemblyFileName, String typeName )
{
var t = ( assemblyFileName, typeName );
if( !typesByName.TryGetValue( t, out Type type ) )
{
if( !loadedAssemblies.TryGetValue( assemblyFileName, out Assembly assembly ) )
{
assembly = Assembly.LoadFrom( assemblyFileName );
loadedAssemblies[ assemblyFileName ] = assembly;
}
type = assembly.GetType( typeName ); // throws if the type doesn't exist
typesByName[ t ] = type;
}
return type;
}
// Usage:
static IDbConnection CreateSqlConnection()
{
const String typeName = "System.Data.SqlClient.SqlConnection";
const String assemblyFileName = "System.Data.SqlClient.dll";
Type sqlConnectionType = GetExpectedTypeFromAssemblyFile( assemblyFileName, typeName );
Object sqlConnectionInstance = Activator.CreateInstance( sqlConnectionType ); // Creates an instance of the specified type using that type's default constructor.
return (IDbConnection)sqlConnectionInstance;
}
