form1.cs

数据库字典的导出到EXL！数据库字典的导出到EXL！

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Drawing;
using System.Text;
using System.Windows.Forms;

namespace DataDictionnaryAutoGenerator
{
    public partial class Form_Dictionary : Form
    {
        public Form_Dictionary()
        {
            InitializeComponent();
        }

        #region 相关 sql 语句
        //获取数据库中表的信息（表名，列名，数据类型，默认值等）
        private string sqlstr(string UOrV)
        {
            return string.Format(@"SELECT Sysobjects.name AS TABLE_NAME, syscolumns.Id, syscolumns.name AS COLUMN_NAME,
systypes.name AS DATA_TYPE, syscolumns.length as CHARACTER_MAXIMUM_LENGTH, 
sys.extended_properties.[value] AS COLUMN_DESCRIPTION,  syscomments.text as
COLUMN_DEFAULT,syscolumns.isnullable as IS_NULLABLE FROM syscolumns
INNER JOIN systypes
    ON syscolumns.xtype = systypes.xtype 
    LEFT JOIN sysobjects ON syscolumns.id = sysobjects.id 
   LEFT OUTER JOIN sys.extended_properties ON 
   ( sys.extended_properties.minor_id = syscolumns.colid
     AND sys.extended_properties.major_id = syscolumns.id) 
   LEFT OUTER JOIN syscomments ON syscolumns.cdefault = syscomments.id 
   WHERE syscolumns.id IN  
    (SELECT id FROM SYSOBJECTS WHERE xtype = '{0}') AND (systypes.name <> 'sysname')
    ORDER BY Sysobjects.Name,syscolumns.colid",UOrV);
        }

        //创建excel中的表
        private static string excelcreatstr = "create table 数据字典(表名 varchar(100),列名 varchar(100),字段类型 varchar(20),默认值 varchar(20),说明 varchar(200))";
       
        //插入excle 表
        private string excelinsertstr(string Table_Name, string Column_Name, string Column_Type, string Column_DefaultValue, string Description)
        {
            return string.Format(@"insert into 数据字典(表名,列名,字段类型,默认值,说明) values('{0}','{1}','{2}','{3}','{4}')",Table_Name,Column_Name,Column_Type,Column_DefaultValue,Description);
        }

        #endregion

        #region 选择存放文件夹
        private void bt_ChooseForlder_Click(object sender, EventArgs e)
        {
            if (this.folderBrowserDialog1.ShowDialog() == DialogResult.OK)
            {
                txt_Forlder.Text = folderBrowserDialog1.SelectedPath;
            }
        }
       #endregion

        #region 导出按钮事件
        private void bt_GennerateExcel_Click(object sender, EventArgs e)
        {
            try
            {
                SqlConnection conn_sql = new SqlConnection(txt_Connection.Text.Trim());
                conn_sql.Open();
                SqlCommand comm_sql = new SqlCommand();
                string t = "U";
                if (radioButton_view.Checked)
                {
                    t = "V";
                }
                comm_sql.CommandText = sqlstr(t);
                comm_sql.Connection = conn_sql;
                SqlDataAdapter da = new SqlDataAdapter(comm_sql);
                DataTable dt = new DataTable();
                da.Fill(dt);
                comm_sql.Dispose();
                conn_sql.Dispose();
                conn_sql.Close();

                string conn_excelstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + txt_Forlder.Text.Trim() + "\\" + filename() + ".xls" + "; Extended Properties=Excel 8.0;";
                OleDbConnection conn_excel = new OleDbConnection(conn_excelstr);
                conn_excel.Open();
                OleDbCommand comm_excel = new OleDbCommand();
                comm_excel.Connection = conn_excel;
                comm_excel.CommandText = excelcreatstr;
                comm_excel.ExecuteNonQuery();

                int n = dt.Rows.Count;
                string table_name = "";
                for (int i = 0; i < n; i++)
                {
                  
                    DataRow dr = dt.Rows[i];
                    
                    if (table_name != dr["TABLE_NAME"].ToString())
                    {
                        comm_excel.CommandText = excelinsertstr(dr["TABLE_NAME"].ToString(), "", "", "", "");
                        comm_excel.ExecuteNonQuery();

                    }
                   
                    comm_excel.CommandText = excelinsertstr("", dr["COLUMN_NAME"].ToString(), datatype(dr["DATA_TYPE"].ToString(), dr["CHARACTER_MAXIMUM_LENGTH"].ToString()), datadefault(dr["COLUMN_DEFAULT"].ToString(), Convert.ToBoolean(dr["IS_NULLABLE"])), dr["COLUMN_DESCRIPTION"].ToString());
                    comm_excel.ExecuteNonQuery();
                   

                    table_name = dr["TABLE_NAME"].ToString();
                }
                comm_excel.Dispose();
                conn_excel.Dispose();
                conn_excel.Close();
                lb_info.Text = "成功导出到" + txt_Forlder.Text +"\\"+ filename() + ".xls";
            }
            catch
            {
                lb_info.Text = "导出失败";
            }

        }
        #endregion

        #region 辅助方法

        //将数据类型跟长度合在一起
        private string datatype(string type,string lenth)
        {
            if (type == "nvarchar")
            {
                int t = int.Parse(lenth);
                t=t/2;
                type = string.Format(@"nvarchar({0})", t);
            }
            return type;
        }

        //默认值，为空时显示NULL,并替换掉数据里可能包含的的单引号
        private string datadefault(string defaultvalue,bool IsNull)
        {
            if(defaultvalue=="")
            {
                defaultvalue = "NULL";
                if (!IsNull)
                {
                    defaultvalue = "NOT NULL";
                }

            }
            defaultvalue=defaultvalue.Replace("'", "");
            return defaultvalue;
 
        }
        //用时间表示文件名
        public static string filename()
        {
            DateTime now;
            now = DateTime.Now;
            string filename = now.Year.ToString() + now.Month.ToString() + now.Day.ToString() + now.Hour.ToString() +
                     now.Minute.ToString() + now.Second.ToString();
            return filename;
        }
        #endregion
    }
}