예제 #1
0
        private void Save_Click(object sender, EventArgs e)
        {
            /*存储列名和数据类型的字典*/
            Dictionary <string, string> dicTab = new Dictionary <string, string>();
            JudgeType jt = new JudgeType();

            string[]      s     = new string[this.dataGridView1.RowCount];
            List <string> pkey  = new List <string>();
            int           count = 0;

            /*计算主键个数*/
            foreach (DataGridViewRow item in this.dataGridView1.Rows)
            {
                if ((bool)item.Cells[4].EditedFormattedValue)
                {
                    count++;
                    pkey.Add(item.Cells[0].Value.ToString());
                }
            }

            /*判断创建表的约束条件:表名列名不为空且符合命名规则,列名不能重复,列名的数据类型和用户输入的默认值必须匹配
             * 当选中char和varchar选项时可以编辑长度,但是长度不为空且必须是整数 ,表名不能和数据库中已经存在的表名重复
             */
            if (text_box_tablename.Text.ToString() == "")
            {
                MessageBox.Show("The table name can't be empty");
                return;
            }

            foreach (DataGridViewRow item in this.dataGridView1.Rows)
            {
                string svalue;
                if (item.Cells[0].Value == null)
                {
                    MessageBox.Show("The column name can't be empty");
                    return;
                }
                else
                {
                    if (item.Cells[1].Value == null)
                    {
                        MessageBox.Show("The datatype can't be empty");
                        return;
                    }
                    if (item.Cells[1].Value.ToString() == "character varying[]" || item.Cells[1].Value.ToString() == "char")
                    {
                        if (item.Cells[2].Value == null)
                        {
                            MessageBox.Show("The length can't be empty");
                            return;
                        }
                        if (!jt.isNoSignInt(item.Cells[2].Value.ToString()) || int.Parse(item.Cells[2].Value.ToString()) == 0)
                        {
                            MessageBox.Show("Length must be a positive integer");
                            return;
                        }
                        svalue = "VarChar(" + item.Cells[2].Value.ToString() + ")";
                    }
                    else
                    {
                        svalue = item.Cells[1].Value.ToString();
                    }
                    if ((bool)item.Cells[3].EditedFormattedValue)
                    {
                        svalue += " not null";
                    }
                    if ((bool)item.Cells[4].EditedFormattedValue && count == 1)
                    {
                        svalue += " primary key";
                    }
                    if (item.Cells[5].Value != null)
                    {
                        int length = 0;
                        if (item.Cells[2].Value.ToString() != "")
                        {
                            length = int.Parse(item.Cells[2].Value.ToString());
                        }
                        if (!jt.judgeType(item.Cells[1].Value.ToString(), item.Cells[5].Value.ToString(), length))
                        {
                            MessageBox.Show("The data type and the default doesn't match");
                            return;
                        }
                        if (jt.isChinese(item.Cells[5].Value.ToString()) || jt.IsDate(item.Cells[5].Value.ToString()) || item.Cells[1].Value.ToString() == "character varying[]" || item.Cells[1].Value.ToString() == "char")
                        {
                            svalue += " default('" + item.Cells[5].Value.ToString() + "')";
                        }
                        else
                        {
                            svalue += " default(" + item.Cells[5].Value.ToString() + ")";
                        }
                    }

                    try
                    {
                        dicTab.Add(item.Cells[0].Value.ToString(), svalue);
                    }
                    catch (System.ArgumentException)
                    {
                        MessageBox.Show("Column name cannot be repeated");
                        return;
                    }
                }
            }



            /*连接数据库,在数据库中创建表*/
            var con = new NpgsqlConnection(MainForm.connString + ";DataBase = " + dbname);

            try
            {
                con.Open();
            }
            catch (Npgsql.PostgresException)
            {
                MessageBox.Show("请选中正确的节点后,再进行创建表操作");
                return;
            }

            /*判断表是否已经存在*/
            var cmd = new NpgsqlCommand("select tablename from pg_tables where schemaname='public'", con);
            NpgsqlDataAdapter da = new NpgsqlDataAdapter(cmd);
            DataTable         dt = new DataTable();

            da.Fill(dt);
            for (int k = 0; k < dt.Rows.Count; k++)
            {
                if (dt.Rows[k]["tablename"].ToString() == text_box_tablename.Text)
                {
                    MessageBox.Show("The table " + text_box_tablename.Text + " has been created");
                    return;
                }
            }
            string cmdstring;

            if (this.dataGridView1.Rows.Count == 0)
            {/*创建没有列的空表*/
                cmdstring = "CREATE TABLE " + text_box_tablename.Text.ToString() + "();";
                var cmdtab = new NpgsqlCommand(cmdstring, con);
                try
                {
                    cmdtab.ExecuteNonQuery();
                }
                catch (Npgsql.NpgsqlException ee)
                {
                    MessageBox.Show("The table cann't be created\n" + ee.Message);
                    return;
                }
                catch (Exception) {
                    MessageBox.Show("The name of table doesn't follow the standard variable naming rule");
                    return;
                }

                var tabnd = new TreeNode();
                tabnd.Text               = text_box_tablename.Text.ToString();
                tabnd.ImageIndex         = 5;
                tabnd.SelectedImageIndex = 5;
                tabnd.ContextMenuStrip   = f.contextMenuStriptab2;
                t.Nodes.Add(tabnd);
            }
            else
            {
                cmdstring = "CREATE TABLE " + text_box_tablename.Text.ToString() + "(";
                int           i;
                List <string> keys = new List <string>(dicTab.Keys);
                for (i = 0; i < keys.Count - 1; i++)
                {
                    cmdstring += keys[i] + " " + dicTab[keys[i]] + ",";
                }
                cmdstring += keys[i] + " " + dicTab[keys[i]];

                if (count > 1)
                {
                    cmdstring += ",primary key (";
                    for (i = 0; i < pkey.Count - 1; i++)
                    {
                        cmdstring += pkey[i] + ",";
                    }

                    cmdstring += pkey[i] + "));";
                }
                else
                {
                    cmdstring += ");";
                }

                var cmdtab = new NpgsqlCommand(cmdstring, con);
                try
                {
                    cmdtab.ExecuteNonQuery();
                }
                catch (Npgsql.PostgresException)
                {
                    MessageBox.Show("The name of the column or table doesn't follow the standard variable naming rule");
                    return;
                }

                /*add Treenode*/
                /*add table node*/
                var tabnd = new TreeNode();
                tabnd.Text               = text_box_tablename.Text.ToString();
                tabnd.ImageIndex         = 5;
                tabnd.SelectedImageIndex = 5;
                tabnd.ContextMenuStrip   = f.contextMenuStriptab2;
                tabnd.NodeFont           = new Font("华文新魏", 9, FontStyle.Bold);
                t.Nodes.Add(tabnd);
                /*add column node*/
                //TreeNode tnd = f.Find(f.treeView1, text_box_tablename.Text.ToString());
                foreach (string key in dicTab.Keys)
                {
                    var cond = new TreeNode();
                    cond.Text               = key;
                    cond.ImageIndex         = 6;
                    cond.SelectedImageIndex = 6;
                    cond.ContextMenuStrip   = f.contextMenuStripco;
                    tabnd.Nodes.Add(cond);
                }
            }


            f.Refresh();
            this.Close();
        }
