Admin Pages using Query String in MasterPage

Moving Databases for ASP.NET 3.5 Admin Pages using Query String in MasterPage

http://www.aspfree.com/c/a/asp-net/moving-databases-for-asp-net-3-5-admin-pages-using-query-string-in-masterpage/

his is the third part of the ASP.NET 3.5 admin pages tutorial. In this part, we’ll reduce the number of MS SQL databases we’re dealing with for our website project by copying one of them over to the other one, update the database connection parameters after we’ve done this, and make sure everything is working fine once we’ve completed this process. We’ll also set the primary key index.

For new readers of this tutorial series, please be guided by the following previous part for references:

Part 1: Creating ASP.NET Admin Pages: http://www.aspfree.com/c/a/ASP.NET/Creating-ASPNET-Admin-Pages-/

Part 2: Creating ASP.NET 3.5 Admin Pages using a Query String in a Master Page: http://www.aspfree.com/c/a/ASP.NET/Creating-ASPNET-35-Admin-Pages-using-a-Query-String-in-a-Master-Page/

Copy database tables to ASPNETDB.MDF

After part two, you probably noticed that there are now actually two MS SQL databases in your website project. This is because, after you enabled the user account in ASP.NET, it automatically created a database called ASPNETDB.MDF.

These two databases are the following (you can see this under App_Data in Solution Explorer):

Database.mdf – this is the one the actually contains your website content.

ASPNETDB.MDF – this is the database that contains the user accounts.

For efficiency during deployment, it is recommended that you use only one MS SQL server database. This means you will transfer the contents of Database.mdf to ASPNETDB.MDF.

ASPNETDB.MDF is an important database because it is used for user management. That is why you will transfer the database tables of Database.mdf to ASPNETDB.MDF. The following are the steps:

Step 1. Launch the masterpageexercise_querystring project in Visual Web Developer Express.

Step 2. Expand "App_Data," right click on "Database.mdf," and then click "Open."

Step 3. Expand "Tables." You should see the "pagetitletable."

Step 4. Take note of the name of the database table, as well as its field and types. To view the field name and its types, right click on "pagetitletable," and then click "Open Table Definition." For example:

Database table name: pagetitletable
Field names and Field Types:

Step 5. Now you have all the information about the table and its fields, you need to expand ASPNETDB.MDF under Data connections.

Step 6. Expand "Tables" under ASPNETDB.MDF. Now let’s create pagetitletable under ASPNETDB.MDF. Right click any available tables, e.g "aspnet_Users," and then click "Add New Table."

Step 7. You are then required to enter the column names (which are also the field names) as well as their data types. Use the information you got from step 4. Type the field name and data types in exactly as they are used in Database.mdf.

Step 8. Go to File -> Save All. Under "Choose Name," use the same database table name, which is pagetitletable.

Then hit OK.

Step 9. Now that the pagetitletable has been created under ASPNETDB.MDF, you also need to copy all of that table’s data in Database.mdf. Go to Database.mdf -> Table -> pagetitletable.

Right click on "pagetitletable," and then click "Show table data."

Step 10. Click this portion in the database table to select all (indicated by the red arrow).

After all cells are highlighted, right click, and then click "Copy."

Step 11. Go to ASPNETDB.MDF -> Tables. Right click on "pagetitletable," and then click "Show Table data."

Step 12. There is still no data for the pagetitletable table under the ASPNETDB.MDF database. Now click the section indicated by the red arrow in the screen shot below:

Right click on it, then click "Paste." This will copy all of the data from the Database.mdf "pagetitletable" to the ASPNETDB.MDF "pagetitletable." A warning shows; just click OK to commit the changes to the database.

{mospagebreak title=Update Database Connection Parameters in the Script and Web.Config}

You have completely transferred the database table with the website’s content to ASPNETDB.MDF. Now you need to reflect these changes in the script.

For the web.config file:

1. In the Solution Explorer, double click "web.config."

2. Find this line:

<add name="ConnectionString" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|Database.mdf;Integrated Security=True;User Instance=True"
            providerName="System.Data.SqlClient" />

