Click here to Skip to main content
15,888,286 members
Search within:


Articles / Desktop Programming / Windows Forms
 
Article

SQL Server to SQL Server Compact Edition Database Copy Utility

Rate me:
Please Sign up or sign in to vote.
4.95/5 (39 votes)
26 Jun 2008BSD2 min read 348.4K   16.8K   123   59
Create and manage your mobile database using SQL Management Studio and export them to SQL Compact Edition databases
Image 1

UPDATE

Please download the latest version of this tool from JohnnyCantCode.com. I have updated this utility based on some feedback from users.

I added the ability to select the schema you wish to copy. For most databases, this will simply be "dbo".

I fixed a bug where the application did not recognize a valid version of "System.Data.SqlServerCe.dll".

Introduction

This utility will copy the schema and data from a normal SQL Server 2000/2005 database and export it to a SQL Server Compact Edition database. The tool supports version 3.1 or 3.5 of SQL Server Compact Edition.

Background

I was working on a mobile application and needed an easy way to manage the table relationships and indexes without having to resort to scripting. I was familiar with using SQL Server Management Studio so I decided that I would write a utility that converted a normal SQL Server database to a mobile database. This way, I could continue using the tool I normally use to create and maintain my databases.

Using the Code

Feel free to take a look at the code and offer your most gracious comments. The application uses a wizard to walk you through converting a database. I have also included a normal WINFORM that also does the conversion, but I stopped development on this in lieu of the wizard, so the form is incomplete. I will be maintaining this code and writing more about it on my blog at www.JohnnyCantCode.com. You can find the original post here.

Points of Interest

This utility will copy Indexes, Primary Keys, Foreign Keys, Table structure and data. SQL Server Compact Edition does not have support for Views, Triggers nor Stored Procedures, therefore this utility does not copy these.

History

  • 1.0 Initial release

License

This article, along with any associated source code and files, is licensed under The BSD License


Written By
johnnycantcode
Software Developer (Senior) Gologic Tech LLC.
United States United States
I work as an independent software architect and senior developer. I have worked on many large enterprise projects as well as small single user applications.

Comments and Discussions

 
FirstPrevNext
Generalgetting error just-in-time (JIT) debugging instead of this dialog box Pin
psnlakshmi30-Oct-10 1:40
psnlakshmi30-Oct-10 1:40 
GeneralBad Image Format Exception Pin
i_lonsdale@hotmail.com15-Oct-10 22:43
i_lonsdale@hotmail.com15-Oct-10 22:43 
Generalproblem with varbinary Pin
pgarbo17-Aug-10 5:05
pgarbo17-Aug-10 5:05 
GeneralThanks Pin
Uros Calakovic8-Aug-10 2:00
Uros Calakovic8-Aug-10 2:00 
QuestionException where Copying Database Pin
EdgarVegaD14-Jan-10 4:36
EdgarVegaD14-Jan-10 4:36 
AnswerRe: Exception where Copying Database Pin
EdgarVegaD14-Jan-10 8:14
EdgarVegaD14-Jan-10 8:14 
AnswerRe: Exception where Copying Database Pin
dille421-May-10 3:34
dille421-May-10 3:34 
GeneralGETUTCDATE() & Multi-Column Key's Pin
michael@merlot.com21-Nov-09 4:41
michael@merlot.com21-Nov-09 4:41 
Hi Johnny,

Nice tool you've created. It certainly fills the lack which MS leaves and avoids manual scripting.
I've noticed two things which caused minor problems: if a column in SQL Server has GETUTCDATE() as default value, there's an issue because CE does not have this function.
So I've solved this by adding the next line in WizardForm.cs - DoCopy():

if (col.DefaultConstraint != null && !String.IsNullOrEmpty(col.DefaultConstraint.Text))
{
    // SQL Server CE 3.5 does not support function GETUTCDATE()
    col.DefaultConstraint.Text = col.DefaultConstraint.Text.Replace("(getutcdate())", "(getdate())");
    string def = col.DefaultConstraint.Text.Replace("((", "(").Replace("))", ")");

    sb.Append(" DEFAULT ").Append(col.DefaultConstraint.Text);
    //sb.Append(" DEFAULT (1) ");
}


The second thing I encountered is the script-creation for a ForeignKey on Multi-Column Key's.
Only the first referenced column was included and there was a problem with the square brackets.

