Creating a simple ASP.NET 4 Web App using Visual Studio 2010

The goal of this end to end walkthrough is to create a really simple ASP.NET 4.0 Web Application which uses SQL Server database and is built using Visual Studio 2010 (VS 10)…  The 11 steps in this walkthrough are:

1. Create a new ASP.NET 4.0 Web Application Project (WAP)

2. Create a simple SQL Server Database using SQL Express

3. Add Tables to the Database and set foreign key relationships

4. Add data to the tables created

5. Add a MasterPage and ContentPage to the ASP.NET 4.0 WAP

6. Modify the Master Page to provide consistent look and feel to the site

7. Add datacontrols like GridView and bind them to SQL Express database

8. Add additional web page and use DataView to display more data

9. Use QueryBuilder to fetch data from multiple tables

10. Use AJAX to refresh only parts of the page rather than complete page

11. Hook up various pages and complete the experience….

To complete this walkthrough you will need Visual Studio 2010 Beta1 which you can download FOR FREE from http://www.microsoft.com/visualstudio/en-us/products/2010/default.mspx

Let us get started:

1. Create a new ASP.NET 4.0 Web Application Project (WAP)

Start an instance of Visual Studio 2010 and create a new WAP project by going to File –> New—> Project –> Web –> ASP.NET Web Application as shown below:

Also notice the nice looking VS 2010 new project dialog box…  If you notice at the the left you will also see “Online Templates” and capabilities to search templates…   This will be pretty handy when there will be a bunch of community created templates available…  Anyways for now go ahead and click “Ok” to create our new “1-ClickPublish” project…  After the creation of the project is successful your solution explorer should look as below:

2. Create a simple SQL Server Database using SQL Express

Visual Studio 2010 comes with SQL Server Express edition in build…  I will be using that for the walkthrough purposes although if you have full SQL Server then feel free to use the same…

Add a new “SQL Server Database” by right clicking on the App_Data folder and choosing Add—> New Item

The “Add New Item dialog box” has also got a new look in Visual Studio 2010 just like “Add New Project dialog box”…  I am going to name the database as 1-ClickPublish database, you can feel free to name it anything but do note that it will matter when it will come to deploying this database to remote web server…

3. Add Tables to the Database and set foreign key relationships

To modify this database and add tables to it I will use server explorer… You can open server explorer by going to View—> Server Explorer

By default the Server Explorer will show the connection to the database closed…  You can refresh the connection to open it…

Once you expand the node now you can right click on the tables folder and choose to add new table as shown below

Next we will create simple “Person” table with four columns (ID, FirstName, MiddleName & LastName)…  The ID column and the FirstName column are required and MiddleName + LastName column allow nulls…  For current illustrations I have used ID to be numeric and all the rest as varchars…  I have also set the ID column as an identity column so that we can create a relationship of the Person table with PersonAddress table which we will create next.  The table editor will look as below once you create all these columns correctly…

On clicking save Visual Studio will allow you to name the table which in my case, I will be naming as “Person”…

Similarly we will next create “PersonAddress” table with 8 columns and following details:

• ID | Numeric | Not Null | Identity
• Person_ID | Numeric | Not Null |
• AddressLine1 | varchar (100) | Not Null
• AddressLine2 | varchar (100) | Allows Null
• City| varchar (100) | Not Null
• State| varchar(100) | Allows Null
• Zip | numeric | Allows Null
• Country | varchar (100) | Not Null

Once the table is created it will look as below:

You can also go into both “Person” and “PersonAddress” tables, right click on the ID column and “Set Primary Key”…

Also now right click on the “PersonAddress” table and click relationships…  In the “Foreign Key Relationship dialog box” click the “Add” button to add a new relationship, then on the right side go to the (Name) column and change the name of the relationship to FK_Person_PersonAddress…

Next click on the “…” on the “Tables And Columns Specifications”as selected above… In the specifications dialog box create a relationship between with ID column of Person table as the primary key table and Person_ID column of the PersonAddress table as the Foreign key table and click “Ok”…

