This assumes that you already have Microsoft SQL Server installed. If you are using the Community Edition of Visual Studio, only the Express version of SQL Server will work.
- In Visual Studio, in the right panel (solution explorer), right-click the name of our site and select Add > Add ASP.NET Folder > App_Data or select Add > New Folder, and name the new folder App_Data.
- With the App_Data folder highlighted, press Ctrl + Shift + A (or right-click and select Add > New Item).
- In the Add New Item dialog box, select C# or Visual Basic > Data >SQL Server Database. Select Add.
Note: If you get error Connections to SQL Server database files (.mdf) require LocalDB or SQL Server Express, go here.
- In Solution Explorer should now be a file named database1.mdf. Right-click database1.mdf and select Open. A new tab appears in the left panel titled Server Explorer.
- In Server Explorer, database1.mdf should have a red X. This means the database has not yet been configured. Right-click database1.mdf and select Modify Connection.
- In the Modify Connection dialog box, with Use Windows Authentication selected, select Test Connection. If the connection is successful, the database1.mdf file should no longer have the red X and should instead have the plug icon. Expanding Database1.mdf will show the available SQL Server folders and files.
Follow these steps to use SQL Server Authentication:
- In Service Explorer, right-click database1.mdf and select Modify Connection. Do the following:
- Select Change and select Microsoft SQL Server (SqlClient)
- Enter the name of your SQL Server (example: server1\\database1)
- Select SQL Server Authentication and enter your SQL Server username and password
- Select Test Connection. The connection should be successful. In Server Explorer, the file should no longer be named Database1.mdf. Instead, in this example, the file would be named server1\\database1.master.dbo. In the App_Data folder, the file is still named Database1.mdf.
Follow these steps to add additional SQL Server database connections to Visual Studio:
- In the list of small icon at the top of Server Explorer, select the Connect to Database icon.
- In the Choose Data Source dialog box, select Microsoft SQL Server and select Continue
- In the Add Connection dialog box do the following:
- The server name in Visual Studio needs to be exact the same as the server name being uses when connecting to SQL Server
- Select SQL Server Authentication. Enter your SQL Server Authentication username and password.
- Select a Database
- Click the Test Connection button to ensure the connection is successful
Follow these directions to show the data in SQL Server on a Web page.
- On one of our pages, go to Design View. In Server Explorer, expand Tables and drag and drop one of your tables into Design View.
- In the Grid View tasks Window, check-mark Enable Paging, Enable Sorting and Enable Selection.
If we view our page in a Web browser, our SQL data will be displayed.
- In the top menu of Visual Studio, select Tools > Options
- In the Options dialog box, select Database Tools > Data Connections
- ??? (LocalDB)\\MSSQLLocalDB