Walkthrough: deploy a web application with SQL CE 4.0 database to IIS host with SQL Server.

Walkthrough: deploy a web application with SQL CE 4.0 database to IIS host with SQL Server.

  • Comments 2

With VS2010 sp1, we added SQL CE 4.0 support. You can view more information about SQL CE 4.0 from the following sites:

Here is a walk through of how to deploy a SQL CE database to SQL Server database using web application publish.

1. Create a web application

2. Add a SQL CE 4.0 database via “Add New item”:

image

3. In server explorer, under the SQL CE database, add a table “Customer”, with 2 columns “ID” and “Name”.  Use “Show Table Data” table context menu to add a few rows of data to the table.

4. Add a web form to the project, and put the following code inside it (I used design view, drag and drop the SQL DataSource control and GridView Control and configured them in designer, which will take care web.config change in step 5 as well):

    <form id="form1" runat="server">
    <div>
    
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
            ConnectionString="<%$ ConnectionStrings:ConnectionString %>" 
            ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>" 
            SelectCommand="SELECT * FROM [Customer]"></asp:SqlDataSource>
    
    </div>
    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 
        DataKeyNames="ID" DataSourceID="SqlDataSource1">
        <Columns>
            <asp:BoundField DataField="ID" HeaderText="ID" InsertVisible="False" 
                ReadOnly="True" SortExpression="ID" />
            <asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" />
        </Columns>
    </asp:GridView>
    </form>

 

5. Put the following connection string into web.config file for SQL CE 4.0 connection string.

    <connectionStrings>
        <add name="ConnectionString" connectionString="Data Source=|DataDirectory|\Database1.sdf"
            providerName="System.Data.SqlServerCe.4.0" />
    </connectionStrings>

6. Ctrl-F5 to verify a table shows up in IE.

7. In project’s property page’s “Package/Publish SQL” tab, click “Import from Web.config” to configure SQL deployment settings.

image

 

8. Configure the destination database after selecting the database entry in the above property page. We should script both schema and data for verification purpose. 

image

 

9. Add the following to web.debug.config for transformation and replace the corresponding database access user ID and password. Note, we need to transform providerName from “System.Data.SqlServerCe.4.0” to “System.Data.SqlClient”.

    <connectionStrings>
        <add name="ConnectionString"
          connectionString="Data Source=VenusIIS7a;Initial Catalog=test16;User ID=MyUserID;Password=MyPwd"
          providerName="System.Data.SqlClient" xdt:Transform="SetAttributes" xdt:Locator="Match(name)"/>
    </connectionStrings>

 

10. Publish the web application to the corresponding server.  You’ll notice that SQL CE 4.0 database are successfully deployed to the destination’s SQL Server, with “web.config” connection string transformed. 

 

That’s the end of walkthrough.  With light weighted SQL CE 4.0 support, one can choose freely on what database to use in design time, no matter it is SQL CE, SQL Express, or SQL Server.

 

Thanks for reading

Xinyang Qiu

SDET

Web Platform And Tools

Leave a Comment
  • Please add 2 and 5 and type the answer here:
  • Post
  • Xinyang,

    Some of your sample code lines are being truncated.

    Thanks for the walkthrough.

  • Thanks, Phil, I've added div style="overflow:scroll" block around my code to solve my blog plugin problem.

Page 1 of 1 (2 items)