Solved

Enumerate Tables and Columns in SQL CE

Posted on 2014-02-07
9
689 Views
Last Modified: 2016-02-10
I need to enumerate tables and columns of a SQL CE database using LinqToSQL in VB.net.

Thanks
0
Comment
Question by:Dodsworth
  • 5
  • 3
9 Comments
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 39842006
Please read this web page, SQL Server Compact and LINQ to SQL, It all depends on what you mean by Enumerate Tables and Columns, please be more specific.
0
 
LVL 1

Author Comment

by:Dodsworth
ID: 39842153
OK I have found that CE has the views:

INFORMATION_SCHEMA.Tables
INFORMATION_SCHEMA.Columns

So in pseudo I need:

For Each Table in INFORMATION_SCHEMA.Tables
    Print Table.Name
        For Each Column in INFORMATION_SCHEMA.columns where TableName = Table.Name
             Print Column.name & " " & Column.DataType

Only I cannot use SQL.  The platform only allows LinqToSQL :(
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 39842711
Then you need to define LINQ2SQL classes and then use the traditional LINQ2SQL approach to retrieve and read those objects.
0
 
LVL 1

Author Comment

by:Dodsworth
ID: 39842756
yes I know that.. I just don't know how to do it ;)
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 62

Expert Comment

by:Fernando Soto
ID: 39843771
Hi Dodsworth;

The below code snippet should get the results you are looking for.

// Where filename is the name of the database file with path.
// The password is the password on the database if any 
connectionString = string.Format("DataSource=\"{0}\"; Password='{1}'", fileName, password);
SqlCeConnection cn = new SqlCeConnection(connectionString);

// Test to see if the connection is open, if not open it.
if (cn.State == ConnectionState.Closed)
{
    cn.Open();
}

// Create a command object
SqlCeCommand cmd;

// SQL query to get table names
string sql = "SELECT * FROM INFORMATION_SCHEMA.TABLES";
cmd = new SqlCeCommand(sql, cn);
var ts = cmd.ExecuteResultSet(ResultSetOptions.Scrollable);
// SQL query to get column names
sql = "SELECT * FROM INFORMATION_SCHEMA.COLUMNS";
cmd = new SqlCeCommand(sql, cn);
var cols = cmd.ExecuteResultSet(ResultSetOptions.Scrollable);

// Convert the columns data so that it can be queryed 
List<SqlCeUpdatableRecord> col = new List<SqlCeUpdatableRecord>();
foreach (SqlCeUpdatableRecord c in cols)
{
    col.Add(c);
}

// Get the table names and its column name
foreach (SqlCeUpdatableRecord t in ts )
{
    // The table name
    var tableName = t.GetValue(2);
    Console.WriteLine(tableName);

    // Display its column names
    foreach(string cName in col.Where(c => c.GetValue(2).Equals(tableName)).Select(c => c.GetValue(3).ToString()).ToList())
    {
        Console.WriteLine("\t\t" + cName);
    }
}

Open in new window

0
 
LVL 1

Author Comment

by:Dodsworth
ID: 39844016
but I can't use SQL only LinqToSQL
0
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 39844432
Well that may be a problem then. It appears that Linq to SQL using SQL CE does not support bringing into the model system tables and without that you can not use Linq to SQL to query the database. Now you can try using the Linq to SQL ExecuteQuery method, if you could get it to work, I have not been able to, to query the system tables. The exception I get is that I am using version 4 of SQL CE but am requesting version 3.5, even though my DLL is version 4 and my database is also version 4.
0
 
LVL 1

Accepted Solution

by:
Dodsworth earned 0 total points
ID: 39845860
No ExecuteQuery for Windows Phone :(

I researched the Information_Schema views and wrote two entity classes, then used

Private Sub test()
        Using dc = New mContext(ConnectionString)
            Dim tabs = From t In dc.Tables Select t.table
            For Each tab In tabs
                System.Diagnostics.Debug.WriteLine(tab)
                Dim cols = From c In dc.Columns Where CStr(c.table) Is tab
                For Each col In cols
                    System.Diagnostics.Debug.WriteLine("     " & col.column & " " & col.datatype)
                Next
            Next
        End Using
    End Sub

Open in new window

0
 
LVL 1

Author Closing Comment

by:Dodsworth
ID: 39866897
Problem resolved
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

758 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now