Check if DataBase Exists

Question

I have the following function coding in VB 2010 but it always returns a value of false.  Is there something wrong with my SQL code to check if a database exists?

 Public Function CheckDBExists(ByVal ConnString As String, ByVal databasename As String) As Boolean

        Dim exists As Byte = 0

        Dim conn As SqlConnection = New SqlConnection(ConnString)

        Dim cmd As SqlCommand = New SqlCommand("SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'" & databasename & "')", conn)

        conn.Open()

        exists = CByte(cmd.ExecuteScalar())

        conn.Close()

        Return CBool(exists)

    End Function

---

Dennis in Longview

Answer 1

The simplest way to check if database exists:

Dim exists As Byte = 0

Dim conn As SqlConnection = New SqlConnection(ConnString)

Dim cmd As SqlCommand = New SqlCommand("

SELECT case when exists (select 1 from sys.Databases 
where Name = @DbName) then 1 else 0 end as DbExists", conn)

cmd.Parameters.AddWithValue("@DbName",databasename)

 conn.Open()

 exists = CByte(cmd.ExecuteScalar())

 conn.Close()

 Return CBool(exists)

 End Function

 

Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog

Answer 2

Hi,

In addition to the method above, we can also use the Server class. I perform a test with the following C# codes, it works fine. Please see:

ServerConnection conn;
Server sqlserver;
conn = new ServerConnection();
conn.LoginSecure = false;
conn.ServerInstance = "ServerName";
conn.Login = "LoginID";
conn.Password = "password";
conn.Connect();
sqlserver = new Server(conn);
if (sqlserver.Databases.Contains("Test"))
{
  Console.WriteLine("Test database exists");
}
else
{
  Console.WriteLine("Test database doesn't exist");
}
Console.ReadLine();

For more information, please see:
Server Class

Thanks,
Ai-Hua Qiu

---

Constant dropping wears away a stone.

Answer 3

Thanks...the below is what ended up working:

 

Public Function CheckDBExists(ByVal ConnString As String, ByVal databasename As String, ByVal exc As Exception) As Boolean

        Dim conn As SqlConnection = Nothing

        Dim v As Object = Nothing

        Try

            conn = New SqlConnection(ConnString)

            Dim c As String = "SELECT COUNT (*) FROM sys.sysdatabases where name='" & databasename & "'"

            conn.Open()

            Dim cmd As SqlCommand = New SqlCommand(c, conn)

            v = cmd.ExecuteScalar()

        Catch ex As Exception

            exc = ex

            Return False

        Finally

            conn.Close()

        End Try

        Return CBool(v)

    End Function

---

Dennis in Longview

Answer 4

I suggest to keep database as a parameter in the above select statement as otherwise this code is opened to SQL injection attack.

---

Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog

Answer 5

Thanks but this doesn't run on the internet...just private computers.  Also, I have no idea what injection attack is.

---

Dennis in Longview

Answer 6

There is very long and comprehensive discussion about this problem here

Avoid SQL injection attacks

---

Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog

Answer 7

Thanks...I read the site but my knowledge of web programming isn't up to the task of understanding.  I thought if the database  server was password protected, it would prevent anyone from accessing the databases on it unless they had the password.

---

Dennis in Longview

Answer 8

Have you actually ran this code...my version of VB.Net 2010 says that there is no .AddWithValues method for Parameters!

---

Dennis in Longview

Answer 9

Since I installed M'soft Sql Visual Studio Management Express, this code doesn't even work to check if a database exists.  I am totally lost as to how to check if a database exists other than try to create it and intercept the error that says it already exists.

---

Dennis in Longview

Answer 10

The name of the method is AddWithValue. See AddWithValue

---

Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog

Answer 11

Tried it...didn't work. Returned value of 0 but I know the database exists as I can read data from it's tables and write to it.

---

Dennis in Longview

Answer 12

Yes I tried Ai-hya's solution but I have no idea how to get access to the server class in VB.Net.  Also, I have no idea what SSMS is!

 

I am new to SQL databases as I always used Access databases before and am just learning VB 2010 although I knew earlier versions of VB new some.

---

Dennis in Longview

Answer 13

I can't get the SQL Server Management Studio to let me have permission to do anything like add a database, attache a database, check databases, etc.  I am closing this link.  I remove SQL from my computer and have installed MySQL and it's management software and I'm very happy with it...much easier than M'soft's SQL and I would recommend it to anyone who isn't a Microsoft programmer!

I used MySQL before and even have a database of recipes on the web at www.cookery911.com.  Still working on the site but you can see the recipes and print them out.

Thanks for all your help.  MICROSOFT's SQL is the pitts.

---

Dennis in Longview