Yeah, it's a pain, and the way you're doing it looks similar to what I do:
order by
case when @SortExpr = 'CustomerName' and @SortDir = 'ASC'
then CustomerName end asc,
case when @SortExpr = 'CustomerName' and @SortDir = 'DESC'
then CustomerName end desc,
...
This, to me, is still much better than building dynamic SQL from code, which turns into a scalability and maintenance nightmare for DBAs.
What I do from code is refactor the paging and sorting so I at least don't have a lot of repetition there with populating values for @SortExpr
and @SortDir
.
As far as the SQL is concerned, keep the design and formatting the same between different stored procedures, so it's at least neat and recognizable when you go in to make changes.
You can't.
There are steps you can take to make it a little more difficult, but ultimately any executable on the local machine is crackable. Eventually, that code has to be converted into native machine code and every application that is runnable is vulnerable.
What you want to do is just make it difficult enough to crack to make it not worth peoples' trouble.
Some suggestions I have for you to help protect your application:
- Obfuscate your code. Dotfuscator has a free edition and comes with Visual Studio.
- Use public/private key or asymmetric encryption to generate your product licenses. This ensures that only you can generate your license codes. Even if your application is cracked, you can be sure that they won't be releasing a key generator for your application, because it is impossible to reverse the key generating algorithm.
- Use a third-party packer to pack your .NET executable into an encrypted Win32 wrapper application. Themida is one of the better ones. This stops people from reflecting your application in .NET Reflector and makes it a pain to unpack for reversing.
- Write your own custom packer. If the third-party packers are too expensive, consider writing your own. Sometimes custom packers can be very effective, because there aren't well published methods on how to unpack them. The tutorial How to write your own packer gives a ton of good information on writing your own Win32 packer.
Ultimately though, if people want your application cracked they will. Look at all the commercial software out there that has a vast amount of resources to protect their applications and yet they are cracked before the applications are even released to the public.
A skilled reverse engineer can fire up IDA-Pro and slice through your application like butter no matter what you do. A packed application can be unpacked and obfuscation only prevents it from making it a walk in the park. All your hard work with your complex license code can be undone with a single byte patch.
You just need to accept that there is a very real chance people are going to pirate your software. There are some people who are never going to pay for your application no matter what and these are the people you don't need to worry about.
There are however, many businesses out there who would never risk a lawsuit and happily buy software licenses and many computer users who either don't want to risk it, find it wrong or are not tech savvy enough to pirate. These are your true customers, and you should focus your efforts on providing them with a good user experience and ignore the people cracking your software.
I've had my application pirated before, and I took it as a personal affront. Here I was, a small-time developer, pouring my heart and soul into an application and these people had the gall to pirate from me?! They were taking money directly from my pocket!
I immediately added in a bunch of draconian DRM code and attempted to sabotage any person using an illegitimate or cracked copy. I should of course have been working on making my application better instead of trying to stop the inevitable. Not only that, but I was hurting my true customers will all these extra protections I was putting in.
After a long battle I realized I was fighting the tides and all this time wasted was for naught. I took out all the phone-home code except for the barebones license functions and never looked back.
Best Answer
The cheaper solution than 'dependency tracker' is the data dictionary table sys.sql_dependencies which from which this data can be queried from the data dictionary. Oracle has a data dictionary view with similar functionality called DBA_DEPENDENCIES (plus equivalent USER_ and ALL_ views) . Using the other data dictionary tables (sys.tables/DBA_TABLES) etc. you can generate object dependency reports.
If you're feeling particularly keen you can use a recursive query (Oracle CONNECT BY or SQL Server Common Table Expressions) to build a complete object dependency graph.
Here's an example of a recursive CTE on sys.sql_dependencies. It will return an entry for every dependency with its depth. Items can occur more than once, possibly at different depths, for every dependency relationship. I don't have a working Oracle instance to hand to build a CONNECT BY query on DBA_DEPENDENCIES so anyone with edit privileges and the time and expertise is welcome to annotate or edit this answer.
Note also with
sys.sql_dependencies
that you can get column references fromreferenced_minor_id
. This could be used (for example) to determine which columns were actually used in the ETL sprocs from a staging area with copies of the DB tables from the source with more columns than are actually used.I've got this to open-up to the community now. Could someone with convenient access to a running Oracle instance post a CONNECT BY recursive query here? Note that this is SQL-server specific and the question owner has since made it clear that he's using Oracle. I don't have a running Oracle instance to hand to develop and test anything.