How can I determine whether a column exists in a SQL Server CE table with C#?

Question

The legacy code does it this way:

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:

A column ID occurred more than once in the specification.

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

and

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

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

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

UPDATE

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;
}

UPDATE 2

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

UPDATE 3

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?

That depends on how isValidField() is called - how the vals for the args are acquired, doesn't it? — B. Clay Shannon-B. Crow Raven, Mar 28 '13 at 23:05
There is a GetSchema method that could give you any details on your table schema, but, if I remember correctly it is available startin from Framework 2.0 — Steve, Mar 28 '13 at 23:09
@ClayShannon - Yes and no. It's more proper to create `SqlParameters` to ensure the data isn't tampered with. — Code Maverick, Mar 28 '13 at 23:12
@Scott: Agreed/geschenckt. I could use the excuse that I'm not at the "gingerbread"/finish carpentry part yet, but...there's no time like the present. — B. Clay Shannon-B. Crow Raven, Mar 28 '13 at 23:13
[Check this article on MSDN](http://msdn.microsoft.com/en-US/library/kcax58fh(v=vs.71).aspx), you need to test with SqlCeConnection, but probably it will work also for that — Steve, Mar 28 '13 at 23:16
@Steve: Ah yes, that's right; this (string.Format) is the only way I know of to do this sort of thing (dynamically building DDL and certain types of queries). — B. Clay Shannon-B. Crow Raven, Mar 28 '13 at 23:19
@Steve - I was referring to SQL Injection in the command text. — Code Maverick, Mar 29 '13 at 02:35
@Scott: but what is being replaced by the string.Format() are a column name and a table name - those can't be parameterized in the normal way, so...what would be the alternate and more secure way to do it? — B. Clay Shannon-B. Crow Raven, Mar 29 '13 at 15:13
@ClayShannon - Maybe this can help you out http://stackoverflow.com/questions/9651582/sanitize-table-column-name-in-dynamic-sql-in-net-prevent-sql-injection-attack — Code Maverick, Mar 29 '13 at 15:53
@Scott: I'm still not seeing any parameterization of vals other than constructs such as "...WHERE Bla = @Blah" If this really is available, it is either something new or something vendor-specific. — B. Clay Shannon-B. Crow Raven, Mar 29 '13 at 15:59
@ClayShannon - Right, when I wrote the SqlParameter comment, I hadn't totally looked over your exact query to notice you didn't have a WHERE clause. I understand what you are saying, but still any ad-hoc query is subject to SQL injection. For example, I could just assign `"1; DROP DA" + "TABASE database_name; --"` to `tableName`, could I not? That's all I'm talking about. *(concatenation due to SO not letting me post without)* — Code Maverick, Mar 29 '13 at 16:22
@Scott: Theoretically, I guess, but how a user cold actually insert/inject that value, in this case, anyway, I'm not seeing. — B. Clay Shannon-B. Crow Raven, Mar 29 '13 at 16:24
@ClayShannon - How your code is used is unknown to us, as we just see your method. So how you get the value for `tableName`, I don't know. In any case, it doesn't have to be a "user", it could be a "malicious developer" who inserts some code that calls that method, who knows? I'm just saying it's possible in this scenario. — Code Maverick, Mar 29 '13 at 16:29
@Scott: If you count malicious developers as threats, you may as well just throw up your hands. Of course a developer can wreak all kinds of havoc if he has access to the code base. At some point you have to stop looking over your shoulder and stop sweating bullets. — B. Clay Shannon-B. Crow Raven, Mar 29 '13 at 16:38

p.s.w.g · Accepted Answer · 2014-08-06T11:43:15.380

10

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;
}

edited Aug 06 '14 at 11:43

answered Mar 28 '13 at 23:28

p.s.w.g

146,324
30
291
331

This code looks way cleaner than the legacy code, but for whatever reason, it causes the app to simply flash exceedingly short-lived dialogs to the screen (the app proper never opens). I changed the isValidField() method back to the legacy (crazy looking code) - the only change - and it's back to what passes for normal. Strange... – B. Clay Shannon-B. Crow Raven Mar 28 '13 at 23:45
@ClayShannon I dropped the call to `checkConnection()`. Not sure if that's necessary, and I also took the `try-catch` blocks out to simplify the code and because it's generally *bad practice* to catch every exception. The main thrust of my answer was just to present a different way of querying the database, so you may need to adapt the code back for your project. – p.s.w.g Mar 28 '13 at 23:49
With a hybrid of the two, I got it to run, but I'm still getting the err msgs, so I'll have to look over my code more closely tomorrow. – B. Clay Shannon-B. Crow Raven Mar 28 '13 at 23:59
Would "SELECT COUNT(*)..." work as well? It would seem more straightforward, if so... – B. Clay Shannon-B. Crow Raven Mar 29 '13 at 16:57

RTK · Answer 2 · 2014-11-17T11:15:47.673

    public bool IsValidField(SqlCeConnection objCon, string tableName, string columnName)
    {
        const string query = "SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS"
                                + " WHERE TABLE_NAME = @TABLENAME AND"
                                + " COLUMN_NAME = @COLUMNNAME";

        using (var cmd = new SqlCeCommand(query, objCon))
        {
            cmd.Parameters.Add("@TABLENAME", SqlDbType.NVarChar, 128).Value = tableName;
            cmd.Parameters.Add("@COLUMNNAME", SqlDbType.NVarChar, 128).Value = columnName;
            var objvalid = cmd.ExecuteScalar(); // will return 1 or null
            return objvalid != null;
        }
    }

How can I determine whether a column exists in a SQL Server CE table with C#?

UPDATE

UPDATE 2

UPDATE 3

2 Answers2

Linked