[Webinar] Streamline your web hosting managementRegister Today

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 345
  • Last Modified:

Create Dataset from SQL CE Database

Is there a way to create a dataset from a sqlce database.  Basically I want to iterate through the structure of a local database on the client computer and compare it with another sqlcedb (strongly typed so that is easy) to update any column or table changes.
 Here is all I have so far.  There are never any tables in the collection, so I beleive I need to "populate" the schema info from the database on the client machine, but I am not sure how to do it.  Thanks.
Dim sConnectionString As String
        sConnectionString = "Data Source=" & My.Settings.DBLocation

        Dim objConn As New SqlCeConnection(sConnectionString)

        Dim dsUserDB As New DataSet("UserDB")

        Dim table As DataTable
        For Each table In dsUserDB.tables

Open in new window

  • 2
1 Solution
Try the following code.

Refer Information Schema (SQL Server Compact)

 SqlCeConnection cn = new SqlCeConnection("YourConnectionString");
            SqlCeDataAdapter da = new SqlCeDataAdapter("SELECT * FROM INFORMATION_SCHEMA.TABLES", cn);
            DataTable dt = new DataTable();
            foreach (DataRow row in dt.Rows)
                Console.WriteLine("Table Name : " + row["TABLE_NAME"].ToString());

Open in new window

VB.NET version of the above code.

Imports System.Data
Imports System.Data.SqlServerCe

Public Class Form1
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim cn As SqlCeConnection = New SqlCeConnection("YourConnectionString")
        Dim da As SqlCeDataAdapter = New SqlCeDataAdapter("SELECT * FROM INFORMATION_SCHEMA.TABLES", cn)
        Dim dt As DataTable = New DataTable()
        For Each row As DataRow In dt.Rows
            Console.WriteLine("Table Name : " & row("TABLE_NAME").ToString())
    End Sub
End Class

Open in new window

vwallaAuthor Commented:
Perfect!  Thanks.  I can figure out iterating the columns from here http://msdn.microsoft.com/en-us/library/ms188348.aspx.

Thanks again!

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now