4. Add data to the tables created

After the above step you will have your table created, save and close the table designers now to populate the tables with some sample data…. Let us go back to the server explorer to do that… The sever explorer should look as below:

You can now right click on each of the tables and start populating data by clicking “Show table data”… Start populating the data in the Person table first as shown below…

Next populate sample data in the “PersonAddress” table as shown below:

With this the database should be good to go…

5. Add a MasterPage and ContentPage to the ASP.NET 4.0 WAP

Now let us go to the solution explorer and start editing the Web App by adding a simple Master Page to the project… Master page will allow us to maintain a consistent theme across the project…

Right click on the project node and click Add –> New Item…  This time navigate to the Web node and select “Master Page”… Rename the Master page here to Site.Master and click Add…

After adding the master page you can go back to the solution explorer and delete the Default.aspx page… The reason why I am deleting the page is coz it currently does not use the new Site.Master that we created…  

After deleting the Default.aspx and adding the master page the solution explorer will look as below:

Next you can go ahead and right click on the project to Add one more new item… This time add a “Web Content Form” and name it “Default.aspx”  when you do that then the next dialog will allow you to select the master page you would like to use… Select Site.Master as shown in the image below and hit OK as shown below…

With this we have a basic skeleton of a web application created and hooked up… 

6. Modify the Master Page to provide consistent look and feel to the site

I will now open the master page and try add some standard boiler plate content to it as shown below:

The above highlighted text will now appear on every page which will use “Site.Master”…

7. Add datacontrols like GridView to the content pages

Now let us go to Default.aspx and add few controls which will use the database we created earlier…  For this simply open Default.aspx and click on the toolbar to the left and open up the Data controls…  Next, drag and drop a Grid view from the tool box on to the “ContentPlaceHolder1” of Default.aspx as shown below:

There is a switch at the bottom of the code editor to move to the design view… Click that switch and view the grid view in the design view…  The reason for doing this is that design view shows us something we call as Smart tags for grid view… In the Smart tag we will choose to create a new Datasource to tie the grid view to our database…

Do note few quick things like Default.aspx’s design view is now showing us the master page content as well…  Also I have to explicitly select the Grid View and then the top right arrow on it will give me the smart tag options…

The New Data Source dialog will look as below and you need to select Database and click “ok”…

On clicking OK the “SQL Datasource” configuration wizard will kick off… and will look as below:

If you notice 1-ClickPublish.mdf database file will already be pre-populated in the drop down…  Simply select the 1-ClickPublish.mdf and click next… Now name the connectionString as “1-ClickPublishDB” and click next…

In next screen select the FirstName, MiddleName and LastName columns of the database and click “Next” as shown below…

Finally click the “Test Query” button to make sure your query is yielding results as shown below and click “Finish”

Now if you select Default.aspx in the Solution Explorer and Hit Ctrl+F5 then you should see our web application up and running as shown below…

