How do you setup a database and connect to it using Razor web pages in Visual Studio?

Question

I've looked and looked but could not come up with a working solution.

As an intermediate with C# and decent with HTML and things, I thought I'd fool around with Razor Web Pages and make a simple website. I was hoping to create a database with a list of all the titles and descriptions of each page. Then I could easily change all instances and references to each html file to stay DRY. But I can't seem to get anything but exceptions from the SQL table.

What I tried using Visual Studio 2015:

Create Database.mdf with a simple table
Connect to Database using Server Explorer

Add connectionStrings to Web.config

<connectionStrings>
    <add name="Database" connectionString="Data Source=|DataDirectory|\Database.mdf" providerName="System.Data.SqlClient" />
</connectionStrings>

Make sure all WebMatrix dll packages are installed with NuGet.

Create default.cshtml

@using WebMatrix.Data
@using System.Data.SqlClient
<Html><body> etc...
@{
    var db = Database.Open("Database");
    try
        {
            db.Query("SELECT * FROM Table");
        }
        catch (SqlException odbcEx)
        {
            System.Diagnostics.Debug.WriteLine("It is an Exception: " + odbcEx);
        }
  }
</body><Html>

Run in Microsoft Edge

After about 10 seconds, the website appears on localhost. But there is results from the table, only an exception in the Output:

It is an Exception: System.Data.SqlClient.SqlException (0x80131904): 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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
   at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling)
   at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
   at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
   at System.Data.SqlClient.SqlConnection.Open()
   at WebMatrix.Data.Database.EnsureConnectionOpen()
   at WebMatrix.Data.Database.<QueryInternal>d__0.MoveNext()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at WebMatrix.Data.Database.Query(String commandText, Object[] parameters)
   at ASP._Page_Default_cshtml.Execute() in c:\Users\Person\OneDrive\Documents\Website\Default.cshtml:line 29

I've searched online and realized that since WebMatrix is out dated, there aren't to many solutions for Visual Studio. Razor doesn't appear to be widely used at this time--as far as I can tell.

I tried changing the connectionStrings to a few different things but with no luck. How can I get this to work?

score 3 · Accepted Answer · edited Jun 20 '20 at 09:12

The Fix:

I believe the problem is with your connection string. A connection string needs to specify a SQL Server. In this case, you have just specified a file path. You need a database server to actually serve that mdf file.

This SO question should show you how to obtain a connection string from an existing server explorer connection:

Then you can see the connection string in the properties of the connected server (choose the connection and press F4 or Alt+Enter or choose Properties on the right click menu).

Explanation:

Here's an explanation of why your current connection string may not be working the way you intended:

It is likely that when you are "connecting" to your mdf file through server explorer, that it is using LocalDb. LocalDb is an on demand service that ships with Visual Studio and allows you to easily develop apps, like you are trying to do, without running a full instance of Sql Server.

Unfortunately, I don't have a running example right this moment with an attached mdf file, but I believe something like this will do the job for you, I grabbed this string from MSDN's article on connection strings

<add name="ConnectionStringName"
    providerName="System.Data.SqlClient"
    connectionString="Data Source=(LocalDB)\v11.0;AttachDbFileName=|DataDirectory|\DatabaseFileName.mdf;InitialCatalog=DatabaseName;Integrated Security=True;MultipleActiveResultSets=True" />

There are a couple important bits here:

Data Source=(LocalDB)\v11.0 - This specifies the start-on-demand localdb instance to use. Note that the version, v11.0 in this string, varies from installation to installation, and you should find the correct version for your string. (You should be able to find it easier below)
AttachDbFilename=|DataDirectory|\DatabaseFilename.mdf - this tells LocalDb to load that database file. Alternatively, localdb persists database files in your user home directory, or AppData directory somewhere.
Integrated Security=true - A connection string needs an authentication scheme. Two possible options are Integrated Security, which uses your windows credentials to attempt access to your database, which works well with many things, LocalDb being one of them. Another option is to use sql authentication and a sql name and password to connect.

Thank you, it works perfectly now! I've been struggling with this for a while now. — Stephen, Sep 05 '16 at 01:31

How do you setup a database and connect to it using Razor web pages in Visual Studio?

1 Answers1

The Fix:

Explanation: