help_eachother

Hi,

I want to run some database scripts before and after running NUnit tests.

I am thinking of creating a seperate project for these scripts and then i can call this exectable assembly before and after NUnit tests

Kindly guide me , if this approach correct.

Should i create a database projects or a normal project



Re: Visual C# IDE Database Scripts

Ji Cheng Wang - MSFT

Hi help_eachother,

As I understand that you would like to run some database scripts through a seperate project, I think you can create a database project or a normal windows project to run it. Either should be OK if you like it.

If you would like to create a database project, you can run the OSQL command to execute the SQL scripts in the database project and try to check out this MSDN document about "How to: Create Database Projects" for details - http://msdn2.microsoft.com/en-us/library/aa833235(VS.80).aspx. Otherwise, you can try to create a windows application to run the database scripts through SqlCommand or OleDbCommand.

Hope this helps,

Regards,






Re: Visual C# IDE Database Scripts

help_eachother

Hi,

Once i have created a database project(scripts for creating tables , and populating initial data), then how can i invoke this project before running my Unit test Project

Thanks & Regards





Re: Visual C# IDE Database Scripts

boban.s

Check out my post here:

http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=1878660&SiteID=1

It describes how to run sql scripts against SQL Server. There, script is from file but you can put your scripts in the code or even better as resource strings in the unit test project:






Re: Visual C# IDE Database Scripts

help_eachother

Hi,

Howe can i use the database projects in running the scripts.

Actually i will be having a build file in which i will be running the calling the database project(scripts) before and after unit tests.

So how can i use it with MSBuild





Re: Visual C# IDE Database Scripts

Ji Cheng Wang - MSFT

Hi help_eachother,

If you would like to run SQL scripts using a custom MSbuild task, you can try to check out this article for reference - http://devauthority.com/blogs/joeydotnet/archive/2006/05/23/1117.aspx

Here's an example of how it can be used in an MSBuild script:

Code Block

< xml version="1.0" encoding="utf-8" >
<Project DefaultTargets="BuildDB" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
<UsingTask AssemblyFile="tools\joeyDotNet\joeyDotNet.MSBuild.Tasks.dll" TaskName="RunSqlScripts" />

<PropertyGroup>
<SqlToolsFolder>C:\program files\microsoft sql server\90\Tools\Binn</SqlToolsFolder>
<Osql>$(SqlToolsFolder)\osql.exe</Osql>
<OsqlConnString>-E</OsqlConnString>
<InitialCatalog>joeyDotNetDB</InitialCatalog>
</PropertyGroup>

<ItemGroup>
<TargetDBScriptFileInclude="sql\Database.sql" />
<TargetTableScriptFileInclude="sql\Tables.sql" />
<TargetSProcs Include="sql\sprocs\**\*.sql" />
</ItemGroup>

<Target Name="BuildDB">
<RunSqlScripts OsqlPath="$(Osql)" SqlScripts="@(TargetDBScriptFile)" WaitForExit="true" />
<RunSqlScripts OsqlPath="$(Osql)" SqlScripts="@(TargetTableScriptFile)" />
<RunSqlScripts OsqlPath="$(Osql)" SqlScripts="@(TargetSProcs)" DatabaseName="$(InitialCatalog)" />
</Target>
</Project>

Hope this helps,

Regards,

This response contains links reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you.
Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there.
There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.






Re: Visual C# IDE Database Scripts

help_eachother

Hi,

I really appreciate ur last post, but i have database scripts for Oracle. So how can i run Oracle Scripts.

Also , if say i put those scripts in a database project , then how can i run the database project

Which approch is better Running scripts directly from MSBuild task or calling database project in MSBuild





Re: Visual C# IDE Database Scripts

Ji Cheng Wang - MSFT

Hi,

Thanks for your quick reply.

Based on my understanding, running database script for Oracle should be similar to that for SQL Server. They are all SQL statements.

If you would like to create a database project, you can run the database script as a independent project. Note the database project can't be compiled into an assembly.

Running database scripts directly from MSBuild task or from a database project or from a windows application, I think one of them should be OK for you.

If you still have any issues when trying my suggestion, please post back to me. Thanks.

Regards,






Re: Visual C# IDE Database Scripts

help_eachother

Hi,

Reaaly like ur responses and they are very detailed and informative

But still i have some queries / doubts :

  1. Whhat would be the real advantage of using Databse Projects over running SQL Scripts directly. (Pros/ Cons)
  2. If i want to use Database Project, how can i run it from MSBuild since as u told its not a assembly what would be the command like.
  3. If i want to run Oracle Scripts directly from MSBuild, then what would be the commad. Kindly provide me with the command, as this can act as a refernce for everybody , since i was not able to find much stuff on Oracle scripts in MSBuild

Thanks & Regards





Re: Visual C# IDE Database Scripts

:)Eric Han

i think you can run some init and release code in NUnit Setup and TearDown period.




Re: Visual C# IDE Database Scripts

help_eachother

Hi,

I do not want to run database init and release scripts in NUnit Setup and tear down beacuse i want to run these database only for Integration environment.

So for my integration environment i will be having a master build file that will initialise the database before running the Unit test.

Could you please sugget which approach is recommended : Database project OR Running the databse scripts directly

What is the real advanatge of using database projects

How can database project be invoked from MSBuild





Re: Visual C# IDE Database Scripts

:)Eric Han

try to write batch file to call the database sciprt (i remember a sqlserver interface can call the database script without IDE), unit test also can run under command line mode .






Re: Visual C# IDE Database Scripts

help_eachother

Hi,

Could u provide a sample batch file for invoking Oracle Scripts





Re: Visual C# IDE Database Scripts

:)Eric Han

sorry,i'm familiar with Oracle....






Re: Visual C# IDE Database Scripts

Ji Cheng Wang - MSFT

help_eachother wrote:

Hi,

Reaaly like ur responses and they are very detailed and informative

But still i have some queries / doubts :

  1. Whhat would be the real advantage of using Databse Projects over running SQL Scripts directly. (Pros/ Cons)
  2. If i want to use Database Project, how can i run it from MSBuild since as u told its not a assembly what would be the command like.
  3. If i want to run Oracle Scripts directly from MSBuild, then what would be the commad. Kindly provide me with the command, as this can act as a refernce for everybody , since i was not able to find much stuff on Oracle scripts in MSBuild

Thanks & Regards

Hi help_eachother,

For your three questions, my understanding just as follows:

1) For database project in VS.NET, you can put your SQL scripts in the database easily and reconstruct the whole database (inc. data!) from within the project. Lots of other abilities, but the main thing is that it allows you to generate the scripts automatically, and even create change scripts automatically when you change the schema. If you would like to learn how to use database projects in VS.NET, you can try to check out this blog for details - http://weblogs.asp.net/rosherove/archive/2004/03/31/104662.aspx

2) Running SQL scripts using a custom MSbuild task is independent of the database project in VS.NET. I don't think you can run the database project through a MSBuild task. How to run a database project, try to check out this PDF file for details - http://www.devx.com/assets/download/5865.pdf

3) I'm not familiar with running Oracle scripts using a MSBuild task. Based on my understanding, this should be similar to that in SQL Server. You can try to post this question to the forums about Oracle for helps.

Hope this helps,

Regards,

This response contains links reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you.
Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there.
There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.