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.
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
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:
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:
Right-click on the “ACME.mdf” file and select “Open” to view the contents of the database in the Server Explorer Window:
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
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:
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”:
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:
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:
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.
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.
If we were to try to save our changes right now, Visual Studio would try to save this script that we “designed” here:
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:
Next, click on “Update Database” and if everything was done correctly it should have successfully created the database:
You may have to right-click on “Data Connections” and then “Refresh” to see the changes in your database:
Back in the Server Explorer window, right-click on “Customers” and select “Show Table Data”:
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:
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:
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.
Lesson 59 - Creating a Database in Visual Studio