|
Luc Pattyn wrote: One way to remedy the situation would be to populate, at start-up, some data
structures describing all relevant field widths, and pass this to the GUI stuff,
so it can check all the lengths and warn the user, before attempting an
insert/update. However that seems more effort than elegance, hence me asking
about some best practices and/or easy solutions.
Generate the database API from the database schema.
At the same time, and in a different API, generate validation classes. That includes length validation and not null validations but does NOT include things like foreign key constraints.
The validation classes are used in the database API before attempting to apply the data to the database. They can also be used elsewhere, because they represent their own API.
This idiom probably works better in a standard distributed arch versus stand alone but the idea still applies.
At any rate that is how I do it and have done it that way for years in a variety of different languages.
But that is how I do it so it is "easy".
The "best-practice" is still - validate it. How you achieve the validation and insure its correctness is a different matter.
|
|
|
|
|
That makes a lot of sense. I'm adding a model layer with validation right now; it will not be all automatically generated, however first results are good, and I think it will fit for right now. Anyway, thanks a lot for the insights.
|
|
|
|
|
Luc Pattyn wrote: the GUI knows all the database field widths
Yes, well you can tell it, e.g. TextBox.MaxLength, but I've never bothered to do that.
To do it properly you'd also need to query the database to get the length at run time somehow.
|
|
|
|
|
Then what do you do?
Do you just have a try-catch and tell the user "Something went wrong, maybe one of your strings is too long"? That is way to vague to my liking.
OTOH passing (part of) the TableSchema information to the GUI seems, well, quite cumbersome.
|
|
|
|
|
Luc Pattyn wrote: try-catch and tell the user "Something went wrong
You must have seen some of my apps. 
That's what I've done so far, but I'm not writing apps to sell to the great unwashed public (not meaning you).
You could probably query the database at compile time rather than run time.
|
|
|
|
|
So far this is the best approach I have come up with (untested!):
1. I'll have my app interrogate the DB structures at startup to basically set up a
Dictionary<string tableName, Dictionary<string fieldName, int fieldLength>> stringMaxLenghts;
That isn't too much trouble, I already have a number of run-time checks for the presence of some tables and some fields (the database is going to exist in many generations).
2. For each relevant TextBox I'll have each of my dialog Load handlers call a static method:
StaticClass.SetTextBoxMaxLength(TextBox textbox, string tableName, string fieldName) {...}
which sets the TextBox.MaxLength property in accordance to the stringMaxLenghts information.
That will eventually cause some beeping at the very moment the user is typing too much text. Not that I like beeping a lot.
3.In the data layer, I'll have my DatabaseCommand.AddStringParameter(...) methods check the length of the string against stringMaxLenghts once more (and truncate if necessary); this to be safe in case some data slips through, e.g. something that doesn't come straight from a TextBox.
That seems a reasonable cost (only one Dict-of-Dicts being passed, plus a number of method calls in Load handlers) to accomplish most of what I want.
Comments are welcome. Better ideas too.
|
|
|
|
|
Luc Pattyn wrote: cause some beeping
Ew. Setting the MaxLength should be enough. And you could have a label or something that counts down the number of characters to go before the field is full.
|
|
|
|
|
it is WinForms that does the beeping, I don't add to it.
I know I could add a "chars remaining" indicator (and I do appreciate those on the web sites that have them, e.g. the CP forum's signature editor), but then that would really alter and enlarge all my dialogs, I don't want to do that.
However I could use a TextBox derivative in a few occasions, that would fit nicely with the current scheme. So phone numbers and ZIP codes would beep on abuse only, whereas big TextBoxes (e.g. a comment field) might be more civilized.
|
|
|
|
|
Luc Pattyn wrote: might be more civilized.
Ah, yes, maybe on KeyDown or something.
|
|
|
|
|
Good strategy.
Item 3 is probably not required, but a good catch-all nonetheless...
Luc Pattyn wrote: stringMaxLenghts
Has a typo in it. stringMaxLengths
|
|
|
|
|
Thanks for the heads up. I'll give it a whirl tomorrow.
|
|
|
|
|
Luc Pattyn wrote: In theory sure. However it seems to imply the GUI knows all the database field
widths,
My applications always have a database API. That API would be the place to insert validation including limit checks.
|
|
|
|
|
Yes, I am implementing that right now. Thanks.
|
|
|
|
|
3. Not that I know of.
2. Size doesn't matter. 
1. Estimate and double. 
If you have a number of large text fields, you could combine them all into one table with an ID (of some type) and a value of NVARCHAR(MAX), then only store the ID in the table that uses the value.
|
|
|
|
|
Wouldn't it be a better idea to have a model layer that sits in between the data access layer and the GUI. The properties in the model layer would know the limits of their respective database fields, and pass this on via an interface such as IDataErrorInfo or something similar. This way you can have GUI validation.
When I was a coder, we worked on algorithms. Today, we memorize APIs for countless libraries — those libraries have the algorithms - Eric Allman
|
|
|
|
|
Thanks Wayne.
Yep, that is the way it seems to be evolving. I already discovered I could make most of my business objects derive from a base class (say DatabaseRecord), which could help taking care of general field information handling.
I'd still appreciate an example though, so if you're aware of some article I should read, please let me know.
|
|
|
|
|
I have a basic article on the IDataErrorInfo that follows the MVVM pattern Validating User Input - WPF MVVM[^] that should get you started.
Good Luck!
When I was a coder, we worked on algorithms. Today, we memorize APIs for countless libraries — those libraries have the algorithms - Eric Allman
|
|
|
|
|
Ah. That looks interesting. Thanks very much, I'll have to read that in the morning, and apply it right away.
|
|
|
|
|
I agree with Wayne, your model should be aware of your data structure and it's limitations. As I always us stored procs that are generated this is a simple excercise, the generator reads the table info from the database meta data and creates the CRUD procs , the model code and the DAL (executes the procs and returns a list<> of the object).
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks. I'm fully convinced by now that is the right way to do things. And I have implemented parts of it already, the TextBoxes now are fully aware of the field widths through a simple model layer and some dictionaries. While not fully automatic, it seems to work well.
|
|
|
|
|
I have never used a pivot before and I am starting to think that one doesnt work. Please help provide guidence.
I have 3 tables
_Contracts, which contain contractid and sepworkstatus
_contracts
--------------------
1,[Sent]
2,[Sent]
3,[No Further Action]
_vendors which has umbrellaname and vendornumber _contracts.vendorid is foreign to _vendors.vendornumber
_vendors
---------------------
um1,1
um2,2
um2,3
and _UmbrellaData which has umbrellaname and a few other needless columns. umbrellaname connects _UmbrellaData and _vendors
_UmbrellaData
---------------------
um1
um2
The outer apply will grab a list of all the statuses and their counts for each umbrella company
The results will look similar to
um1,sent,1
um2,No Further Action, 1
um2,sent,1
um3,Executed
my output needs to look like this
umbrellaname, sent, [No Further Action], [Executed]
um1,1,null,null
um2,1,1,null
My current query looks like this
select
u.umbrellaname, u.stream, functionalarea,
[In Negotiation],[Prework Complete],[Sent],[No Further Action],[Executed],[Separation Work Started],[Ready to Send],[Prework Started]
from dbo._UmbrellaData u
outer apply (
select v.umbrellaname, c.sepworkstatus, count(c.sepworkstatus) as counte from dbo._Vendor v
join dbo._Contracts c on c.vendorid = v.vendornumber
group by v.umbrellaname, c.sepworkstatus
) as oa
pivot(max(oa.counte) for oa.sepworkstatus in (
[In Negotiation],[Prework Complete],[Sent],[No Further Action],[Executed],[Separation Work Started],[Ready to Send],[Prework Started]
)) as cw
where oa.umbrellaname = u.umbrellaname
Unfortunately when I run it I get the following errors.
Msg 8156, Level 16, State 1, Line 2
The column 'umbrellaname' was specified multiple times for 'cw'.
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "oa.umbrellaname" could not be bound.
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "u.umbrellaname" could not be bound.
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "u.umbrellaname" could not be bound.
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "u.stream" could not be bound.
Why? How do I get a pivot to work with an outer apply?
Any help on this would be greatly appreciated.
|
|
|
|
|
You might want to work through this article[^], not so much for the detail but more for the way the pivot is constructed.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks. Somehow I had missed that article. It does give a shove in the right direction.
So far we have built a temp table instead. Maybe that can be made cleaner.
Don
|
|
|
|
|
I am trying to add a record to MySql using C# and MySql .net connectors. The field is varchar and I am using this as sql command statement:
insert_command = "INSERT INTO cdr_log (cdr_raw) VALUES ('" + cdr_raw + "')";
but I am getting this error:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '0')' at line 1
because sometimes my cdr_log will look like this:
09/11/11 07:18PM 299 02 19751002 00:00'04
so how can I avoid the error?
Thanks,
Jassim
|
|
|
|
|
You can use parameters, something like this:
cmd.CommandText = "INSERT INTO cdr_log (cdr_row) values (?p_cdr_raw)";
cmd.Parameters.Add("p_cdr_raw", MySqlDbType.VarChar);
...
cmd.Parameters["p_cdr_raw"].Value = cdr_raw;
cmd.ExecuteNonQuery();
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
