1

I'm using Visual Studio 2010 ASP.NET with C# on .NET 4.0 and SQL Server 2008 R2.

I would like to create a database on the server named WINSRV\SQLSRV.

How do I loop to test for a unique database name?

My default database should be ab. In case this name is taken, I would like to create a database named ab1, and if this one is also taken - ab2 and so forth.

I need it to rum from a web page created in the Visual Studio 2010 ASP.NET C# script, and not from the SQL server management studio.

Thanks.

elarrow
  • 687
  • 3
  • 11
  • 19

2 Answers2

1
Declare @Iteration int
Set @Iteration = 0
Declare @DBName varchar(100)

while(@Iteration <> -1)
begin
    if(@Iteration = 0)
        Set @DBName = 'ab'
    Else
        Set @DBName = 'ab' + Convert(varchar, @Iteration)
    IF NOT EXISTS(select Object_Id from sys.databases where name = 'ab')
    Begin

        Exec('create database ' +  @DBName)
        return;
    End
    Else
    Begin
        Set @Iteration = @Iteration + 1
    End
End

Edit

To Work from C# click here

Community
  • 1
  • 1
Pankaj
  • 9,749
  • 32
  • 139
  • 283
  • It does not work! To fix it I made this correction: IF NOT EXISTS(select name from sys.databases where name = @DBName) Now, this is not the C# I need, and this is a huge part of the problem. Where should I open a connection? Should I use transact? Wouldn't it be better to use try/catch? Therefore, I found nothing helpful in the link attached. Any directions? thanks. BTW, is there an automatic translator from T-SQL to C#? – elarrow Mar 16 '12 at 13:48
0

You will want to check to see if your choices for databases exist:

You can do it primary sql side as so:

IF (EXISTS (SELECT name FROM master.sys.databases WHERE name = 'ab')))
    --Do what you need to do

or you could get a list databases and check .net side:

SELECT name FROM master.sys.databases

Hope this helps.

Khan
  • 17,904
  • 5
  • 47
  • 59