2

I'm working on re-designing a ASP.NET application that uses a pre-existing database. The back-end has a whole bunch of white spaces that were added long before I arrived, and so now its got me wondering ... Would it be better for me to trim the white spaces in the SQL queries that pull data, or better for me to handle it in the ASP.NET application? I've heard that formatting of data should -always- be done in the front-end, but what if the back-end data is already messy?

user2242661
  • 55
  • 9
  • 1
    The data in database with extra white spaces was likely due to a datatype like `nchar(10)` - must have 10 chars vs `nvarchar(10)` can be 0 to 10 chars. – OneFineDay Feb 04 '14 at 01:00
  • Yes, this is correct. Unfortunately I'm not really able to alter the database ... – user2242661 Feb 04 '14 at 01:02
  • Once you have the string you can always use the `Trim` method. – OneFineDay Feb 04 '14 at 01:02
  • Thank you, I like these responses. Overall after hearing what everyone says I think fixing the DB so that these nchar fields are switched to nvarchar is the best solution, but if this fails I'll look into creating a view. If all else fails, trimming whitespace in the front-end seems to be only solution left. – user2242661 Feb 04 '14 at 18:54

4 Answers4

1

All depends...

How many fields are like that. Will you ever have access to change the DB Schema?

I would prefer to fix the DB. That way you don't waste space on the DB server and data over the network is also reduced. (Maybe less work than fixing all the places in the front end)

If you can't 100% ensure that all the data from the DB is without white-spaces it might be worth while to test for it in critical spots in the front end.

Bertus Kruger
  • 1,345
  • 1
  • 19
  • 31
  • All of the fields are like this. This DB was set up back in 2000-2002, long before I arrived however, I may be able to work with the DBA into getting these fields corrected; This seems to be the correct solution. Thanks! – user2242661 Feb 04 '14 at 18:57
0

It makes more sense to me to trim it on the front-end. The back-end is in charge of storing data, the front end is in charge of presenting data. Trimming white-space is definitely the latter, and is therefore the job of the front-end.

SomeGuy
  • 485
  • 3
  • 12
0

I would say that this should be done in the application tier of your software. By this I mean that it should exist in the C# but should be deep down in the data access layer. The code that makes the calls to the database should do the trimming when converting the returned data into domain objects. This way the rest of the application tier has no clue that the database is a mess.

Andacious
  • 1,162
  • 10
  • 17
0

If the data are meant to be treated as, say, nvarchar(10), but just happen to be stored as nchar(10), then consider creating a view over the base data. The view can apply the Trim function.

John Saunders
  • 160,644
  • 26
  • 247
  • 397
  • I hadn't thought about a view. Thanks I'll look into that if I'm absolutely unable to change the field types in the DB. – user2242661 Feb 04 '14 at 18:55