예제 #2
0
        private void save_Click_1(object sender, EventArgs e)
        {
            /*判断创建列的约束条件:列名不为空且符合命名规则,列名不能在表中已经存在,列名的数据类型和用户输入的默认值必须匹配
             * 当选中char和varchar选项时可以编辑长度,但是长度不为空且必须是整数
             */
            JudgeType jt = new JudgeType();

            if (textBox_name.Text.ToString() == "")
            {
                MessageBox.Show("Column name cann't be empty");
                return;
            }
            if (comboBox_datatype.SelectedItem == null)
            {
                MessageBox.Show("Column datatype cann't be empty");
                return;
            }
            if (comboBox_datatype.SelectedItem.ToString() == "character varying[]" || comboBox_datatype.SelectedItem.ToString() == "char")
            {
                if (textBox_length.Text.ToString() == "")
                {
                    MessageBox.Show("The length of character varying[] cann't be empty");
                    return;
                }
                else if (!jt.isNoSignInt(textBox_length.Text.ToString()) || int.Parse(textBox_length.Text) == 0)
                {
                    MessageBox.Show("Length must be a positive integer");
                    return;
                }
            }

            var cmd = new NpgsqlCommand("select column_name from information_schema.columns where table_schema='public' and table_name= '" + selecttn.Text + "'", con);
            NpgsqlDataAdapter da = new NpgsqlDataAdapter(cmd);
            DataTable         dt = new DataTable();

            try
            {
                da.Fill(dt);
            }
            catch (Npgsql.NpgsqlException)
            {
                MessageBox.Show("请选中正确的节点后,再进行创建列操作");
                return;
            }

            for (int k = 0; k < dt.Rows.Count; k++)
            {
                if (dt.Rows[k]["column_name"].ToString() == textBox_name.Text)
                {
                    MessageBox.Show("The column " + textBox_name.Text + " has been created");
                    return;
                }
            }

            string s;

            if (comboBox_datatype.SelectedItem.ToString() == "character varying[]")
            {
                s = "VarChar(" + textBox_length.Text.ToString() + ")";
            }
            else if (comboBox_datatype.SelectedItem.ToString() == "char")
            {
                s = "VarChar(" + textBox_length.Text.ToString() + ")";
            }
            else
            {
                s = comboBox_datatype.SelectedItem.ToString();
            }
            if (checkBox_notnull.Checked)
            {
                s += " not null";
            }
            //default
            if (textBox_default.Text != "")
            {
                int length = 0;
                if (jt.isChinese(textBox_default.Text) || jt.IsDate(textBox_default.Text))
                {
                    s += " default('" + textBox_default.Text + "')";
                }
                else
                {
                    s += " default(" + textBox_default.Text + ")";
                }
                if (!(textBox_length.Text.ToString() == ""))
                {
                    length = int.Parse(textBox_length.Text);
                }
                if (!judgeType(comboBox_datatype.Text, textBox_default.Text, length))
                {
                    MessageBox.Show("The data type and the default doesn't match");
                    return;
                }
            }

            var cmdcreate = new NpgsqlCommand("ALTER TABLE " + selecttn.Text.ToString() + " ADD " + textBox_name.Text + " " + s + ";", con);

            try
            {
                cmdcreate.ExecuteNonQuery();
            }
            catch (Npgsql.PostgresException)
            {
                MessageBox.Show("The name of the column doesn't follow the standard variable naming rule");
                return;
            }
            /*add con node*/
            var cond = new TreeNode();

            cond.Text               = textBox_name.Text;
            cond.ImageIndex         = 6;
            cond.SelectedImageIndex = 6;
            cond.ContextMenuStrip   = f.contextMenuStripco;
            cond.NodeFont           = new Font("华文新魏", 9, FontStyle.Bold);
            f.treeView1.SelectedNode.Nodes.Add(cond);
            f.Refresh();
            this.Close();
        }