C#分析数据库结构，使用XSL模板自动生成代码

ChinaItLab

佚名

2007-5-8

保存本文

推荐给好友

收藏本页

欢迎进入.NET社区论坛，与200万技术人员互动交流 >>进入

<html>
<head>
<TITLE>分析数据库结构,自动生成代码</TITLE>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312">
</head>
<frameset cols="237,767" rows="*">
<frame src="dbxml.aspx">
<frame name="code" src="about:blank">
</frameset>
</html>
########################### dbxml.aspx 文件内容，该文件没有C#代码文件 #############
<script language="C#" runat ="server">
System.Xml.XmlDocument myCfgXML = new System.Xml.XmlDocument();
// 获得系统配置字符串
string GetAppConfig(string strKey)
{
System.Xml.XmlElement cfgElement = myCfgXML.SelectSingleNode ("//setting[@key='" + strKey + "']" )
as System.Xml.XmlElement ;
if( cfgElement == null )
return "";
else
return cfgElement.InnerText ;
}

// 判断字符串是否是空白字符串
bool isBlankString(string strText )
{
if(strText != null)
{
int iCount;
for(iCount=0;iCount<strText.Length ;iCount++)
{
if(System.Char.IsWhiteSpace ( strText[iCount])==false)
return false;
}
}
return true;
}
void Page_Load(Object sender, EventArgs e)
{
// 加载系统配置文件
myCfgXML.Load(this.MapPath(".") + "\\dbxmlcfg.xml");
string strType = this.Request["type"];
string strXSL = "main.xml";

if(strType == null)
strType = "querytable";
System.Xml.XmlDocument myDoc = new System.Xml.XmlDocument();
myDoc.LoadXml("<dbxml />");
string strConnection = GetAppConfig("conndbxml");
System.Text.Encoding myEncode = System.Text.Encoding.GetEncoding(936);

if(isBlankString(strConnection)==false)
{
using(System.Data.OleDb.OleDbConnection myConn = new System.Data.OleDb.OleDbConnection(strConnection))
{
myConn.Open();
if(myConn.State == System.Data.ConnectionState.Open )
{
string strSQL = GetAppConfig(strType + "_" + myConn.Provider);
if(isBlankString(strSQL)==false)
{
using(System.Data.OleDb.OleDbCommand myCmd = myConn.CreateCommand())
{
string strTableName = null;
if(strType.Equals("queryfield"))
{
// 修正SQL语句
string strTableList = this.Request.Form["tablelist"];
string []strTables = strTableList.Split(",".ToCharArray());
strXSL = System.Web.HttpUtility.UrlPathEncode(this.Request.Form["template"] ) + ".xml";
strTableList = null;
for(int iCount = 0 ; iCount < strTables.Length ; iCount ++ )
{
if(isBlankString(strTables[iCount])==false)
{
if(strTableList == null)
strTableList = "'" + strTables[iCount] + "'";
else
strTableList = strTableList + ",'" + strTables[iCount] + "'";
}
}
strSQL = strSQL.Replace("#tablelist", strTableList);
myCmd.CommandText = strSQL ;
string strLastTableName = null;
string strFieldName = null;
System.Xml.XmlElement TableElement = null;
System.Data.OleDb.OleDbDataReader myReader = myCmd.ExecuteReader();
while(myReader.Read())
{
strTableName = myReader[0].ToString().ToUpper();
if(strTableName.Equals(strLastTableName)==false)
{
// 填充表说明元素
strLastTableName = strTableName ;
TableElement = myDoc.CreateElement("table");
TableElement.SetAttribute("tablename", strTableName);
myDoc.DocumentElement.AppendChild(TableElement);
}
// 填充字段说明元素
System.Xml.XmlElement FieldElement = myDoc.CreateElement("field");
FieldElement.SetAttribute("fieldname", myReader[1].ToString());
FieldElement.SetAttribute("fieldtype", myReader[2].ToString());
FieldElement.SetAttribute("fieldwidth", myReader[3].ToString());
FieldElement.SetAttribute("isstring", (myReader[2].ToString().ToUpper().IndexOf("CHAR")>=0?"1":"0"));
strFieldName = myReader[1].ToString();
int iLen = myEncode.GetByteCount(strFieldName);
if(iLen < 20)
FieldElement.SetAttribute("fixname", strFieldName + new string(' ', 20 - iLen));
TableElement.AppendChild(FieldElement);
}
myReader.Close();
}
else
{
// 填充模板列表
string [] strFileNames = System.IO.Directory.GetFiles(this.Server.MapPath("."),"temp_*.xml");
for(int iCount = 0 ; iCount < strFileNames.Length ; iCount ++ )
{
System.Xml.XmlElement tempXML = myDoc.CreateElement("template");
tempXML.SetAttribute("key",System.IO.Path.GetFileNameWithoutExtension(strFileNames[iCount]));
myDoc.DocumentElement.AppendChild(tempXML);
}
// 填充表名列表
myCmd.CommandText = strSQL ;
System.Data.OleDb.OleDbDataReader myReader = myCmd.ExecuteReader();
System.Xml.XmlElement TableElement = null;
while(myReader.Read())
{
TableElement = myDoc.CreateElement("table");
myDoc.DocumentElement.AppendChild(TableElement);
strTableName = myReader[0].ToString();
TableElement.SetAttribute("name", strTableName );
TableElement.SetAttribute("count", myReader[1].ToString());
int iLen = myEncode.GetByteCount(strTableName);
if(iLen < 20 )
TableElement.SetAttribute("fixname",strTableName + new string(' ', 20 - iLen));
}
myReader.Close();
}
}
}
}
myConn.Close();
}
}
// 输出文档
this.Response.ContentType = "text/xml";
this.Response.ContentEncoding = myEncode ;
this.Response.Write("<?xml version=\"1.0\" encoding=\"GB2312\" ?>");
this.Response.Write("<?xml-stylesheet type=\"text/xsl\" href=\"" + strXSL + "\"?>");
this.Response.Write(myDoc.DocumentElement.OuterXml);
}
</script>

