3

I'm currently working on a c# application that grabs a bunch of data from a user specified access(.mdb) database and does a bunch of stuff with that data. A problem that I've recently come across is that some of the a database is missing a column that has existed in all of the others.

How can I do a select on a database, but gracefully fail (throw null in the data or something) when a column doesn't exist in the database?

Currently, my code looks something like this:

OleDbConnection aConnection = new 
    OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FileName);

string sqlQuery = "SELECT [Table1].[Index], [Table1].[Optional Info], 
    [Table2].[Other Info], .... 
    FROM [Table1] INNER JOIN [Table2] ON [Table1].[Index]=[Table2].[Index] 
    ORDER BY [Table1].[Index]";

OleDbCommand aCommand = new OleDbCommand(sqlQuery, aConnection);
OleDbDataReader aReader = aCommand.ExecuteReader();

(proceed to read data in line by line, using fabulous magic numbers)

I think it's obvious that this is one of my first experiences with databases. I'm not overly concerned as long as it works, but it's stopped working for a database that does not contain the [Table1].[Optional Info] column. It's throwing an OleDbException: "No value given for one or more required parameters."

Any help would be appreciated.

Improve this question

4 Answers 4

Reset to default
3

I might be missing something but...

SELECT Table1.*, Table2.otherInfo
FROM ...

Should do the trick, and let the client process the result set, with an important caveat: there is no way to exclude a column from Table1 in the above.

(I am not aware of any method to "dynamically shape" -- with the viewpoint of the caller -- a SELECT except with a * in the column list as above.)

Happy coding.

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

note the OP remarks about "magic numbers", presumably column indices - in which case, they need a little tweaking when using .*
Yes, the advice to stop using magic numbers is clearly good advice regardless of any other issues.
Just so I understand clearly, I was under the impression that using *'s in SQL is generally a bad practice. (kinda like me baking column indices into the code...) I'm quite happy if the only effect is reduced efficiency due to reading in more data than necessary, but are there any other pitfalls here?
@tugs I would agree that it's a "generally non-ideal practice" as it hides the shape of the result-set. However, the first rule about rules is that they are made to be broken and in this particular case it may be beneficial to get back a "dynamic shape" and then process it on the client as appropriate ;-) The other alternative, of course, is to strongly-shape the original query (as discussed in other answers). I am not aware of other issues; with the t1.*, t2.* etc. syntax there should be no unexpected clobbering of names (use the fully qualified names and DataTable inspection) IIRC.
@tugs Using a * really makes little difference to the query planner, excepting it may pick up more columns than if they are typed out manually if any are omitted. The query is resolved to a fixed-shape before the execution, just as if they were all explicitly specified. The caller just doesn't know the shape until it gets the result-set. (And it's the caller not knowing the shape -- and perhaps getting too much or too little -- that makes the use of * such a controversy ;-)
|
2

The way to do that is to not use magic numbers, but to fetch the field names from the reader and use them - for example GetName etc.

Alternatively, use a mapper like "dapper" that will do this for you.

Improve this answer

6 Comments

if a column from his SELECT clause is missing he's not even going a result set back from his query...
Yep, wouldn't compile. I didn't think that all the way through.
@MichaelEdenfield I was making the implicit assumption that there was a .* involved here somewhere...
Even if there were, including a column that didn't exist followed by a * would fail the query; just using SELECT * would of course avoid the whole problem :)
@Michael doesn't need to be "just" *, hence my very deliberate mention of .*, i.e. select x.a, x.b, y.*, z.c, z.d, z.e
|
2

There is no way to do this in a single query: you cannot run a query that includes columns that don't exist in the source tables. When the server tries to compile the query, it will simply fail.

If you absolutely need to support different scemas, you will need different queries for each of them.

To make things even more awesome, there is no documented way to check if an Access table has a particular column on it via SQL. In SQL Server, you could query the system schema, like sys.objects or sys.columns. In Access, the MsysObjects table has the information you need but it's schema is liable to change on you without notice.

Probably the safest way to go about this is to do a single, up front check where you execute a command such as

SELECT * FROM Table1

then scan the resulting column names to see if your optional column exists; your C# code would then become:

string sqlQuery = string.Empty;
if (optionalColumnExists)
{
  sqlQuery = "SELECT [Table1].[Index], [Table1].[Optional Info], -- etc."
}
else
{
  sqlQuery = "SELECT [Table1].[Index], '' AS [Optional Info], -- etc."
}
Improve this answer

2 Comments

I originally posted an answer with a query to check if the column exists but you made a great point... the query wouldn't run because the server wouldn't compile the query. Good catch.
+1. The interesting think to note is that only one part of the SQL changes -- [col] to value as [col]. That is, the SQL statements could be combined further. However, this does require the initial column-check/detection :(
1

There is a way to extract the table schema using OleDbDataReader.GetSchemaTable and that can be used

OleDbConnection aConnection = new 
    OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FileName);

OleDbCommand aCommand = new OleDbCommand("Table1", aConnection);
aCommand.CommandType = CommandType.TableDirect;
aConnection.Open();
OleDbDataReader aReader = cmd.ExecuteReader(CommandBehavior.SchemaOnly);
DataTable schemaTable = aReader.GetSchemaTable();
aReader.Close();
aConnection.Close();

bool optionalInfoColumnExists = schemaTable.Columns.Contains("Optional Info");

Now later in the code

string sqlQuery = @"SELECT [Table1].[Index], {0} 
    [Table2].[Other Info], .... 
    FROM [Table1] INNER JOIN [Table2] ON [Table1].[Index]=[Table2].[Index] 
    ORDER BY [Table1].[Index]";

if (optionalInfoColumnExists)
{
    sqlQuery = string.Format(sqlQuery, "[Table1].[Optional Info],");
}
else
{
    sqlQuery = string.Format(sqlQuery, "");
}

and while reading use similar logic.

I don't know what kind of application this is but the optionalInfoColumnExists should be populated at the application or session start and reused throughout the life of the app i.e. don't execute the GetSchemaTable everytime a query is run on this table (assuming that the mdb won't change while the app is active).

Either way, it seems like that it is going to make the code to have "if else" just to take care of presense and absence of a column in a table.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.