[Webinar] Streamline your web hosting managementRegister Today

x
  • 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)
        objConn.Open()


        Dim dsUserDB As New DataSet("UserDB")

       
       
        Dim table As DataTable
        For Each table In dsUserDB.tables
            Console.WriteLine(table.TableName)
        Next

Open in new window

0
vwalla
Asked:
vwalla
  • 2
1 Solution
 
RameshSCommented:
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();
            da.Fill(dt);
            foreach (DataRow row in dt.Rows)
            {
                Console.WriteLine("Table Name : " + row["TABLE_NAME"].ToString());
            }

Open in new window

0
 
RameshSCommented:
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()
        da.Fill(dt)
        For Each row As DataRow In dt.Rows
            Console.WriteLine("Table Name : " & row("TABLE_NAME").ToString())
        Next
    End Sub
End Class

Open in new window

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

Thanks again!
0

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