Creating a Database with Visual Web Developer Database Explorer
Hopefully, you reached this page from the About Visual Basic ASP.NET Hub Page. If not, you might want to click the link above and go there. That article is the starting point to learn everything about programming in ASP.NET. The article itself is very basic because the main goal is to be a container for links to other articles that explain the details. But the main page does take you through the process of developing a web site using Visual Basic and ASP.NET.
Most applications require data to work with and a database is probably the way most applications get that data. Fortunately, both Visual Web Developer Express (VWD below) and Visual Studio provide a built in tool to get the job done. In the "Express" (that is, "free") developer products, the tool is named Database Explorer. Because the full Visual Studio does more, in that product the tool is called Server Explorer. But for databases, they both work about the same way. Here's the tool shown in VWD:
--------
Click Here to display the illustration
--------
The example database shown is the new "SQL Server Compact 4.0 Local Database". (Note the file qualifier, ".sdf".) Microsoft developed this version of their relational database to solve some of the problems of portability and the difficulty of working with the full, industrial scale SQL Server database, especially on mobile devices and desktops. Previous versions were called, "SQL Server for Windows CE" (which worked with SQL Server 2000 so it's a decade old) and "SQL Server Mobile Edition" (labeled "2005" by Microsoft).
So they've been working on this for a while. This version supports Framework 4.0 but it drops support for Windows Mobile since that platform has now been replaced by Windows Phone. The "database" on Windows Phone isn't really a database. Microsoft describes it as a POCO. ("Plain Old CLR Object" - Will acronyms piled on other acronynms never cease!)
The creation of a database for an ASP.NET app is fairly easy using the Database Explorer or Server Explorer tool. The walkthrough here will use VWD and Database Explorer.
Create a new website using the ASP.NET website template. (File -> New Website ...) I'm using all of the default settings but in a real app, this is where you change the name and file location of your new website to something unique. Click OK to proceed.
Check Solution Explorer and notice that one of the folders VWB has added is named App_Data. Your new database will normally be in this folder. When you find App_Data, right-click on it. Select Add, then New Item ... This time, I'll choose a new SQL Server Database and I'll rename it to CustomerExample.mdf.
--------
Click Here to display the illustration
--------
You have to have an active connection to a SQL Server database for this step to work! And there are a whole bunch of other problems that can occur at this step. If this walkthrough doesn't work for you, this is probably where it will break down. As a Microsoft article states, "Unfortunately, connecting to those files as a database from your application is not as simple and automatic as it ought to be." It's difficult to try to summarize all of the things that can go wrong, but they generally relate to the fact that the database is a separate system that your application simply has to use; not one that is actually part of your application. This is complicated by another fact, that the database has unique and important security requirements.
(Off topic metaphor - Skip if you're in a hurry.)
The bank robber, Willie Sutton, was quoted as saying that he robbed banks because, "That's where the money is." Hackers attack databases because that's where the data is. And that's one big reason that databases are often hard to work with.
(End of off topic metaphor.)
Fortunately, one of the few Microsoft articles I have ever seen that really helps is available to explain how this works. If you're having trouble here, try, SQL Server 2005 Express Edition User Instances. Ignore the fact that it's written for SQL Server 2005. It's still good.
VWD will create the necessary files for the new database, which are viewable in the Database Explorer pane. If you do not see this, click on “View” in the Main Menu and select the Database Explorer window. The data in a database is stored in tables, so the next step is to add one. Right-click on the Tables folder in the Database Explorer and select Add New Table. This opens an editor screen where you can add the columns in the database. Each row in the editor is a column in the database.
The image below shows the sequence of windows you will see as you add the database and a table in the database.
--------
Click Here to display the illustration
--------
You'll notice that the "Data Type" choices you get are nothing like anything in VB.NET. There's no "nvarchar(50)" in any NET language, for example. This has been a problem in using databases forever. (And it's not unique to Microsoft databases, either. Other vendors have the same problem.) Since this article is about VB.NET and ASP.NET, I won't go into detail. (If you're interested, my article LINQ to SQL in VB.NET covers it.) Just be aware that you have to use the types available in SQL Server.
Every database table usually has a unique way to identify each record. One column in the table needs to hold a unique value, often named something like CustID, the name I used in this table. After adding this column, you can click the icon in the toolbar at the top to Set Primary Key. Click on this to set the column as a key identifier. You can get SQL Server to help you make sure this is a unique value by changing the IsIdentity value from No to Yes. This will automatically increment the CustID value of each new record added. You can create other tables the same way and relate them with the Relationships window, but this takes us deeper and deeper into database design issues.
--------
Click Here to display the illustration
--------
Databases are normally designed and maintained by specialists ("Data Base Administrator" or DBA) in larger organizations and individual programmers usually don't get involved in issues like this. But if you're working on your own or you're just experimenting, you can do everything on your own computer. Try my series on databases to get started: A Database Centered Tutorial for Beginners
Adding Data to Tables
Even when a DBA takes care of the database, you will often add data to it, even as a developer. One of the main reasons is to test the operation of your application. You can do this through VWD. (Developers sometimes create test ASP.NET pages just for this, however. Especially if a group of people will be adding the data.) Using the VWB is quite easy however. With VWB, expand the Tables folder in Database Explorer to show the existing tables. Then right-click the table and select Show Table Data. A window will open with the first empty record showing nulls. Now you can enter the data, using the Tab key to move across the fields. When you complete the data in a row, the cursor will move on to a new record and the identifier will increment if you have changed IsIdentity from No to Yes.
You can do nearly anything you need to do with databases right inside VWD. But for heavy duty production work, you will probably be more efficient if you use other tools designed more for the job. The full Visual Studio system is a good step up and, if you're using SQL Server, you might want to use SSMS - The SQL Server Management Studio - to work with the database.
Most applications require data to work with and a database is probably the way most applications get that data. Fortunately, both Visual Web Developer Express (VWD below) and Visual Studio provide a built in tool to get the job done. In the "Express" (that is, "free") developer products, the tool is named Database Explorer. Because the full Visual Studio does more, in that product the tool is called Server Explorer. But for databases, they both work about the same way. Here's the tool shown in VWD:
--------
Click Here to display the illustration
--------
The example database shown is the new "SQL Server Compact 4.0 Local Database". (Note the file qualifier, ".sdf".) Microsoft developed this version of their relational database to solve some of the problems of portability and the difficulty of working with the full, industrial scale SQL Server database, especially on mobile devices and desktops. Previous versions were called, "SQL Server for Windows CE" (which worked with SQL Server 2000 so it's a decade old) and "SQL Server Mobile Edition" (labeled "2005" by Microsoft).
So they've been working on this for a while. This version supports Framework 4.0 but it drops support for Windows Mobile since that platform has now been replaced by Windows Phone. The "database" on Windows Phone isn't really a database. Microsoft describes it as a POCO. ("Plain Old CLR Object" - Will acronyms piled on other acronynms never cease!)
The creation of a database for an ASP.NET app is fairly easy using the Database Explorer or Server Explorer tool. The walkthrough here will use VWD and Database Explorer.
Create a new website using the ASP.NET website template. (File -> New Website ...) I'm using all of the default settings but in a real app, this is where you change the name and file location of your new website to something unique. Click OK to proceed.
Check Solution Explorer and notice that one of the folders VWB has added is named App_Data. Your new database will normally be in this folder. When you find App_Data, right-click on it. Select Add, then New Item ... This time, I'll choose a new SQL Server Database and I'll rename it to CustomerExample.mdf.
--------
Click Here to display the illustration
--------
You have to have an active connection to a SQL Server database for this step to work! And there are a whole bunch of other problems that can occur at this step. If this walkthrough doesn't work for you, this is probably where it will break down. As a Microsoft article states, "Unfortunately, connecting to those files as a database from your application is not as simple and automatic as it ought to be." It's difficult to try to summarize all of the things that can go wrong, but they generally relate to the fact that the database is a separate system that your application simply has to use; not one that is actually part of your application. This is complicated by another fact, that the database has unique and important security requirements.
(Off topic metaphor - Skip if you're in a hurry.)
The bank robber, Willie Sutton, was quoted as saying that he robbed banks because, "That's where the money is." Hackers attack databases because that's where the data is. And that's one big reason that databases are often hard to work with.
(End of off topic metaphor.)
Fortunately, one of the few Microsoft articles I have ever seen that really helps is available to explain how this works. If you're having trouble here, try, SQL Server 2005 Express Edition User Instances. Ignore the fact that it's written for SQL Server 2005. It's still good.
VWD will create the necessary files for the new database, which are viewable in the Database Explorer pane. If you do not see this, click on “View” in the Main Menu and select the Database Explorer window. The data in a database is stored in tables, so the next step is to add one. Right-click on the Tables folder in the Database Explorer and select Add New Table. This opens an editor screen where you can add the columns in the database. Each row in the editor is a column in the database.
The image below shows the sequence of windows you will see as you add the database and a table in the database.
--------
Click Here to display the illustration
--------
You'll notice that the "Data Type" choices you get are nothing like anything in VB.NET. There's no "nvarchar(50)" in any NET language, for example. This has been a problem in using databases forever. (And it's not unique to Microsoft databases, either. Other vendors have the same problem.) Since this article is about VB.NET and ASP.NET, I won't go into detail. (If you're interested, my article LINQ to SQL in VB.NET covers it.) Just be aware that you have to use the types available in SQL Server.
Every database table usually has a unique way to identify each record. One column in the table needs to hold a unique value, often named something like CustID, the name I used in this table. After adding this column, you can click the icon in the toolbar at the top to Set Primary Key. Click on this to set the column as a key identifier. You can get SQL Server to help you make sure this is a unique value by changing the IsIdentity value from No to Yes. This will automatically increment the CustID value of each new record added. You can create other tables the same way and relate them with the Relationships window, but this takes us deeper and deeper into database design issues.
--------
Click Here to display the illustration
--------
Databases are normally designed and maintained by specialists ("Data Base Administrator" or DBA) in larger organizations and individual programmers usually don't get involved in issues like this. But if you're working on your own or you're just experimenting, you can do everything on your own computer. Try my series on databases to get started: A Database Centered Tutorial for Beginners
Adding Data to Tables
Even when a DBA takes care of the database, you will often add data to it, even as a developer. One of the main reasons is to test the operation of your application. You can do this through VWD. (Developers sometimes create test ASP.NET pages just for this, however. Especially if a group of people will be adding the data.) Using the VWB is quite easy however. With VWB, expand the Tables folder in Database Explorer to show the existing tables. Then right-click the table and select Show Table Data. A window will open with the first empty record showing nulls. Now you can enter the data, using the Tab key to move across the fields. When you complete the data in a row, the cursor will move on to a new record and the identifier will increment if you have changed IsIdentity from No to Yes.
You can do nearly anything you need to do with databases right inside VWD. But for heavy duty production work, you will probably be more efficient if you use other tools designed more for the job. The full Visual Studio system is a good step up and, if you're using SQL Server, you might want to use SSMS - The SQL Server Management Studio - to work with the database.
Source...