asp.Netデータベーステーブル構造エクスポートWord
7027 ワード
.aspx
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Untitled Page</title>
<style type="text/css">
table{border-collapse:collapse}
td{font-size: medium}
.tr1 td{ background-color:Aqua;font-size :larger; }
</style>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Button runat="server" ID="btnExport2" Text="Fubon WORD" onclick="btnExport_Click" />
<asp:Literal runat="server" ID="ltlBody"></asp:Literal>
</div>
</form>
</body>
</html>protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
ltlBody.Text = GetTableStr("FubonConnectionString");
}
}
string GetTableStr(string conStr)
{
DataBase db = null;
if (string.IsNullOrEmpty(conStr))
db = new DataBase();
else
db = new DataBase(conStr);
string sql = @"
select TableName = O.name,
OWNER = USER_NAME(O.uid),
FieldId = C.colid,
FieldName = C.name,
FieldType = T.name
+ CASE
WHEN T.name IN (N'decimal',N'numeric')
THEN N'(' + CAST(C.prec as varchar) + N',' + CAST(C.scale as varchar) + N')'
WHEN T.name = N'float' OR T.name like N'%char'
OR T.name like N'%binary'
THEN N'(' + CAST(C.prec as varchar) + N')'
ELSE N''
END,
AllowNulls =
CASE
WHEN C.isnullable = 1 THEN N''
ELSE N' NOT'
END + N' NULL',
FieldDescription = ISNULL(PFD.value, ''),
DefileLength = C.length,
FiledDefault = ISNULL(DF.text, N''),
IsIDENTITY = COLUMNPROPERTY(O.id, C.name, N'IsIdentity'),
IsComputed = COLUMNPROPERTY(O.id, C.name, N'IsComputed'),
IsROWGUID = COLUMNPROPERTY(O.id, C.name, N'IsRowGuidCol'),
IsPrimaryKey = CASE
WHEN OPK.xtype IS NULL THEN 0
ELSE 1
END
FROM sysobjects O
JOIN syscolumns C
ON C.id = O.id AND OBJECTPROPERTY(O.id, N'IsUserTable') = 1
JOIN systypes T
ON T.xusertype = C.xusertype
LEFT JOIN syscomments DF
ON DF.id = C.cdefault
LEFT JOIN sys.extended_properties PFD
ON PFD.major_id = C.id AND PFD.minor_id = C.colid
LEFT JOIN sysindexkeys IDXK
ON IDXK.id = O.id AND IDXK.colid = C.colid
LEFT JOIN sysindexes IDX
ON IDX.indid = IDXK.indid AND IDX.id =IDXK.id AND IDX.indid NOT IN(0,255)
LEFT JOIN sysobjects OPK
ON OPK.parent_obj = O.id AND OPK.name = IDX.name AND OBJECTPROPERTY(OPK.id, N'IsPrimaryKey') = 1
ORDER BY O.name, C.colid";
DataTable dt = db.GetDataSet(sql).Tables[0];
if (dt == null || dt.Rows.Count == 0) return "";
string tableName = dt.Rows[0]["TableName"].ToString();
const string tableStr = "<table border='1' width='100%'><tr><td align='center' style='background-color:Aqua'>TableName</td><td colspan='7'>";
const string columnStr = "<tr class='tr1'><td align='center'>Id</td><td>ColumnName</td><td>DataType</td><td>DefaultValue</td><td>IsPrimaryKey</td><td>IsIdentity</td><td>AllowNulls</td><td>Description</td></tr>";
StringBuilder sbTable = new StringBuilder();
sbTable.Append(tableStr + tableName + "</td>" + columnStr);
foreach (DataRow dr in dt.Rows)
{
if (tableName != dr["TableName"].ToString())
{
sbTable.Append("</table><br />");
tableName = dr["TableName"].ToString();
sbTable.Append(tableStr + tableName + "</td>" + columnStr);
}
sbTable.AppendFormat("<tr><td align='center'>{0}</td><td>{1}</td><td>{2}</td><td>{3}</td><td>{4}</td><td>{5}</td><td>{6}</td><td>{7}</td></tr>", dr["FieldId"].ToString(), dr["FieldName"].ToString(), dr["FieldType"].ToString(), dr["FiledDefault"].ToString().Replace("((", "").Replace("))", ""), dr["IsPrimaryKey"].ToString() == "1" ? "√" : "", dr["IsIDENTITY"].ToString().ToString() == "1" ? "√" : "", dr["AllowNulls"].ToString().Trim() == "NULL" ? "√" : "", dr["FieldDescription"].ToString());
}
sbTable.Append("</table>");
return sbTable.ToString();
}
protected void btnExport_Click(object sender, EventArgs e)
{
string header = string.Format("<table width='100%'><tr><td align='center' style='font-size:17px;font-weight:bolder'>{0}</td></tr><tr><td align='center' style='font-size:13px'>({1})</td></tr></table>", " ", DateTime.Now.ToString("yyyy MM dd "));
ResponseWrite(header + "<br />" + GetTableStr("FubonConnectionString"));
}
void ResponseWrite(string str)
{
Response.Clear();
Response.Buffer = true;
Response.Charset = "UTF-8";
Response.ContentEncoding = Encoding.GetEncoding("UTF-8");
string filename = string.Format(" _{0}.doc", DateTime.Now.ToString("yyyyMMdd"));
Response.AddHeader("Content-Disposition", "attachment;filename=" + System.Web.HttpUtility.UrlEncode(filename,System.Text.Encoding.UTF8));
Response.ContentType = "application/ms-word";
Response.Write(@"
<style type='text/css'>
table{border-collapse:collapse}
td{font-size:14px}
.tr1 td{ background-color:Aqua;font-size:15px}
</style>");
Response.Write(str);
Response.End();
}