Find the Folder created.

MiPakTeh 1,476 Reputation points
2021-12-14T12:32:20.037+00:00

Hi All,

I run this code no problem but I didn't find the folder "bin/Debug/Data and message

"Warning CS0219 The variable 'catalog' is assigned but its value is never used CreateDatabase C:\Users\family\source\repos\CreateDatabase\CreateDatabase\Form1.cs"

somebody can tell me.

using System;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Reflection;
using System.Windows.Forms;

namespace CreateDatabase
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        /// <summary>
        /// Create database
        /// Create table
        /// load empty data into a DataTable
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void CreateButton_Click(object sender, EventArgs e)
        {

            if (DataOperations.CreateDatabase())
            {
                var customers = DataOperations.Read();
            }
            else
            {
                MessageBox.Show("Failed");
            }
        }
    }

    public class DataOperations
    {
        public const string DB_DIRECTORY = "Data";
        private const string _connectionStringDb = "Data Source=(LocalDB)\\MSSQLLocalDB;Initial Catalog=AppData;Integrated Security=True";

        public static DataTable Read()
        {
            DataTable table = new DataTable();

            using (var cn = new SqlConnection(_connectionStringDb))
            {
                using (var cmd = new SqlCommand($"SELECT Identifier, CompanyName  FROM Customer", cn))
                {
                    cn.Open();
                    table.Load(cmd.ExecuteReader());
                }
            }

            return table;
        }
        /// <summary>
        /// Create database in Data folder under the application folder
        /// ASSUMES Data folder exists
        /// </summary>
        /// <returns></returns>
        public static bool CreateDatabase()
        {
            string catalog = "master";
            string connectionStringMaster = $"Data Source=(LocalDB)\\MSSQLLocalDB;Initial Catalog=master;Integrated Security=True";

            string dbName = "AppData";

            string outputFolder = Path.Combine(Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location), DB_DIRECTORY);

            string mdfFilename = $"{dbName}.mdf";
            string dbFileName = Path.Combine(outputFolder, mdfFilename);
            string logFileName = Path.Combine(outputFolder, $"{dbName}_log.ldf");

            string createTableCommand = @"
             CREATE TABLE [dbo].[Customer](
              [Identifier] [int] IDENTITY(1,1) NOT NULL,
              [CompanyName] [nvarchar](255) NULL,
              [ContactName] [nvarchar](255) NULL,
              CONSTRAINT [Customer$PrimaryKey] PRIMARY KEY CLUSTERED 
             (
              [Identifier] ASC
             )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
             ) ON [PRIMARY]
             ";

            try
            {

                if (File.Exists(dbFileName))
                {


                    if (!DetachDatabase(dbFileName, logFileName))
                    {
                        return false;
                    }
                }

                using (var cn = new SqlConnection(connectionStringMaster))
                {
                    using (var cmd = new SqlCommand($"CREATE DATABASE {dbName} ON (NAME = N'{dbName}', FILENAME = '{dbFileName}')", cn))
                    {
                        cn.Open();
                        cmd.ExecuteNonQuery();
                        cn.Close();

                        cn.ConnectionString = _connectionStringDb;
                        cmd.CommandText = createTableCommand;
                        cn.Open();
                        cmd.ExecuteNonQuery();
                        cn.Close();
                        return true;
                    }
                }
            }
            catch (Exception)
            {
                return false;
            }

        }
        /// <summary>
        /// Drop database and delete physical files
        /// </summary>
        /// <param name="dbName">database name including path</param>
        /// <param name="logFile">log name including path</param>
        /// <returns>success</returns>
        public static bool DetachDatabase(string dbName, string logFile)
        {
            try
            {
                string connectionString = $"Data Source=(LocalDB)\\MSSQLLocalDB;Initial Catalog=master;Integrated Security=True";

                if (File.Exists(dbName))
                {
                    using (var cn = new SqlConnection(connectionString))
                    {
                        cn.Open();
                        SqlCommand cmd = cn.CreateCommand();

                        /*
                         * This needs to be done if you create the database repetitively
                         * in one session, otherwise not needed. Note it take several seconds
                         * to execute
                         */
                        cmd.CommandText = $"ALTER DATABASE {Path.GetFileNameWithoutExtension(dbName)} SET SINGLE_USER WITH ROLLBACK IMMEDIATE";

                        cmd.ExecuteNonQuery();
                        cmd.CommandText = $"exec sp_detach_db '{Path.GetFileNameWithoutExtension(dbName)}'";
                        cmd.ExecuteNonQuery();
                        cn.Close();

                        if (File.Exists(dbName))
                        {
                            File.Delete(dbName);
                        }

                        if (File.Exists(logFile))
                        {
                            File.Delete(logFile);
                        }

                        return true;
                    }
                }
                else
                {
                    return true;
                }
            }
            catch
            {
                return false;
            }
        }
    }
}
C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
9,963 questions
0 comments No comments
{count} votes

Accepted answer
  1. Karen Payne MVP 35,016 Reputation points
    2021-12-16T13:56:00.63+00:00

    The variable string catalog = "master"; was something I didn't remove.

    You should check out my GitHub repository for a clean version for creating a database and populating tables.

    https://github.com/karenpayneoregon/localdb-samples

    See this for checking if the folder exists

    public static bool DatabaseFolderExists => Directory.Exists(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, DataOperations.DB_DIRECTORY));  
    

    158257-figure1.png

    0 comments No comments

2 additional answers

Sort by: Most helpful
  1. Bruce (SqlWork.com) 52,586 Reputation points
    2021-12-14T15:47:05.767+00:00

    The error is pretty clear. You defined a variable named catalog, but never use it in the code.

    0 comments No comments

  2. Michael Taylor 44,471 Reputation points
    2021-12-14T16:22:33.203+00:00

    I suspect your code is throwing an exception but you aren't capturing it. For example if the directory structure doesn't exist then the call will fail as SQL won't create the directory. Beyond that the file will be created (assuming you have permissions). If you're running within VS then it'll be under the output directory of your project. For example if you're app is at c:\users\<profile>\source\repos\myproject AND your build configuration has the output going to bin\debug then the produced file will be at c:\users\<profile>\source\repos\myproject\bin\debug\data.

    As for your catalog error. The only place you likely want to use it is in your create/delete and you hard coded the master database. Thus you don't really need the catalog variable. Of course if you move the connection string to a config file then you will eventually need to do that.