static DatabaseSettingViewModel()
{
if (DatabaseModel == null)
{
DatabaseModel = new DatabaseSettingModel();
}
}
public Stream Export(DatabaseSettingModel setting, IEnumerable <TableInfoWithColumnsModel> tableInfos)
{
var sb = new StringBuilder();
sb.AppendLine($@"
<!DOCTYPE html>
<html lang=""en"">
<head>
<meta charset=""UTF-8"">
<meta name=""viewport"" content = ""width=device-width, initial-scale=1.0"">
<meta http-equiv=""X-UA-Compatible"" content = ""ie=edge"">
<title>{setting.DatabaseName} Table Schema</title>
</head>
<body>");
var isFirst = true;
foreach (var tableInfo in tableInfos)
{
if (isFirst)
{
isFirst = false;
}
else
{
sb.Append("</table>");
}
var comment = string.IsNullOrWhiteSpace(tableInfo.Comment) ? string.Empty : $"<h4>{tableInfo.Comment}</h4>";
sb.AppendLine($"<h1>{tableInfo.Name}</h1>{comment}");
sb.AppendLine(@"<table border=""1"" width=""100%"">");
sb.AppendLine("<tr><th>Column Name</th><th>Data Type</th><th>Default Value</th><th>Allow NULL</th><th>Comment</th></tr>");
foreach (var columnInfo in tableInfo.Columns)
{
sb.AppendLine($"<tr><td>{columnInfo.Name}</td><td>{columnInfo.Type}</td><td>{columnInfo.DefaultValue}</td><td>{columnInfo.IsCanNull}</td><td>{columnInfo.Comment}</td></tr>");
}
}
sb.Append("</table></body></html>");
var byteArray = Encoding.UTF8.GetBytes(sb.ToString());
return(new MemoryStream(byteArray));
}
static void Main(string[] args)
{
try
{
var argList = ReadArgs(args);
if (argList == null)
{
return;
}
var dbType = GetSwitchInput(argList, "-t", "Database Type:\n1. SQL Server\n2. MySQL\n3. PostgreSQL", new Dictionary <string, string> {
{ "1", _msSql }, { "2", _mySql }, { "3", _postgreSql }
});
var connectionString = GetInput(argList, "-c", "ConnectionString:", optional: true);
var skip = !string.IsNullOrWhiteSpace(connectionString);
var setting = new DatabaseSettingModel
{
ConnectionString = connectionString,
DbType = dbType,
HostString = GetInput(argList, "-s", "Server name or IP address:", skip: skip),
DatabaseName = GetInput(argList, "-d", "Database name:", skip: skip && dbType == _msSql),
UserName = GetInput(argList, "-u", "Username:"******"-p", "Password:"******"Can't get any table info");
}
IExporter exporter = new HtmlExporter();
var stream = exporter.Export(setting, tableInfos);
stream.Seek(0, SeekOrigin.Begin);
using (var fileStream = new FileStream($"{setting.DatabaseName}.html", FileMode.Create))
{
stream.CopyTo(fileStream);
}
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
System.Threading.Thread.Sleep(5000);
}
}
public IEnumerable <TableInfoWithColumnsModel> GetTableInfos(DatabaseSettingModel settingModel)
{
var resultTable = new DataTable();
NpgsqlConnection connection = null;
try
{
connection = new NpgsqlConnection(
settingModel.HasConnectionString ? settingModel.ConnectionString :
$"Host={settingModel.Host};Port={settingModel.Port};Database={settingModel.DatabaseName};Username={settingModel.UserName};Password={settingModel.Password};");
connection.Open();
#region SqlCommandString
var sqlCommandString = @"SELECT obj_description(g.OID) as table_comment, col.table_catalog, col.table_schema,col.table_name, col.column_name, col.column_default, col.is_nullable,
CASE WHEN col.data_type = 'USER-DEFINED' THEN col.udt_name ELSE col.data_type END,
col.character_maximum_length, g.description
FROM information_schema.columns AS col
LEFT JOIN (SELECT c.OID, c.relname AS table_name, a.attname As column_name, d.description
FROM pg_class As c
INNER JOIN pg_attribute As a ON c.oid = a.attrelid
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace
LEFT JOIN pg_description As d ON (d.objoid = c.oid AND d.objsubid = a.attnum)
WHERE c.relkind IN('r', 'v') AND n.nspname = 'public'
ORDER BY n.nspname, c.relname, a.attname) AS g ON col.table_name = g.table_name AND col.column_name = g.column_name
WHERE table_schema NOT IN ('information_schema' , 'pg_catalog' ,'topology')
AND table_catalog = @dbname
AND col.table_name NOT IN ('raster_overviews','raster_columns','spatial_ref_sys')
ORDER BY col.table_schema ASC, col.table_name ASC, col.ordinal_position ASC";
#endregion
var command = connection.CreateCommand();
command.CommandTimeout = 120;
command.CommandType = System.Data.CommandType.Text;
command.CommandText = sqlCommandString;
command.Parameters.AddWithValue("@dbname", settingModel.DatabaseName);
var adapter = new NpgsqlDataAdapter(command);
adapter.Fill(resultTable);
}
finally
{
if (connection != null)
{
connection.Close();
connection.Dispose();
}
}
var result = new List <TableInfoWithColumnsModel>();
TableInfoModel tableModel = null;
var columnInfos = new List <ColumnInfoModel>();
foreach (DataRow row in resultTable.Rows)
{
var schema = Convert.ToString(row["table_schema"]);
var table = schema == "public" ? Convert.ToString(row["table_name"]) : $"{schema}.{Convert.ToString(row["table_name"])}";
var tableComment = Convert.ToString(row["table_comment"]);
var column = Convert.ToString(row["column_name"]);
var dataType = Convert.ToString(row["data_type"]);
var lengthX = row["character_maximum_length"] == DBNull.Value ? 0 : Convert.ToInt32(row["character_maximum_length"]);
var length = lengthX == -1 ? "(MAX)" : lengthX == 0 ? "" : $"({lengthX})";
var defaultValue = Convert.ToString(row["column_default"]);
var isNull = Convert.ToString(row["is_nullable"]) != "NO";
var description = Convert.ToString(row["description"]);
if (tableModel == null || tableModel.Name != table)
{
if (tableModel != null)
{
result.Add(new TableInfoWithColumnsModel(tableModel, columnInfos));
}
tableModel = new TableInfoModel {
Name = table, Comment = tableComment
};
columnInfos = new List <ColumnInfoModel>();
}
columnInfos.Add(new ColumnInfoModel
{
Name = column,
Type = $"{dataType}{length}",
DefaultValue = defaultValue,
IsCanNull = isNull,
Comment = description,
});
}
result.Add(new TableInfoWithColumnsModel(tableModel, columnInfos));
return(result);
}
public IEnumerable <TableInfoWithColumnsModel> GetTableInfos(DatabaseSettingModel settingModel)
{
var resultTable = new DataTable();
SqlConnection connection = null;
try
{
connection = new SqlConnection(
settingModel.HasConnectionString ? settingModel.ConnectionString :
$"Data Source={settingModel.Host},{settingModel.Port};Initial Catalog={settingModel.DatabaseName};Persist Security Info=True;User ID={settingModel.UserName};Password={settingModel.Password}");
connection.Open();
#region SqlCommandString
var sqlCommandString = @"
SELECT a.Table_schema +'.'+a.Table_name as [Table]
,b.COLUMN_NAME as [Column]
,b.DATA_TYPE as [DataType]
,isnull(b.CHARACTER_MAXIMUM_LENGTH,'') as [Length]
,isnull(b.COLUMN_DEFAULT,'') as [DefaultValue]
,b.IS_NULLABLE as [IsNull]
,( SELECT value
FROM fn_listextendedproperty (NULL, 'schema', a.Table_schema, 'table', a.TABLE_NAME, 'column', default)
WHERE name='MS_Description' and objtype='COLUMN'
and objname Collate Chinese_Taiwan_Stroke_CI_AS = b.COLUMN_NAME
) as [Description]
FROM INFORMATION_SCHEMA.TABLES a
LEFT JOIN INFORMATION_SCHEMA.COLUMNS b ON a.TABLE_NAME = b.TABLE_NAME
WHERE TABLE_TYPE='BASE TABLE'
ORDER BY a.TABLE_NAME , b.ORDINAL_POSITION
";
#endregion
var command = connection.CreateCommand();
command.CommandTimeout = 120;
command.CommandType = System.Data.CommandType.Text;
command.CommandText = sqlCommandString;
var adapter = new SqlDataAdapter(command);
adapter.Fill(resultTable);
}
finally
{
if (connection != null)
{
connection.Close();
connection.Dispose();
}
}
var result = new List <TableInfoWithColumnsModel>();
TableInfoModel tableModel = null;
var columnInfos = new List <ColumnInfoModel>();
foreach (DataRow row in resultTable.Rows)
{
var table = Convert.ToString(row["Table"]);
var column = Convert.ToString(row["Column"]);
var dataType = Convert.ToString(row["DataType"]);
var lengthX = Convert.ToInt32(row["Length"]);
var length = lengthX == -1 ? "(MAX)" : lengthX == 0 ? "" : $"({lengthX})";
var defaultValue = Convert.ToString(row["DefaultValue"]);
var isNull = Convert.ToString(row["IsNull"]) != "NO";
var description = Convert.ToString(row["Description"]);
if ("dbo.sysdiagrams" == table)
{
continue;
}
if (tableModel == null || tableModel.Name != table)
{
if (tableModel != null)
{
result.Add(new TableInfoWithColumnsModel(tableModel, columnInfos));
}
tableModel = new TableInfoModel {
Name = table
};
columnInfos = new List <ColumnInfoModel>();
}
columnInfos.Add(new ColumnInfoModel
{
Name = column,
Type = $"{dataType}{length}",
DefaultValue = defaultValue,
IsCanNull = isNull,
Comment = description,
});
}
result.Add(new TableInfoWithColumnsModel(tableModel, columnInfos));
return(result);
}
public IEnumerable <TableInfoWithColumnsModel> GetTableInfos(DatabaseSettingModel settingModel)
{
var resultTable = new DataTable();
MySqlConnection connection = null;
try
{
connection = new MySqlConnection(
settingModel.HasConnectionString ? settingModel.ConnectionString :
$"server={settingModel.Host};port={settingModel.Port};database={settingModel.DatabaseName};user id={settingModel.UserName};password={settingModel.Password};charset=utf8;");
connection.Open();
#region SqlCommandString
var sqlCommandString = @"
SELECT
TABLE_NAME AS ""Table"",
COLUMN_NAME AS ""Column"",
DATA_TYPE AS ""DataType"",
CHARACTER_MAXIMUM_LENGTH AS ""Length"",
COLUMN_DEFAULT AS ""DefaultValue"",
IS_NULLABLE AS ""IsNull"",
COLUMN_COMMENT AS ""Description""
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA like @dbname
ORDER BY TABLE_NAME , ORDINAL_POSITION
";
#endregion
var command = connection.CreateCommand();
command.CommandTimeout = 120;
command.CommandType = System.Data.CommandType.Text;
command.CommandText = sqlCommandString;
command.Parameters.AddWithValue("@dbname", settingModel.DatabaseName);
var adapter = new MySqlDataAdapter(command);
adapter.Fill(resultTable);
}
finally
{
if (connection != null)
{
connection.Close();
connection.Dispose();
}
}
var result = new List <TableInfoWithColumnsModel>();
TableInfoModel tableModel = null;
var columnInfos = new List <ColumnInfoModel>();
foreach (DataRow row in resultTable.Rows)
{
var table = Convert.ToString(row["Table"]);
var column = Convert.ToString(row["Column"]);
var dataType = Convert.ToString(row["DataType"]);
var lengthX = Convert.ToString(row["Length"]);
var length = string.IsNullOrWhiteSpace(lengthX) ? string.Empty : $"({lengthX})";
var defaultValue = Convert.ToString(row["DefaultValue"]);
var isNull = Convert.ToString(row["IsNull"]) != "NO";
var description = Convert.ToString(row["Description"]);
if (tableModel == null || tableModel.Name != table)
{
if (tableModel != null)
{
result.Add(new TableInfoWithColumnsModel(tableModel, columnInfos));
}
tableModel = new TableInfoModel {
Name = table
};
columnInfos = new List <ColumnInfoModel>();
}
columnInfos.Add(new ColumnInfoModel
{
Name = column,
Type = $"{dataType}{length}",
DefaultValue = defaultValue,
IsCanNull = isNull,
Comment = description,
});
}
result.Add(new TableInfoWithColumnsModel(tableModel, columnInfos));
return(result);
}
