Exporting a database from Sql Server Compact to Sql Server 2008
I'm developing an application, which originally used a Sql Server Compact database.The reason to chose rhis database was it's easy use, fine integration with Visual Basic.Net and the possibility to copy the database with maximum ease.
But during an early test-phase I stumbled onto the field length limitations in this database, as the xml-data which had te be stored was too long. It seemed to me the best solution for this challenge was converting the Sql Server Compact Database to Sql Server 2008.
With confidence I started to search the internet for a freeware tool or script that could do the job for me. After a search of an hour I gave up. I couldn't find what I was looking for. As I can be stubborn I decided to write the tool myself.
Engineering a tool to copy structure from Sql Server Compact to Sql Server 2008
I thought about what the tool had to do. I came up with the following two points:
- Examine the structure of the Sql Server Compact Database, and retrieve all data
- Create the tables and copy the data to the Sql Server 2008 database
Examine the database structure from a Sql Server Compact Database
In a previous article I wrote about examining the database structure from a Sql Server Compact 3.5 database. This part was the easy part:
- create and open a connection to the database file
- Loop through the fields for each table, reading each fieldname and fieldtype
- Loop through each record for each table, reading each value from each field
Creating the table structure in Sql Server 2008
With the structure found in the first step, it should be possible to create a table in Sql Server 2008. Building a SQL CREATE command, with the right fields and field types, could do the job.
'connSqlCompactDatabase is a SqlCeConnection, defining the connection to the Sql Server Compact Database
'sTable is a string, filled with the Table name to copy
Dim cmd As New SqlCeCommand("Select * from " & sTable, connSqlCompactDatabase)
Dim dr As SqlCeDataReader
Dim flds As String = "" 'Variable to hold the fields of the table
dr = cmd.ExecuteReader 'read complete table
'Sql variable is a string in which the complete CREATE TABLE Sql statement will be build
Dim Sql As String = "Create table " & sTable & "( "
Dim comma As String = "" 'Variable to seperate fields in the query.
For i As Integer = 0 To dr.FieldCount - 1
'All my tables have an Id-field, which is a bigint, defining Identity.
'If you use this code, probably you have to rewrite this part
If dr.GetName(i) = "id" Then
Sql &= comma & dr.GetName(i) & " bigint IDENTITY(1, 1) PRIMARY KEY CLUSTERED "
flds &= comma & dr.GetName(i)
Else
'Sql Server 2008 doesn't like a field name "user", so translate this field name into "username"
If dr.GetName(i) = "user" Then
Sql &= comma & " username "
Else
Sql &= comma & dr.GetName(i) & " "
End If
'Now the Sql Statement contains the field name. Now add the field type.
'If the fieldname of my table is "xml", I want to make it a xml-type. If the name contains "xml", but isn't "xml", like "xml_definition", it has to be of type "text".
If dr.GetName(i).Contains("xml") Then
If dr.GetName(i) = "xml" Then
Sql &= " xml "
Else
Sql &= " text "
End If
Else
'This part was made in trial and error mode. If someone knows a better solution, please
This e-mail address is being protected from spambots. You need JavaScript enabled to view it
.
'Although I don't like the code very much, it worked fine for the purpose it had to serve.
Select Case dr.GetFieldType(i).ToString.Replace("System.", "")
Case "String"
Sql &= " nvarchar(500) "
Case "Int64", "Int32"
Sql &= " bigint "
Case "Boolean"
Sql &= " tinyint "
Case Else
Sql &= " " & dr.GetFieldType(i).ToString.Replace("System.", "")
End Select
End If
'I allow all fields to be NULL
Sql &= " NULL "
End If
comma = ", " 'After the first field, the comma-variable is changed to ",". As I conatenate it between the fields, this works fine to seperate the fields in an easy way. Other solutions, like defining the fields first in an array, and joining them together with the Join-statement is also possible.
Next
'Don't forget the closing bracket for the SQL-statement
Sql &= ")"
Dim cmdSql As New SqlCommand(Sql, ConnSql)
cmdSql.ExecuteNonQuery()
So, now the table is created, having an identity field called Id.Now Copy the data. But before that, you must tell the Sql Server 2008 database you allow to insert records with an identity field already filled.
Soothing the Identity complaint
Sql Server 2008 will complain about your identity field, if the Identity is defined as in the code above, and if you try to insert data with the Id-field filled. As I want to copy the data from an another database, without changing the identity records, I wanted to sooth the complaints from Sql Server 2008.
Sql = "SET IDENTITY_INSERT " & sTable & " ON"
cmdSql = New SqlCommand(Sql, ConnSql)
cmdSql.ExecuteNonQuery()
This code does the trick. After filling the table, you can do the reverse:
Sql = "SET IDENTITY_INSERT " & sTable & " OFF"
cmdSql = New SqlCommand(Sql, ConnSql)
cmdSql.ExecuteNonQuery()
Copy data to the Sql Server 2008 database
Now my tool is ready to copy data from the Sql Server Compact Database to the Sql Server 2008 database.
Dim SkipRec As Boolean = False ' True if record must be skipped
Do While dr.Read 'Loop through data-records
SkipRec = False
'I use the flds-variable I filled in the code above for the INSERT statement as well
Sql = "INSERT INTO " & sTable & " ( " & flds & " ) VALUES ( "
comma = ""
'For each field you have to examine the field type, as you have to place quotes around the strings.
For i As Integer = 0 To dr.FieldCount - 1
If Not IsDBNull(dr.Item(i)) Then
Select Case dr.GetFieldType(i).ToString.Replace("System.", "")
Case "String", "DateTime"
Sql &= comma & " '" & dr.Item(i).ToString.Replace("'", """") & "' "
Case "Boolean"
If dr.Item(i) = False Then
Sql &= comma & " 0 "
Else
Sql &= comma & " 1 "
End If
Case Else
Sql &= comma & dr.Item(i)
End Select
Else
If dr.GetName(i) = "id" Then
SkipRec = True
End If
Sql &= comma & " NULL "
End If
comma = ", "
Next i
Sql &= ")"
If Not SkipRec Then
cmdSql = New SqlCommand(Sql, ConnSql)
cmdSql.ExecuteNonQuery()
End If
Loop
This code worked fine for the situation I stumbled into. I hope you can use it. But it may require some modifications to work for your problem!
|
