Testing your databases with C # tests

Question

I was tasked with checking our database. This is a test database, and we can do whatever we want and easily roll back it. I was given this task because we are still under development (this means that any changes at any given time can happen ... renaming the Person.FirstName column to [First_Name] and then renaming it to [Name]. My goals - Establish a rough estimate of what pain we are going to when we make changes so that we can plan ahead of time.We can also expect such changes in the production process.

The elements that I have on my list are and have written tests for:

• Send the word null (not the literal null, but "null"), because using dynamic SQL, it might flip over, thinking you really mean zero. We found this out because someone with the surname "null" threw an exception.

• Using single quotes because dynamic SQL cannot have single quotes. Again, someone with one in his name caused a crash.

I’ve never done this before, it's about everything that I know that can crash. Any other ideas? We are trying to emulate the data that the user can enter.

edit 1: Our problem is that we have a search screen with about 25 fields that they can search. Some of these search fields are simple (for example, name), some of them are less simple (category 1 with a date less than 2, but also has category 2 with a date greater than 2 OR has category 4 in any time period). The search screen allows the user to select various parameters and predicates with each of these 25 fields. Is there a better way to handle this than with dynamic SQL? I am in a position and a moment in time when we can change something else, if it is better.

edit 2: I don't know if it's worth mentioning, but we use LINQ to access stored procedures. My research has shown that dynamic LINQ will not do what we need to do the way the dynamic SQL query will be executed. Perhaps I am mistaken.

Answer 1

Does this "; Drop Table Person; -" also cause a crash?

You should consider migrating your strategy from dynamic SQL to parameterized queries to avoid SQL injection methods.

As for the C # testing of your database, you can use transactional queries and nUnit for unit testing - mods. Strictly speaking, unit testing should separate your application from the data warehouse so that components can be tested without performance penalties when accessing and modifying the data warehouse. However, you can use very similar methods to check your data store, if that is what you decide. Create a transaction in TestFixtureSetup and drop it back into TestFixtureTearDown so that your database returns to its original state after testing is complete.

Of course, you should be aware that there is a performance limitation when testing data warehouses. Testing your device will not work like all other applications, provided that the rest of your application is tuned for performance.