GridView + System.Data.SqlClient.SqlException："无效的对象名称'List'

Question

我有问题...

我想在我的Form2 ...

中显示我的" 自动创建"表

我得到错误：

system.data.sqlclient.sqlexception："无效对象名称'列表'....

，但仅适用于"自动(List(创建的表"，"手动(List1(创建的表"在Form2 ...

中正常工作

idk为什么不适用于自动创建的表...

我使用此代码显示表(适用于List1，但不适合List( ->

    private void Form2_Load(object sender, EventArgs e)
    {
        EmployeesListDataGridView.DataSource = GetEmployeesList();
    }
    private DataTable GetEmployeesList()
    {
        DataTable dtEmployees = new DataTable();
        string connString = ConfigurationManager.ConnectionStrings["WindowsFormsApp1.Properties.Settings.ConsoleApp7ConnectionString2"].ConnectionString;
        using (SqlConnection con = new SqlConnection(connString))
        {
            using (SqlCommand cmd = new SqlCommand("SELECT * FROM [List]", con))// List1 for Testing...
            {
                con.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                dtEmployees.Load(reader);
            }
        }
        return dtEmployees;
    }

在这里，我会在我的应用程序中自动创建一个表格(列表(----->

                        SqlCommand cmd1 = new SqlCommand("CREATE TABLE [dbo].[List]("
                            + "[IDNumbers] INT IDENTITY (1, 1) NOT NULL,"
                            + "[Numbers] DECIMAL (18, 8) NULL,"
                            + "CONSTRAINT [PK_List] PRIMARY KEY CLUSTERED ([IDNumbers] ASC))", connection);

创建的表在ServerExplorer中看起来像 ->

//自动创建，不用在GridView

中

CREATE TABLE [dbo].[List] (
    [IDNumbers] INT             IDENTITY (1, 1) NOT NULL,
    [Numbers]   DECIMAL (18, 8) NULL,
    CONSTRAINT [PK_List] PRIMARY KEY CLUSTERED ([IDNumbers] ASC)
);

所以...我手动创建了一个测试表(List1(之后，其作品以在GridView中显示我的数据库..->

//手动创建

CREATE TABLE [dbo].[List1] (
    [IDNumbers] INT             IDENTITY (1, 1) NOT NULL,
    [Numbers]   DECIMAL (18, 8) NULL,
    CONSTRAINT [PK_List1] PRIMARY KEY CLUSTERED ([IDNumbers] ASC)
);

//编辑，连接字符串

Data Source=(LocalDB)MSSQLLocalDB;AttachDbFilename=|DataDirectory|ConsoleApp7.mdf;Integrated Security=True;MultipleActiveResultSets=True;Connect Timeout=30

//编辑，创建数据库

        // Create New Database
    private void button7_Click(object sender, EventArgs e)
    {
        if (MessageBox.Show("Do you want to Delete the full Table and Create a new Table?", "Delete Table, and Create a new Table!", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
        {
         Task t5 = new Task(CreateNewDatabase); Task.Run(() => CreateNewDatabase());
        }
        else
        {   
        }
    }

        private async void CreateNewDatabase()
    {
        string str = @"Data Source=(LocalDB)MSSQLLocalDB;AttachDbFilename=C:UsersxxxsourcereposConsoleApp7ConsoleApp7ConsoleApp7.mdf;Integrated Security=True;MultipleActiveResultSets=True;Connect Timeout=30";
        using (SqlConnection connection = new SqlConnection(str))
        {
            connection.Open();
            string tableToDelete = "List";   //table name
            bool tableExists = false;
            DataTable dt = connection.GetSchema("tables");
            foreach (DataRow row in dt.Rows)
            {
                if (row["TABLE_NAME"].ToString() == tableToDelete)
                {
                    tableExists = true;
                    break;
                }
                else
                {
                }
            }
            if (tableExists)
            {
                using (SqlCommand cmd2 = new  SqlCommand(string.Format("DROP TABLE {0}", tableToDelete), connection))
                {
                    cmd2.ExecuteNonQuery();
                }
            }
            else
            {
            }
            {
 SqlCommand cmd1 = new SqlCommand("CREATE TABLE [dbo].[List]("
                            + "[IDNumbers] INT IDENTITY (1, 1) NOT NULL,"
                            + "[Numbers] DECIMAL (18, 8) NULL,"
                            + "CONSTRAINT [PK_List] PRIMARY KEY CLUSTERED ([IDNumbers] ASC))", connection);
                    {
                        cmd1.ExecuteNonQuery();
                        AppendTextBox("Full Clear Database Table created.");
                        connection.Close();
                }
            }
        }
    }

Answer 1

如果我不误解您的问题您的代码将以这种方式工作

private DataTable GetEmployeesList()
    {
        DataTable dtEmployees = new DataTable();

        using (SqlConnection con = new SqlConnection("Data Source=yourconnectin;Initial Catalog=dbtest;Integrated Security=True"))
        {
            cn.Open();
            string createTable = "IF OBJECT_ID('List') IS  NULL Begin " +
               "CREATE TABLE [dbo].[List]("+
                      " [IDNumbers] INT IDENTITY(1, 1) NOT NULL,"+
                        "[Numbers] DECIMAL(18, 8) NULL," +
                        "  CONSTRAINT[PK_List] PRIMARY KEY CLUSTERED([IDNumbers] ASC))" +
                 "END";
            using (SqlCommand cmddd = new SqlCommand(createTable, cn))
            {
                cmddd.ExecuteNonQuery();
            }
            using (SqlCommand cmd = new SqlCommand("SELECT * FROM List", cn))// List1 for Testing...
            {
                SqlDataReader reader = cmd.ExecuteReader();
                dtEmployees.Load(reader);
            }
        }
        return dtEmployees;
    }