/// <summary>执行 /// 执行 /// </summary> /// <param name="sender">sender</param> /// <param name="e">e</param> private void btnRun_Click(object sender, EventArgs e) { if (CheckInput()) { return; } if (CheckDesignTable() == false) { return; } DataTable dtSource = (grdTable.DataSource as DataTable); if (dtSource == null) { return; } DataTable dtChanges = dtSource.GetChanges(); if (dtChanges == null) { return; } try { if (IsNew) { //第一次执行生成表名 string strSqlAddTable = AddTableScript(m_databaseSchema).Replace("GO", ""); m_dbHelper.CreateCommand(strSqlAddTable + " ; SELECT 'OK'"); DataTable dtResult = m_dbHelper.ExecuteQuery(); if (dtResult != null && dtResult.Rows.Count != 1) { MessageBox.Show("新建失败"); return; } else { m_sda.Update(dtChanges); dtSource.AcceptChanges(); m_strTableName = txtTableName.Text.Trim(); //切换成编辑模式 IsNew = false; DatabaseTable databaseTable = m_databaseSchema.Tables.Where(t => t.Name == m_strTableName).FirstOrDefault(); TreeNode tableRoot = TreeViewHelper.FindTableNode(GlobalHelp.TreeView.Nodes, m_strServer, m_strDbName, "表"); if (tableRoot != null) { //及时刷新TreeView SchemaToTreeview.FillOneTable(databaseTable, tableRoot, 2); } MessageBox.Show("新建成功!"); } } } catch (Exception) { throw; } }
private void grdTable_CellEndEdit(object sender, DataGridViewCellEventArgs e) { if (grdTable.Rows[e.RowIndex].Cells[colColumnName.Name].Value == null) { return; } if (IsNew) { return; } DataTable dtSource = (grdTable.DataSource as DataTable); if (dtSource == null) { return; } DataTable dtChanges = dtSource.GetChanges(); if (dtChanges == null) { return; } if (CheckDesignTable() == false) { return; } try { StringBuilder sb = new StringBuilder(); bool blnFlag = false; foreach (DataRow dr in dtChanges.Rows) { #region 修改列 if (dr.RowState == DataRowState.Modified) { DatabaseColumn originalColumn = grdTable.Rows[e.RowIndex].Tag as DatabaseColumn; if (originalColumn != null) { DatabaseColumn databaseColumn = GenDatabaseColumn(grdTable.Rows[e.RowIndex], originalColumn.Table); //修改类名 if (databaseColumn.Name != originalColumn.Name) { sb.AppendLine(m_sqlTasks.RenameColumn(originalColumn.Table, databaseColumn, originalColumn)); } if (databaseColumn.DbDataType.ToLower() != originalColumn.DbDataType.ToLower()) { //修改类型、默认值等 sb.AppendLine(m_sqlTasks.AlterColumn(originalColumn.Table, databaseColumn, originalColumn)); } //修改主键 if (originalColumn.IsPrimaryKey != databaseColumn.IsPrimaryKey) { if (originalColumn.IsPrimaryKey) { //删除主键 sb.AppendLine(" ALTER TABLE " + databaseColumn.Table.Name + " DROP CONSTRAINT " + databaseColumn.Table.Name + " ;"); } else { sb.AppendLine(" ALTER TABLE " + databaseColumn.Table.Name + " ALTER COLUMN " + databaseColumn.Name + " " + databaseColumn.DbDataType + " NOT NULL ;"); sb.AppendLine(" ALTER TABLE " + databaseColumn.Table.Name + " ADD CONSTRAINT PK_" + databaseColumn.Table.Name + " PRIMARY KEY(" + databaseColumn.Name + ") ;"); } } //修改自增长 if (originalColumn.IsAutoNumber != databaseColumn.IsAutoNumber) { if (originalColumn.IsAutoNumber) { //删除自增长 sb.AppendLine(" ALTER TABLE " + databaseColumn.Table.Name + " ADD " + databaseColumn.Name + "1" + databaseColumn.DbDataType); sb.AppendLine(" UPDATE " + databaseColumn.Table.Name + " SET " + databaseColumn.Name + "1= " + databaseColumn.Name); sb.AppendLine(" ALTER TABLE " + databaseColumn.Table.Name + " DROP COLUMN " + databaseColumn.Name); sb.AppendLine(" EXEC sp_rename '" + databaseColumn.Name + "1','" + databaseColumn.Name + "', 'COLUMN'"); } else { sb.AppendLine(" ALTER TABLE " + databaseColumn.Table.Name + " DROP COLUMN " + databaseColumn.Name); sb.AppendLine(" ALTER TABLE " + databaseColumn.Table.Name + " ADD " + databaseColumn.Name + " " + databaseColumn.DbDataType + " IDENTITY(1,1) NOT NULL"); } } if (sb.ToString() != string.Empty) { string strSql = sb.AppendLine(" ; SELECT 'OK'").ToString(); blnFlag = RunSql(DataRowState.Modified, strSql, dtSource, grdTable.Rows[e.RowIndex], databaseColumn); if (blnFlag) { grdTable.Rows[e.RowIndex].Tag = databaseColumn; originalColumn.Table.Columns.Add(databaseColumn); DatabaseTable databaseTable = m_databaseSchema.Tables.Where(t => t.Name == m_strTableName).FirstOrDefault(); TreeNode tableRoot = TreeViewHelper.FindTableNode(GlobalHelp.TreeView.Nodes, m_strServer, m_strDbName, "表"); if (tableRoot != null && tableRoot.Nodes.Count > 0) { TreeNode treeNode = TreeViewHelper.FindNodeByName(tableRoot.Nodes, m_strTableName); if (treeNode != null) { TreeNode nodeColumn = TreeViewHelper.FindColumnNodeByName(treeNode.Nodes, originalColumn.Name); int intIndex = nodeColumn.Index; originalColumn.Table.Columns.Remove(originalColumn); SchemaToTreeview.FillColumn(treeNode, databaseColumn, 3, intIndex); treeNode.Nodes.Remove(nodeColumn); } } AddLog(dr, "修改列"); } //重新设置Tag ResetTag(databaseColumn.Table); } } } #endregion 修改列 #region 新增列 else if (dr.RowState == DataRowState.Added) { DatabaseTable dt = m_databaseSchema.FindTableByName(m_strTableName); DatabaseColumn databaseColumn = GenDatabaseColumn(grdTable.Rows[e.RowIndex], dt); var migration = new DdlGeneratorFactory(m_dbType).MigrationGenerator(); string strSql = migration.AddColumn(dt, databaseColumn); strSql += " ; SELECT 'OK'"; #region 获取要插入的列位置 //int intIndex = 0; //DatabaseTable databaseTable = m_databaseSchema.Tables.Where(t => t.Name == m_strTableName).FirstOrDefault(); //TreeNode tableRoot = TreeViewHelper.FindTableNode(GlobalHelp.TreeView.Nodes, m_strServer, m_strDbName, "表"); //if (tableRoot != null && tableRoot.Nodes.Count > 0) //{ // TreeNode treeNode = TreeViewHelper.FindNodeByName(tableRoot.Nodes, m_strTableName); // if (treeNode != null) // { // if (e.RowIndex > 0) // { // DatabaseColumn preColumn = grdTable.Rows[e.RowIndex - 1].Tag as DatabaseColumn; // if (preColumn != null) // { // string strNodeName = SchemaToTreeview.GetColumnName(preColumn); // TreeNode nodeColumn = TreeViewHelper.FindNodeByName(treeNode.Nodes, strNodeName); // intIndex = nodeColumn.Index; // } // } // } //} #endregion 获取要插入的列位置 blnFlag = RunSql(DataRowState.Added, strSql, dtSource, grdTable.Rows[e.RowIndex], databaseColumn); if (blnFlag) { databaseColumn.Table.Columns.Add(databaseColumn); TreeNode tableRoot = TreeViewHelper.FindTableNode(GlobalHelp.TreeView.Nodes, m_strServer, m_strDbName, "表"); TreeNode treeNode = TreeViewHelper.FindNodeByName(tableRoot.Nodes, m_strTableName); SchemaToTreeview.FillColumn(treeNode, databaseColumn, 3, e.RowIndex); AddLog(dr, "新增列"); //重新设置Tag ResetTag(databaseColumn.Table); } } #endregion 新增列 } } catch (Exception) { throw; } }