Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 869
  • Last Modified:

Enumerate Tables and Columns in SQL CE

I need to enumerate tables and columns of a SQL CE database using LinqToSQL in VB.net.

Thanks
0
Dodsworth
Asked:
Dodsworth
  • 5
  • 3
1 Solution
 
Fernando SotoRetiredCommented:
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
 
DodsworthAuthor Commented:
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
 
CodeCruiserCommented:
Then you need to define LINQ2SQL classes and then use the traditional LINQ2SQL approach to retrieve and read those objects.
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
DodsworthAuthor Commented:
yes I know that.. I just don't know how to do it ;)
0
 
Fernando SotoRetiredCommented:
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
 
DodsworthAuthor Commented:
but I can't use SQL only LinqToSQL
0
 
Fernando SotoRetiredCommented:
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
 
DodsworthAuthor Commented:
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
 
DodsworthAuthor Commented:
Problem resolved
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

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