C# 查询表和字段是否存在,不存在则新建

猿掌柜
2022-06-01 / 0 评论 / 10 阅读 / 正在检测是否收录...

自动检测服务器上的数据库是否存在某表和某字段,
如果表不存在,先新建表,再在表下查询某字段,
如果不存在,则新建字段。

private void Database_Operation(ComboBox ICChip)
        {
            try
            {
                if (PubVar.SQL_Connection)//如果数据库能打开
                {
                    using (SqlConnection conn = new SqlConnection(PubVar.connStr))//创建连接对象,并使用using释放(关闭),连接用完后会被自动关闭
                    {
                        //if (conn.State == ConnectionState.Open) conn.Close();//如果打开则先关闭
                        conn.Open(); // 打开数据库连接

                        string sql = "select 1 from INFORMATION_SCHEMA.TABLES where TABLE_NAME='" + ICChip.Text.Trim() + "'";
                        SqlCommand command = new SqlCommand(sql, conn);//调用公共类中的ExceRead方法创建数据阅读器
                        var Read = command.ExecuteScalar();

                        if (Read == null)//加个判断,没有则创建表
                        {
                            sql = "create table [" + ICChip.Text.Trim() + "]([NO.] [int] identity(1,1))";//([NO.] [int] identity(1,1))";//创建表 //identity(1,1)自增ID
                            using (SqlConnection Conn = new SqlConnection(PubVar.connStr))
                            {
                                Conn.Open();
                                command = new SqlCommand(sql, Conn);
                                command.ExecuteNonQuery();
                            }

                        }
                        //判断字段是否存在
                        sql = "SELECT *FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '" + ICChip.Text.Trim() + "' AND COLUMN_NAME = '新字段'";
                        using (SqlConnection Conn = new SqlConnection(PubVar.connStr))
                        {
                            Conn.Open();
                            command = new SqlCommand(sql, Conn);
                            Read = command.ExecuteScalar();
                        }

                        if (Read == null)//如果没有字段则创建
                        {
                            sql = "ALTER TABLE [" + ICChip.Text.Trim() + "] ADD [新字段] [text] null";//id int primary key IDENTITY(1,1) NOT NULL //创建字段
                            using (SqlConnection Conn = new SqlConnection(PubVar.connStr))
                            {
                                Conn.Open();
                                command = new SqlCommand(sql, Conn);
                                command.ExecuteNonQuery();
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("错误信息:" + ex.Message, "创建数据库失败!");
            }
        }
3

评论

博主关闭了当前页面的评论