开发者

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')
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