public void Export2File(string connection, string sql, string dbType, string excelType, string fileName)
{
var ds = new DataSet();
JlDatabase.Fill(HttpUtility.UrlDecode(connection), HttpUtility.UrlDecode(sql), ds, null, (JlDatabaseType)Enum.Parse(typeof(JlDatabaseType), dbType));
var wbType = JlWorkbookType.HSSF;
if (excelType == "xlsx")
{
wbType = JlWorkbookType.XSSF;
}
else if (excelType == "xls" && ds.Tables[0].Rows.Count > 65535)
{
throw new Exception("xls格式限制单sheet数据不超过65535行");
}
var wb = JlExcel.ExportToWorkbook(ds, wbType);
System.Web.HttpContext.Current.Response.Clear();
System.Web.HttpContext.Current.Response.AddHeader("content-disposition", "attachment; filename=" + (string.IsNullOrEmpty(fileName) ? DateTime.Now.ToString("yyyyMMddHHmmss") : fileName) + "." + excelType);
System.Web.HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
wb.Write(System.Web.HttpContext.Current.Response.OutputStream);
//ep.SaveAs(Response.OutputStream); 第二种方式
System.Web.HttpContext.Current.Response.Flush();
System.Web.HttpContext.Current.Response.End();
}
public ActionResult GetDatas(string connection, string sql, string dbType)
{
sql = HttpUtility.UrlDecode(sql);
connection = HttpUtility.UrlDecode(connection);
if (sql.Contains("delete ") || sql.Contains("update ") || sql.Contains("drop "))
{
return(new JlJsonResult()
{
Content = JlJson.ToJson(new { Message = "涉及敏感关键字" })
});
}
var ds = new DataSet();
try
{
JlDatabase.Fill(connection, sql, ds, null, (JlDatabaseType)Enum.Parse(typeof(JlDatabaseType), dbType));
}
catch (Exception ex)
{
return(new JlJsonResult()
{
Content = JlJson.ToJson(new { Message = ex.Message })
});
}
var result = CommonMethod.ConvertDataTableToList(ds.Tables[0], true);
return(new JlJsonResult()
{
Content = JlJson.ToJson(result)
});
}
private static List <string> GetDatabaseTables_SqlServer(string connectionString)
{
var sql = @"SELECT name AS Name FROM SYSOBJECTS WHERE XTYPE IN ('V','U') AND NAME<>'DTPROPERTIES' ORDER BY Name ASC";
var dataTable = new DataTable();
JlDatabase.Fill(connectionString, sql, dataTable);
return(dataTable.AsEnumerable().Select(row => row["Name"].ToString()).ToList());
}
private static List <string> GetDatabaseTables_MySql(string connectionString)
{
var dbName = connectionString.Split(new[] { "database=" }, StringSplitOptions.RemoveEmptyEntries)[1].Split(';')[0];
var sql = @"SELECT TABLE_NAME Name FROM information_schema.TABLES WHERE TABLE_SCHEMA = '" + dbName + "' AND TABLE_TYPE = 'BASE TABLE'";
var dataTable = new DataTable();
JlDatabase.Fill(connectionString, sql, dataTable, databaseType: JlDatabaseType.MySql);
return(dataTable.AsEnumerable().Select(row => row["Name"].ToString()).ToList());
}
private static Dictionary <string, string> GetDatabaseTableDic_PostgreSql(string connectionString)
{
var result = new Dictionary <string, string>();
var sql = @"select relname as Name,cast(obj_description(relfilenode,'pg_class') as varchar) as comment from pg_class c
where relkind = 'r' and relname not like 'pg_%' and relname not like 'sql_%' order by relname";
var ds = new DataSet();
JlDatabase.Fill(connectionString, sql, ds, null, JlDatabaseType.PostgreSql);
ds.Tables[0].AsEnumerable().ForEach(row =>
{
result.Add(row["Name"].ToString(), row["comment"].ToString());
});
return(result);
}
private static List <JlFieldDescription> GetDatabaseColumns_PostgreSql(string connectionString, string tableName)
{
var dbName = connectionString.Split(new[] { "database=" }, StringSplitOptions.RemoveEmptyEntries)[1].Split(';')[0];
var sql = @"
select ordinal_position as Colorder,column_name as Name,data_type as DbType,
coalesce(character_maximum_length,numeric_precision,-1) as Length,numeric_scale as Scale,
case is_nullable when 'NO' then 0 else 1 end as IsNullable,column_default as DefaultVal,
case when position('nextval' in column_default)>0 then 1 else 0 end as IsIdentity,
case when b.pk_name is null then 0 else 1 end as IsPK,c.DeText Description
from information_schema.columns
left join (
select pg_attr.attname as colname,pg_constraint.conname as pk_name from pg_constraint
inner join pg_class on pg_constraint.conrelid = pg_class.oid
inner join pg_attribute pg_attr on pg_attr.attrelid = pg_class.oid and pg_attr.attnum = pg_constraint.conkey[1]
inner join pg_type on pg_type.oid = pg_attr.atttypid
where pg_class.relname = '{0}' and pg_constraint.contype='p'
) b on b.colname = information_schema.columns.column_name
left join (
select attname,description as DeText from pg_class
left join pg_attribute pg_attr on pg_attr.attrelid= pg_class.oid
left join pg_description pg_desc on pg_desc.objoid = pg_attr.attrelid and pg_desc.objsubid=pg_attr.attnum
where pg_attr.attnum>0 and pg_attr.attrelid=pg_class.oid and pg_class.relname='{0}'
)c on c.attname = information_schema.columns.column_name
where table_schema='public' and table_name='{0}' order by ispk desc,isidentity desc,ordinal_position asc";
sql = string.Format(sql, tableName);
var ds = new DataSet();
JlDatabase.Fill(connectionString, sql, ds, null, JlDatabaseType.PostgreSql);
return(ds.Tables[0].AsEnumerable().Select(row => new JlFieldDescription()
{
Name = row["Name"].ToString(),
DbType = row["DbType"].ToString(),
Length = JlConvert.TryToInt(row["Length"]),
IsNullable = JlConvert.TryToBool(row["IsNullable"].ToString() == "1"),
IsIdentity = JlConvert.TryToBool(row["IsIdentity"].ToString() == "1"),
ColumnKey = row["IsPK"].ToString() == "1" ? "PRI" : "",
Description = HttpUtility.HtmlEncode(row["Description"].ToString())
}).ToList());
}
private static List <JlFieldDescription> GetDatabaseColumns_MySql(string connectionString, string tableName)
{
var dbName = connectionString.Split(new[] { "database=" }, StringSplitOptions.RemoveEmptyEntries)[1].Split(';')[0];
var sql = @"
SELECT COLUMN_NAME Name, COLUMN_COMMENT Description, DATA_TYPE DbType, IS_NULLABLE IsNullable, CHARACTER_MAXIMUM_LENGTH Length, Extra, COLUMN_KEY
FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = '" + dbName + "' AND TABLE_NAME like '{0}'";
sql = string.Format(sql, tableName);
var dataTable = new DataTable();
JlDatabase.Fill(connectionString, sql, dataTable, databaseType: JlDatabaseType.MySql);
return(dataTable.AsEnumerable().Select(row => new JlFieldDescription()
{
Name = JlString.ReplaceUnderline(row["Name"].ToString()),
DbType = row["DbType"].ToString(),
Length = JlConvert.TryToInt(row["Length"]),
IsNullable = JlConvert.TryToBool(row["IsNullable"].ToString().ToLower() == "yes"),
IsIdentity = JlConvert.TryToBool(row["Extra"].ToString().Contains("auto_increment")),
ColumnKey = row["COLUMN_KEY"].ToString(),
Description = HttpUtility.HtmlEncode(row["Description"].ToString())
}).ToList());
}
private static List <JlFieldDescription> GetDatabaseColumns_SqlServer(string connectionString, string tableName)
{
var sql = @"
SELECT
Name=C.name,
DbType=T.name,
PrimaryKey=ISNULL(IDX.PrimaryKey,N''),
IsIdentity=CASE WHEN C.is_identity=1 THEN N'true'ELSE N'false' END,
Length=C.max_length,
IsNullable=CASE WHEN C.is_nullable=1 THEN N'true'ELSE N'false' END,
Description=ISNULL(PFD.[value],N'')
FROM sys.columns C
INNER JOIN sys.objects O
ON C.[object_id]=O.[object_id]
AND O.type='U'
AND O.is_ms_shipped=0
INNER JOIN sys.types T
ON C.user_type_id=T.user_type_id
LEFT JOIN sys.default_constraints D
ON C.[object_id]=D.parent_object_id
AND C.column_id=D.parent_column_id
AND C.default_object_id=D.[object_id]
LEFT JOIN sys.extended_properties PFD
ON PFD.class=1
AND C.[object_id]=PFD.major_id
AND C.column_id=PFD.minor_id
-- AND PFD.name='Caption' -- 字段说明对应的描述名称(一个字段可以添加多个不同name的描述)
LEFT JOIN sys.extended_properties PTB
ON PTB.class=1
AND PTB.minor_id=0
AND C.[object_id]=PTB.major_id
-- AND PFD.name='Caption' -- 表说明对应的描述名称(一个表可以添加多个不同name的描述)
LEFT JOIN -- 索引及主键信息
(
SELECT
IDXC.[object_id],
IDXC.column_id,
Sort=CASE INDEXKEY_PROPERTY(IDXC.[object_id],IDXC.index_id,IDXC.index_column_id,'IsDescending')
WHEN 1 THEN 'DESC' WHEN 0 THEN 'ASC' ELSE '' END,
PrimaryKey=CASE WHEN IDX.is_primary_key=1 THEN N'PRI'ELSE N'' END,
IndexName=IDX.Name
FROM sys.indexes IDX
INNER JOIN sys.index_columns IDXC
ON IDX.[object_id]=IDXC.[object_id]
AND IDX.index_id=IDXC.index_id
LEFT JOIN sys.key_constraints KC
ON IDX.[object_id]=KC.[parent_object_id]
AND IDX.index_id=KC.unique_index_id
INNER JOIN -- 对于一个列包含多个索引的情况,只显示第1个索引信息
(
SELECT [object_id], Column_id, index_id=MIN(index_id)
FROM sys.index_columns
GROUP BY [object_id], Column_id
) IDXCUQ
ON IDXC.[object_id]=IDXCUQ.[object_id]
AND IDXC.Column_id=IDXCUQ.Column_id
AND IDXC.index_id=IDXCUQ.index_id
) IDX
ON C.[object_id]=IDX.[object_id]
AND C.column_id=IDX.column_id
where O.name = '{0}'
order by c.column_id";
sql = string.Format(sql, tableName);
var dataTable = new DataTable();
JlDatabase.Fill(connectionString, sql, dataTable);
return(dataTable.AsEnumerable().Select(row => new JlFieldDescription()
{
Name = JlString.ReplaceUnderline(row["Name"].ToString()),
DbType = row["DbType"].ToString(),
Length = JlConvert.TryToInt(row["Length"]),
IsNullable = JlConvert.TryToBool(row["IsNullable"].ToString()),
IsIdentity = JlConvert.TryToBool(row["IsIdentity"].ToString()),
Description = HttpUtility.HtmlEncode(row["Description"].ToString()),
ColumnKey = row["PrimaryKey"].ToString()
}).ToList());
}