MXQ_SQL_数据库辅助工具

V1.0 版本

功能

1.快速设置数据字段说明

2.导出数据库文档 WORD文件

3.导出数据库字段说明SQL语句 文本文件

问题

打开导出WORD发现第二页的第一个表格 会和第一页末尾的表格叠加在一起,目前的办法不是最优的,不知道谁有好的建议,此问题将在下次更新时解决

V1.1 版本

增加可视化链接数据配置

快速生成 创建 添加 删除 修改 查询SQL语句

代码分享

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Microsoft.Win32;
using System.Threading;
using Microsoft.Office.Interop.Word;
using System.Xml;
using System.Reflection;
using System.IO;



namespace MXQ_SQLServer_Manager
{
    public partial class fm_mxq_sql_manager : Form
    {
        private string DataBasesName = "Master";//数据库
        private string TableName;               //数据表
        private string TableColumnName;         //数据列名
        private StringBuilder sqlStr;
        private bool connStringState = false;

        public fm_mxq_sql_manager()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            while (!connStringState)
            {
                try
                {
                    connStringState = true;
                    BeginInvoke((MethodInvoker)delegate()
                    {
                        Bind_DataBasesNameList();
                        Bind_DataTableNameList();
                    });
                }
                catch
                {
                    connStringState = false;
                    tmLoadEvent.Start();
                }
            }
        }

        //绑定数据库名称列表
        private void Bind_DataBasesNameList()
        {
            try
            {

                cboDataBaseName.Text = "";
                cboDataBaseName.Items.Clear();
                sqlStr = new StringBuilder();
                sqlStr.Append(" USE [Master] ");
                sqlStr.Append(" SELECT DBID,NAME,CRDATE,FILENAME ");
                sqlStr.Append(" FROM SYSDATABASES WHERE name NOT IN('master','tempdb','model','msdb','ReportServer','ReportServerTempDB')  ");
                sqlStr.Append(" ORDER BY NAME ");
                DataSet dataBasesLists = SqlHelper.ExecuteDataTable(SqlHelper.ConnectionStringLocalTransaction
                    , CommandType.Text, sqlStr.ToString(), null);
                foreach (DataRow dt_dr in dataBasesLists.Tables[0].Rows)
                {
                    cboDataBaseName.Items.Add(dt_dr["NAME"]);
                }
                DataBasesName = cboDataBaseName.Items[0].ToString();
            }
            catch
            {
                return;
            }
        }

        //绑定数据表名称列表
        private void Bind_DataTableNameList()
        {

            try
            {
                DataBasesName = cboDataBaseName.SelectedItem != null ? cboDataBaseName.SelectedItem.ToString()
                    : cboDataBaseName.Items[0].ToString();
                cboTableNames.Text = "";
                cboTableNames.Items.Clear();
                sqlStr = new StringBuilder();
                sqlStr.Append(" USE [" + DataBasesName + "] ");
                sqlStr.Append(" SELECT object_id,name,create_date,modify_date,type,type_desc  ");
                sqlStr.Append(" FROM sys.tables ");
                sqlStr.Append(" ORDER BY name ");

                DataSet dataTableLists = SqlHelper.ExecuteDataTable(SqlHelper.ConnectionStringLocalTransaction
                    , CommandType.Text, sqlStr.ToString(), null);
                foreach (DataRow dt_dr in dataTableLists.Tables[0].Rows)
                {
                    cboTableNames.Items.Add(dt_dr["name"]);
                }
                TableName = cboTableNames.Items[0].ToString();
            }
            catch
            {
                return;
                // throw;
            }
        }

        //绑定数据表结构信息
        private void Bind_TableInfo()
        {
            dgvTableInfo.DataSource = null;
            TableName = cboTableNames.SelectedItem != null ? cboTableNames.SelectedItem.ToString() : cboTableNames.Items[0].ToString();
            dgvTableInfo.DataSource = GetDataTableInfo(TableName);
        }