文件 dbxmlcfg.xml内容

--------------------------------------------------------------------------------

<?xml version="1.0" encoding="utf-8" ?>
<application>
<settings>
<setting key="conn">
Provider=SQLOLEDB.1;
Password=123456;
Persist Security Info=True;
User ID=CPR;
Initial Catalog=HTIOA;
Data Source=192.168.0.124
</setting>
<setting key="conndbxml">
Provider=SQLOLEDB.1;
Integrated Security=SSPI;
Persist Security Info=False;
Initial Catalog=IssueVision;
Data Source=(local)
</setting>

<setting key="queryfield_OraOLEDB.Oracle.1">
Select TName ,CName ,coltype ,width From Col where tname in (#tablelist) Order by TName,CName
</setting>
<setting key="querytable_OraOLEDB.Oracle.1">Select TName ,count(*) From Col group by tname Order by TName </setting>
<setting key="queryfield_SQLOLEDB.1">
<![CDATA[select sysobjects.name ,syscolumns.name ,systypes.name ,syscolumns.length
from syscolumns,sysobjects,systypes
where syscolumns.id=sysobjects.id and syscolumns.xtype=systypes.xtype and sysobjects.type='U' and
systypes.name <>'_default_' and systypes.name<>'sysname' and sysobjects.name in (#tablelist)
order by sysobjects.name,syscolumns.name]]>
</setting>
<setting key="querytable_SQLOLEDB.1">
<![CDATA[select sysobjects.name ,count(*)
from syscolumns,sysobjects,systypes
where syscolumns.id=sysobjects.id and syscolumns.xtype=systypes.xtype and sysobjects.type='U' and
systypes.name <>'_default_' and systypes.name<>'sysname'
group by sysobjects.name order by sysobjects.name]]>
</setting>
</settings>
</application>

[1] [2] [3] [4] 下一页

【责编:Peng】

文件夹选项-消失了注册表被禁用怎么办？
Visual C# 编程操作Excel
C#线程池的实现
程序开发中C#创建不规则窗体代码
C#与Java相似之处的对比
Visual Studio 2008的性能改进以及十大新功
认识C#中使用Nullable类型
C#高级编程：给数据库添加会议数据
C#高级编程：会议登记 Web服务
C #中几个线程同步对象的使用方法