如何确定与C＃中的SQL Server CE表是否存在一列？ [英] How can I determine whether a column exists in a SQL Server CE table with C#?

问题描述

遗留代码做这种方式：

public bool isValidField(string tableName, string fieldName)
{
    bool retVal;
    string tblQuery = string.Format("SELECT {0} FROM {1}", fieldName, tableName);
    checkConnection();
    try
    {
        SqlCeCommand cmd = objCon.CreateCommand();
        cmd.CommandText = tblQuery;
        object objvalid = cmd.ExecuteScalar();
        retVal = (null != objvalid);
    }
    catch
    {
        retVal = false; 
    }
    return retVal;
}

...但我觉得它并不总是工作。调用该方法，并获得后假，一些代码改变了表中添加某些列，但我发现：

...but I find it doesn't always work. After calling that method, and getting false, some code ALTERS the table to add some columns, but I'm getting:

发生了列ID比在规范中多次。

A column ID occurred more than once in the specification.

我发现这里在计算器上一几个有前途的SQL语句：

I found here on StackOverflow a couple of promising SQL statements:

if exists(select * from sys.columns 
        where Name = N'columnName' and Object_ID = Object_ID(N'tableName'))  

和

IF COL_LENGTH('table_name','column_name') IS NULL

...但我不知道如何实现这在C＃/。NET 1.1

...but am not sure how to implement this in C# / .NET 1.1

我是否需要使用的ExecuteScalar和转换返回值为BOOL？还是其他什么东西。

Do I need to use ExecuteScalar and cast the returned value to a bool? Or something else?

将其更改为这并没有纠正的问题：

Changing it to this didn't rectify matters:

public bool isValidField(string tableName, string columnName)
{
    bool retVal;
    string tblQuery = string.Format(
        "COL_LENGTH({0},{1}) IS NULL",
        tableName,
        columnName);

    checkConnection();
    try
    {
        SqlCeCommand cmd = objCon.CreateCommand();
        cmd.CommandText = tblQuery;
        object objvalid = cmd.ExecuteScalar();
        retVal = Convert.ToBoolean(objvalid);
    }
    catch
    {
        retVal = false; 
    }
    return retVal;
}

更新2

奇怪的是，我看到更多的新代码的错误消息。

UPDATE 2

Oddly enough, I see even more of those error messages with the new code.

据没有什么区别时，我改变了这样的代码：

It made no difference when I altered the code this way:

string tblQuery = "SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS"
                  + " WHERE TABLE_NAME = @tableName AND COLUMN_NAME"
                  + " = @columnName";

checkConnection();
try
{
    SqlCeCommand cmd = objCon.CreateCommand();
    cmd.CommandText = tblQuery;
    SqlCeParameter tblNameParam = new SqlCeParameter(
        "@tableName",
        SqlDbType.NVarChar,
        128);

    tblNameParam.Value = tableName;
    cmd.Parameters.Add(tblNameParam);
    SqlCeParameter colNameParam = new SqlCeParameter(
        "@columnName",
        SqlDbType.NVarChar,
        128);

    colNameParam.Value = tableName;
    cmd.Parameters.Add(colNameParam);
    int i = (int)cmd.ExecuteScalar();
    retVal = i > 0;
}

...所以我不知道哪条路是优选的;这似乎更直接给我...任何想法，任何人？

...so I don't know which way is preferable; this does seem more straightforward to me...any thoughts, anyone?

推荐答案

您可以直接查询的信息模式表以获得您想要的信息：

You can just query the information schema tables to get the information you want:

public bool isValidField(string tableName, string columnName)
{
    var tblQuery = "SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS"
                   + " WHERE TABLE_NAME = @tableName AND"
                   + " COLUMN_NAME = @columnName";

    SqlCeCommand cmd = objCon.CreateCommand();
    cmd.CommandText = tblQuery;
    var tblNameParam = new SqlCeParameter(
        "@tableName",
        SqlDbType.NVarChar,
        128);

    tblNameParam.Value = tableName
    cmd.Parameters.Add(tblNameParam);
    var colNameParam = new SqlCeParameter(
        "@columnName",
        SqlDbType.NVarChar,
        128);

    colNameParam.Value = columnName
    cmd.Parameters.Add(colNameParam);
    object objvalid = cmd.ExecuteScalar(); // will return 1 or null
    return objvalid != null;
}

这篇关于如何确定与C＃中的SQL Server CE表是否存在一列？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！