        private DataTable GetDataTableInfo(string dataTableName)
        {

            sqlStr = new StringBuilder();
            sqlStr.Append(" USE [" + DataBasesName + "] ");
            sqlStr.Append(" declare @objname nvarchar(776) = NULL, @objid int , @sysobj_type char(2), @dbname    sysname,@no varchar(35), ");
            sqlStr.Append("         @yes varchar(35), @none varchar(35), @precscaletypes nvarchar(150),@colname sysname ");
            sqlStr.Append(" set @objname='" + dataTableName + "' ");
            sqlStr.Append(" select @objid = object_id, @sysobj_type = type from sys.all_objects where object_id = object_id(@objname) ");
            sqlStr.Append(" select @no = '', @yes = '√', @none = 'none' ");
            sqlStr.Append(" select @precscaletypes =  ");
            sqlStr.Append(" N'tinyint,smallint,decimal,int,real,money,float,numeric,smallmoney,date,time,datetime2,datetimeoffset,' ");
            sqlStr.Append(" select ");
            sqlStr.Append(" '序号'                =column_id, ");
            sqlStr.Append(" '列名'                = name, ");
            sqlStr.Append(" '类型'                    = type_name(user_type_id), ");
            sqlStr.Append(" '长度'                    = convert(int, max_length), ");
            sqlStr.Append(" '精度'                    = case when charindex(type_name(system_type_id) + ',', @precscaletypes) > 0 ");
            sqlStr.Append(" then convert(char(5),ColumnProperty(object_id, name, 'precision')) ");
            sqlStr.Append(" else '     ' end, ");
            sqlStr.Append(" '小数'                    = case when charindex(type_name(system_type_id) + ',', @precscaletypes) > 0 ");
            sqlStr.Append(" then convert(char(5),OdbcScale(system_type_id,scale)) ");
            sqlStr.Append(" else '     ' end, ");
            sqlStr.Append(" '说明'=ISNULL((SELECT TOP 1 PFD.value FROM  sys.extended_properties PFD WHERE PFD.class=1  ");
            sqlStr.Append("  AND PFD.major_id=@objid  AND PFD.minor_id=column_id),N'') , ");
            sqlStr.Append(" '允许NULL值'                = case when is_nullable = 0 then @no else @yes end, ");
            sqlStr.Append(" '标识列'=case when (select col_name(@objid, column_id) ");
            sqlStr.Append("  from sys.identity_columns where object_id=@objid)=name then @yes else @no end  ");
            sqlStr.Append(" from sys.all_columns AS AC where object_id =@objid ");
            DataSet dataTableInfo = SqlHelper.ExecuteDataTable(SqlHelper.ConnectionStringLocalTransaction
                , CommandType.Text, sqlStr.ToString(), null);
            return dataTableInfo.Tables[0];
        }