Now let us put some reasonable headings etc to the page by going into the source view of Default.aspx (by clicking the source button at the bottom of the page...

Type in the code as shown below and switch to “Design View” again to preview your page:

The preview should look as below…

Now what we want to achieve is that when user clicks “View Addresses” button we want to be able to show a page with Persons with their Address details… 

8. Add additional web page and use DataView to display more data

For this let us add a new "Web Content Form” called “Address.aspx” which also uses the Site.Master… Once we do so then our solution explorer should look as below:

Let us double click Address.aspx and open it in the “Source View” and this time drag and drop a details view from the toolbar, which will make the page look as below:

Let us switch back to design view and try to add a SQL data source similar to the one we added for Default.aspx… 

9. Use QueryBuilder to fetch data from multiple tables

Although this time we want to be able to show the content of not only the PersonAddress table but also want to get First Name and Last Name from the Person table…  Hence on the “Configure Select Statements” step of the datasource wizard we will try to add a custom SQL Query to the datasource by following below steps:

On clicking next on the dialog you will get an opportunity to select a query builder as shown below:

On clicking the query builder you will be asked to “Add tables”… Add both “Person” and “PersonAddress” tables by clicking the Add button as shown below:

Now the two tables will get added to the query builder… In the query builder now feel free to select the columns you want to display in the detailed view…  Also note how the query builder is building the SQL statements on your behalf…

Click “OK” on query builder and you should be taken back to the SQL Data source configuration wizard

Notice that now the query is populated, click “Next”…  As we did for default.aspx, test your query and make sure that the data is getting retrieved…

You should now be seeing following dialog:

Click “Finish” now… In the “Design View” now click on the “DetailsView” control and click “F4”…. This should launch the properties pane for the “DetailsView” as shown below:

In the Properties of the DetailsView1 control set “AllowPaging”= True… This will allow your page to iteratively move between addresses instead of just showing one address… 

10. Use AJAX to refresh only parts of the page rather than complete page

Now let us switch to the source view and add quick AJAX capabilities to the page so that the entire page does not get refreshed every time you change the pagination on the Details View… To do this put the below specified code to your page…

Once you do this you should be good to test your “Address.aspx” page…  To test your “Address.aspx” page simply select it in the solution explorer and hit “Ctrl+F5”… You should now be able see your page as below:

11. Hook up various pages and complete the experience….

Now finally we want to link the Default.aspx Page to Address.aspx page by click of “View Addresses” button… To do this let us open “Default.aspx” page in Design View and double click the “View Addresses” button… This will open the Default.aspx.cs page with the Button Click event hooked up…  We can now write Response.Redirect(“Address.aspx”); into the event handler as shown below…

With this we now have a fully functional app  connected with backend database… If you now select Default.aspx and hit Ctrl+F5 then you should see the below page":

On clicking the “View Addresses” button you should be able to see the below page with pagination to view addresses one after another:

With you web application should be ready to be deployed…

Hope this walkthrough was useful!!

-Vishal

Database Deployment with Visual Studio 10

Visual Studio 2010 (VS 10) Beta1 was released recently...  You can download VS 10 Beta1 from Microsoft download page….

Do check out the update on Visual Studio features for Web Developers on our team blog…

We have been discussing about a bunch of deployment features previously and today we will discuss about Database Deployment with VS 10….

VS 10 will allow you to deploy your databases both while creating a web package as well as while publishing directly to a web server provided by your hoster…

To catch up on the previous web deployment posts check out the below links:

Web Deployment with VS 2010 and IIS

Web Packaging: Creating a Web Package using VS 2010

Web Packaging: Creating web packages using MSBuild

How does Web Deployment with VS 10 & MSDeploy Work?

Installing Web Packages using Command Line

Web.config Transformation

 

Apart from the “Package/Publish” tab, with VS10 we will now have a new tab called “Deploy-SQL” in the project properties of Web Application Projects (WAPs)

The “Deploy SQL” tab looks like below:

Firstly let us note that this tab is Build configuration aware i.e. you can configure different settings for “Release” vs “Debug” environment… I have set the configuration to “Release”  and Platform to “Any CPU” for the above example…

TIP:  If you are deploying to a hosted environment then you can create different build configurations per hoster e.g. “DiscountASP” or “OrcsWeb”, this way you can configure various Database deployment settings per hoster…  You can use the same principle for various different database servers you are going to even within your intranet…

Anyways, the first grid on “Deploy SQL”  tab looks like below and actually is a MASTER part of MASTER-DETAIL view of your Database Deployment Settings.  Every Database Entry in the grid below is a representative of a group of database settings representing source and destination database information:

i.e. Database Entry (Friendly Name) = Source Database Information + Destination Database Information

My advice would be to create one entry per every destination database you are deploying to…  The reason why I am emphasizing destination database is coz many folks want to deploy more than one source databases to a single destination database…

One classic example of this is when you are using ASP.NET membership or login features then ASP.NET automatically creates ASP.NET DB in your project AppData directory, apart from this people also like to create a separate ApplicationDB which has the application specific business data.  As you can imagine this results into two different source DBs.  When you are trying to deploy to a shared hosting environment then many times you are paying per database to your hoster and the obvious thought “my ApplicationDB does not conflict with ASP.NET DB in anyway, I am happy to have both of the DBs to go to the same destination DB without paying for two databases”…  In such a situation you would want to create one database entry in the grid above for every destination connection string that you have.

The “Add” button at the bottom allows you to add a “Friendly Name” for your Database Entry…  If you would like to go to more than one destination database you can feel free to add as many Database Entries in the grid above as you want… For simplicity of tracking I would recommend using your connectionString name as the Friendly Name in the grid above.

As you might have already noticed each entry in the grid also has a check box next to it…  The reason why this check box is provided is so that once you configure the database entry then you do not have to completely remove it  by clicking “Remove” button (and loose all the configured data)… If you do not want to include a  particular database Entry (friendly name) in a particular deployment scenario you can simply exclude it by un-checking the checkbox. 

In order to permanently “Remove” the database entry you can select the entry to be removed and can click the “Remove” button….

Let us now take a look at the details of a database entry (e.g details of “productsDB” in the above example).  As I mentioned above the details are going to be broken down into two major categories…

1. Destination Database Information

2. Source Database Information

_____________________________________________________________________________________________________

Destination Database Information: This section looks as below:

It is relatively simple, coz all it contains is connection string for the destination database… Typically when you get a hosted account your hoster will send you the connection string of your hosted database in an email.  This connectionString should go into the above text box.

This field is kept optional and the reason behind it is that if you are using Web Packaging as a model for your deployment and are likely to hand over the web package to your system administrator or someone who will provide connectionString at install time then you can feel free to leave this field empty. 

Do note that if you are directly publishing to a hoster (i.e. not packaging) and you have left this field empty then you will get an error during publishing.

__________________________________________________________________________________________________

Source Database Information: This section looks as below:

The first thing you will see in the Source Database Information is a check box which indicates “Pull data from an existing database” … If you have a local SQL Server database (SQL 2005 or 2008) which you are using for development then this will allow you to extract your database to deploy to the destination database (mentioned in the section above)….

The first setting under “Pull data from an existing database” is Connection string for the source database… This text box is enabled only when you check the check box for “Pull data from…”  You can also click the “…” button next to the connection string text box which will launch the connection String builder dialog as below:

This dialog can help you easily create the connection String for your local database…

The next set of controls under the source connection String text box allow you to determine what kind of SQL Script would like to generate from your source database.  Currently there are two options available…

Schema Only  - Choosing this will only script the schema of your source database

Complete Database – Choosing this will script both schema and data of your source database

On your dev SQL Server it is likely that you might have some test data, in that situation you would want to choose “Schema Only” option which will only pick up the DB schema leaving out the data…  On the other hand if you choose complete database everything from your source database will be scripted…

Insider Note:  VS 10 Database Deployment is using SQL Management Objects (SMO) behind the scenes to script your database…  SMO is a tried and tested API for scripting huge databases… scriptSchema & scriptData are SMO scripting options which Visual Studio is setting when you choose one of the options above…

The next check box is “Exclude object with no schema information” which will give you further fine grain control on scripting your database.  If you do not care about schemaQualify feel free to uncheck this checkbox…

Database Source Scripts -  You might have noticed that in this section there is already an entry called “[Auto-generated Schema Only]”… This entry is added to the source database scripts as soon as you check the “Pull data from an existing database”… At the bottom of Database Source Scripts grid you will notice “Add Script” and “Remove Script” button…  These buttons will allow you to add & remove extra .SQL scripts as your source…

Most of the times developers or DBAs create .SQL scripts to deploy databases…  These custom .SQL scripts can be added and ordered in the grid (and thereby during deployment) using the up and down arrows…  The “[Auto-generated Schema Only]” is a representative of the script that VS will automatically generate from your source database, it is added to the grid so that you can order it up and down along with the rest of the .SQL scripts that you might want to add…

Also note that if you are generating your .SQL scripts via tools like Visual Studio Database Edition (Data Dude) or Red Gate then you can add those scripts here to be included in your web package as well. 

NOTE: There is a way to set up automated build and deployment environment in which you can collect .SQL scripts from Data Dude or Red Gate and provide to VS 10 during web deployment.  I will cover this topic separately in a different blog post…

_________________________________________________________________________________

The last section of the “Deploy SQL” tab is a place for you to provide Database Deployment notes so that you can note specific steps you took or scripts you used while deploying databases; this is purely for your reference purposes…. The deployment notes section looks as below…

Once you have provided all of this information you are good to deploy your databases using Visual Studio 2010 Web Deployment…

Web Packaging  + DB Deployment

After setting up your DB deployment as shown above you can now right click on your project and hit Package –> Create Package as shown below…

The package created as a result will contain your database…  You can read details about creating and installing the web packages below:

Web Packaging: Creating a Web Package using VS 2010

Web Packaging: Creating web packages using MSBuild

Installing Web Packages using Command Line

 

Web Publishing + DB Deployment

Once you set up the DB then not only will it get included in Web Packages but will also be available to be deployed via Publishing…  You can publish you project by right clicking on your project and hit Publish as shown below…

 

I will soon write a detailed post on how to use the new “Publishing” features of Visual Studio 2010…

Hope this information was useful…

-Vishal

I just got a Microsoft Tag !!

In various conferences, events and other places I keep wondering if there was an easy way for me to share my information with folks and since Microsoft Tag came up I have been hoping to get one for myself…

Finally I got one… In case you would like to get my V-Card feel free to download the tag resolver from http://microsoft.com/tag and scan it…

-Vishal

VS 2010 Project Conversion+ Upgrade

Visual Studio 2010 will allow you to move your projects from previous versions of Visual Studios  to VS 2010 with ease, I will call this process as “Converting” the project from VS 200X to VS 2010… 

VS 2010 will also allow you to change your project’s Target Framework Version to .NET 4.0 from .NET 2.0, 3.0 or 3.5…  I will call this process as “Upgrading”…

The reason why VS allows you to deal with these two concepts separately is due to various reasons, some of which can be:

• You might be ready to use the latest and greatest tooling features of VS 2010 even if you are not yet ready to move to .NET 4.0 just yet…
• You might have a big solution with various projects and you might want to move few of the project to .NET 4.0 first and then take a staged approach to the rest of the projects…  Obviously you would not want to open various projects in different versions of VS…

Project Conversion-  When you are using projects with .csproj/.vbproj files (e.g. Class Library, Web Application Project etc) then these files typically contain some type of information about VS IDE version example in .csproj file you can see below:

<Project ToolsVersion="3.5" DefaultTargets="Build" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
  <PropertyGroup>
    <Configuration Condition=" '$(Configuration)' == '' ">Debug</Configuration>
    <Platform Condition=" '$(Platform)' == '' ">AnyCPU</Platform>
    <ProductVersion>9.0.30729</ProductVersion>
    <SchemaVersion>2.0</SchemaVersion>

  In the above example  you can notice that there is msbuild/2003 in the schema namespace… There is also ProductVersion 9.0 in the sample above.  If you examine your project file in further depth you will also find references to the .targets files for the Web Application Projects like below:

 <Import Project="$(MSBuildExtensionsPath)\Microsoft\VisualStudio\v9.0\WebApplications\Microsoft.WebApplication.targets" />

Notice that the location of the targets file is in above example is in “V9.0” directory in the above piece of xml…  This all needs to be changed to Visual Studio 2010 references (i.e. “V 10.0”) to allow your pre VS 2010 project to use new features of VS 2010. 

The process by which pre VS 2010 (e.g. VS 2008, VS 2005)  project and solution files are modified to move to VS 2010 format is what I am referring to as Project Conversion.

VS 2010 conversion wizard is very similar to how VS 2003/VS 2005 to VS 2008 conversion Wizard use to look like…  To convert pre VS 2010 project to VS 2010 you can double click the project or solution file on a box which has VS 2010 and the conversion wizard will kick off… Following will be the steps that you will see:

Step 1: UI describing that your project will be converted

Step 2: Option to back up your project (I would encourage you to create a back up of your project before converting so that you have a copy of your project in case there are any errors in conversion)

Step 3: Summary before the conversion begins

Note that so far you have not been asked about your .NET Framework Version.  Do not worry about it just yet and hit the “Finish” button above… You will be allowed to make a decision on whether or not you would like to “Upgrade” your project to .NET 4.0 in the next step.

As soon as you click Finish, the project conversion will kick in the Project Upgrade dialog.

Project Upgrade-  Step 3 of project conversion step is pretty much common to any VS project or solution file, by this what I mean is that some of the things like Product Version, Target File Paths etc are typically required to be changed for most projects including Web Projects… Apart from this step there are certain .NET Framework specific steps required to be done e.g. changes to the project file to indicate which .NET Framework version should be used for the project, similar type of changes in the solution file if need be and additionally for web projects changing the web.config file correctly based on the the framework version you are targeting… These changes are what I am referring to as “Project Upgrade”…

Once you click “Finish” button in Step 3 above you should see the below  Framework Version dialog

Step 4:  Choose whether or not to upgrade to .NET 4.0

I will click “Yes” on the button above but you can choose to click “No” if you are not yet ready to move to ASP.NET 4.0 on the server.   As the dialog specifies you can change the target framework of your project eventually when you are ready by going to the project properties…

If I click “Yes” my web.config file will be modified to conform to ASP.NET 4.0 format...  None of the additional items that you have added to your web.config file (e.g. appSettings, connectionStrings etc) will be altered but rather most of the assembly references in the web.config file will be modified to 4.0…

In addition with ASP.NET 4.0 there is a need for a new attribute on the compilation tag as shown below:

<compilation debug="true" targetFrameworkMoniker=".NETFramework,Version=v4.0">
            <assemblies>
                <add assembly="System.Core, Version=4.0.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089"/>
                <add assembly="System.Data.DataSetExtensions, Version=4.0.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089"/>
                <add assembly="System.Web.Extensions, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
                <add assembly="System.Xml.Linq, Version=4.0.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089"/></assemblies>
        </compilation>

The targetFrameworkMoniker attribute allows VS 2010 to identify that the web is a 4.0 web.  This allows VS 2010 to customize the intellisense, toolbox, references etc to 4.0 for your project. 

As part of this project upgrade VS will automatically inject targetFrameworkMoniker attribute into your web.config file.   For a 4.0 web in VS 2010 and ASP.NET 4.0 this attribute is required, if you try to run a 4.0 web without this attribute in the web.config file then you will get an error  At this point you should get below screen after the conversion is successful.

Step 5: Conversion Complete + Logs Screen

You can choose to open the conversion logs at this time or hit Close…  At this point your project should be ready to be used…

Eventually I will write more about Conversion, Upgrade and details about it but in the meantime I hope this will help…

-Vishal

How to find Delegated Vs Non Delegated IIS Settings?

IIS Manager has a very nice tool to tell you what features are delegated vs non delegated on the server…  To find out go to Start—>Run –> InetMgr

Now you can click on the server node and then select the “Feature Delegation” as shown below:

On clicking on the feature delegation IIS Manager will show you all the features which are delegated..

In most of the “Read/Write” scenarios when you modify your site settings using IISManager they are written into the application’s web.config file… Although certain features are locked down by IIS Manager and they cannot be changed in your application’s web.config file…

These features are marked as “Read Only” and “Not Delegated”… By default following features are Read/Only and Not Delegated

In Win 7  there is work being done to allow changing Error Pages in the web.config file as well, although up until Win 7 that was not possible…

Hope this helps!!

How to transfer your DB using MSDeploy command Line

Problem Statement: Lets say you have a database on a server which is live, and you think that there is some problem on the server that you would like to troubleshoot…  You typically want to move your database to your Dev DB box and troubleshoot it there instead of troubleshooting a live server…

MSDeploy Solution:  MSDeploy can help you achieve this in very simple steps below:

Learn more about how MsDeploy works

Step 1: Download MSDeploy and install it on source as well as target machines

At MIX 2009 we released MSDeploy RC and it is available for free download below:

• MsDeploy RC1 x86
• MsDeploy RC2 x64

You can just download the exe and install standard installation, that should work…

Step 2:  Create a source manifest for your source machine DB

Source manifest should look as below:

<sitemanifest>

<dbFullSql path=" Data Source=VIJOSHI-DEMO;Initial Catalog=Delete;Integrated Security=True" ScriptSchema=True ScriptData=False />

</sitemanifest>

You can save this xml at any location you like… In my case I will save it at C:\Users\vijoshi\Desktop\DBManifest.xml

The highlighted syntax are scripting options provided by SQL Management Objects… There are more scripting options like TargetServerVersion  which indicates the destination SQL Server version for which you want to generate SQL Scripts…

ScriptSchema option above will allow you to take the schema of the database (typically you will want this most of the time)

ScriptData option will allow you to take the data from the DB as well, in our problem statement above you do need the data as well but normally this is a very resource costly option and also might be restricted due to confidentiality policies around data.

Step 3: Open MSDeploy command prompt

Start –> All Programs –> IIS Extensions –> MSDeploy Command Console

check the figure below:

Step 4:  Run package command to create a .zip file which will contain your .sql scripts

Call MSDeploy command with below details:

• verb:sync – This instructs msdeploy to create an action of syncing and as we want to sync our DB that is the verb we will choose…
• source:manifest – This will tell msdeploy what to use as a source…  In our case we have created a manifest file in Step 2 which is our source (note that for MSDeploy the xml is the source, which inturn calls db as the source)
• dest:Package -   This will tell msdeploy to create a package of the source.  In our case the DB will be scripted into .sql file and will be put into .zip package.

The command that you will use on command line will be as below:

C:\Program Files\IIS\Microsoft Web Deploy>msdeploy -verb:sync -source:manifest=C:\Users\vijoshi\Desktop\DBManifest.xml -dest:Packa
ge=C:\Users\vijoshi\Desktop\DBPackage.zip

The output of this command should show something like below:

Step 5:  Transport your .zip package to the destination

Now you can simply copy the .zip file to your target machine and if you just need the .sql file to use SQL Management studio to install it then just open the .zip file in windows explorer and you should have the .sql file in there… 

Alternatively if you want to use MSDeploy itself to recreate the DB on the destination then it is equally easy as well…

Step 6: Run MSdeploy command to unzip the packaged DB

This time the zip file is the source and we want to put the DB into different DB, I will call it “TargetDB”…

For that I will create destination manifest similar to the source manifest I created in Step 2… My destination manifest will look as below:

<sitemanifest>

<dbFullSql path=" Data Source=VIJOSHI-DEMO;Initial Catalog=TargetDB;Integrated Security=True"/>

</sitemanifest>

Few things to note:

• My connection string has to change on the destination to match the destination credentials
• I do not need SMO options now as I am no more scripting anything rather I am just deploying the already scripted objects
• I will save this file as C:\Users\vijoshi\Desktop\DBDestinationManifest.xml

Again on MSDeploy command prompt I can provide below MSDeploy command

C:\Program Files\IIS\Microsoft Web Deploy>msdeploy -verb:sync -source:package=C:\Users\vijoshi\desktop\DBPackage.zip -dest:manifes
t=C:\Users\vijoshi\Desktop\DBDestinationManifest.xml

After running the command you should have your DB updated on your local box as below:

Hope this helps!!

-Vishal

Technorati Tags: DB,Deployment,Packaging,Transfer,SQL,Scripting,SMO,MSDeploy,SQL Server