Check if that DataBase is exist.

Question

Hi All,

What to do;

Check if name of database is exist every time to create.Bellow some code I'm trying.
Thank

using System;  
using System.Collections.Generic;  
using System.ComponentModel;  
using System.Data;  
using System.Data.SqlClient;  
using System.Drawing;  
using System.Linq;  
using System.Text;  
using System.Threading.Tasks;  
using System.Windows.Forms;  
  
namespace DataBase_C  
{  
    public partial class Form1 : Form  
    {  
        SqlConnection connection = new SqlConnection(@"server=(localdb)\v11.0");  
        public Form1()  
        {  
            InitializeComponent();  
        }  
  
        private void button1_Click(object sender, EventArgs e)  
        {  
            if (CheckDatabaseExists(textBox1.Text))  
            {  
                MessageBox.Show("Database already Created!", "Database Exists", MessageBoxButtons.OK, MessageBoxIcon.Information);  
            }  
            else  
            {  
                CreateDatabase(textBox1.Text);  
                MessageBox.Show("DataBase is Created Successfully!", "Database Exists", MessageBoxButtons.OK, MessageBoxIcon.Information);  
  
            }  
        }  
        public void CreateDatabase(string dataBase)  
        {  
            string sql = string.Format(@"CREATE DATABASE[Test9]ON PRIMARY (NAME=Tes9t_data, FILENAME = '{0}\Test9_data.mdf')  
                                           LOG ON (NAME=Test9_log, FILENAME = '{0}\Test9_log.ldf')", @"C:\Users\family\Documents\SqL\");   
            SqlConnection connection = new SqlConnection(sql);  
            string str = sql + dataBase;  
            SqlCommand cmd = new SqlCommand(str, connection);  
            connection.Open();  
            cmd.ExecuteNonQuery();  
            connection.Close();  
        }  
        public static bool CheckDatabaseExists(string dataBase)  
        {  
            string conStr = @"server=(localDB)\\v11.0;" + "AttachDbFileName = 'C:\\Users\\family\\Documents\\SqL\\Test9_data.mdf';" +  
                            "Integrated Security = yes";  
  
            string cmdText = conStr + dataBase + "'";  
            bool isExist = false;  
            using (SqlConnection connection = new SqlConnection(conStr))  
            {  
                connection.Open();  
                using (SqlCommand cmd = new SqlCommand(cmdText, connection))  
                {  
                    using (SqlDataReader reader = cmd.ExecuteReader())  
                    {  
                        isExist = reader.HasRows;  
                    }  
                }  
                connection.Close();  
            }  
            return isExist;  
        }  
      
  
    }  
}  


  

Answer 1

@MiPakTeh , Based on my test, you could try the following code to check if the database exists.

public static bool CheckDatabaseExists(string dataBase)  
    {  
        string conStr = @"connstr";  
  
        string cmdText = String.Format("SELECT * FROM sys.databases where Name='{0}'", dataBase);  
        bool isExist = false;  
        using (SqlConnection connection = new SqlConnection(conStr))  
        {  
            connection.Open();  
            using (SqlCommand cmd = new SqlCommand(cmdText, connection))  
            {  
                using (SqlDataReader reader = cmd.ExecuteReader())  
                {  
                    isExist = reader.HasRows;  
                }  
            }  
            connection.Close();  
        }  
        return isExist;  
    }  

Please use the following sql text to check if the database exists.

SELECT * FROM sys.databases where Name='test'  

Update for CreateDatabase problem:

public void CreateDatabase(string dataBase)  
        {  
            SqlConnection connection = new SqlConnection(@"str");  
            using (connection)  
            {  
                connection.Open();  
  
                string sql = string.Format(@"CREATE DATABASE [{0}]ON PRIMARY (NAME={0}, FILENAME = '{1}\{0}.mdf')  
                                            LOG ON (NAME=txt, FILENAME = '{1}\{0}.ldf')", dataBase,@"D:\DB");  
  
                SqlCommand command = new SqlCommand(sql, connection);  
                command.ExecuteNonQuery();  
            }  
        }  

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Best regards,
Jack

Answer 2

The following comes from my GitHub repository.

There are three samples using three buttons were the logic was done for teaching purposes.

First method

DECLARE @DbName AS nvarchar(50) = 'TODO'  
SELECT name FROM sys.databases WHERE Name = @DbName  

Then there is a drawn out method

Answer 3

@"server= (localDB)\v11.0;" + "AttachDbFileName = 'C:\Users\family\Documents\SqL\Test9_data.mdf';" + "Integrated Security = yes";

I don't understand what you want to check? You use LocalDB and the database exists when you attach the database with the connection string.
The database file is located in your user profile, where only you can start/attach it from.