.NET

ASP.NET - Miscellaneous info

ASPNETDB.MDF info

The ASPNETDB.MDF is the default database for using the ASP.NET Application Services, which includes profiles, roles, membership and more. You can use a different database, by using the aspnet_regsql.exe tool: http://msdn.microsoft.com/en-us/library/ms229862.aspx

ASPNETDB.MDF gets automatically generated whenever you use one of the application services (profiles, membership, ...). The generated .mdf file is a database (SQL 2005 express). It stores the information you store in your profile or membership or roles or ... when you use the standard sql providers in ASP.NET.

You can store membership info somewhere else if you want to: Create Membership tables in another database than the standard aspnetdb.mdf. Or run aspnet_regsql -W to get a wizard approach.

MSSQLLocalDB (Visual Studio Community 2015)

Connection string parameters info:

Data Source=(LocalDb)\MSSQLLocalDB;

Path to the MSSQLLocalDB exe file:
C:\Program\Microsoft SQL Server\130\Tools\Binn\SQLLocalDB.exe

The MSSQLLocalDB instances are stored in this location:
C:\Users\User\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances\MSSQLLocalDB

AttachDbFilename=|DataDirectory|\...

An ASP.NET application resolves |DataDirectory| to the "<application root>/app_data" folder.

Other info

ASP.NET Session State Overview

http://stackoverflow.com/questions/35145547/how-to-connect-to-database-created-in-vs2015-sql-server-data-tools
https://xpert360.wordpress.com/2015/08/11/visual-studio-2015-and-localdb-instance-for-lightswitch/
http://stackoverflow.com/questions/32830336/how-to-add-localdb-to-visual-studio-2015-communitys-sql-server-object-explorer
http://stackoverflow.com/questions/26977106/visual-studio-2013-does-not-create-sql-server-2014-localdb-database

https://msdn.microsoft.com/en-us/library/hh510202(v=sql.120).aspx
- SQL Server 2014 Express LocalDB

https://msdn.microsoft.com/en-us/library/ms143547.aspx
- File Locations for Default and Named Instances of SQL Server

https://msdn.microsoft.com/en-us/library/8s2t4x15.aspx
https://msdn.microsoft.com/library/8s2t4x15(v=VS.140).aspx
- Add new data sources (Visual Studio 2015)

http://sqlcetoolbox.codeplex.com/documentation
https://visualstudiogallery.msdn.microsoft.com/0e313dfd-be80-4afb-b5e9-6e74d369f7a1?SRC=VSIDE
- SQL Server Compact/SQLite Toolbox

https://blogs.msdn.microsoft.com/gaurav/2013/12/21/deployment-of-sql-express-localdb-on-iis/
https://blogs.msdn.microsoft.com/aspnetue/2012/08/14/choosing-a-sql-server-edition-for-asp-net-web-application-development/
http://stackoverflow.com/questions/13571331/is-it-normal-to-use-localdb-in-production
https://blogs.msdn.microsoft.com/sqlexpress/2011/12/08/using-localdb-with-full-iis-part-1-user-profile/
https://blogs.msdn.microsoft.com/sqlexpress/2011/12/08/using-localdb-with-full-iis-part-2-instance-ownership/

General Webapp environment info:

Scenario: The default development environment.
Recommended SQL Server Edition and Web Server: LocalDB with IIS Express.

Scenario: First choice if you want a closer match to your production environment.
Recommended SQL Server Edition and Web Server: The same edition and version of SQL Server as in production, with IIS.

Scenario: Second choice if you want a closer match to your production environment and the first choice is not feasible.
Recommended SQL Server Edition and Web Server: SQL Server Express 2012 with IIS.

To top

https://www.asp.net/mvc/overview/getting-started/introduction/creating-a-connection-string
http://stackoverflow.com/questions/15853382/how-to-manually-create-a-mdf-file-for-localdb-to-use
https://www.mssqltips.com/sqlservertip/2694/getting-started-with-sql-server-2012-express-localdb/

https://www.asp.net/mvc/overview/older-versions-1/getting-started-with-mvc/getting-started-with-mvc-part4
- Creating a Database