3. Change the database’s name from "Database.mdf" to "ASPNETDB.MDF." So this is the revised code:

<add name="ConnectionString" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|ASPNETDB.MDF;Integrated Security=True;User Instance=True"
            providerName="System.Data.SqlClient" />

4. Go to File->Save all.

For MasterPage.master.vb:

1. In the Solution Explorer, expand MasterPage.master.

2. Double click MasterPage.master.vb to edit.

3. Find this line:

myCommand.Connection = New SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=L:aspdotnetprojectsmasterpageexercise_querystring
App_DataDatabase.mdf;Integrated Security=True;User Instance=True")

4. Change the database’s name from "Database.mdf" to "ASPNETDB.MDF." This is similar to the previous example. Go to File->Save all.

Since Database.mdf is not needed anymore, you can safely exclude it from the project:

1. Go to Solution Explorer.

2. Expand "App_Data," right click on "Database.mdf" and click "Exclude from Project."

3. Go to the actual App_data folder in masterpageexercise_querystring in your ASP.NET folder. You need to physically delete Database.mdf.exclude and Database_log.ldf.exclude.

4. Go to Solution Explorer in the masterpageexercise_querystring. Hit the refresh button in Solution Explorer. You should only see one database under App_Data, and that is ASPNETDB.MDF.

5. Try running the website in the browser. Go to the Default.aspx source code, and then File -> View in Browser. You should not see any errors. It should look exactly the same as was stated under the "Project Testing in Web Browser" section here:http://www.aspfree.com/c/a/ASP.NET/Creating-ASPNET-35-Admin-Pages-using-a-Query-String-in-a-Master-Page/1/

Setting id as the Primary Key Index in Pagetitletable

To be able to insert, update and delete statements in the pagetitletable, you need to assign a primary key index. In this case, the id field should be the primary key index. It will auto-increment if there are new records added to the database.

Follow all of the steps below.

1. Go to ASPNETDB.MDF -> Tables, right click on pagetitletable, and then click "Open Table definition."

2. Select the entire id row. This will highlight the entire row.

3. Go to Table Designer -> Set Primary Key.

4. On the bottom, you should find column properties for "id." Scroll down and expand "Identity Specification."

5. In the (Is Identity) section, change it from "No" to "Yes."

Leave the others with the same values as provided by default:

Identity Increment = 1
Identity Seed = 1

6. Go to File -> Save all.

To test if this working, go to the Default.aspx source code, and then launch the website in the browser (File -> View in Browser).

1. Click the "Admin" link in the sidebar navigation.

2. Log in using the "Username" and "Password" you set up in the second part of the tutorial. Then click "Login."

3. Go back again to the "Admin" page, and you should see the "Create New Content" link. Click this link.

4. Let’s try creating a new page in your website with the following inputs:

Title: My Resources
Text Content: {you can create random content here: http://www.lipsum.com/ }
NavigationLinkName: Resources

This is how it looks before submitting the content to the database:

5. Now click "Insert." This will finally create the page and save the content in the database.

6. Click "Logout" in the sidebar navigation. You should see the "Resources" page created.

Now go back to masterpageexercise_querystring project in Visual Web Developer:

1. Go to View -> Database Explorer.

2. Go to ASPNETDB.MDF -> Expand tables. Right click "pagetitletable," and then click "Show table data."

This is how the pagetitletable will look after adding the new page ("My Resources" page).

As you may have noticed, the id auto-increments, and the "My Resources" page has an id value of 8. Also, there is no database value for "pagename" because it is no longer needed for this project. This is an obsolete field name, because the website now uses a "query string" (content stored in database) and not static ASP.NET pages.

To remove the "pagename" field:

1. Close the "pagetitletable" database table.

2. Go back to ASPNETDB.MDF -> Tables. Right click "pagetitletable," and then click "Open Table definition."

3. Click the entire "pagename" row, right click, and then click "Delete column."

The following are the final table definitions (after removing the pagename field):