nHibernate and SQL Server 2012 LocalDB

Question

Is it possible to use LocalDB databases with NHibernate? If yes, what should be installed/configured?

Currently when trying to use connection string like Data Source=(LocalDb)\v11.0;Initial Catalog=tst1;Integrated Security=SSPI when creating SessionFactory I get

System.Data.SqlClient.SqlException : A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) ----> System.ComponentModel.Win32Exception : The network path was not found

However, I'm able to connect to (LocalDb)\v11.0 via SQL Server Object Explorer, and Entity Framework works with that connection string. So, what am I doing wrong with NH, or it's impossible to use LocalDB with NH at the moment?

Answer 1

You might have to use the archaic syntax with nH, e.g.:

np:\\.\pipe\LOCALDB#ABB78D50\tsql\query

(For some background on why this syntax is sometimes required for connection strings, see this tip on mssqltips.com.)

I realize this isn't an ideal answer, but until nHibernate updates to officially support SqlLocalDb, it might be your only choice... hopefully I'm wrong and someone has figured out a more elegant way to connect.

Answer 2

You don't need to use np:\ as this type of connection string is a big headache for all. The address of the localdb named pipe changes frequently so you have to reflect that on your connection string.

To use NHibernate with localDB, get the latest version of NHibernate and use the following connection string:

Server=(localdb)\v11.0;Initial Catalog=<dbname>;User ID=<user>;password=<pwd>;Integrated Security=false;AttachDBFilename=<dbfile_path><dbfilename>.mdf

The trick is the AttachDBFilename.

Answer 3

Yes, it is possible to connect. Go to View -> Server Explorer -> expand Data Connections then right click on your and go to Properties. You will see the areas Identity, Conenction and Misc. In Connection area you will find the Connection String needed for .xml file where NHibernate is configured. Copy that string under the connection.string property like :

<property name="connection.connection_string">Data Source=(LocalDb)\v11.0;AttachDbFilename={path}\aspnet-{projectName}-20141201132517.mdf;Initial Catalog=aspnet-{projectName}-20141201132517;Integrated Security=True</property>

I've added NHibernate to my MVC project following this post.

Hope this help.

Answer 4

Make sure that you install this update: http://support.microsoft.com/kb/2544514

Then make sure that the database is created:

• Using Database Explorer or Sql Management Studio makes a connection to (localdb)\v11.0
• Run CREATE DATABASE [dbname]
• Create tables if necessary

Change connection string to:

Data Source=(LocalDb)\v11.0;Initial Catalog=dbname;Integrated Security=true

And run the web. It should work.

Answer 5

I use also AttachDBFilename=|DataDirectory|\Database_name.mdf in the connection string.

<configSections>
  <section name="hibernate-configuration" type="NHibernate.Cfg.ConfigurationSectionHandler, NHibernate" />
</configSections>
<hibernate-configuration xmlns="urn:nhibernate-configuration-2.2">
  <session-factory name="">
    <property name="connection.provider">NHibernate.Connection.DriverConnectionProvider</property>
    <property name="connection.driver_class">NHibernate.Driver.SqlClientDriver</property>
    <property name="connection.connection_string_name">LocalDb</property>
    <property name="dialect">NHibernate.Dialect.MsSql2008Dialect</property>
  </session-factory>
</hibernate-configuration>
<connectionStrings>
  <add name="LocalDb" connectionString="Data Source=(LocalDb)\v11.0;Initial Catalog=MyDatabase;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|\MyDatabase.mdf" providerName="System.Data.SqlClient" />
</connectionStrings>

and MyDatabase.mdf is in App_Data directory of the application