Lesson 59 - Creating a Database in Visual Studio

Tutorial Series: Free C# Fundamentals via ASP.NET Web Apps

Previous Article  |  Next Article


Get GitHub Code

During the lessons that covered Application Architecture, we referred to the three typical layers of concern including Domain, Presentation, and Persistence. We haven’t had the chance to cover persistence in detail, but now we will be able to demonstrate it when using a database to hold long-term, persistent data. A database can run locally or remotely.

Step 1: Create a New Project

For this lesson, create a typical ASP.NET application, called it “LocalDBExample.” Right-click the project name in the Solution Explorer and select from the menu:

Add > Item

Step 2: Create and Add a Database

From the resulting dialog, navigate to Visual C# > Data and add a SQL Server Database, found amongst these templates. Name it “ACME.mdf” to signify a company name:


cs-asp-059---creating-a-database-in-visual-studio.001


After that, Visual Studio will ask you whether or not you want to create an “App_Data” folder to store this database within your project. Affirm that you do want to do that, and then you will see the database in the Solution Explorer:


cs-asp-059---creating-a-database-in-visual-studio.002


Step 3: Manage the Database via the Server Explorer Window

Right-click on the “ACME.mdf” file and select “Open” to view the contents of the database in the Server Explorer Window:


cs-asp-059---creating-a-database-in-visual-studio.003


If the Server Explorer Window is not visible, access it through the keyboard shortcut (Ctrl+Alt+S) or the Visual Studio menu:

View > Server Explorer


cs-asp-059---creating-a-database-in-visual-studio.004


Notice from the Server Explorer that Visual Studio has automatically created a Data Connection to ACME.mdf within our solution. This is shown by a green “plug” icon that indicates that the connection has been established to the database within your project. If you do not see this indicator, it could mean that the connection is not being made in which case you will have to troubleshoot it or wait a few seconds for Visual Studio to make the connection:


cs-asp-059---creating-a-database-in-visual-studio.005


Step 4: Design a Database Table

From within the Server Explorer, we can start designing a table for storing values. You can think of a table as the rough equivalent of an Excel spreadsheet where you have rows of information, as well as columns and headers, that connote the meaning of that structured information. From here, right-click on “Tables” and select “Add New Table”:


cs-asp-059---creating-a-database-in-visual-studio.006


This brings us to the Table Design view. From here, change the table name to (1) “Customers,” the Id to (2) “CustomerId,” and also change the Data Type to (3) “uniqueidentifier” which will allow us to use a unique GUID for each customer in the database. Also, make sure that “Allow Nulls” remains unchecked. This will ensure that each row of customer data is populated with values, avoiding a potential runtime error:


cs-asp-059---creating-a-database-in-visual-studio.007


Step 5: Understanding Database Data Types

Let’s round out our table with some more data points that can store information about our customers. We chose “varchar” as the Data Type because it stores Unicode characters – meaning that we can use non-English characters if we have international customers. The value in the parentheses after varchar signifies the maximum allowed characters (“MAX” means there is virtually no limit). Also, checkmark “Allow Nulls” for the Notes data point since it is not essential to have filled out for each and every customer:


cs-asp-059---creating-a-database-in-visual-studio.008


Tip:

There’s probably no country in the world that would have a potential of 50 characters for a postal code, but we're going to go ahead and leave that now. Typically, however, you would want to restrict the stored data only to the precisely necessary type, and number of characters, needed to represent all possible values.

Step 6: Run Built-In Methods to Populate Table Entries

Notice that, under the “Default” entry for the CustomerId, you can run methods available to you. In this case, we will want to run a built-in method to SQL Server called newid(), which will ensure that if we don't supply a GUID whenever a new customer is created, one will be generated for us.


cs-asp-059---creating-a-database-in-visual-studio.011


Step 7: Save Table Design Query or Update it Immediately

If we were to try to save our changes right now, Visual Studio would try to save this script that we “designed” here:


cs-asp-059---creating-a-database-in-visual-studio.012


This might be preferable in a development environment where you may want to easily change the database – for, say, testing purposes – but for now let’s just build the database using these settings defined by the script we created. You can do that by clicking on the “Update” button:


cs-asp-059---creating-a-database-in-visual-studio.013


Next, click on “Update Database” and if everything was done correctly it should have successfully created the database:


cs-asp-059---creating-a-database-in-visual-studio.014


You may have to right-click on “Data Connections” and then “Refresh” to see the changes in your database:


cs-asp-059---creating-a-database-in-visual-studio.015


CustomerTableUpdated


Step 8: Manually Enter Table Data Values

Back in the Server Explorer window, right-click on “Customers” and select “Show Table Data”:


cs-asp-059---creating-a-database-in-visual-studio.016


Right now there are no values entered for each table element, so you can supply them right here if you wish, pressing Enter when finished to commit the data to the table:


cs-asp-059---creating-a-database-in-visual-studio.017


At first glance, it may seem concerning that we are allowed to have a NULL CustomerId, however the data hasn’t actually been populated until you hit the refresh button, at which point the default newid() method will return a valid GUID:


cs-asp-059---creating-a-database-in-visual-studio.018


That’s the basics of creating a database in Visual Studio and in the next lesson we will look at how to access the database from within our C# code.


Related Articles in this Tutorial:

Lesson 1 - Series Introduction

Lesson 2 - Installing Visual Studio 2015

Lesson 3 - Building Your First Web App

Lesson 4 - Understanding What You Just Did

Lesson 5 - Working with Projects in Visual Studio

Lesson 6 - Simple Web Page Formatting in Visual Studio

Challenge 1

Solution 1

Lesson 7 - Variables and Data Types

