/// <summary>
/// 获得sqlserver数据库的所有表结构
/// </summary>
/// <param name="postParam">请求参数体</param>
/// <returns></returns>
public static List <TableModel> Generation(PostParamModel postParam)
{
//获取连接字符串
string connectionString = SqlserverDal.GetConnectionString(postParam);
//获取连接
SqlConnection connection = SqlserverDal.GetConnect(connectionString);
//查询sql
StringBuilder sql = new StringBuilder("SELECT ");
sql.Append("a.name AS TableName,");
sql.Append("CONVERT(NVARCHAR(100),isnull(g.[value],'-')) AS TableComment ");
sql.Append("FROM sys.tables a ");
sql.Append("LEFT JOIN sys.extended_properties g ON (a.object_id = g.major_id AND g.minor_id = 0) ");
sql.Append("WHERE a.name IN (" + postParam.Tb + ")");
connection.Open();
List <TableModel> tabList = SqlserverDal.GetQueryData <TableModel>(connection, sql.ToString(), null, typeof(TableModel));
//循环赋值colList
for (int i = 0; i < tabList.Count; i++)
{
TableModel table = tabList[i];
//查询sql
StringBuilder colSql = new StringBuilder("SELECT ");
colSql.Append("a.name AS ColumnName,");
colSql.Append("COLUMNPROPERTY(a.id,a.name,'IsIdentity') AS 'Extra',");
colSql.Append("case when exists(");
colSql.Append("SELECT xtype FROM sysobjects WHERE xtype='PK' AND name IN(");
colSql.Append("SELECT name FROM sysindexes WHERE indid IN(");
colSql.Append("SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid");
colSql.Append("))) then 'true' else '' end AS 'ColumnKey',");
colSql.Append("b.name AS ColumnType,");
colSql.Append("a.isnullable AS IsNullable,");
colSql.Append("isnull(e.text,'') AS ColumnDefault,");
colSql.Append("convert(varchar,isnull(g.[value],'')) AS ColumnComment ");
colSql.Append("FROM syscolumns a ");
colSql.Append("LEFT JOIN systypes b ON a.xusertype=b.xusertype ");
colSql.Append("INNER JOIN sysobjects d ON a.id=d.id ");
colSql.Append("LEFT JOIN syscomments e ON a.cdefault=e.id ");
colSql.Append("LEFT JOIN sys.extended_properties g ON a.id=g.major_id AND a.colid=g.minor_id ");
colSql.Append("WHERE d.name=@name ");
colSql.Append("ORDER BY a.id,a.colorder");
//赋值
SqlParameter[] cmdParams = { new SqlParameter("@name", table.TableName) };
List <ColumnModel> colList = SqlserverDal.GetQueryData <ColumnModel>(connection, colSql.ToString(), cmdParams, typeof(ColumnModel));
table.Colums = colList;
}
connection.Close();
return(tabList);
}
/// <summary>
/// 获得oracle数据库的所有表结构
/// </summary>
/// <param name="postParam"></param>
/// <returns></returns>
public static List <TableModel> Generation(PostParamModel postParam)
{
//获取连接字符串
string connectionString = OracleDal.GetConnectionString(postParam);
//获取连接
OracleConnection connection = OracleDal.GetConnect(connectionString);
//查询sql
StringBuilder sql = new StringBuilder("SELECT ");
sql.Append("t.table_name AS \"TableName\",f.comments AS \"TableComment\" ");
sql.Append("FROM user_tables t ");
sql.Append("INNER JOIN user_tab_comments f ON t.table_name = f.table_name ");
sql.Append("WHERE t.table_name IN (" + postParam.Tb + ")");
OracleParameter[] tabParams = { new OracleParameter("@table_schema", postParam.Dbname) };
connection.Open();
List <TableModel> tabList = OracleDal.GetQueryData <TableModel>(connection, sql.ToString(), tabParams, typeof(TableModel));
//表的描述
StringBuilder colField = new StringBuilder();
colField.Append("col.column_name AS \"ColumnName\",");
colField.Append("col.data_type AS \"ColumnType\",");
colField.Append("col.data_default AS \"ColumnDefault\",");
colField.Append("col.nullable AS \"IsNullable\",");
colField.Append("cns.constraint_type AS \"ColumnKey\",");
colField.Append("ucc.comments AS \"ColumnComment\" ");
//表的连接
StringBuilder joinTab = new StringBuilder();
joinTab.Append("LEFT JOIN user_col_comments ucc ON ucc.table_name=col.table_name ");
joinTab.Append("AND ucc.column_name=col.column_name ");
joinTab.Append("LEFT JOIN user_cons_columns ccs ON ccs.table_name=col.table_name ");
joinTab.Append("AND ccs.column_name=col.column_name AND ccs.position=col.column_id ");
joinTab.Append("LEFT JOIN user_constraints cns ON col.table_name=cns.table_name ");
joinTab.Append("AND cns.constraint_type='P' AND ccs.constraint_name=cns.constraint_name ");
//表名
string tab = " user_tab_columns col ";
string order = " ORDER BY col.column_id ASC ";
for (int i = 0; i < tabList.Count; i++)
{
TableModel table = tabList[i];
//查询sql
string sqlCol = string.Format("SELECT {0} FROM {1} {2} WHERE col.table_name='{3}' {4}", colField.ToString(), tab, joinTab.ToString(), table.TableName, order);
List <ColumnModel> colList = OracleDal.GetQueryData <ColumnModel>(connection, sqlCol, null, typeof(ColumnModel));
table.Colums = colList;
}
connection.Close();
return(tabList);
}
/// <summary>
/// 测试pgsql数据库连接
/// </summary>
/// <param name="postParam"></param>
/// <returns></returns>
public static bool TestConnect(PostParamModel postParam)
{
//获取连接字符串
string connectionString = PostgreSqlDal.GetConnectionString(postParam);
//获取连接
NpgsqlConnection connection = PostgreSqlDal.GetConnect(connectionString);
//查询sql
string sql = "select count(1)";
connection.Open();
int count = Convert.ToInt32(PostgreSqlDal.GetSingle(connection, sql));
connection.Close();
return(count > 0);
}
/// <summary>
/// 测试sqlserver数据库连接
/// </summary>
/// <param name="postParam">请求参数体</param>
/// <returns></returns>
public static bool TestConnect(PostParamModel postParam)
{
//获取连接字符串
string connectionString = SqlserverDal.GetConnectionString(postParam);
//获取连接
SqlConnection connection = SqlserverDal.GetConnect(connectionString);
//查询sql
string sql = "select count(1)";
connection.Open();
int count = (int)SqlserverDal.GetSingle(connection, sql);
connection.Close();
return(count > 0);
}
/// <summary>
/// 测试oracle数据库连接
/// </summary>
/// <param name="postParam"></param>
/// <returns></returns>
public static bool TestConnect(PostParamModel postParam)
{
//获取连接字符串
string connectionString = OracleDal.GetConnectionString(postParam);
//获取连接
OracleConnection connection = OracleDal.GetConnect(connectionString);
//查询sql
string sql = "select count(1) from dual";
connection.Open();
int count = Convert.ToInt32(OracleDal.GetSingle(connection, sql));
connection.Close();
return(count > 0);
}
/// <summary>
/// 获得请求的参数
/// </summary>
/// <param name="request">request</param>
/// <returns></returns>
private PostParamModel GetPostParam(HttpRequest request)
{
PostParamModel param = new PostParamModel();
param.Method = request.Form.Get("method");
param.Dbms = request.Form.Get("dbms");
param.Host = request.Form.Get("host");
param.Port = request.Form.Get("port");
param.Dbname = request.Form.Get("dbname");
param.User = request.Form.Get("user");
param.Pass = request.Form.Get("pass");
param.Tb = request.Form.Get("tb");
param.Cols = request.Form.Get("cols");
param.SearchInputPlaceholder = request.Form.Get("search_input_placeholder");
return(param);
}
/// <summary>
/// 获得pgsql数据库的所有表结构
/// </summary>
/// <param name="postParam"></param>
/// <returns></returns>
public static List <TableModel> Generation(PostParamModel postParam)
{
//获取连接字符串
string connectionString = PostgreSqlDal.GetConnectionString(postParam);
//获取连接
NpgsqlConnection connection = PostgreSqlDal.GetConnect(connectionString);
//查询sql
StringBuilder sql = new StringBuilder("SELECT ");
sql.Append("tb.tablename AS \"TableName\",");
sql.Append("cast(obj_description(c.relfilenode,'pg_class') AS varchar) AS \"TableComment\" ");
sql.Append("FROM pg_tables tb ");
sql.Append("LEFT JOIN pg_class c ON tb.tablename=relname ");
sql.Append("WHERE schemaname = 'public' AND tb.tablename IN (" + postParam.Tb + ")");
connection.Open();
List <TableModel> tabList = PostgreSqlDal.GetQueryData <TableModel>(connection, sql.ToString(), null, typeof(TableModel));
for (int i = 0; i < tabList.Count; i++)
{
TableModel table = tabList[i];
//查询sql
StringBuilder colSql = new StringBuilder("SELECT DISTINCT ");
colSql.Append("a.attnum as num,");
colSql.Append("a.attname as \"ColumnName\",");
colSql.Append("format_type(a.atttypid, a.atttypmod) as \"ColumnType\",");
colSql.Append("a.attnotnull as \"IsNullable\",");
colSql.Append("com.description as \"ColumnComment\",");
colSql.Append("coalesce(i.indisprimary,false) as \"ColumnKey\",");
colSql.Append("def.adsrc as \"ColumnDefault\" ");
colSql.Append("FROM pg_attribute a ");
colSql.Append("JOIN pg_class pgc ON pgc.oid = a.attrelid ");
colSql.Append("LEFT JOIN pg_index i ON (pgc.oid = i.indrelid AND i.indkey[0] = a.attnum) ");
colSql.Append("LEFT JOIN pg_description com ON (pgc.oid = com.objoid AND a.attnum = com.objsubid) ");
colSql.Append("LEFT JOIN pg_attrdef def ON (a.attrelid = def.adrelid AND a.attnum = def.adnum) ");
colSql.Append("WHERE a.attnum > 0 AND pgc.oid = a.attrelid ");
colSql.Append("AND pg_table_is_visible(pgc.oid) ");
colSql.Append("AND NOT a.attisdropped ");
colSql.Append("AND pgc.relname = @table_name ");
colSql.Append("ORDER BY a.attnum");
//赋值
NpgsqlParameter[] cmdParams = { new NpgsqlParameter("@table_name", table.TableName) };
List <ColumnModel> colList = PostgreSqlDal.GetQueryData <ColumnModel>(connection, colSql.ToString(), cmdParams, typeof(ColumnModel));
table.Colums = colList;
}
connection.Close();
return(tabList);
}
/// <summary>
/// 获取oracle所有表名
/// </summary>
/// <param name="postParam"></param>
/// <returns></returns>
public static List <TableModel> GetTables(PostParamModel postParam)
{
//获取连接字符串
string connectionString = OracleDal.GetConnectionString(postParam);
//获取连接
OracleConnection connection = OracleDal.GetConnect(connectionString);
//查询sql
StringBuilder sql = new StringBuilder("SELECT ");
sql.Append("t.table_name AS \"TableName\",f.comments AS \"TableComment\" ");
sql.Append("FROM user_tables t ");
sql.Append("INNER JOIN user_tab_comments f ON t.table_name = f.table_name ");
connection.Open();
List <TableModel> list = OracleDal.GetQueryData <TableModel>(connection, sql.ToString(), null, typeof(TableModel));
connection.Close();
return(list);
}
/// <summary>
/// 获取mysql所有表名
/// </summary>
/// <param name="postParam"></param>
/// <returns></returns>
public static List <TableModel> GetTables(PostParamModel postParam)
{
//获取连接字符串
string connectionString = MySqlDal.GetConnectionString(postParam);
//获取连接
MySqlConnection connection = MySqlDal.GetConnect(connectionString);
//查询sql
StringBuilder sql = new StringBuilder("SELECT ");
sql.Append("table_name AS TableName,table_comment AS TableComment ");
sql.Append("FROM information_schema.tables WHERE table_schema=@table_schema ");
connection.Open();
MySqlParameter[] cmdParams = { new MySqlParameter("@table_schema", postParam.Dbname) };
List <TableModel> list = MySqlDal.GetQueryData <TableModel>(connection, sql.ToString(), cmdParams, typeof(TableModel));
connection.Close();
return(list);
}
/// <summary>
/// 获取sqlserver所有表名
/// </summary>
/// <param name="postParam">请求参数体</param>
/// <returns></returns>
public static List <TableModel> GetTables(PostParamModel postParam)
{
//获取连接字符串
string connectionString = SqlserverDal.GetConnectionString(postParam);
//获取连接
SqlConnection connection = SqlserverDal.GetConnect(connectionString);
//查询sql
StringBuilder sql = new StringBuilder("SELECT ");
sql.Append("a.name AS TableName,");
sql.Append("CONVERT(NVARCHAR(100),isnull(g.[value],'-')) AS TableComment ");
sql.Append("FROM sys.tables a ");
sql.Append("LEFT JOIN sys.extended_properties g ON (a.object_id = g.major_id AND g.minor_id = 0) ");
connection.Open();
List <TableModel> list = SqlserverDal.GetQueryData <TableModel>(connection, sql.ToString(), null, typeof(TableModel));
connection.Close();
return(list);
}
/// <summary>
/// 获取pgsql所有表名
/// </summary>
/// <param name="postParam"></param>
/// <returns></returns>
public static List <TableModel> GetTables(PostParamModel postParam)
{
//获取连接字符串
string connectionString = PostgreSqlDal.GetConnectionString(postParam);
//获取连接
NpgsqlConnection connection = PostgreSqlDal.GetConnect(connectionString);
//查询sql
StringBuilder sql = new StringBuilder("SELECT ");
sql.Append("tb.tablename AS \"TableName\",");
sql.Append("cast(obj_description(c.relfilenode,'pg_class') AS varchar) AS \"TableComment\" ");
sql.Append("FROM pg_tables tb ");
sql.Append("LEFT JOIN pg_class c ON tb.tablename=relname ");
sql.Append("WHERE schemaname = 'public'");
connection.Open();
List <TableModel> list = PostgreSqlDal.GetQueryData <TableModel>(connection, sql.ToString(), null, typeof(TableModel));
connection.Close();
return(list);
}
/// <summary>
/// 获得mysql数据库的所有表结构
/// </summary>
/// <param name="postParam"></param>
/// <returns></returns>
public static List <TableModel> Generation(PostParamModel postParam)
{
//获取连接字符串
string connectionString = MySqlDal.GetConnectionString(postParam);
//获取连接
MySqlConnection connection = MySqlDal.GetConnect(connectionString);
//查询sql
StringBuilder sql = new StringBuilder("SELECT ");
sql.Append("table_name AS TableName,table_comment AS TableComment ");
sql.Append("FROM information_schema.tables WHERE table_schema=@table_schema ");
sql.Append("AND table_name IN (" + postParam.Tb + ")");
MySqlParameter[] tabParams = { new MySqlParameter("@table_schema", postParam.Dbname) };
connection.Open();
List <TableModel> tabList = MySqlDal.GetQueryData <TableModel>(connection, sql.ToString(), tabParams, typeof(TableModel));
//循环赋值colList
StringBuilder colField = new StringBuilder();
colField.Append("column_name AS ColumnName,");
colField.Append("column_type AS ColumnType,");
colField.Append("column_default AS ColumnDefault,");
colField.Append("is_nullable AS IsNullable,");
colField.Append("extra AS Extra,");
colField.Append("column_key AS ColumnKey,");
colField.Append("column_comment AS ColumnComment");
//表名
string colTab = "information_schema.columns";
for (int i = 0; i < tabList.Count; i++)
{
TableModel table = tabList[i];
//查询sql
string colSql = string.Format("SELECT {0} FROM {1} WHERE table_name=@table_name", colField.ToString(), colTab);
//赋值
MySqlParameter[] cmdParams = { new MySqlParameter("@table_name", table.TableName) };
List <ColumnModel> colList = MySqlDal.GetQueryData <ColumnModel>(connection, colSql, cmdParams, typeof(ColumnModel));
table.Colums = colList;
}
connection.Close();
return(tabList);
}
/// <summary>
/// 获取连接字符串
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static string GetConnectionString(PostParamModel model)
{
return(string.Format("Server={0};Port={1};User Id={2};Password={3};Database={4}", model.Host, model.Port, model.User, model.Pass, model.Dbname));
}
/// <summary>
/// 获取连接字符串
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static string GetConnectionString(PostParamModel model)
{
return(string.Format("Data Source={0},{1};Initial Catalog={2};User Id={3};Password={4}", model.Host, model.Port, model.Dbname, model.User, model.Pass));
}
/// <summary>
/// 获取连接字符串
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static string GetConnectionString(PostParamModel model)
{
return(string.Format("Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST={0})(PORT={1}))(CONNECT_DATA=(SERVICE_NAME={2})));Persist Security Info=True;User ID={3};Password={4};", model.Host, model.Port, model.Dbname, model.User, model.Pass));
}
/// <summary>
/// 后台统一处理入口
/// </summary>
/// <param name="context"></param>
public void ProcessRequest(HttpContext context)
{
HttpRequest request = context.Request;
HttpResponse response = context.Response;
response.ContentEncoding = Encoding.GetEncoding("utf-8");
request.ContentEncoding = Encoding.GetEncoding("utf-8");
response.ContentType = "text/html;charset=utf-8";
try
{
//数据库类型
string dbmsMysql = "mysql";
string dbmsOracle = "oracle";
string dbmsSqlserver = "sqlserver";
string dbmsPostgresql = "postgresql";
//封装请求参数
PostParamModel postParam = GetPostParam(request);
if ("test_connect".Equals(postParam.Method))
{
//测试连接
if (dbmsMysql.Equals(postParam.Dbms))
{
//mysql
if (MySqlBll.TestConnect(postParam))
{
string json = JsonConvert.SerializeObject(JsonModel.Ok("", null));
response.Write(json);
}
}
else if (dbmsPostgresql.Equals(postParam.Dbms))
{
//pgsql
if (PostgreSqlBll.TestConnect(postParam))
{
string json = JsonConvert.SerializeObject(JsonModel.Ok("", null));
response.Write(json);
}
}
else if (dbmsSqlserver.Equals(postParam.Dbms))
{
//sqlserver
if (SqlserverBll.TestConnect(postParam))
{
string json = JsonConvert.SerializeObject(JsonModel.Ok("", null));
response.Write(json);
}
}
else if (dbmsOracle.Equals(postParam.Dbms))
{
//oracle
if (OracleBll.TestConnect(postParam))
{
string json = JsonConvert.SerializeObject(JsonModel.Ok("", null));
response.Write(json);
}
}
}
else if ("tables".Equals(postParam.Method))
{
//显示所有表
List <TableModel> dataList = null;
if (dbmsMysql.Equals(postParam.Dbms))
{
//mysql
dataList = MySqlBll.GetTables(postParam);
}
else if (dbmsPostgresql.Equals(postParam.Dbms))
{
//pgsql
dataList = PostgreSqlBll.GetTables(postParam);
}
else if (dbmsSqlserver.Equals(postParam.Dbms))
{
//sqlserver
dataList = SqlserverBll.GetTables(postParam);
}
else if (dbmsOracle.Equals(postParam.Dbms))
{
//oracle
dataList = OracleBll.GetTables(postParam);
}
string json = JsonConvert.SerializeObject(JsonModel.Ok("", dataList));
response.Write(json);
}
else if ("generation".Equals(postParam.Method))
{
//反向生成表结构
List <TableModel> dataList = null;
if (dbmsMysql.Equals(postParam.Dbms))
{
//mysql
dataList = MySqlBll.Generation(postParam);
}
else if (dbmsPostgresql.Equals(postParam.Dbms))
{
//pgsql
dataList = PostgreSqlBll.Generation(postParam);
}
else if (dbmsSqlserver.Equals(postParam.Dbms))
{
//sqlserver
dataList = SqlserverBll.Generation(postParam);
}
else if (dbmsOracle.Equals(postParam.Dbms))
{
//oracle
dataList = OracleBll.Generation(postParam);
}
string json = JsonConvert.SerializeObject(JsonModel.Ok("", dataList));
response.Write(json);
}
else if ("save_html".Equals(postParam.Method))
{
//模版路径
string templateFilePath = null;
//当前上下文
string ctx = context.Server.MapPath(request.ApplicationPath);
//数据集
List <TableModel> dataList = null;
if (dbmsMysql.Equals(postParam.Dbms))
{
//mysql
dataList = MySqlBll.Generation(postParam);
templateFilePath = string.Format("{0}/template/mysql/mysql_save.html", ctx);
}
else if (dbmsPostgresql.Equals(postParam.Dbms))
{
//pgsql
dataList = PostgreSqlBll.Generation(postParam);
templateFilePath = string.Format("{0}/template/postgresql/postgresql_save.html", ctx);
}
else if (dbmsSqlserver.Equals(postParam.Dbms))
{
//sqlserver
dataList = SqlserverBll.Generation(postParam);
templateFilePath = string.Format("{0}/template/sqlserver/sqlserver_save.html", ctx);
}
else if (dbmsOracle.Equals(postParam.Dbms))
{
//oracle
dataList = OracleBll.Generation(postParam);
templateFilePath = string.Format("{0}/template/oracle/oracle_save.html", ctx);
}
//需要替换的json结果集
string json = JsonConvert.SerializeObject(dataList);
//读取文件内容
string content = File.ReadAllText(templateFilePath);
//替换文件
content = content.Replace("#tableDataJson#", json);
content = content.Replace("#dbms#", postParam.Dbms);
content = content.Replace("#dbname#", postParam.Dbname);
content = content.Replace("#tableCols#", postParam.Cols);
content = content.Replace("#search_input_placeholder#", postParam.SearchInputPlaceholder);
//下载文件
string downFileName = string.Format("{0}_{1}_{2}.html", postParam.Dbms, postParam.Dbname, DateTime.Now.ToString("yyyy_MM_dd_HH_mm_ss"));
DownFile(downFileName, content, response);
}
}
catch (Exception ex)
{
string json = JsonConvert.SerializeObject(JsonModel.Fail(ex.Message, null));
response.Write(json);
}
}
