Running sql script from Windows Forms Application

Question

I am building a VB.NET desktop application in VS2013 that uses a .mdf file created in SQL Server 2012. I have the .mdf file imported in to the application. Along with the datagridviews I am using to view/modify the database tables I need to execute a SQL script when the user clicks on a button.

I have been trying several examples I've found, without any luck. I'm getting a FileLoadException error on the ExecuteNonQuery line:

An unhandled exception of type 'System.IO.FileLoadException' occurred in mscorlib.dll

Additional information: Mixed mode assembly is built against version 'v2.0.50727' of the runtime and cannot be loaded in the 4.0 runtime without additional configuration information.

The "Troubleshooting tips" section has "Make sure that the file is a valid .NET Framework assembly". I clicked on that link, but the information there isn't very helpful to me, as I really am not understanding what it's saying.

I've tried these two examples so far:

Dim sqlScript As String = "C:\HL_Time_Entry_Reporting\SQL Scripts\stage_employee_hours_load.sql"
Dim connString As String = "Server=(LocalDB)\v11.0;Integrated Security=True;AttachDbFilename=C:\HL_Time_Entry_Reporting\Database\hours_analysis.mdf"
Dim connectionString As String = connString,
    scriptText As String = sqlScript
Dim sqlConnection As New SqlConnection(connectionString)
Dim svrConnection As New ServerConnection(sqlConnection)
Dim server As New Server(svrConnection)
server.ConnectionContext.ExecuteNonQuery(scriptText)

from this site. And

Dim sqlConnectionString As String = "Server=(LocalDB)\v11.0;Integrated Security=True;AttachDbFilename=C:\HL_Time_Entry_Reporting\Database\hours_analysis.mdf"
Dim file As New FileInfo("C:\HL_Time_Entry_Reporting\SQL Scripts\stage_employee_hours_load.sql")
Dim script As String = file.OpenText().ReadToEnd()
Dim conn As New SqlConnection(sqlConnectionString)
Dim server As New Server(New ServerConnection(conn))
server.ConnectionContext.ExecuteNonQuery(script)

from this site.

One thing is, I'm not confident I have the correct connection string for this. That's the connection string I have for the datagridviews, which works fine, but is that also what I should use to connect for the sql script file as well?

UPDATE

Per request, here is the stack trace:

at System.Reflection.RuntimeAssembly.GetType(RuntimeAssembly assembly, String name, Boolean throwOnError, Boolean ignoreCase, ObjectHandleOnStack type) at System.Reflection.RuntimeAssembly.GetType(String name, Boolean throwOnError, Boolean ignoreCase) at System.Reflection.Assembly.GetType(String name, Boolean throwOnError) at Microsoft.SqlServer.Management.Common.ServerConnection.GetStatements(String query, ExecutionTypes executionType, Int32& statementsToReverse) at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType) at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand) at test.main.btnLoadMasterData_Click(Object sender, EventArgs e) in C:\Users\marky\Documents\Visual Studio 2013\Projects\Hours Analysis\Hours Analysis\Form1.vb:line 212 at System.Windows.Forms.Control.OnClick(EventArgs e) at System.Windows.Forms.Button.OnClick(EventArgs e) at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent) at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks) at System.Windows.Forms.Control.WndProc(Message& m) at System.Windows.Forms.ButtonBase.WndProc(Message& m) at System.Windows.Forms.Button.WndProc(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m) at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam) at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData) at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context) at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context) at System.Windows.Forms.Application.Run(ApplicationContext context) at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun() at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel() at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine) at test.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81 at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args) at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args) at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx) at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx) at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state) at System.Threading.ThreadHelper.ThreadStart()

UPDATE #2

As suggested, I added the startup tag in this SO question to my appconfig file but got a new error on the line "Dim server As New Server(New ServerConnection(conn))" in the second code example. It's a TypeInitializationException. Here's the info:

An unhandled exception of type 'System.TypeInitializationException' occurred in System.Data.dll

Additional information: The type initializer for 'System.Data.SqlClient.SqlConnection' threw an exception.

Stack trace:

at System.Data.SqlClient.SqlConnection..ctor() at System.Data.SqlClient.SqlConnection..ctor(String connectionString, SqlCredential credential) at System.Data.SqlClient.SqlConnection..ctor(String connectionString)
at test.main.btnLoadMasterData_Click(Object sender, EventArgs e) in C:\Users\marky\Documents\Visual Studio 2013\Projects\Hours Analysis\Hours Analysis\Form1.vb:line 210 at System.Windows.Forms.Control.OnClick(EventArgs e) at System.Windows.Forms.Button.OnClick(EventArgs e) at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent) at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks) at System.Windows.Forms.Control.WndProc(Message& m) at System.Windows.Forms.ButtonBase.WndProc(Message& m) at System.Windows.Forms.Button.WndProc(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m) at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam) at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData) at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context) at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context) at System.Windows.Forms.Application.Run(ApplicationContext context) at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun() at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel() at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine) at test.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81 at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args) at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args) at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx) at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx) at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state) at System.Threading.ThreadHelper.ThreadStart()

Answer 1

It looks like you may be using an obsolete version of the SMO library, as explained here: https://stackoverflow.com/a/11286124/109122.

Update:

If the config setting recommended at the link does not work, try this simpler one instead:

<startup useLegacyV2RuntimeActivationPolicy="true">
    <supportedRuntime version="v4.0"/>
</startup>

---

Unfortunately, it appears that the config file changes do not fix all cases.