Visual Web Developer Team Blog

Dev10 Beta2, Import from web.config file and other changes for DB deployment UI

Dev10 Beta2 DB Deployment property page has some changes from Beta1.  Please provide your valuable feedback for us to make it better for you.  Thanks.

1. Tab page name changed from “Deploy/SQL” To “Deploy SQL”

2. Customer can use “Import from Web.config” button to import all the connection strings defined in root web.config to the database entries list.  For example, if web.config contains the following connection strings:

  <connectionStrings>
    <add name="ApplicationServices"
         connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|\aspnetdb.mdf;User Instance=true"
         providerName="System.Data.SqlClient" />
    <add name="AdventureWorksConnection"
         connectionString="Data Source=1p18-fwg36;Initial Catalog=AdventureWorks;Integrated Security=True"
         providerName="System.Data.SqlClient" />
  </connectionStrings>

Clicking “Import from Web.config” button, you will get two connection names in the database entries list.  Each entry corresponds to a connection string in the web.config, with the connectionString content as the “connection string for the source database”. 

3. Database scripting options is changed to a combo box with 3 choices: “Schema Only”, “Schema And Data” and “Data Only”  (Beta2 only has schema only and complete database choices).  “Exclude objects with no schema information from the generated script” option is removed form UI.  (This SMO setting is called SchemaQualify, default is false, you can set to true for each connection in project file if that is what desired.

4. As Beta1, we hide complex scripting SMO options in the project file.  If you view the project file of this web application, you will see the following:

    <PublishDatabaseSettings>
      <Objects>
        <ObjectGroup Name="ApplicationServices" Order="1" Enabled="False">
          <Destination Path="" />
          <Object Type="dbFullSql">
            <PreSource Path="data source=.\SQLEXPRESS%3bIntegrated Security=SSPI%3bAttachDBFilename=|DataDirectory|\aspnetdb.mdf%3bUser Instance=true" ScriptSchema="True" ScriptData="False" CopyAllFullTextCatalogs="False" />
            <Source Path="obj\Debug\AutoScripts\ApplicationServices_SchemaOnly.sql" />
          </Object>
        </ObjectGroup>
        <ObjectGroup Name="AdventureWorksConnection" Order="2" Enabled="False">
          <Destination Path="" />
          <Object Type="dbFullSql">
            <PreSource Path="Data Source=1p18-fwg36%3bInitial Catalog=AdventureWorks%3bIntegrated Security=True" ScriptSchema="True" ScriptData="False" CopyAllFullTextCatalogs="False" />
            <Source Path="obj\Debug\AutoScripts\AdventureWorksConnection_SchemaOnly.sql" />
          </Object>
        </ObjectGroup>
      </Objects>
    </PublishDatabaseSettings>

    We have CopyAllFullTextCatalogs=”False” defined as default to avoid script full text catalog creation SQL script into the script file.  If  it is specified as true or not defined, SQL database with full text catalog will generate a statement which is not transact-able during deploy:

CREATE FULLTEXT CATALOG [test1234]
WITH ACCENT_SENSITIVITY = ON
AUTHORIZATION [dbo]

5. Some SQL files are meant to be deployed without transaction, such as statement with “Create Database”, “Create FULLTEXT CATALOG” etc.  In this case, we need to make sure msdeploy package them with setting Transacted=”False” in their Source XML tag in the project file.  Such as following:

        <ObjectGroup Name="NewConnection1" Order="3" xmlns="">
          <Destination Path="" />
          <Object Type="dbFullSql">
            <PreSource Path="Data Source=1p18-fwg35%3bInitial Catalog=test1%3bIntegrated Security=True" ScriptSchema="True" ScriptData="False" CopyAllFullTextCatalogs="True" />
            <Source Path="obj\Debug\AutoScripts\NewConnection1_SchemaOnly.sql" Transacted="False" />
          </Object>
        </ObjectGroup>

Note, Source tag is used for this SMO option, not Presource tag, since this SMO option will be used in the source manifest file during packaging the generated script to the package file time.  Our process is first use Presource SMO options to script a database to a temporary SQL file, then use Source  SMO options in a manifest file to package the temporary SQL file to the web application package.  Similar SMO options include “dropDestinationDatabase”.  

Thanks

Xinyang Qiu
SDETII
Visual Studio Web Tools