DB Record Typedef w/ Auto Incrementing Key

[FireFist-Redhawk]

Afternoon,

Our SQL Server databases are going to be using auto incrementing primary keys more and more. There is an issue I've started running into for tables with that feature. Making a typedef for a table record, the PK must be included in that typedef for the Selects that need to happen. But for the Inserts and the Updates, you need to have the same exact cluster as the typedef but without the PK integer at the beginning. My current way of dealing with this is not going to be very maintainable as things scale; that way is to disconnect the typedef, remove the PK integer, and add a bookmark annotating what I did. See attached image. More than a few places doing that, and I'll probably end up making two typedefs, one with the PK and one without, and update them both as the table inevitably evolves from feature creep. But two typedefs per table in an app with potentially dozens of tables is also a little bleh, so I've not implemented that anywhere yet.

I'm wondering if anyone has an even cleaner implementation, one where a single typedef can be used for both the Selects and the Inserts/Updates, for tables that use an auto-incrementing PK. Thanks!

Spoiler

[Mark_Yedinak]

I would go with two typedefs. I would put all of your DB typedefs into a single library and possibly use virtual folders to organize things on a per table basis. That way when you do need to update everything is in the same place. Plus, seeing the two typedefs side by side you will remember both need to be updated.

However, my actual preferred method for working with data bases is to use stored procedures for the API with the database. Very easy to change the underlying DB schema provided you keep the same parameters for the stored procedure. When you need to add parameters create a new stored procedure. The name of the stored procedure can include a version number if necessary. We have found that this method works much easier for maintaining the code. Also, much easier to hand the documentation for the stored procedures to someone else who needs to interact with the language. Most programmers will under a function/procedure call but often aren't very familiar with SQL. A possible advantage would be that it could be easier to switch the DB itself (SQL Server to MySQL for example) because you implement the same stored procedures in the new DB and your code will need fewer updates to work with the new DB. In our case we actually use classes for accessing the DB as a further abstraction to minimize impacts on the code due to DB changes. Made witching from the DB toolkit to a MySQL specific library much easier. The DB Toolkit is single threaded very deep in its code which severely impacts code performance when there is frequent DB calls.

[FireFist-Redhawk]

Thank you for the input! I might have to look into leveraging stored procedures in our apps.