{"id":2704,"date":"2011-06-07T10:45:23","date_gmt":"2011-06-07T10:45:23","guid":{"rendered":"https:\/\/blogs.msdn.microsoft.com\/webdev\/2011\/06\/07\/walkthrough-deploy-a-web-application-with-sql-ce-4-0-database-to-iis-host-with-sql-server\/"},"modified":"2022-08-12T02:48:44","modified_gmt":"2022-08-12T09:48:44","slug":"walkthrough-deploy-a-web-application-with-sql-ce-4-0-database-to-iis-host-with-sql-server","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/dotnet\/walkthrough-deploy-a-web-application-with-sql-ce-4-0-database-to-iis-host-with-sql-server\/","title":{"rendered":"Walkthrough: deploy a web application with SQL CE 4.0 database to IIS host with SQL Server."},"content":{"rendered":"<p>With VS2010 sp1, we added SQL CE 4.0 support. You can view more information about SQL CE 4.0 from the following sites:<\/p>\n<ul>\n<li><a title=\"http:\/\/weblogs.asp.net\/scottgu\/archive\/2010\/06\/30\/new-embedded-database-support-with-asp-net.aspx\" href=\"http:\/\/weblogs.asp.net\/scottgu\/archive\/2010\/06\/30\/new-embedded-database-support-with-asp-net.aspx\">http:\/\/weblogs.asp.net\/scottgu\/archive\/2010\/06\/30\/new-embedded-database-support-with-asp-net.aspx<\/a>&#160; <\/li>\n<li><a title=\"http:\/\/blogs.msdn.com\/b\/webdevtools\/archive\/2011\/01\/06\/how-to-bin-deploy-sql-compact-edition-4-0-and-razor-web-projects.aspx\" href=\"http:\/\/blogs.msdn.com\/b\/webdevtools\/archive\/2011\/01\/06\/how-to-bin-deploy-sql-compact-edition-4-0-and-razor-web-projects.aspx\">http:\/\/blogs.msdn.com\/b\/webdevtools\/archive\/2011\/01\/06\/how-to-bin-deploy-sql-compact-edition-4-0-and-razor-web-projects.aspx<\/a> <\/li>\n<li><a title=\"http:\/\/www.microsoft.com\/downloads\/en\/details.aspx?FamilyID=033cfb76-5382-44fb-bc7e-b3c8174832e2\" href=\"http:\/\/www.microsoft.com\/downloads\/en\/details.aspx?FamilyID=033cfb76-5382-44fb-bc7e-b3c8174832e2\">http:\/\/www.microsoft.com\/downloads\/en\/details.aspx?FamilyID=033cfb76-5382-44fb-bc7e-b3c8174832e2<\/a> <\/li>\n<\/ul>\n<p>Here is a walk through of how to deploy a SQL CE database to SQL Server database using web application publish.<\/p>\n<p>1. Create a web application<\/p>\n<p>2. Add a SQL CE 4.0 database via \u201cAdd New item\u201d:<\/p>\n<p><a href=\"https:\/\/devblogs.microsoft.com\/dotnet\/wp-content\/uploads\/sites\/10\/2011\/06\/1184.image_thumb.png\"><img decoding=\"async\" style=\"border-right-width: 0px;padding-left: 0px;padding-right: 0px;border-top-width: 0px;border-bottom-width: 0px;border-left-width: 0px;padding-top: 0px\" title=\"image\" border=\"0\" alt=\"image\" src=\"https:\/\/devblogs.microsoft.com\/dotnet\/wp-content\/uploads\/sites\/10\/2011\/06\/1184.image_thumb.png\" width=\"594\" height=\"284\" \/><\/a><\/p>\n<p>3. In server explorer, under the SQL CE database, add a table \u201cCustomer\u201d, with 2 columns \u201cID\u201d and \u201cName\u201d.&#160; Use \u201cShow Table Data\u201d table context menu to add a few rows of data to the table.<\/p>\n<p>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):<\/p>\n<div style=\"overflow: scroll\">\n<pre class=\"code\">    <span style=\"color: blue\">&lt;<\/span><span style=\"color: maroon\">form <\/span><span style=\"color: red\">id<\/span><span style=\"color: blue\">=&quot;form1&quot; <\/span><span style=\"color: red\">runat<\/span><span style=\"color: blue\">=&quot;server&quot;&gt;\r\n    &lt;<\/span><span style=\"color: maroon\">div<\/span><span style=\"color: blue\">&gt;\r\n    \r\n        &lt;<\/span><span style=\"color: maroon\">asp<\/span><span style=\"color: blue\">:<\/span><span style=\"color: maroon\">SqlDataSource <\/span><span style=\"color: red\">ID<\/span><span style=\"color: blue\">=&quot;SqlDataSource1&quot; <\/span><span style=\"color: red\">runat<\/span><span style=\"color: blue\">=&quot;server&quot; \r\n            <\/span><span style=\"color: red\">ConnectionString<\/span><span style=\"color: blue\">=&quot;<\/span><span style=\"background: yellow\">&lt;%<\/span><span style=\"color: blue\">$ <\/span>ConnectionStrings:ConnectionString <span style=\"background: yellow\">%&gt;<\/span><span style=\"color: blue\">&quot; \r\n            <\/span><span style=\"color: red\">ProviderName<\/span><span style=\"color: blue\">=&quot;<\/span><span style=\"background: yellow\">&lt;%<\/span><span style=\"color: blue\">$ <\/span>ConnectionStrings:ConnectionString.ProviderName <span style=\"background: yellow\">%&gt;<\/span><span style=\"color: blue\">&quot; \r\n            <\/span><span style=\"color: red\">SelectCommand<\/span><span style=\"color: blue\">=&quot;SELECT * FROM [Customer]&quot;&gt;&lt;\/<\/span><span style=\"color: maroon\">asp<\/span><span style=\"color: blue\">:<\/span><span style=\"color: maroon\">SqlDataSource<\/span><span style=\"color: blue\">&gt;\r\n    \r\n    &lt;\/<\/span><span style=\"color: maroon\">div<\/span><span style=\"color: blue\">&gt;\r\n    &lt;<\/span><span style=\"color: maroon\">asp<\/span><span style=\"color: blue\">:<\/span><span style=\"color: maroon\">GridView <\/span><span style=\"color: red\">ID<\/span><span style=\"color: blue\">=&quot;GridView1&quot; <\/span><span style=\"color: red\">runat<\/span><span style=\"color: blue\">=&quot;server&quot; <\/span><span style=\"color: red\">AutoGenerateColumns<\/span><span style=\"color: blue\">=&quot;False&quot; \r\n        <\/span><span style=\"color: red\">DataKeyNames<\/span><span style=\"color: blue\">=&quot;ID&quot; <\/span><span style=\"color: red\">DataSourceID<\/span><span style=\"color: blue\">=&quot;SqlDataSource1&quot;&gt;\r\n        &lt;<\/span><span style=\"color: maroon\">Columns<\/span><span style=\"color: blue\">&gt;\r\n            &lt;<\/span><span style=\"color: maroon\">asp<\/span><span style=\"color: blue\">:<\/span><span style=\"color: maroon\">BoundField <\/span><span style=\"color: red\">DataField<\/span><span style=\"color: blue\">=&quot;ID&quot; <\/span><span style=\"color: red\">HeaderText<\/span><span style=\"color: blue\">=&quot;ID&quot; <\/span><span style=\"color: red\">InsertVisible<\/span><span style=\"color: blue\">=&quot;False&quot; \r\n                <\/span><span style=\"color: red\">ReadOnly<\/span><span style=\"color: blue\">=&quot;True&quot; <\/span><span style=\"color: red\">SortExpression<\/span><span style=\"color: blue\">=&quot;ID&quot; \/&gt;\r\n            &lt;<\/span><span style=\"color: maroon\">asp<\/span><span style=\"color: blue\">:<\/span><span style=\"color: maroon\">BoundField <\/span><span style=\"color: red\">DataField<\/span><span style=\"color: blue\">=&quot;Name&quot; <\/span><span style=\"color: red\">HeaderText<\/span><span style=\"color: blue\">=&quot;Name&quot; <\/span><span style=\"color: red\">SortExpression<\/span><span style=\"color: blue\">=&quot;Name&quot; \/&gt;\r\n        &lt;\/<\/span><span style=\"color: maroon\">Columns<\/span><span style=\"color: blue\">&gt;\r\n    &lt;\/<\/span><span style=\"color: maroon\">asp<\/span><span style=\"color: blue\">:<\/span><span style=\"color: maroon\">GridView<\/span><span style=\"color: blue\">&gt;\r\n    &lt;\/<\/span><span style=\"color: maroon\">form<\/span><span style=\"color: blue\">&gt;\r\n<\/span><\/pre>\n<\/div>\n<p>&#160;<\/p>\n<p>5. Put the following connection string into web.config file for SQL CE 4.0 connection string.<\/p>\n<div style=\"overflow: scroll\">\n<pre class=\"code\">    <span style=\"color: blue\">&lt;<\/span><span style=\"color: #a31515\">connectionStrings<\/span><span style=\"color: blue\">&gt;\r\n        &lt;<\/span><span style=\"color: #a31515\">add <\/span><span style=\"color: red\">name<\/span><span style=\"color: blue\">=<\/span>&quot;<span style=\"color: blue\">ConnectionString<\/span>&quot; <span style=\"color: red\">connectionString<\/span><span style=\"color: blue\">=<\/span>&quot;<span style=\"color: blue\">Data Source=|DataDirectory|Database1.sdf<\/span>&quot;\r\n            <span style=\"color: red\">providerName<\/span><span style=\"color: blue\">=<\/span>&quot;<span style=\"color: blue\">System.Data.SqlServerCe.4.0<\/span>&quot; <span style=\"color: blue\">\/&gt;\r\n    &lt;\/<\/span><span style=\"color: #a31515\">connectionStrings<\/span><span style=\"color: blue\">&gt;\r\n<\/span><\/pre>\n<\/div>\n<p>6. Ctrl-F5 to verify a table shows up in IE. <\/p>\n<p>7. In project\u2019s property page\u2019s \u201cPackage\/Publish SQL\u201d tab, click \u201cImport from Web.config\u201d to configure SQL deployment settings.<\/p>\n<p><a href=\"https:\/\/devblogs.microsoft.com\/dotnet\/wp-content\/uploads\/sites\/10\/2011\/06\/5314.image_thumb_1.png\"><img decoding=\"async\" style=\"border-right-width: 0px;padding-left: 0px;padding-right: 0px;border-top-width: 0px;border-bottom-width: 0px;border-left-width: 0px;padding-top: 0px\" title=\"image\" border=\"0\" alt=\"image\" src=\"https:\/\/devblogs.microsoft.com\/dotnet\/wp-content\/uploads\/sites\/10\/2011\/06\/5314.image_thumb_1.png\" width=\"669\" height=\"184\" \/><\/a><\/p>\n<p>&#160;<\/p>\n<p>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.&#160; <\/p>\n<p><a href=\"https:\/\/devblogs.microsoft.com\/dotnet\/wp-content\/uploads\/sites\/10\/2011\/06\/6787.image_thumb_2.png\"><img decoding=\"async\" style=\"border-right-width: 0px;padding-left: 0px;padding-right: 0px;border-top-width: 0px;border-bottom-width: 0px;border-left-width: 0px;padding-top: 0px\" title=\"image\" border=\"0\" alt=\"image\" src=\"https:\/\/devblogs.microsoft.com\/dotnet\/wp-content\/uploads\/sites\/10\/2011\/06\/6787.image_thumb_2.png\" width=\"420\" height=\"405\" \/><\/a><\/p>\n<p>&#160;<\/p>\n<p>9. Add the following to web.debug.config for transformation and replace the corresponding database access user ID and password. <strong>Note, we need to transform providerName from \u201c<span style=\"color: blue\">System.Data.SqlServerCe.4.0<\/span>\u201d to \u201c<span style=\"color: blue\">System.Data.SqlClient<\/span>\u201d.<\/strong><\/p>\n<div style=\"overflow: scroll\">\n<pre class=\"code\">    <span style=\"color: blue\">&lt;<\/span><span style=\"color: #a31515\">connectionStrings<\/span><span style=\"color: blue\">&gt;\r\n        &lt;<\/span><span style=\"color: #a31515\">add <\/span><span style=\"color: red\">name<\/span><span style=\"color: blue\">=<\/span>&quot;<span style=\"color: blue\">ConnectionString<\/span>&quot;\r\n          <span style=\"color: red\">connectionString<\/span><span style=\"color: blue\">=<\/span>&quot;<span style=\"color: blue\">Data Source=VenusIIS7a;Initial Catalog=test16;User ID=MyUserID;Password=MyPwd<\/span>&quot;\r\n          <span style=\"color: red\">providerName<\/span><span style=\"color: blue\">=<\/span>&quot;<span style=\"color: blue\">System.Data.SqlClient<\/span>&quot; <span style=\"color: red\">xdt:Transform<\/span><span style=\"color: blue\">=<\/span>&quot;<span style=\"color: blue\">SetAttributes<\/span>&quot; <span style=\"color: red\">xdt:Locator<\/span><span style=\"color: blue\">=<\/span>&quot;<span style=\"color: blue\">Match(name)<\/span>&quot;<span style=\"color: blue\">\/&gt;\r\n    &lt;\/<\/span><span style=\"color: #a31515\">connectionStrings<\/span><span style=\"color: blue\">&gt;\r\n<\/span><\/pre>\n<\/div>\n<p>&#160;<\/p>\n<p>10. <a href=\"http:\/\/blogs.msdn.com\/b\/webdevtools\/archive\/2010\/04\/20\/one-click-publish-what-s-new-since-beta-2.aspx\">Publish<\/a> the web application to the corresponding server.&#160; You\u2019ll notice that SQL CE 4.0 database are successfully deployed to the destination\u2019s SQL Server, with \u201cweb.config\u201d connection string transformed.&#160; <\/p>\n<p>&#160;<\/p>\n<p>That\u2019s the end of walkthrough.&#160; 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.<\/p>\n<p>&#160;<\/p>\n<p>Thanks for reading<\/p>\n<p>Xinyang Qiu<\/p>\n<p>SDET<\/p>\n<p>Web Platform And Tools<\/p>\n","protected":false},"excerpt":{"rendered":"<p>With VS2010 sp1, we added SQL CE 4.0 support. You can view more information about SQL CE 4.0 from the following sites: http:\/\/weblogs.asp.net\/scottgu\/archive\/2010\/06\/30\/new-embedded-database-support-with-asp-net.aspx&#160; http:\/\/blogs.msdn.com\/b\/webdevtools\/archive\/2011\/01\/06\/how-to-bin-deploy-sql-compact-edition-4-0-and-razor-web-projects.aspx http:\/\/www.microsoft.com\/downloads\/en\/details.aspx?FamilyID=033cfb76-5382-44fb-bc7e-b3c8174832e2 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 [&hellip;]<\/p>\n","protected":false},"author":404,"featured_media":58792,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[197],"tags":[7343,7390,7387,7340],"class_list":["post-2704","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-aspnet","tag-publish","tag-sql-ce","tag-visual-studio-2010-sp1","tag-web-config-transformation"],"acf":[],"blog_post_summary":"<p>With VS2010 sp1, we added SQL CE 4.0 support. You can view more information about SQL CE 4.0 from the following sites: http:\/\/weblogs.asp.net\/scottgu\/archive\/2010\/06\/30\/new-embedded-database-support-with-asp-net.aspx&#160; http:\/\/blogs.msdn.com\/b\/webdevtools\/archive\/2011\/01\/06\/how-to-bin-deploy-sql-compact-edition-4-0-and-razor-web-projects.aspx http:\/\/www.microsoft.com\/downloads\/en\/details.aspx?FamilyID=033cfb76-5382-44fb-bc7e-b3c8174832e2 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 [&hellip;]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/dotnet\/wp-json\/wp\/v2\/posts\/2704","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/dotnet\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/dotnet\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/dotnet\/wp-json\/wp\/v2\/users\/404"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/dotnet\/wp-json\/wp\/v2\/comments?post=2704"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/dotnet\/wp-json\/wp\/v2\/posts\/2704\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/dotnet\/wp-json\/wp\/v2\/media\/58792"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/dotnet\/wp-json\/wp\/v2\/media?parent=2704"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/dotnet\/wp-json\/wp\/v2\/categories?post=2704"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/dotnet\/wp-json\/wp\/v2\/tags?post=2704"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}