Lesson 8 - Data Type Conversion

Lesson 9 - Arithmetic Operators

Lesson 10 - C# Syntax Basics

Challenge 2 - ChallengeSimpleCalculator

Solution - ChallengeSimpleCalculator

Lesson 11 - Conditional If Statements

Lesson 12 - The Conditional Ternary Operator

Challenge 3 - ChallengeConditionalRadioButton

Solution - Challenge Conditional RadioButton

Lesson 13 - Comparison and Logical Operators

Lesson 13 Challenge - First Papa Bob's Website

Solution - Challenge First Papa Bob's Website

Lesson 14 - Working with Dates and Times

Lesson 15 - Working With Spans of Time

Lesson 16 - Working with the Calendar Server Control

Challenge 4 - Challenge Days Between Dates

Solution - Challenge Days Between Dates

Lesson 17 - Page_Load and Page.IsPostBack

Lesson 18 - Setting a Break Point and Debugging

Lesson 19 - Formatting Strings

Challenge 5 - Challenge Epic Spies Assignment

Solution - Challenge Epic Spies Assignment

Lesson 20 - Maintaining State with ViewState

Lesson 21 - Storing Values in Arrays

Lesson 22 - Understanding Multidimensional Arrays

Lesson 23 - Changing the Length of an Array

Challenge 6 - Challenge Epic Spies Asset Tracker

Solution - Challenge Epic Spies Asset Tracker

Lesson 24 - Understanding Variable Scope

Lesson 25 - Code Blocks and Nested If Statements

Lesson 26 - Looping with the For Iteration Statement

Challenge 7 - Challenge For Xmen Battle Count

Solution - Challenge For Xmen Battle Count

Lesson 27 - Looping with the while() & do...while() Iteration Statements

Lesson 28 - Creating and Calling Simple Helper Methods

Lesson 29 - Creating Methods with Input Parameters

Lesson 30 - Returning Values from Methods

Lesson 31 - Creating Overloaded Methods

Lesson 32 - Creating Optional Parameters

Lesson 33 - Creating Names Parameters

Lesson 34 - Creating Methods with Output Parameters

Challenge 8 - Challenge Postal Calculator Helper Methods

Solution - Challenge Postal Calculator Helper Methods

Mega Challenge Casino

Solution - Mega Challenge Casino

Lesson 35 - Manipulating Strings

Challenge 9 - Phun With Strings

Solution - Challenge Phun With Strings

Lesson 36 - Introduction to Classes and Objects

Challenge - Hero Monster Classes Part 1

Solution - Hero Monster Classes Part 1

Challenge - Hero Monster Classes Part 2

Solution - Challenge Hero Monster Classes Part 2

Lesson 37 - Creating Class Files Creating Cohesive Classes and Code Navigation

Lesson 38 - Understanding Object References and Object Lifetime

Lesson 39 - Understanding the .NET Framework and Compilation

Lesson 40 - Namespaces and Using Directives

Lesson 41 - Creating Class Libraries and Adding References to Assemblies

Lesson 42 - Accessibility Modifiers, Fields and Properties

Lesson 43 - Creating Constructor Methods

Lesson 44 - Naming Conventions for Identifiers

Lesson 45 - Static vs Instance Members

Challenge 10 - Challenge Simple Darts

Solution - Challenge Simple Darts

Lesson 46 - Working with the List Collection

Lesson 47 - Object Initializers

Lesson 48 - Collection Initializers

Lesson 49 - Working with the Dictionary Collection

Lesson 50 - Looping with the foreach Iteration Statement

Lesson 51 - Implicitly-Typed Variables with the var Keyword

Challenge 11 - Challenge Student Courses

Solution - Challenge Student Courses

Mega Challenge War

Solution - Mega Challenge War

Lesson 52 - Creating GUIDs

Lesson 53 - Working with Enumerations

Lesson 54 - Understanding the switch() Statement

Lesson 55 - First Pass at the Separation of Concerns Principle

Lesson 56 - Understanding Exception Handling

Lesson 57 - Understanding Global Exception Handling

Lesson 58 - Understanding Custom Exceptions

Lesson 59 - Creating a Database in Visual Studio

Lesson 60 - Creating an Entity Data Model

Lesson 61 - Displaying the DbSet Result in an ASP.NET GridView

Lesson 62 - Implementing a Button Command in a GridView

Lesson 63 - Using a Tools-Centric Approach to Building a Database Application

Lesson 64 - Using a Maintenance-Driven Approach to Building a Database Application

Lesson 65 - Creating a New Instance of an Entity and Persisting it to the Database

Lesson 66 - Package Management with NuGet

Lesson 67 - NuGet No-Commit Workflow

Lesson 68 - Introduction the Twitter Bootstrap CSS Framework

Lesson 69 - Mapping Enum Types to Entity Properties in the Framework Designer

Lesson 70 - Deploying the App to Microsoft Azure Web Services Web Apps

Papa Bob's Mega Challenge

Papa Bob's Mega Solution Part 1 - Setting up the Solution

Papa Bob's Mega Solution Part 2 - Adding an Order to the Database

Papa Bob's Mega Solution Part 3 - Passing an Order from the Presentation Layer

Papa Bob's Mega Solution Part 4 - Creating the Order Form

Papa Bob's Mega Solution Part 5 - Adding Enums

Papa Bob's Mega Solution Part 6 - Creating an Order with Validation

Papa Bob's Mega Solution Part 7 - Calculating the Order Price

Papa Bob's Mega Solution Part 8 - Displaying the Price to the User

Papa Bob's Mega Solution Part 9 - Creating the Order Management Page


Comments

Please login or register to add a comment