https://www.tutorialspoint.com/asp.net_mvc/asp.net_mvc_databases.htm
http://kristianguevara.net/creating-your-asp-net-mvc-5-application-from-scratch-for-beginners-using-entity-framework-6-and-identity-with-crud-functionalities/
https://www.asp.net/mvc/overview/getting-started/getting-started-with-ef-using-mvc/migrations-and-deployment-with-the-entity-framework-in-an-asp-net-mvc-application
https://forums.asp.net/t/1973638.aspx?I+can+t+find+the+database+for+asp+net+Identity+which+is+the+new+way+to+handle+security
http://dba.stackexchange.com/questions/121150/how-do-i-connect-to-localdb-from-sql-server-2014-express

#####

http://stackoverflow.com/questions/11628316/sql2012-localdb-how-to-check-in-c-sharp-if-it-is-currently-installed

Check if LocalDB is installed, by looking for this registry key:
[HKLM\SOFTWARE\Microsoft\Microsoft SQL Server Local DB\Installed Versions\11.0]

SQLNCLI11 - check the file version and presence of this file: C:\WINDOWS\system32\sqlncli.dll

#####

https://docs.microsoft.com/en-us/aspnet/core/tutorials/first-mvc-app/start-mvc

https://www.safaribooksonline.com/library/view/aspnet-mvc-5/9781491914373/ch04.html
- ASP.NET MVC 5 with Bootstrap and Knockout.js-Chapter 4. Working with a Database

To top

http://dba.stackexchange.com/questions/121150/how-do-i-connect-to-localdb-from-sql-server-2014-express
http://www.tugberkugurlu.com/archive/managing-sql-server-express-localdb-instances-through-sqllocaldb-exe-utility-tool
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/b75bef7e-13f3-4658-8d43-9df12ab4c320/connecting-localdb-using-sql-server-management-studio-express?forum=sqlexpress
https://www.asp.net/mvc/overview/deployment/visual-studio-web-deployment/preparing-databases

https://www.asp.net/whitepapers/aspnet-data-access-content-map#sqlserver
- Working with SQL Server Databases

https://msdn.microsoft.com/en-us/library/ms178359.aspx#sqlserver
- SQL Server Editions

https://msdn.microsoft.com/en-us/library/hh212961(v=sql.120).aspx
- SqlLocalDB Utility

=====

SQL Server Express LocalDB (commonly referred to as just "LocalDB") is not a service-based application: it does not show up in "Services",nor does it start up when the machine starts or even stay running until manually shut-down.

LocalDB is user-mode background process. It starts either when the first request to connect comes in (assuming the request is using the "(localdb)\InstanceName" syntax) or when explicitly started via the SqlLocalDB.exe command-line utility.

So, if you know the instance name, just try to connect and the first time will take a moment while it starts up, but then subsequent connections (assuming consistent activity) will be quick.

If you don't know the name of the instance, you can get the list by using the SqlLocalDB.exe utility as follows:

C:\>SqlLocalDB i

That will return a list of instances that your Windows login has access to. If an instance name starts with .\ then it is a shared instance that other users can access as well (the default is that instances are private to the login that created them).

From there, you can get more info (including whether the LocalDB instance is "Running" or "Stopped") by doing:

C:\>SqlLocalDB i {InstanceName}

And you can start it by doing:

C:\>SqlLocalDB s {InstanceName}

Once the last connection to a LocalDB instance is closed, the instance will automatically stop after about 5 minutes (this is the default setting but can be configured to be longer). The delayed shutdown is to prevent needing to wait for the startup again for connections that come in shortly after the last connection ended.

LocalDB always has a "default" instance that is created when installing, even if you never use it. In SQL Server 2012 Express LocalDB, the default instance name is v11.0. In the 2014 version of LocalDB, the default instance name is MSSQLLocalDB. And, just as a period "." can be used to connect to a non-LocalDB default instance:

C:\>SQLCMD -S . -E -Q "SELECT @@SERVERNAME;"

it can also be used with the "(LocalDB)\InstanceName" syntax to connect to the default instance:

C:\>SQLCMD -S (LocalDB)\. -E -Q "SELECT @@SERVERNAME;"