// Check If Multi-Column Key's => Iterate Keys
string createFKSql = String.Empty;
if (sourceDb.Tables[fk.ReferencedTable].Indexes[fk.ReferencedKey].IndexedColumns.Count == 1)
{
    // Single PK so use the original string
    createFKSql = String.Format(fkSql, tbl.Name, fk.Name, "{0}", fk.ReferencedTable, sourceDb.Tables[fk.ReferencedTable].Indexes[fk.ReferencedKey].IndexedColumns[0].Name);
}
else if (sourceDb.Tables[fk.ReferencedTable].Indexes[fk.ReferencedKey].IndexedColumns.Count > 1)
{
    // Multi-Column PK
    createFKSql = String.Format(fkSql, tbl.Name, fk.Name, "{0}", fk.ReferencedTable, "{1}");
    string referencedColumns = String.Empty;
    foreach(IndexedColumn iCol in sourceDb.Tables[fk.ReferencedTable].Indexes[fk.ReferencedKey].IndexedColumns)
    {
        // Separate Columns including the square brackets
        // to identify each referenced column.
        if (referencedColumns.Length > 0)
            referencedColumns = referencedColumns + "], [";

        referencedColumns = referencedColumns + iCol.Name;
    }
    createFKSql = String.Format(createFKSql, "{0}", referencedColumns);
}

StringBuilder sbFk = new StringBuilder();
foreach (ForeignKeyColumn col in fk.Columns)
{
    // Added square brackets to identify each column.
    if (sbFk.Length > 0)
        sbFk.Append("], [");

    sbFk.Append(col.Name);
}


But, as I said: only minor issues on a great tool Smile | :)
Sorry if I haven't used the 'best' coding-practice on these snippets, but I'm not a C#, but VB.Net developer.

Kind regards,

Michael
GeneralFor SQL Server CE 3.5 SP1 Pin
kshakir23-Jul-09 10:08
kshakir23-Jul-09 10:08 
QuestionSql 2008 support? Pin
khanricksteele29-Apr-09 7:26
khanricksteele29-Apr-09 7:26 
QuestionUnknown datatype Pin
amre3l18-Mar-09 23:38
amre3l18-Mar-09 23:38 
AnswerRe: Unknown datatype Pin
Francois YACOB16-Jun-09 6:32
Francois YACOB16-Jun-09 6:32 
GeneralPrimery Keys are not correct after import to SQL Compact Pin
voellinger26-Jan-09 0:51
voellinger26-Jan-09 0:51 
GeneralOut of memory error Pin
cess30-Nov-08 9:36
cess30-Nov-08 9:36 
Generalthere is problem with NVARCHAR(MAX) Pin
Tajniak520-Nov-08 5:51
Tajniak520-Nov-08 5:51 
Generaliv been messing with this for a while until i found this.TNX!!!!! Pin
zivzuv18-Nov-08 20:37
zivzuv18-Nov-08 20:37 
Generalprimary key contraint names Pin
mape108219-Aug-08 5:04
mape108219-Aug-08 5:04 
GeneralRe: primary key contraint names Pin
Francois YACOB16-Jun-09 6:38
Francois YACOB16-Jun-09 6:38 
GeneralThis thing rocks! Pin
granadaCoder5-Aug-08 10:32
granadaCoder5-Aug-08 10:32 
GeneralGreat work!!! only one thing and an Idea for future work Pin
mape108223-Jul-08 6:19
mape108223-Jul-08 6:19 
GeneralRe: Great work!!! only one thing and an Idea for future work Pin
Francois YACOB16-Jun-09 6:43
Francois YACOB16-Jun-09 6:43 
GeneralType Conversion errors Pin
gratajik15-Jul-08 13:25
gratajik15-Jul-08 13:25 
GeneralON DELETE ... ON UPDATE ... Pin
Hoang Cuong12-Jul-08 22:21
Hoang Cuong12-Jul-08 22:21 
Generaldata transfer Pin
Shaikh Sahrif28-Jun-08 19:18
Shaikh Sahrif28-Jun-08 19:18 
GeneralBrilliant Pin
Johnny J.26-Jun-08 20:44
professionalJohnny J.26-Jun-08 20:44 
Last Visit: 31-Dec-99 18:00     Last Update: 29-Apr-24 5:44Refresh123 Next ᐅ

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink
Advertise
Privacy
Cookies
Terms of Use
Layout: fixed | fluid

Posted 29 Apr 2008

Article Copyright 2008 by johnnycantcode
Everything else Copyright © CodeProject, 1999-2024

Web04 2.8:2024-04-02:1