VS Unit test on database procedures inMemory that can be integrated with continuous delivery pipeline
up vote
2
down vote
favorite
So Im trying to figure out a way to properly unit test database procedures in visual studio so I can integrate it into our deployment pipeline.
what I am doing right now is create a database project and link it to our database. Then generate unit test with visual studio on the procedures. The problem is that unit testing the procedures this way will make changes to data in the actual database (which I don't want). Another problem is when someone make change to the database without updating that database project
I can either:
do a rollback after every unit test (but there are some procedures that contain
commit, so even if Irollbackinc#, some part will still get committed)create a testing database (not an option in my situation)
create a in-memory database (I prefer this option. I checked out
SQLite. However it doesn't seem to support procedures)
Any advises or suggestions are appreciated
sql
sql-server unit-testing tsql stored-procedures edited Nov 12 at 9:56
Rahul Neekhra
602627
asked Nov 11 at 16:25
user308553
5511823
add a comment |
up vote
2
down vote
favorite
So Im trying to figure out a way to properly unit test database procedures in visual studio so I can integrate it into our deployment pipeline.
what I am doing right now is create a database project and link it to our database. Then generate unit test with visual studio on the procedures. The problem is that unit testing the procedures this way will make changes to data in the actual database (which I don't want). Another problem is when someone make change to the database without updating that database project
I can either:
do a rollback after every unit test (but there are some procedures that contain
commit, so even if Irollbackinc#, some part will still get committed)create a testing database (not an option in my situation)
create a in-memory database (I prefer this option. I checked out
SQLite. However it doesn't seem to support procedures)
Any advises or suggestions are appreciated
sql
sql-server unit-testing tsql stored-procedures edited Nov 12 at 9:56
Rahul Neekhra
602627
asked Nov 11 at 16:25
user308553
5511823
add a comment |
up vote
2
down vote
favorite
up vote
2
down vote
favorite
So Im trying to figure out a way to properly unit test database procedures in visual studio so I can integrate it into our deployment pipeline.
what I am doing right now is create a database project and link it to our database. Then generate unit test with visual studio on the procedures. The problem is that unit testing the procedures this way will make changes to data in the actual database (which I don't want). Another problem is when someone make change to the database without updating that database project
I can either:
do a rollback after every unit test (but there are some procedures that contain
commit, so even if Irollbackinc#, some part will still get committed)create a testing database (not an option in my situation)
create a in-memory database (I prefer this option. I checked out
SQLite. However it doesn't seem to support procedures)
Any advises or suggestions are appreciated
sql
sql-server unit-testing tsql stored-procedures edited Nov 12 at 9:56
Rahul Neekhra
602627
asked Nov 11 at 16:25
user308553
5511823
So Im trying to figure out a way to properly unit test database procedures in visual studio so I can integrate it into our deployment pipeline.
what I am doing right now is create a database project and link it to our database. Then generate unit test with visual studio on the procedures. The problem is that unit testing the procedures this way will make changes to data in the actual database (which I don't want). Another problem is when someone make change to the database without updating that database project
I can either:
do a rollback after every unit test (but there are some procedures that contain
commit, so even if Irollbackinc#, some part will still get committed)create a testing database (not an option in my situation)
create a in-memory database (I prefer this option. I checked out
SQLite. However it doesn't seem to support procedures)
Any advises or suggestions are appreciated
sql
sql-server unit-testing tsql stored-procedures sql
sql-server unit-testing tsql stored-procedures edited Nov 12 at 9:56
Rahul Neekhra
602627
asked Nov 11 at 16:25
user308553
5511823
edited Nov 12 at 9:56
Rahul Neekhra
602627
asked Nov 11 at 16:25
user308553
5511823
edited Nov 12 at 9:56
Rahul Neekhra
602627
edited Nov 12 at 9:56
Rahul Neekhra
602627
edited Nov 12 at 9:56
Rahul Neekhra
602627
602627
asked Nov 11 at 16:25
user308553
5511823
asked Nov 11 at 16:25
user308553
5511823
asked Nov 11 at 16:25
user308553
5511823
5511823
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
up vote
2
down vote
accepted
- create a in-memory database (I prefer this option. I checked out SQLite. However it doesn't seem to support procedures)
SQL Server do not support in-memory option
- create a testing database (not an option in my situation)
This is usually a best option for tests against an actual database.
Tests should be executed in isolation of each other to produce consistent and trusted result.
Nobody will break your testing database, because you will run a script to create it and fill with the required data before tests.
- do a rollback after every unit test (but there are some procedures that contain commit, so even if I rollback in c#, some part will still
get committed)
Try to use TransactionScope Class which should rollback internal transactions committed during test.
But if you are using some database which used by others, your tests can possibly fail because somebody made changes.
answered Nov 12 at 3:59
Fabio
18.9k22044
For the rollback option. Let's say I do everything in a Transaction scope. Running a script to prep the database, run my procedure, check if data is correct, then do a rollback. This is a dev database use by other teams. What sort of problem would I have to watch out for if I use this rollback option?
– user308553
Nov 20 at 22:26
Don't use dev database, create own for tests.
– Fabio
Nov 21 at 4:26
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
2
down vote
accepted
- create a in-memory database (I prefer this option. I checked out SQLite. However it doesn't seem to support procedures)
SQL Server do not support in-memory option
- create a testing database (not an option in my situation)
This is usually a best option for tests against an actual database.
Tests should be executed in isolation of each other to produce consistent and trusted result.
Nobody will break your testing database, because you will run a script to create it and fill with the required data before tests.
- do a rollback after every unit test (but there are some procedures that contain commit, so even if I rollback in c#, some part will still
get committed)
Try to use TransactionScope Class which should rollback internal transactions committed during test.
But if you are using some database which used by others, your tests can possibly fail because somebody made changes.
answered Nov 12 at 3:59
Fabio
18.9k22044
For the rollback option. Let's say I do everything in a Transaction scope. Running a script to prep the database, run my procedure, check if data is correct, then do a rollback. This is a dev database use by other teams. What sort of problem would I have to watch out for if I use this rollback option?
– user308553
Nov 20 at 22:26
Don't use dev database, create own for tests.
– Fabio
Nov 21 at 4:26
add a comment |
up vote
2
down vote
accepted
- create a in-memory database (I prefer this option. I checked out SQLite. However it doesn't seem to support procedures)
SQL Server do not support in-memory option
- create a testing database (not an option in my situation)
This is usually a best option for tests against an actual database.
Tests should be executed in isolation of each other to produce consistent and trusted result.
Nobody will break your testing database, because you will run a script to create it and fill with the required data before tests.
- do a rollback after every unit test (but there are some procedures that contain commit, so even if I rollback in c#, some part will still
get committed)
Try to use TransactionScope Class which should rollback internal transactions committed during test.
But if you are using some database which used by others, your tests can possibly fail because somebody made changes.
answered Nov 12 at 3:59
Fabio
18.9k22044
For the rollback option. Let's say I do everything in a Transaction scope. Running a script to prep the database, run my procedure, check if data is correct, then do a rollback. This is a dev database use by other teams. What sort of problem would I have to watch out for if I use this rollback option?
– user308553
Nov 20 at 22:26
Don't use dev database, create own for tests.
– Fabio
Nov 21 at 4:26
add a comment |
up vote
2
down vote
accepted
up vote
2
down vote
accepted
- create a in-memory database (I prefer this option. I checked out SQLite. However it doesn't seem to support procedures)
SQL Server do not support in-memory option
- create a testing database (not an option in my situation)
This is usually a best option for tests against an actual database.
Tests should be executed in isolation of each other to produce consistent and trusted result.
Nobody will break your testing database, because you will run a script to create it and fill with the required data before tests.
- do a rollback after every unit test (but there are some procedures that contain commit, so even if I rollback in c#, some part will still
get committed)
Try to use TransactionScope Class which should rollback internal transactions committed during test.
But if you are using some database which used by others, your tests can possibly fail because somebody made changes.
answered Nov 12 at 3:59
Fabio
18.9k22044
- create a in-memory database (I prefer this option. I checked out SQLite. However it doesn't seem to support procedures)
SQL Server do not support in-memory option
- create a testing database (not an option in my situation)
This is usually a best option for tests against an actual database.
Tests should be executed in isolation of each other to produce consistent and trusted result.
Nobody will break your testing database, because you will run a script to create it and fill with the required data before tests.
- do a rollback after every unit test (but there are some procedures that contain commit, so even if I rollback in c#, some part will still
get committed)
Try to use TransactionScope Class which should rollback internal transactions committed during test.
But if you are using some database which used by others, your tests can possibly fail because somebody made changes.
answered Nov 12 at 3:59
Fabio
18.9k22044
answered Nov 12 at 3:59
Fabio
18.9k22044
answered Nov 12 at 3:59
Fabio
18.9k22044
answered Nov 12 at 3:59
Fabio
18.9k22044
18.9k22044
For the rollback option. Let's say I do everything in a Transaction scope. Running a script to prep the database, run my procedure, check if data is correct, then do a rollback. This is a dev database use by other teams. What sort of problem would I have to watch out for if I use this rollback option?
– user308553
Nov 20 at 22:26
Don't use dev database, create own for tests.
– Fabio
Nov 21 at 4:26
add a comment |
For the rollback option. Let's say I do everything in a Transaction scope. Running a script to prep the database, run my procedure, check if data is correct, then do a rollback. This is a dev database use by other teams. What sort of problem would I have to watch out for if I use this rollback option?
– user308553
Nov 20 at 22:26
Don't use dev database, create own for tests.
– Fabio
Nov 21 at 4:26
For the rollback option. Let's say I do everything in a Transaction scope. Running a script to prep the database, run my procedure, check if data is correct, then do a rollback. This is a dev database use by other teams. What sort of problem would I have to watch out for if I use this rollback option?
– user308553
Nov 20 at 22:26
For the rollback option. Let's say I do everything in a Transaction scope. Running a script to prep the database, run my procedure, check if data is correct, then do a rollback. This is a dev database use by other teams. What sort of problem would I have to watch out for if I use this rollback option?
– user308553
Nov 20 at 22:26
Don't use dev database, create own for tests.
– Fabio
Nov 21 at 4:26
Don't use dev database, create own for tests.
– Fabio
Nov 21 at 4:26
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53250748%2fvs-unit-test-on-database-procedures-inmemory-that-can-be-integrated-with-continu%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
