How to find out SQL Server CE column's length
I created a table using SQL Server CE like this:
SqlCeCommand createTableCmd = new SqlCeCommand();
createTableCmd.CommandText = "Create table docEntry (id nvarchar (70) not null PRIMARY KEY, "
+ "parent nvarchar(70), "
+ "lmt big开发者_如何学Pythonint not null, "
+ "fileName nvarchar(70) not null)";
Table name is docEntry
, and the column width I need to find out is fileName column.
The purpose is to detect if the column width is 70, if yes, I need to expand it to other size, else leave it.
I tried
SELECT COL_LENGTH(docEntry, fileName)
it caused exception:
SqlCeException was caught:
The column name is not valid. [ Node name (if any) = ,Column name = docEntry ]
I don't know why...
Anyone knows?
Think you will have to do it the long way:
SELECT character_maximum_length
FROM information_schema.columns
WHERE table_name = 'docentry'
AND column_name = 'filename'
beside providerspecific solution from @HadleyHope there is a solution that works for all Dabases (at least i tried with mssql2005, oracle10, SQlite3 and MsAccess via OleDB. i have no sqlce on my machine to verify):
DbConnection.GetSchema()
This codes works for MsSql.
using (DbConnection con = new SqlConnection())
{
con.ConnectionString = ...;
con.Open();
DataTable tabeWithSchemaInfo = con.GetSchema("AllColumns");
You have to replace con = new SqlConnection()
if "AllColumns" is not supported by SqlCeConnection call con.GetSchema()
to get a list of supported properties.
For more info see GetSchema - DbConnection.GetSchema in ADO.NET 2.0 - Retrieve Databases Tables Columns Views etc. from Database Connection and msdn DbConnection.GetSchema()
Table and column names need to be in quotes:
SELECT COL_LENGTH('docEntry', 'fileName')
精彩评论