Dodsworth
asked on
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
Thanks
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.
ASKER
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 :(
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
Print Column.name & " " & Column.DataType
Only I cannot use SQL. The platform only allows LinqToSQL :(
Then you need to define LINQ2SQL classes and then use the traditional LINQ2SQL approach to retrieve and read those objects.
ASKER
yes I know that.. I just don't know how to do it ;)
Hi Dodsworth;
The below code snippet should get the results you are looking for.
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);
}
}
ASKER
but I can't use SQL only LinqToSQL
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Problem resolved