|
Ok a little more detail is required. you have 2 options with this scenario.
You can try and delete the record and hit the constraint and then deal with it by informing the user. This imposes an error on the DB.
You can also query the DB to see if the record can be deleted using an if EXIST query on the related table(s).
The first method is programming by error and is generally used by lazy sods who have no craftsmanship.
The second requires 2 or more round trips to the database and IMHO is a waste of effort. I write a delete procedure that does the checking before it deletes (or deletes the FK children BEFORE it tries to delete the master record). The proc returns the number of master records deleted (1) or failed (0) or if it is not deleting the children and one exists then it returns (-1). This moves the decision into the database and requires one 1 round trip and supplies the feedback to the user if there is an active constraint.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
You're still missing what I'm aiming for...
I want to find out IF the PayType record can be deleted. If its PK is in use in another table, then I don't want to try to delete it. I DO however want to tell the user it's in use.
If it's not broken, fix it until it is
|
|
|
|
|
So how are going to find out if the PayType records is in use by another table(s)? Query the related tables and see if the ID is in use!
You could write a generic procedure that queries sysobjects to identify the PKs and their related fields and then query the tables and return a boolean if you can delete the record. A matter of fact that sounds like an excellent widget to build, why is there not one of those?
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
All that's fine, and is what I am already doing... querying the tables that use the PK.
My original question still is "What's the best way to notify the front end of a business rule or possible FK violation?"
Raise an exception? Return a string?
How does the UI find out that the backend can't delete the row?
If it's not broken, fix it until it is
|
|
|
|
|
I would create a special exception type, which is only used for potential business rule violations the user needs to be informed of. Then you can catch that exception at the appropriate location (where the user started the invalid action) and inform the user using a message box or email or whatever.
The good thing about pessimism is, that you are always either right or pleasently surprised.
|
|
|
|
|
Mycroft Holmes wrote: Here is one reason I ALWAYS use a stored proc, the delete procedure check the FK
Then you must work in an unusual environment.
Normally the applications that are in use (the code external to the database) should present the requests that even allow that situation.
Thus checking the FK first every time is a waste of bandwidth and adds nothing but complexity.
And allowing the exception in such cases is quite appropriate.
Mycroft Holmes wrote: and only deletes the record if there is no constraint.
But presumably it does in fact still throw an error. If the caller is attempting to delete something and it does not in fact get deleted but still exists then the caller must be told that it did not get deleted. It cannot appear as though it did.
|
|
|
|
|
Actually I do manage this in the VM layer but on the rare occasions where I need to delete a master record there is a stored proc and it never relies on the exception, just the way I build it I guess. Seems many are comfortable with the exception being thrown!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
A couple of options spring to mind immediately.
First, you can create a custom exception type (e.g. PayTypeInUseAndCannotBeDeletedException) and throw that, then catch this type in the UI. This has the advantage of not changing the method signature, but if feels like exception as business logic.
Personally I'd return a status on the method. Probably you can get away with a boolean pass/fail as you only have one failure mode.
public static bool DeletePayType(int PayTypeId)
{
using (var dc = getDataContext())
{
bool inUse = isPayTypeInUse(PayTypeId);
if (inUse)
{
return false;
}
else
{
var payType = (from ca in dc.PayTypes
where ca.PayTypeId == PayTypeId
select ca).FirstOrDefault();
if (payType != null)
{
dc.PayTypes.DeleteOnSubmit(payType);
try
{
dc.SubmitChanges();
return true;
}
catch (Exception e)
{
throw e;
}
}
}
}
}
I'd also get rid of the try/catch around the submit. It isn't achieving anything other than removing potentially useful stack trace (See here[^] if you don't know what I mean)), unless this was intended of course.
|
|
|
|
|
In general, it's a bad idea in a database system to test for the existence of a value and then perform an action based on it as a separate operation. This is because you are working in a multi-user environment where you could have a race condition. So, assuming that you don't want to rely on the database throwing an exception and you having to deal with it, it's best to put the test as part of your delete statement. Then, test to see if there were any rows affected or not - this can then be returned up the chain to indicate that the key was in use at this point.
I'm not a big fan of exceptions to control business logic - they should really just indicate that something was wrong, so I'd just pass the result back up the chain.
|
|
|
|
|
Pete O'Hanlon wrote: I'm not a big fan of exceptions to control business logic
You'd love our code base. And the several hundred custom exceptions to handle such exceptional things as a user not having set up a payment method yet. 
|
|
|
|
|
I'd start by creating a DeletePayTypeResult class and having a flag that indicates if the Pay Type was deleted and a string field containing the reason for not being able to delete. And then I would return this type from the method.
class DeletePayTypeResult {
public bool Deleted { get; set; }
public string ReasonNotDeleted { get; set; }
}
public static DeletePayTypeResult DeletePayType(int PayTypeId)
{
}
|
|
|
|
|
Kevin Marois wrote: What's the best way to notify the front end of a business rule or possible FK
violation?
Normally the front end should prevent business flows that allow that in the first place.
So if it does occur then an exception is acceptable.
Kevin Marois wrote: Throwing an exception doesn't seem right
For the most part is it exactly correct. The FK exists to represent a relationship that must always exist. For that failure to occur it means either a programming bug or a work flow problem. Either represents an 'exceptional' situation and thus an exception is in fact entirely appropriate.
In some cases, mostly where the front end might be less stringent then it might be the back ends responsibility to validate for errors. Reporting errors can have variety of solutions but it is something that the layer, not one specific method, should already have an idiom for dealing with. And exceptions are a possible solution although generally it would not be a generic exception but rather a layer specific one.
|
|
|
|
|
The module should be a windows application, made using visual
studio.There are two parts one for client side and the other for
server side.
Client side: client side part should detect the drive which is plugged
in. The details of the drive and the time for which it was plugged in
should be stored in database of server.
Server side: server side part contains a GUI in which all the IP
addresses of all the systems in the network are given in a list and
whenever an IP address is selected the log of all the drives plugged
in is retrieved from the database. ( for IP addresses there must be IP
detection system inbuilt in the module of server side which will store
the info of all systems in database.)
Please help me out. I don't have much time to submit this module.
Thankyou.
|
|
|
|
|
Shibbi wrote: Please help me out
Help you with what exactly? This is just a specification for the work you are supposed to be doing. Just because you have left it until the last minute to get started does not mean that someone else has to do it for you.
|
|
|
|
|
The module should be a windows application, made using visual
studio.There are two parts one for client side and the other for
server side.
Client side: client side part should detect the drive which is plugged
in. The details of the drive and the time for which it was plugged in
should be stored in database of server.
Server side: server side part contains a GUI in which all the IP
addresses of all the systems in the network are given in a list and
whenever an IP address is selected the log of all the drives plugged
in is retrieved from the database. ( for IP addresses there must be IP
detection system inbuilt in the module of server side which will store
the info of all systems in database.)
Please help me out. I don't have much time to submit this module.
Thankyou.
|
|
|
|
|
Hi To all
I am working on an auctioning system that i am developing with C# .NET 4.0. These auctioneers usually have outside auctions which are carried outside (where it would unreasonable to set up a computer network). The bidding's are recorded on paper and then saved onto the database from the papers.
What I want to do is this, I want each auctioneer to have their own handheld mobile computer like the ones offered by cipher labs (http://us.cipherlab.com/catalog.asp?CatID=7&SubcatID=2). The auctioneer will record each bidding on that mobile computer (these recordings should be saved as text file) so when they decide to upload the the bidding these files will be read by a separate module (developed by me) that will then read the file and save to the data to a database.
So can you assist me with devices and libraries that are compatible with such functions and that will help me achieve this feature that i would like to incorporate into my system
|
|
|
|
|
I think you might be starting with the wrong technology. Those hand held computers are primarily designed for reading bar codes and RF information.
What kind of auction do you envision? Something where the auctioneer posts and item and people bid out loud for it? Silent auction? Internet auction?
Obviously, the vocal bids could be pretty fast paced and difficult to record with something like this. You would be far better off with a tablet solution using Windows, Android or iOS.
I am not sure how much room you have to work with the solution you have above. I would definitely start off with an easier platform. I am sure that a small tablet is every bit as affordable and more technically open to work with.
Barring that, it does not look to me like this mobile computer works on the .NET platform, but rather plain C or BASIC.
|
|
|
|
|
hi guyz, i'm, having a problem. i've created a c# application. i want to package it and create a setup for it. the application is a database driven app but when i tried creating the setup, it installed on my own pc(the pc i use to develop it) but the setup does not install on any other computer. please how can i go about this.... thankz......
|
|
|
|
|
Well not being both telepathic and having a touch of magic I cannot read your thoughts to identify the error and cannot see the process and where it fails on any other machine.
A little LOT more detail is going to be required to trouble shoot that problem.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
@mycroft holmes, in short, i want to create a setup for an app that uses sqldatabaseCE(sql compact) as its database, how i'm gonna go about this.....
McLux
|
|
|
|
|
You need some Google foo[^] there is no in short about this issue, work through the examples.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi everybody,
I am making a simple tool to synchronize data from csv files to SQL Server Database.
My SCADA Signal transfer data through csv files (3 SCADA signals --> 3 csv files) and datas will be transfered 1time/1day.
And I will make a simple tool, read csv files and import to SQL Server Database.
My issues are:
1. I have no ideas how to handle the csv files when they were transfered back with diffirent names.
2. How to set time to synchronize (1time/1day).
3. Can I make a windows service to synchronize?
Please help me.
If you have the user interface for issue 1, please give me!
Thank you very much
|
|
|
|
|
|
I would use SQL Server Integration Service (SSIS) to do this using a scheduled job. All the infrastructure is already there, file handling, import and write to the database, job scheduling and logging can all be found in SSIS.
The only issue would be getting the files from your SCADA to a folder that SQL Server can see and you could use a mapped drive for that!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I am using a Transaction by the following syntax for opening transactions
using(TransactionScope scope = new TransactionScope())
{
...
scope.Complete();
}
That's fine so far, but what I don't know is, if I have to explicity need to set the Transaction in the Command/Connection Object or if this is done implicity
My second question would be, if it is not done explicity: I actually create an Instance of another class within the TransactionScope where I do all the database operations, is there a way to access the open Transaction without passing the scope variable as a method parameter?
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
