Arguments with regard to SpareField1 or specific schema

11-Sep-1414 Leave a comment

Because each new feature or property addition requires database changes there is the idea that instead you hold data in fields such as SpareText1, SpareInt1 etc. and this post discusses the relative merits of this and its alternatives. Another place this may be used is to create a common Lookup Field table.

It is my opinion that this should normally be avoided and instead specific schema is used.

Advantages of specific schema

  1. Data type integrity
  2. Required and non-required control by product (Null or Non-Null)
  3. Strongly typed code
  4. Reporting is easier
  5. Discoverability is easier – stay in context
  6. For lookup tables, you will gain referential integrity, and in any case it is possible and not too difficult to create abstract data update forms which may update any of these with data-driven addition of which tables to edit.

Disadvantages of specific schema

  1. You have to change the schema for each change
  2. If you have multiple client databases you may need to change several. If you have many this may be difficult

Mitigations to disadvantages of specific schema

  1. Learn and use simpler design techniques to speed up development. E.g. Entity Framework, code generation
  2. Write forms which may edit any tables. For instance for Lookup tables

Disadvantages of SpareField1

  1. You have to keep a record of what each Spare Field represents. Control and communication of that is difficult, control of a specific, e.g. Length field is not necessary because it says what it is
  2. That may get confusing, or there is a danger that Spare Field may be used in different ways by different people. So in the end it is more complex

Data type integrity

If you have a decimal with precision 4 and scale 2, i.e. 2 dp, max 99.99, then this controls the data. A SpareDecimal1 may have any data type definition, probably too large so as to cope with the general case, and as such bad data may appear, so for instance if you know length is always less than 100 units, then a data entry of 101 is bad data.

Also data type integrity would include Null control

Filed under SQL Server Tagged with ,

Dataset TableAdapter CommandTimeout

04-Apr-1111 Leave a comment

If get error:
“Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding”

Setting the connection string timeout does not work.

Following links led me to my own visual basic translation. I found i had to add the check for the commandCollection Is Nothing
http://www.velocityreviews.com/forums/t123478-how-to-set-commandtimeout-for-getdata-method-of-tableadapter-asp-net-2-0-a.html
http://www.pcreview.co.uk/forums/tableadapter-and-commandtimeout-t2354775.html

Because the DatasetName.Designer.vb is a partial class, it is possible to create a second partial class which will not be overwritten on changes to the dataset. Remember to use

Namespace xxxTableAdapters

    Partial Public Class xxxTableAdapter

        Public WriteOnly Property SetSelectCommandTimeout() As Integer
            Set(ByVal value As Integer)
                If Me._commandCollection Is Nothing Then
                    Me.InitCommandCollection()
                End If
                Me.CommandCollection(0).CommandTimeout = value
                ‘For Each cmd In Me._commandCollection  ‘To do all
                ‘    cmd.CommandTimeout = value
                ‘Next
            End Set
        End Property

    End Class

End Namespace

Filed under ADO.Net, Errors, VB.Net, Visual Studio Tagged with