Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

.NET Compact - Save Dataset as SQLCE Database

Posted on 2005-04-15
7
Medium Priority
?
1,612 Views
Last Modified: 2008-02-01
I'm working in VB.NET Compact Framework on a Pocket PC.  I have a simple one table dataset that I created from an XML file.  I would lnow like to save this dataset as a SQLCE database on the mobile device.

Thanks!
0
Comment
Question by:gerrystrat
  • 4
  • 3
7 Comments
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 13791063
do you want to create a database and a table or just the table (in an existing database)
0
 

Author Comment

by:gerrystrat
ID: 13791138
Both the database and the table.
0
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 13792416
Do you now the structure of the table when you want to create it , or is your datatable dynamic.

I'm going home now, but I'll answer your question tomorrow morning, I hope this is not a problem.

the solution is quite simple but it will take some coding
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:gerrystrat
ID: 13793170
The structure will be known.  It will be read in, along with the data, through am xml file.  I want to use a sdf file to store changes after each record edit.  

I figured that I could use a sqlcommand to create the table and another to insert the data, but I'm not quite sure how to get the data from the dataset.  It seems like there should be a method similar to "Dataset.WriteXml"

Thanks
0
 
LVL 25

Accepted Solution

by:
RonaldBiemans earned 2000 total points
ID: 13796639
 Private Sub CreateDatabase()
    Dim cn As System.Data.SqlServerCe.SqlCeConnection
    Dim SQL As String
    Dim SQLEngine As System.Data.SqlServerCe.Engine

' If the database exists, blow it away.
'    If System.IO.File.Exists("\My Documents\yourdb.sdf") Then
'      System.IO.File.Delete("\My Documents\yourdb.sdf")
'    End If

'A check is made to determine if the database exists. If not, it is created. At this point the database is empty, so there is 'no structure or content.

' If the database does not already exist, then create it.
    If Not System.IO.File.Exists("\My Documents\yourtable.sdf") Then
      SQLEngine = New System.Data.SqlServerCe.Engine _
       ("data source=\My Documents\yourdb.sdf")
      SQLEngine.CreateDatabase()

'Now that the database exists, it can be opened.

' Next, open the database.
      cn = New _
        System.Data.SqlServerCe.SqlCeConnection("Provider=Microsoft.SQLServer.O _
        LEDB.CE.1.0;Data Source=\My Documents\yourdb.sdf")
      cn.Open()

'create a table

SQL = "CREATE TABLE Yourtable (ID nchar(5) Primary Key _
        NOT NULL,Name nvarchar(40) NOT NULL)"
      Dim cmd As New System.Data.SqlServerCe.SqlCeCommand(SQL, cn)
      cmd.CommandType = CommandType.Text
      cmd.ExecuteNonQuery()
      cn.close
end sub


' now to fill this table with the data 'DS2 is the name of the dataset you created from the XML i

private sub filldata()
      cn = New _
        System.Data.SqlServerCe.SqlCeConnection("Provider=Microsoft.SQLServer.O _
        LEDB.CE.1.0;Data Source=\My Documents\yourdb.sdf")
      cn.Open()
        dim ds as new dataset
        Dim daCE As New SqlServerCe.SqlCeDataAdapter("select * from mytable", cn)
        Dim cm As New SqlServerCe.SqlCeCommandBuilder(daCE)
        daCe.fill(ds)
        daCe.Update(ds2)
        cn.close
        'ds and ds2 should be identical in structure for this to work
end sub
0
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 13796640
also, you should reference

system.data.common and
system.data.sqlserverce

0
 

Author Comment

by:gerrystrat
ID: 13798907
Thanks for the excellent response!  I was close to the right track but not on it.  

I had to make a couple changes:

Dim SQLEngine As System.Data.SqlServerCe.SqlCeEngine
instead of
Dim SQLEngine As System.Data.SqlServerCe.Engine

and on:
      cn = New _
        System.Data.SqlServerCe.SqlCeConnection("Provider=Microsoft.SQLServer.O _
        LEDB.CE.1.0;Data Source=\My Documents\yourdb.sdf")
It would not run unless I omitted the provider.

And on the saCE update from ds2 I just had to add the tablemapping.

Thanks again for posting over the weekend.  You've helped me a lot.  This is an important project and I'll be working on it non-stop.

Gerry
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…
Suggested Courses

577 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question