Use LocalDB to create Database.

Question

Hi All,

Test using LocalDB to create Database, Table and Column or fields.
Somebody can show how to make it and then show all that on Form.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace DataBase_
{
    public partial class Form1 : Form
    {
        string nom = "123";
        public Form1()
        {
            InitializeComponent();
        }
        private void button1_Click(object sender, EventArgs e)
        {

            SqlConnection connection = new SqlConnection(@"server=(localdb)\v11.0");
            using (connection)
            {
                connection.Open();

                string sql = string.Format(@"CREATE DATABASE[Test_A]ON PRIMARY (NAME=Test_data, FILENAME = '{0}\Test_data.mdf')
                                           LOG ON (NAME=Test_log, FILENAME = '{0}\Test_log.ldf')", @"C:\Users\family\Documents\");

                SqlCommand command = new SqlCommand(sql, connection);
                command.ExecuteNonQuery();

                using (SqlConnection connexion = new SqlConnection(sql))
                {
                    SqlCommand command_ =
                        new SqlCommand("CREATE TABLE StoreItems( " +
                               "StoreItemID int IDENTITY(1, 1) NOT NULL, " +
                               "Category varchar(50), " +
                               "[Item Name] varchar(100) NOT NULL, " +
                               "Size varchar(20), " +
                               "[Unit Price] money);",
                               connection);
                    connection.Open();
                    command_.ExecuteNonQuery();

                    MessageBox.Show("A new table named StoreItems has been crated.");
                }
            }
        }
    }
}

Answer 1

@MiPakTeh , Based on my test, I reproduced your problem.

We need to use connectionstring as the parameter of the sqlconnection class instead of sql text.

Please change:

using (SqlConnection connexion = new SqlConnection(sql))  

into

using (SqlConnection connexion = new SqlConnection(@"server=(localdb)\v11.0"))  

Best Regards,
Jack

---

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.

Answer 2

The following creates a database under bin\debug\Data with one table. Note I wrote this code so that if you ever need to delete the database and recreate there is a method which handles this that detaches the database and log file, deletes them. If never going to recreate don't call DetachDatabase

Note my connection string is different as I have a different version of localDb.

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;
            }
        }
    }
}

Answer 3

Thank for your feedback Jackjjun.
Here a error message;
    System.ArgumentException
      HResult=0x80070057
      Message=Keyword not supported: 'create database[test_A]on primary (name'.
      Source=System.Data
      StackTrace:
       at System.Data.Common.DbConnectionOptions.ParseInternal(Hashtable parsetable, String connectionString, Boolean buildChain, Hashtable synonyms, Boolean firstKey)
       at System.Data.Common.DbConnectionOptions..ctor(String connectionString, Hashtable synonyms, Boolean useOdbcRules)
       at System.Data.SqlClient.SqlConnectionString..ctor(String connectionString)
       at System.Data.SqlClient.SqlConnectionFactory.CreateConnectionOptions(String connectionString, DbConnectionOptions previous)
       at System.Data.ProviderBase.DbConnectionFactory.GetConnectionPoolGroup(DbConnectionPoolKey key, DbConnectionPoolGroupOptions poolOptions, DbConnectionOptions& userConnectionOptions)
       at System.Data.SqlClient.SqlConnection.ConnectionString_Set(DbConnectionPoolKey key)
       at System.Data.SqlClient.SqlConnection.set_ConnectionString(String value)
       at System.Data.SqlClient.SqlConnection..ctor(String connectionString, SqlCredential credential)
       at System.Data.SqlClient.SqlConnection..ctor(String connectionString)
       at DataBase_B.Form1.button1_Click(Object sender, EventArgs e) in C:\Users\family\source\repos\DataBase_B\DataBase_B\Form1.cs:line 34
       at System.Windows.Forms.Control.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
       at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
       at System.Windows.Forms.Control.WndProc(Message& m)
       at System.Windows.Forms.ButtonBase.WndProc(Message& m)
       at System.Windows.Forms.Button.WndProc(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
       at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
       at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
       at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.Run(Form mainForm)
       at DataBase_B.Program.Main() in C:\Users\family\source\repos\DataBase_B\DataBase_B\Program.cs:line 19

      This exception was originally thrown at this call stack:
        System.Data.Common.DbConnectionOptions.ParseInternal(System.Collections.Hashtable, string, bool, System.Collections.Hashtable, bool)
        System.Data.Common.DbConnectionOptions.DbConnectionOptions(string, System.Collections.Hashtable, bool)
        System.Data.SqlClient.SqlConnectionString.SqlConnectionString(string)
        System.Data.SqlClient.SqlConnectionFactory.CreateConnectionOptions(string, System.Data.Common.DbConnectionOptions)
        System.Data.ProviderBase.DbConnectionFactory.GetConnectionPoolGroup(System.Data.Common.DbConnectionPoolKey, System.Data.ProviderBase.DbConnectionPoolGroupOptions, ref System.Data.Common.DbConnectionOptions)
        System.Data.SqlClient.SqlConnection.ConnectionString_Set(System.Data.Common.DbConnectionPoolKey)
        System.Data.SqlClient.SqlConnection.ConnectionString.set(string)
        System.Data.SqlClient.SqlConnection.SqlConnection(string, System.Data.SqlClient.SqlCredential)
        System.Data.SqlClient.SqlConnection.SqlConnection(string)
        DataBase_B.Form1.button1_Click(object, System.EventArgs) in Form1.cs
        ...
        [Call Stack Truncated]

Answer 4

you still appear to not understand that you pass a connection string to SqlConnection(), not a sql statement string.