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