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); }