c# - 创建数据库和表

Question

我必须创建一个数据库，然后在创建的数据库中创建一个表。

我已通过连接字符串连接到ma​​ster，但是我想在连接和创建时添加我刚刚在初始目录中创建的新数据库名称表。

这就是我所拥有的

string conn = "SERVER=BRIAN-PC\\SQLEXPRESS; Initial Catalog=master; user id =sa; Password=kagiso";

            String str = "CREATE DATABASE IF NOT EXIST PSAHoldings ON PRIMARY"
                + "(NAME = PSAHoldings_Data,"
                + "FILENAME = 'C:\\PSAHoldings.mdf'',"
                + "SIZE = 2MB, FILEGROWTH =10%)"
                + "LOG ON (NAME = PSAHoldings_Log,"
                + "FILENAME = 'C:\\PSAHoldingsLog,idf',"
                + "SIZE = 1MB,"
                + "FILEGROTH = 10%)";

SqlConnection connection = new SqlConnection(conn);
            connection.Open();

try
            {
                //SqlCommand to create database
                SqlCommand cmd = new SqlCommand(str, connection);
                cmd.ExecuteNonQuery();

                MessageBox.Show("DataBase was successfully created", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information);

string connect = "Data Source=Esther-PC\\SQLEXPRESS; Initial Catalog=PSAHoldings; user id =sa; Password=kagiso";

            string table = "CREATE TABLE IF NOT EXIST t_original (" +
                "empId varChar(10) NOT NULL PRIMARY KEY," +
                "paycode varChar(10) NOT NULL," +
                "amount int NOT NULL," +
                ")";


            SqlConnection con = new SqlConnection(connect);
            con.Open();

            SqlCommand createTable = new SqlCommand(table, con);
            createTable.ExecuteNonQuery();
            }
}
            catch (SqlException sqlEx)
            {
                MessageBox.Show(sqlEx.ToString(), "Exception Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {
                connection.Close();
                con.Close();
            }

当我运行应用程序时，我不断收到异常错误。

异常错误显示System.Data.SqlClient.SqlException(0x80131904):关键字“IF”附近的语法不正确。在需要条件的上下文中指定的非 bool 类型表达式，靠近“PSAHoldings”，字符串“Size=1mb, filegrowth=10%)”后面有未闭合的引号。

Answer 1

我不认为有这样的代码 CREATE DATABASE IF NOT EXIST - 在 MSDN 上找不到任何提及它的信息: https://msdn.microsoft.com/en-us/library/ms176061.aspx

试试这个。

IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = 'PSAHoldings')
  CREATE DATABASE PSAHoldings ON PRIMARY 
    (NAME = PSAHoldings_Data,
    FILENAME = 'C:\\PSAHoldings.mdf',
    SIZE = 3MB, FILEGROWTH =10%)
    LOG ON (NAME = PSAHoldings_Log,
    FILENAME = 'C:\\PSAHoldingsLog.idf',
    SIZE = 1MB,
    FILEGROWTH = 10%)

(请注意，修复的其他一些错误类型也将文件大小更改为 3MB，因为我有一个错误说它需要至少 3MB)