        //数据库选中项改变
        private void cboDataBaseName_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (cboDataBaseName.SelectedIndex != -1)
            {
                Bind_DataTableNameList();
            }
            dgvTableInfo.DataSource = null;
        }

        //数据表选中项改变
        private void cboTableNames_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (cboTableNames.SelectedIndex != -1)
            {
                Bind_TableInfo();
            }
        }

        //添加或修改数据表字段说明
        private void addDataColumnDescription()
        {
            try
            {
                sqlStr = new StringBuilder();
                sqlStr.Append(" USE [" + DataBasesName + "] ");
                sqlStr.Append(" EXEC sys.sp_addextendedproperty @name=N'MS_Description',  ");
                sqlStr.Append(" @value=@DataColumnDescription, @level0type=N'SCHEMA', ");
                sqlStr.Append(" @level0name=N'dbo',@level1type=N'TABLE',@level1name=N'" + TableName + "', ");
                sqlStr.Append(" @level2type=N'COLUMN',@level2name=N'" + TableColumnName + "' ");
                SqlParameter[] sp = new SqlParameter[] {
                    new  SqlParameter("@DataColumnDescription",txtDataColumnDescription.Text.Trim()),
                };
                SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, sqlStr.ToString(), sp);
            }
            catch
            {
                sqlStr = new StringBuilder();
                sqlStr.Append(" USE [" + DataBasesName + "] ");
                sqlStr.Append(" DECLARE @v sql_variant  ");
                sqlStr.Append(" SET @v=@DataColumnDescription ");
                sqlStr.Append(" EXECUTE sp_updateextendedproperty N'MS_Description', @v, N'SCHEMA', ");
                sqlStr.Append(" N'dbo', N'TABLE', N'" + TableName + "', N'COLUMN', N'" + TableColumnName + "' ");
                SqlParameter[] sp = new SqlParameter[] {
                    new  SqlParameter("@DataColumnDescription",txtDataColumnDescription.Text.Trim()),
                };
                SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, sqlStr.ToString(), sp);
                //throw;
            }
            Bind_TableInfo();
        }

        //dgv改变选中单元格
        private void dgvTableInfo_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            if (e.RowIndex != -1 && e.ColumnIndex != -1)
            {
                txtDataColumnDescription.Text = dgvTableInfo.Rows[e.RowIndex].Cells["说明"].Value.ToString();
            }
        }

        //文本框失去焦点
        private void txtDataColumnDescription_Leave(object sender, EventArgs e)
        {
            addDataColumnDescription();
        }

        private void dgvTableInfo_RowEnter(object sender, DataGridViewCellEventArgs e)
        {
            if (e.RowIndex != -1 && e.ColumnIndex != -1)
            {
                TableColumnName = dgvTableInfo.Rows[e.RowIndex].Cells["列名"].Value.ToString();
                txtDataColumnDescription.Text = dgvTableInfo.Rows[e.RowIndex].Cells["说明"].Value.ToString();
            }
        }

        private void btn_isTop_Click(object sender, EventArgs e)
        {
            if (this.btn_isTop.Text == "曱")
            {
                this.TopMost = false;
                btn_isTop.Text = "甴";
            }
            else
            {
                this.TopMost = true;
                btn_isTop.Text = "曱";
            }
        }

        private void tsmi_ShowMain_Click(object sender, EventArgs e)
        {
            this.TopMost = true;
            this.WindowState = System.Windows.Forms.FormWindowState.Normal;
            this.TopMost = false;
        }

        private void tsmi_exit_Click(object sender, EventArgs e)
        {
            System.Windows.Forms.Application.Exit();
        }

        private void tsmi_start_Click(object sender, EventArgs e)
        {
            SetAutoRun("MXQ_SQLServer_Manager", "\"" + System.Windows.Forms.Application.StartupPath + "\\MXQ_SQLServer_Manager.exe\" /start");
        }

        #region 开机自动启动
        /// <summary>
        ///开机自动启动
        /// </summary>
        /// <param name="keyName">注册表key.</param>
        /// <param name="filePath">软件路径</param>
        /// <returns>返回：Boolean 类型</returns>
        public static bool SetAutoRun(string ShortFileName, string filePath)
        {
            try
            {
                //打开子键节点
                RegistryKey MyReg = Registry.LocalMachine.OpenSubKey(@"SOFTWARE\\Microsoft\\Windows\\CurrentVersion\\Run", true);
                if (MyReg == null)
                {//如果子键节点不存在，则创建之
                    MyReg = Registry.LocalMachine.CreateSubKey(@"SOFTWARE\\Microsoft\\Windows\\CurrentVersion\\Run");
                }
                //在注册表中设置自启动程序
                MyReg.SetValue(ShortFileName, filePath);
                MyReg.Close();
            }
            catch
            {
                return false;
            }
            return true;
        }
    #endregion

        private void btnImportOut_Click(object sender, EventArgs e)
        {

            #region 导出数据库说明到Word
            if (rdoDataBaseDescription.Checked)
            {
                string[] tableColumnNames = new string[] { "序号", "列名", "类型", "长度" , "精度" ,
                                                        "小数", "说明", "允许NULL值", "标识列" };
                string fileName = "数据库 " + DataBasesName + DateTime.Now.ToString("yyyy年MM月dd日hhmms");
                SaveFileDialog sfd = new SaveFileDialog();
                sfd.AddExtension = true;
                sfd.RestoreDirectory = true;
                sfd.FileName = fileName;
                sfd.DefaultExt = ".doc";
                sfd.Filter = "word文档|*.doc|所有文件|*.*";
                sfd.Title = "导出数据库说明文档";
                if (sfd.ShowDialog() == DialogResult.OK)
                {
                    foreach (System.Diagnostics.Process p in System.Diagnostics.Process.GetProcessesByName("WINWORD"))
                    {
                        p.Kill();
                    }
                    string filePatch = sfd.FileName;
                    //创建Word文档
                    Object Nothing = Missing.Value;
                    Microsoft.Office.Interop.Word.Application WordApp = new Microsoft.Office.Interop.Word.Application();
                    Microsoft.Office.Interop.Word.Document WordDoc = WordApp.Documents.Add(ref Nothing, ref Nothing, ref Nothing, ref Nothing);
                    WordApp.Selection.ParagraphFormat.LineSpacing = 3f;//设置文档的行间距
                    //文档中创建表格
                    foreach (var objItem in cboTableNames.Items)
                    {
                        DataTable dt = GetDataTableInfo(objItem.ToString());
                        object count = 20;
                        object WdLine = WdUnits.wdLine;//换一行;
                        WordApp.Selection.MoveDown(ref WdLine, ref count, ref Nothing);//移动焦点
                        WordApp.Selection.TypeParagraph();//插入段落
                        Microsoft.Office.Interop.Word.Table newTable = WordDoc.Tables.Add(WordApp.Selection.Range, dt.Rows.Count + 1, 9, ref Nothing, ref Nothing);
                        //设置表格样式
                        WordDoc.Content.Font.Size = 10;
                        newTable.Borders.OutsideLineStyle = WdLineStyle.wdLineStyleDouble;
                        newTable.Borders.InsideLineStyle = WdLineStyle.wdLineStyleSingle;
                        newTable.Columns[1].Width = 40f;
                        newTable.Columns[2].Width = 100f;
                        newTable.Columns[3].Width = 60f;
                        newTable.Columns[4].Width = 40f;
                        newTable.Columns[5].Width = 40f;
                        newTable.Columns[6].Width = 40f;
                        newTable.Columns[7].Width = 80f;
                        newTable.Columns[8].Width = 40f;
                        newTable.Columns[9].Width = 40f;

                        for (int i = 0; i < dt.Rows.Count; i++)
                        {
                            for (int j = 0; j < 9; j++)
                            {
                                if (i < 3)
                                {
                                    newTable.Cell(1, 1).Range.Text = "表名：" + objItem.ToString();
                                    newTable.Cell(2, j + 1).Range.Text = tableColumnNames[j];
                                }
                                newTable.Cell(i + 3, j + 1).Range.Text = dt.Rows[i][j].ToString();
                            }
                        }
                        newTable.Cell(1, 1).Merge(newTable.Cell(1, 9));

                        try
                        {
                            //文件保存
                            WordDoc.SaveAs(filePatch);
                            MessageBox.Show("创建成功! " + filePatch);
                        }
                        catch (Exception ex)
                        {
                            MessageBox.Show("异常错误" + ex.Message.ToString());
                        }
                        finally
                        {
                            WordDoc.Close();
                            WordApp.Quit();
                        }

                    }
                }
            }
            #endregion

            #region 导出数据库字段说明SQL语句
            if (rdoDataBasesDescriptionAdd.Checked)
            {
                string fileName = "数据库 " + DataBasesName + "说明SQL语句" + DateTime.Now.ToString("yyyy年MM月dd日hhmmss");
                SaveFileDialog sfd = new SaveFileDialog();
                sfd.AddExtension = true;
                sfd.RestoreDirectory = true;
                sfd.FileName = fileName;
                sfd.DefaultExt = ".txt";
                sfd.Filter = "文本文档|*.txt|所有文件|*.*";
                sfd.Title = "导出数据库说明SQL语句";
                if (sfd.ShowDialog() == DialogResult.OK)
                {
                    try
                    {
                        if (Directory.Exists(sfd.FileName))
                        {
                            Directory.CreateDirectory(sfd.FileName);
                        }
                        StringBuilder dtsql = new StringBuilder();
                        foreach (var objItem in cboTableNames.Items)
                        {
                            DataTable dtSql = GetDataTableInfo(objItem.ToString());
                            foreach (DataRow dt_dr in dtSql.Rows)
                            {
                                if (dt_dr["说明"].ToString() != "")
                                {
                                    dtsql.Append(" USE [" + DataBasesName + "] ");
                                    dtsql.Append(" EXEC sys.sp_addextendedproperty @name=N'MS_Description',  ");
                                    dtsql.Append(" @value=N'" + dt_dr["说明"] + "', @level0type=N'SCHEMA', ");
                                    dtsql.Append(" @level0name=N'dbo',@level1type=N'TABLE',@level1name=N'" + objItem.ToString() + "', ");
                                    dtsql.Append(" @level2type=N'COLUMN',@level2name=N'" + dt_dr["列名"] + "' ");
                                    dtsql.Append(" \r\n ");
                                }
                            }
                        }
                        using (StreamWriter sw = new StreamWriter(sfd.FileName, false))
                        {
                            sw.Write(dtsql);
                            sw.Flush();
                            sw.Close();
                        }
                        MessageBox.Show("创建成功! " + sfd.FileName);
                    }
                    catch (Exception)
                    {
                        
                        throw;
                    }
                }
            }
            #endregion
        }
    }
}