将SqlServer表转换为Markdown

Program.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Text;

namespace BuildMd
{
    class Program
    {
        static void Main(string[] args)
        {
            string pathBase = AppDomain.CurrentDomain.BaseDirectory;

            string mdPath = pathBase;
            File.Delete(mdPath + "test.md");
            DataTable tables = ExecuteDataTable("select * from INFORMATION_SCHEMA.TABLES");
            foreach (DataRow tableName in tables.Rows)
            {
                string tablename = (string)tableName["TABLE_NAME"];
                //if (tablename.StartsWith("WX_"))
                //{
                List<string> Rows = new List<string>();
                Rows.Add("### " + tablename + "(*快来修改我*)");
                Rows.Add("");
                Rows.Add("| 列名           | 字段                       | 数据类型           | PK   | NULL  | DEFAULT      | 描述                                                                               |");
                Rows.Add("|----------------|----------------------------|--------------------|------|-------|--------------|------------------------------------------------------------------------------------|");
                DataTable tableColumns = ExecuteDataTable("select * from INFORMATION_SCHEMA.COLUMNS where [e-mail]",
            new SqlParameter("tablename", tablename));
                foreach (DataRow column in tableColumns.Rows)
                {
                    string column_name = (string)column["COLUMN_NAME"];
                    string data_type = (string)column["DATA_TYPE"];
                    string character_maximum_length = column["CHARACTER_MAXIMUM_LENGTH"].ToString();
                    string column_default = column["COLUMN_DEFAULT"].ToString();
                    string is_nullable = column["IS_NULLABLE"].ToString();

                    MDTableRow mdtablerow = new MDTableRow();
                    mdtablerow.column_name = column_name;
                    mdtablerow.data_type = data_type;
                    mdtablerow.character_maximum_length = character_maximum_length;
                    mdtablerow.column_default = column_default;
                    mdtablerow.is_nullable = is_nullable;

                    Rows.Add(WriteTableRow(mdtablerow));
                }
                //File.Delete(mdPath + "test.md");
                File.AppendAllLines(mdPath + string.Format("{0}.md", tableName["TABLE_CATALOG"]), Rows);
                Console.WriteLine(tablename + " 生成完成");
                //}
            }
            Console.WriteLine();
            Console.WriteLine("生成结束！");
            Console.ReadKey();
        }

        public static string WriteTableRow(MDTableRow mdtablerow)
        {
            string datatype = mdtablerow.character_maximum_length == "" || mdtablerow.character_maximum_length == "2147483647"
                ? "`" + mdtablerow.data_type.ToUpper() + "`" : "`" + mdtablerow.data_type.ToUpper() + "(" + mdtablerow.character_maximum_length + ")`";

            string data_tp = "`" + System.Threading.Thread.CurrentThread.CurrentCulture.TextInfo.ToTitleCase(SqlDataTypeToNetDataType(mdtablerow.data_type)) + "`";

            string str = string.Format("|{0}|{1}|{2}|{3}|{4}|{5}|{6}|"
                , Pad("", "----------------".Length, false)
                , Pad(mdtablerow.column_name, "----------------------------".Length)
                , Pad(mdtablerow.character_maximum_length == "-1" ? "`" + mdtablerow.data_type.ToUpper() + "(MAX)`" : datatype, "--------------------".Length)
                , Pad(mdtablerow.column_name == "ID" ? "PK" : "", "------".Length)
                , Pad(mdtablerow.is_nullable == "NO" ? "" : "NULL", "-------".Length)
                , Pad(mdtablerow.column_default, "--------------".Length)
                , Pad("", "------------------------------------------------------------------------------------".Length)
                );
            return str;
        }

        public static string Pad(string basestr, int padlength, bool isCN = false)
        {
            int baselength = (int)(basestr.Length * (isCN ? 2 : 1));
            int length = padlength - baselength;
            return basestr.PadLeft(2).PadRight(padlength - (isCN ? basestr.Length : 0));
        }


        public static DataTable ExecuteDataTable(string cmdText, params SqlParameter[] parameters)
        {
            using (SqlConnection conn = new SqlConnection(@"server=WZY-PC;database=HibernateBlog;uid=sa;pwd=xxxxxx;"))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = cmdText;
                    cmd.Parameters.AddRange(parameters);
                    DataTable dt = new DataTable();
                    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                    adapter.Fill(dt);
                    return dt;
                }
            }
        }

        private static string SqlDataTypeToNetDataType(string datatype)
        {
            switch (datatype)
            {
                case "int":
                    return "int";

                case "nvarchar":
                case "varchar":
                case "nchar":
                case "char":
                    return "string";

                case "bit":
                    return "bool";

                case "datetime":
                case "datetime2":
                    return "DateTime";

                case "decimal":
                    return "decimal";

                default:
                    return "object";

            }
        }
    }

    class MDTableRow
    {
        public string column_name { get; set; }
        public string data_type { get; set; }
        public string character_maximum_length { get; set; }
        public string column_default { get; set; }
        public string is_nullable { get; set; }
    }
}

运行结果如图:

---

---

UsersBlogs(快来修改)

列名	字段	数据类型	PK	NULL	DEFAULT	描述
	UserId	INT
	BlogId	INT

Blogs(快来修改)

列名	字段	数据类型	PK	NULL	DEFAULT	描述
	Id	INT
	Title	NVARCHAR(255)		NULL
	Subtitle	NVARCHAR(255)		NULL
	AllowsComments	BIT		NULL
	CreatedAt	DATETIME		NULL

Categories(快来修改)

列名	字段	数据类型	PK	NULL	DEFAULT	描述
	Id	INT
	Name	NVARCHAR(255)		NULL

CategoriesPosts(快来修改)

列名	字段	数据类型	PK	NULL	DEFAULT	描述
	CategoryId	INT
	PostId	INT

Comments(快来修改)

列名	字段	数据类型	PK	NULL	DEFAULT	描述
	Id	INT
	Name	NVARCHAR(255)		NULL
	Email	NVARCHAR(255)		NULL
	HomePage	NVARCHAR(255)		NULL
	Ip	INT		NULL
	Text	NVARCHAR(255)		NULL
	PostId	INT		NULL

Posts(快来修改)

列名	字段	数据类型	PK	NULL	DEFAULT	描述
	Id	INT
	Title	NVARCHAR(255)		NULL
	Text	NVARCHAR(255)		NULL
	PostedAt	DATETIME		NULL
	BlogId	INT		NULL
	UserId	INT		NULL

Tags(快来修改)

列名	字段	数据类型	PK	NULL	DEFAULT	描述
	Id	INT
	Name	NVARCHAR(255)		NULL
	ItemId	INT		NULL
	ItemType	INT		NULL

sysdiagrams(快来修改)

列名	字段	数据类型	PK	NULL	DEFAULT	描述
	name	NVARCHAR(128)
	principal_id	INT
	diagram_id	INT
	version	INT		NULL
	definition	VARBINARY(MAX)		NULL

Users(快来修改)

列名	字段	数据类型	PK	NULL	DEFAULT	描述
	Id	INT
	Password	VARBINARY(8000)		NULL
	Username	NVARCHAR(255)		NULL
	Email	NVARCHAR(255)		NULL
	CreatedAt	DATETIME		NULL
	Bio	NVARCHAR(255)		NULL