Is strongly typed database code worth it? It's giving me a headache!
-
OK, I love to learn new and better ways to do things, but this just doesn't seem one of them. I'm originally from the Java camp so I've used straight DB code such as hard coded calls to the database and ORM tools like Hibernate. I've been working in VB for the past 5 years on various projects and have used similar techniques here as well. I am working on a fairly large project and decided to use strongly typed db access such as what is taught in these fairly popular tutorials: http://www.asp.net/learn/data-access/[^] Boy am I getting a headache. It seems to me that the only benefits I get are IntelliSense and compile time checking. IntelliSense wasn't available before strongly typed database code so this is more of a convenience than any real need. Compile time checking is nice but I've already had several errors that are not caught until runtime. These errors largely deal with enforcing constraints when filling a DataTable using a standard DataAdapter. Most of these errors are extremely trivial, do not interfere with my actual code and would never have been brought to my attention without strongly typed db code. So here is what I see as the major negative. I feel like I am getting much more code dependence rather than the current shift towards independence. This project largely works with data from a vendor database. If they ever update their schema (which can be as often as once a week) my app will break, even if they simply change a field from varchar(10) to varchar(12). I'm starting to lean towards either rolling my own db code or using NHibernate again. Also, LINQ seems to be a departure from this strategy as well. Does anyone else have any comments? Have any of you used strongly typed db code on a large project and found it to be indispensable? What am I missing? Thanks! Matt Penner
-
OK, I love to learn new and better ways to do things, but this just doesn't seem one of them. I'm originally from the Java camp so I've used straight DB code such as hard coded calls to the database and ORM tools like Hibernate. I've been working in VB for the past 5 years on various projects and have used similar techniques here as well. I am working on a fairly large project and decided to use strongly typed db access such as what is taught in these fairly popular tutorials: http://www.asp.net/learn/data-access/[^] Boy am I getting a headache. It seems to me that the only benefits I get are IntelliSense and compile time checking. IntelliSense wasn't available before strongly typed database code so this is more of a convenience than any real need. Compile time checking is nice but I've already had several errors that are not caught until runtime. These errors largely deal with enforcing constraints when filling a DataTable using a standard DataAdapter. Most of these errors are extremely trivial, do not interfere with my actual code and would never have been brought to my attention without strongly typed db code. So here is what I see as the major negative. I feel like I am getting much more code dependence rather than the current shift towards independence. This project largely works with data from a vendor database. If they ever update their schema (which can be as often as once a week) my app will break, even if they simply change a field from varchar(10) to varchar(12). I'm starting to lean towards either rolling my own db code or using NHibernate again. Also, LINQ seems to be a departure from this strategy as well. Does anyone else have any comments? Have any of you used strongly typed db code on a large project and found it to be indispensable? What am I missing? Thanks! Matt Penner
We use XPO[^] here at work. We had done it "by hand" previously, and XPO saves us tons of time. That said, it's not a walk in the park. There are still issues, such as the db scheme change breakings (although XPO handles most schema changes automatically OK). Most O/R mappers give you database independence; your code will work with MS SQL Server, MySQL, Oracle, Access, whatever. Overall, I'm certainly glad we have an O/R mapper. It could be better, but it's far, far better than doing it by hand.
Tech, life, family, faith: Give me a visit. I'm currently blogging about: The Lord Is So Good The apostle Paul, modernly speaking: Epistles of Paul Judah Himango
-
We use XPO[^] here at work. We had done it "by hand" previously, and XPO saves us tons of time. That said, it's not a walk in the park. There are still issues, such as the db scheme change breakings (although XPO handles most schema changes automatically OK). Most O/R mappers give you database independence; your code will work with MS SQL Server, MySQL, Oracle, Access, whatever. Overall, I'm certainly glad we have an O/R mapper. It could be better, but it's far, far better than doing it by hand.
Tech, life, family, faith: Give me a visit. I'm currently blogging about: The Lord Is So Good The apostle Paul, modernly speaking: Epistles of Paul Judah Himango
Judah Himango wrote:
It could be better
Yes it could. As a work around, don't do development that uses databases... actually don't do development that uses data at all.... oh what heck just don't do software development. Hey maybe the circus that's in town could use another clown? ;)
-
OK, I love to learn new and better ways to do things, but this just doesn't seem one of them. I'm originally from the Java camp so I've used straight DB code such as hard coded calls to the database and ORM tools like Hibernate. I've been working in VB for the past 5 years on various projects and have used similar techniques here as well. I am working on a fairly large project and decided to use strongly typed db access such as what is taught in these fairly popular tutorials: http://www.asp.net/learn/data-access/[^] Boy am I getting a headache. It seems to me that the only benefits I get are IntelliSense and compile time checking. IntelliSense wasn't available before strongly typed database code so this is more of a convenience than any real need. Compile time checking is nice but I've already had several errors that are not caught until runtime. These errors largely deal with enforcing constraints when filling a DataTable using a standard DataAdapter. Most of these errors are extremely trivial, do not interfere with my actual code and would never have been brought to my attention without strongly typed db code. So here is what I see as the major negative. I feel like I am getting much more code dependence rather than the current shift towards independence. This project largely works with data from a vendor database. If they ever update their schema (which can be as often as once a week) my app will break, even if they simply change a field from varchar(10) to varchar(12). I'm starting to lean towards either rolling my own db code or using NHibernate again. Also, LINQ seems to be a departure from this strategy as well. Does anyone else have any comments? Have any of you used strongly typed db code on a large project and found it to be indispensable? What am I missing? Thanks! Matt Penner
MattPenner wrote:
What am I missing?
This requirement of yours seems to stand out... to me anyway.
MattPenner wrote:
If they ever update their schema (which can be as often as once a week)
That seems to be a significant requirement to me. Based on that requirement I would not expect standard database techniques to sufficiently meet that requirement. It might warrant specific custom design to accomplish flexibility and extensibility.
-
OK, I love to learn new and better ways to do things, but this just doesn't seem one of them. I'm originally from the Java camp so I've used straight DB code such as hard coded calls to the database and ORM tools like Hibernate. I've been working in VB for the past 5 years on various projects and have used similar techniques here as well. I am working on a fairly large project and decided to use strongly typed db access such as what is taught in these fairly popular tutorials: http://www.asp.net/learn/data-access/[^] Boy am I getting a headache. It seems to me that the only benefits I get are IntelliSense and compile time checking. IntelliSense wasn't available before strongly typed database code so this is more of a convenience than any real need. Compile time checking is nice but I've already had several errors that are not caught until runtime. These errors largely deal with enforcing constraints when filling a DataTable using a standard DataAdapter. Most of these errors are extremely trivial, do not interfere with my actual code and would never have been brought to my attention without strongly typed db code. So here is what I see as the major negative. I feel like I am getting much more code dependence rather than the current shift towards independence. This project largely works with data from a vendor database. If they ever update their schema (which can be as often as once a week) my app will break, even if they simply change a field from varchar(10) to varchar(12). I'm starting to lean towards either rolling my own db code or using NHibernate again. Also, LINQ seems to be a departure from this strategy as well. Does anyone else have any comments? Have any of you used strongly typed db code on a large project and found it to be indispensable? What am I missing? Thanks! Matt Penner
On program startup, you could query the schema for all your tables. Build class to hold information like field names, value types and lengths. Here's some of my code, I'm sure most people here can come up with something more effecient :-D. The tabbing is getting killed but you get the idea. Mine looks something like this
namespace DBWrapper { public class FieldInfo { private string strField = string.Empty; private string strType = string.Empty; private int nLen = 0; public string FieldName { get { return strField; } set { strField = value; } } public string FieldType { get { return strType; } set { strType = value; } } public int FieldLen { get { return nLen; } set { nLen = value; } } } public class TableData : List { /// /// Returns a FieldInfo Struct based on the provided field name. /// /// /// null on failure public FieldInfo Get(string strFieldName) { FieldInfo retFieldInfo = new FieldInfo(); foreach (FieldInfo fiInfo in this) { if (string.Compare(fiInfo.FieldName, strFieldName, true) == 0) return fiInfo; } return new FieldInfo(); } } }To get the schema info I use this (I think this is half my code and half somebody elses but it's been so long since I touched it I don't remember) protected bool GetTableSchema(out DataTable dtSchema) { dtSchema = new DataTable(); SqlCommand dbCommand = new SqlCommand(); SqlDataReader dbReader; bool bRetValue; if (!OpenDatabase()) return false; try { dbCommand.CommandText = "SELECT * FROM " + m_strTableName; dbCommand.Connection = m_DBConnection; dbReader = dbCommand.ExecuteReader(CommandBehavior.KeyInfo); dtSchema = dbReader.GetSchemaTable(); bRetValue = true; } catch (Exception ex) { string strErrorMessage = "Error Getting Table Schema " + dbCommand.CommandText + "\r\nMsg: " + ex.Message; #if DEBUG Console.WriteLine(strErrorMessage); #else m_LogHandler.WriteLogLine(strErrorMessage); #endif bRetValue = fal -
We use XPO[^] here at work. We had done it "by hand" previously, and XPO saves us tons of time. That said, it's not a walk in the park. There are still issues, such as the db scheme change breakings (although XPO handles most schema changes automatically OK). Most O/R mappers give you database independence; your code will work with MS SQL Server, MySQL, Oracle, Access, whatever. Overall, I'm certainly glad we have an O/R mapper. It could be better, but it's far, far better than doing it by hand.
Tech, life, family, faith: Give me a visit. I'm currently blogging about: The Lord Is So Good The apostle Paul, modernly speaking: Epistles of Paul Judah Himango
Yay XPO. I like XPO - even with its quirks.
Deja View - the feeling that you've seen this post before.
-
Judah Himango wrote:
It could be better
Yes it could. As a work around, don't do development that uses databases... actually don't do development that uses data at all.... oh what heck just don't do software development. Hey maybe the circus that's in town could use another clown? ;)
led mike wrote:
Hey maybe the circus that's in town could use another clown?
Sorry, I went there last night and took the LAST CLOWN JOB IN TOWN. Ha. Take that, you clown-wannabe. ;)
Tech, life, family, faith: Give me a visit. I'm currently blogging about: The Lord Is So Good The apostle Paul, modernly speaking: Epistles of Paul Judah Himango
-
led mike wrote:
Hey maybe the circus that's in town could use another clown?
Sorry, I went there last night and took the LAST CLOWN JOB IN TOWN. Ha. Take that, you clown-wannabe. ;)
Tech, life, family, faith: Give me a visit. I'm currently blogging about: The Lord Is So Good The apostle Paul, modernly speaking: Epistles of Paul Judah Himango
-
OK, I love to learn new and better ways to do things, but this just doesn't seem one of them. I'm originally from the Java camp so I've used straight DB code such as hard coded calls to the database and ORM tools like Hibernate. I've been working in VB for the past 5 years on various projects and have used similar techniques here as well. I am working on a fairly large project and decided to use strongly typed db access such as what is taught in these fairly popular tutorials: http://www.asp.net/learn/data-access/[^] Boy am I getting a headache. It seems to me that the only benefits I get are IntelliSense and compile time checking. IntelliSense wasn't available before strongly typed database code so this is more of a convenience than any real need. Compile time checking is nice but I've already had several errors that are not caught until runtime. These errors largely deal with enforcing constraints when filling a DataTable using a standard DataAdapter. Most of these errors are extremely trivial, do not interfere with my actual code and would never have been brought to my attention without strongly typed db code. So here is what I see as the major negative. I feel like I am getting much more code dependence rather than the current shift towards independence. This project largely works with data from a vendor database. If they ever update their schema (which can be as often as once a week) my app will break, even if they simply change a field from varchar(10) to varchar(12). I'm starting to lean towards either rolling my own db code or using NHibernate again. Also, LINQ seems to be a departure from this strategy as well. Does anyone else have any comments? Have any of you used strongly typed db code on a large project and found it to be indispensable? What am I missing? Thanks! Matt Penner