CODEWITHSUNDEEP
c#sql-serverwpf
v31
v31

Reputation: 163

SQL Server LocalDB deployment on the end-users computers

I have VSTO Excel tool and I use SQL LocalDB as a data source. The connection with the database is through Entity Framework 6.1.3. I also have WPF form with a DataGrid to display the data.

Everything works perfectly on the development computer, but when I’m trying to deploy the tool with InstallShield LE, after a successful installation the WPF database form comes empty without any error message. After several attempts, finally I managed to run the database on one of the test computers. But when I've tried to install it on another one, again - empty form and no connection with the database.

Since the installation is sometimes successful, sometimes not, I think there is nothing wrong with the code or with the installation program. It must be something else.

LocalDB starts as a process only when there is a call to the database and shortly afterward it automatically shuts down. I am relatively new with the databases, but maybe starting the LocalDB service and then the attempt to make the connection for the first time takes a lot of time and that could be a reason for connection to fail.

The question is: Is it possible the timeout to be a reason for connection failure? If yes, how can I increase this timeout? What else should I do in order to avoid problems with the LocalDB installation?

I spent a lot of time developing this tool and now I cannot publish it. Any help would be really appreciated. Thank you!

Upvotes: 0

Views: 304

Answers (2)

v31
v31

Reputation: 163

Below is my App.config where I increased Connect Timeout to 3000, to be sure that this is not a problem.

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <connectionStrings>
    <add name="MDBEntities1" connectionString="metadata=res://*/MDBModel.csdl|res://*/MDBModel.ssdl|res://*/MDBModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=(LocalDB)\MSSQLLocalDB;attachdbfilename=|DataDirectory|\MapDatabase.mdf;integrated security=True;connect timeout=3000;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />
  </connectionStrings>
  <startup>
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.6.1" />
  </startup>
</configuration>

Also I’ve increased Command Timeout in the Entity Framework constructor. I’m afraid that the attempt to connect with the database is already made before this line of code. 

public MDBEntities1() : base("name=MDBEntities1")
{
    Database.CommandTimeout = 30000;
}

The strange thing for me is that once a connection has been established with a specific folder, I have no problem with this folder, even after I uninstall the app, delete the folder, and then install it again.

But if I change the folder - the database is coming empty. I need to connect the MDF file manually to the LocalDB, then delete it, and then to install the app. In this case the new installation folder will work OK like the first one. But I cannot do this on each user computer. I don’t know how to force LocalDB to add my database during the installation.

Sorry if my terminology is not correct, but I don’t have much experience with the databases. The previous version I used was SQL Server Compact and I never had these issues.

Upvotes: 0

Roger Wolf
Roger Wolf

Reputation: 7692

I had similar issues with my app that also uses LocalDB. I assume your InstallShield package reliably installs LocalDB engine; if not, add a corresponding MSM package as one of its requirements.

Yes, LocalDB starts automatically when someone tries to connect to it. You can explicitly start it first and establish SQL connection after, but I have found that it's not really necessary.

The timeout you experience is a connection timeout and this can be mitigated by setting sufficiently large value for the SqlConnectionStringBuilder.ConnectTimeout property. In my case, some of the customers had old laptops with very slow HDDs, so spinning up an instance took a while. However, 120 seconds appeared to be enough for pretty much everybody.

Of course, you can always choose an alternative path, i.e. spawn a process like

sqllocaldb.exe s "InstanceName"

and then wait for its completion. Might be more reliable in the end, even.

Upvotes: 2

Related Questions