/*判断用户输入的数值是否和选中的数据类型匹配*/
public bool judgeType(string type, string content, int length)
{
JudgeType jt = new JudgeType();
bool flag = true;
switch (type)
{
case "Int":
flag = jt.isInt(content);
break;
case "character varying[]":
flag = (content.Length <= length);
break;
case "date":
flag = jt.IsDate(content);
break;
case "smallint":
flag = jt.isSmallint(content);
break;
case "char":
flag = (content.Length <= length);
break;
default:
flag = true;
break;
}
return(flag);
}
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();
}
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();
}