Please note that you can only have one default instance of LocalDB specified as being the default instance. Meaning, if you have both the 2012 and 2014 versions of LocalDB installed, using (LocalDB)\. will refer to the MSSQLLocalDB instance and not the v11.0 instance.

=====

=====

Its March 2014 and I have an update for SQL Server 2014 Express LocalDB:

Use ServerName = (LocalDb)\MSSQLLocalDB to connect to the default LocalDb 2014 instance with SQL Server Management Studio. Using (LocalDb)\V11.0 does not work! using (LocalDb)\MSSQLLocalDB also works as the server name when creating an ODBC data source with the V11 Native Client drivers.

How did I find this out ? Browsing the internet I found that the executable for LocalDb is: SqlLocalDb.exe Executing the command:> where SqlLocaldb.exe ;revels the location of the server executable in:

"C:\Program Files\Microsoft SQL Server\120\Tools\Binn\SqlLocalDB.exe"

You can get more help on SqlLocaldb.exe with the command:> SqlLocaldb -?

Executing the command:> SqlLocalDB.exe info MSSQLLocalDB ;Will yield the the named pipe that the (LocalDb)\MSSQLLocalDB instance is configured for. in my case: Instance pipe name: np:\\.\pipe\LOCALDB#E965B440\tsql\query. Leave out the leading "np:", so the pipe name I used was:

\\.\pipe\LOCALDB#E965B440\tsql\query

It turns out that (LocalDb) is a build in alias for the following directory:

"C:\Users\<user>\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances\"

And the default LocalDb instance created during installation is found in:

"C:\Users\<user>\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances\MSSQLLocalDB"

A new LocalDb instance created with the command:> SqlLocalDb.exe create <NewInstanceName> ;is stored in:

"C:\Users\<user>\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances\<NewInstanceName>

This new instance can be connected to with ssms by specifying ServerName=(LocalDb)\<NewInstanceName>

=====

Example path to project Microsoft SQL Server Local DB folder:
C:\Users\User\source\repos\WebApplication\WebApplication\App_Data

=====

SQL Server Express LocalDB

The sample application uses SQL Server Express LocalDB. SQL Server Express is the free edition of SQL Server. It is commonly used during development because it is based on the same database engine as full versions of SQL Server. You can test with SQL Server Express and be assured that the application will behave the same in production, with a few exceptions for features that vary between SQL Server Editions.

LocalDB is a special execution mode of SQL Server Express that enables you to work with databases as .mdf files. Typically, LocalDB database files are kept in the App_Data folder of a web project. The user instance feature in SQL Server Express also enables you to work with .mdf files, but the user instance feature is deprecated; therefore, LocalDB is recommended for working with .mdf files.

Typically SQL Server Express is not used for production web applications. LocalDB in particular is not recommended for production use with a web application because it is not designed to work with IIS.

In Visual Studio 2012, LocalDB is installed by default with Visual Studio. In Visual Studio 2010 and earlier versions, SQL Server Express (without LocalDB) is installed by default with Visual Studio; that is why you installed it as one of the prerequisites in the first tutorial in this series.

For more information about SQL Server editions, including LocalDB, see the following resources Working with SQL Server Databases

Creating a Connection String and Working with SQL Server LocalDB
Work with SQL in ASP.NET Core
How can I find out the location of my (localdb) SQL Server 2012 database and back it up?

Installing ASP.NET Membership Database in SQL Server

Create ASP.Net membership database structure in existing database
Examining ASP.NET's Membership, Roles, and Profile - Part 3

If you have access to the database, run the following commands in command line:

cd "C:\Windows\Microsoft.NET\Framework\v2.0.50727" aspnet_regsql

That will start a guide that will help you add all the tables and sprocs to any table you can access from that computer.

-- To use Windows Authentication (i.e., a "trusted connection"), use:

aspnet_regsql.exe -S <server> -E -d <database> -A all

-- To use SQL Server credentials (a UserID and Password), use:

aspnet_regsql.exe -S <server> -U <login id> -P <password> -d <database> -A all

There is also a wizard to help you through the steps

The system database files for the database are stored in the users' local AppData path which is normally hidden. For example C:\Users\--user--\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances\LocalDBApp1. User database files are stored where the user designates, typically somewhere in the C:\Users\user\Documents\ folder.

To top