至顶网›软件频道 ›C#分析数据库结构，使用XSL模板自动生成代码(1)

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

扫一扫
分享文章到微信
扫一扫
关注官方公众号
至顶头条

分析数据库结构,自动生成代码

作者：中国IT实验室来源：中国IT实验室 2007年9月7日

&url=http://soft.zhiding.cn/software_zone/2007/0907/495396.shtml&pic=&ralateUid=#495396" class="s_wb" id="weibo">

<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>

####################### main.xml ##################################
<?xml version="1.0" encoding="gb2312" ?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
<xsl:output method="xml" indent="yes" />
<xsl:template match="/*">
<html>
<head>
<title></title>
<style>
select2{ font-family: "宋体"; font-size: 12px}
body{ font-family: "宋体"; font-size: 12px}
table{ width:100%; border-collapse:collapse;
border: 1px #CC0066 solid; font-family: "宋体";
font-size: 12px}
.tablehead{background-color:#CCCCFF}
td{ border: 1px #CC0066 solid} </style>
</head>
<body leftmargin='1' rightmargin='1' topmargin="1">
<form name="frm" target="code" method="POST" action="dbxml.aspx?type=queryfield">
模板<select name="template">
<xsl:for-each select="template">
<option>
<xsl:attribute name="value">
<xsl:value-of select="@key" />
</xsl:attribute>
<xsl:value-of select="@key" />
</option>
</xsl:for-each>
</select>
<input type="submit" value="提交" />
<table>
<tr class="tablehead">
<td nowrap="1">选择</td>
<td nowrap="1">表名</td>
<td nowrap="1">字段个数</td>
</tr>
<xsl:for-each select="table">
<tr>
<td nowrap="1">
<input type="checkbox" name="tablelist" style="width:13;height:13">
<xsl:attribute name="value">
<xsl:value-of select="@name" />
</xsl:attribute>
</input>
</td>
<td>
<xsl:value-of select="@name" />
</td>
<td>
<xsl:value-of select="@count" />
</td>
</tr>
</xsl:for-each>
</table>
</form>
</body>
</html>
</xsl:template>
</xsl:stylesheet>

########################## temp_CSharp.xml 内容 ############################################
<?xml version="1.0" encoding="utf-8" ?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
<xsl:output method="xml" indent="yes" />
<xsl:template match="/">
<html>
<head></head>
<body>
<xsl:for-each select="*/table">
<br />-------------- 文件名 <xsl:value-of select="@tablename" />.cs -----------------------------
<pre style=" background-color:gainsboro">
//-----------------------------------------------------------------------------
<xsl:text disable-output-escaping="yes">
/// <summary></xsl:text>
/// 数据库表 <xsl:value-of select="@tablename" /> 操作对象
/// 编制：代码生成器
/// 时间：
<xsl:text disable-output-escaping="yes"> /// </summary></xsl:text>
public class Struct<xsl:value-of select="@tablename" /> : CommonStruct
{ private const string c_TableName ="<xsl:value-of select="@tablename" />";
// 定义数据库字段变量 ////////////////////////////////////////////////////////////////
<xsl:for-each select="*">
<xsl:variable name="csharptype">
<xsl:choose>
<xsl:when test="@isstring='1'">string </xsl:when>
<xsl:when test="boolean('true')">int </xsl:when>
</xsl:choose>
</xsl:variable>
<xsl:variable name="lowfieldname">
<xsl:value-of select="translate(@fieldname,'ABCDEFGHIJKLMNOPQRSTUVWXYZ','abcdefghijklmnopqrstuvwxyz')" />
</xsl:variable>
private <xsl:value-of select="$csharptype" /> m_<xsl:value-of select="@fixname" /> ; // 字段 <xsl:value-of select="@cname" />
</xsl:for-each>

// 定义属性 ///////////////////////////////////////////////////////////
<xsl:for-each select="*">
<xsl:variable name="csharptype">
<xsl:choose>
<xsl:when test="@isstring='1'">string </xsl:when>
<xsl:when test="boolean('true')">int </xsl:when>
</xsl:choose>
</xsl:variable>
/// <xsl:text disable-output-escaping="yes"><summary></xsl:text>
/// 设置/返回数据库字段属性 <xsl:value-of select="@cname" />
/// <xsl:text disable-output-escaping="yes"></summary></xsl:text>
/// <xsl:text disable-output-escaping="yes"><returns></xsl:text><xsl:value-of select="@cname" /><xsl:text disable-output-escaping="yes"> </returns></xsl:text>
public <xsl:value-of select="$csharptype" /> m<xsl:value-of select="@fieldname" />
{
get{ return m_<xsl:value-of select="@fieldname" /> ;}
set{ m_<xsl:value-of select="@fieldname" /> = value ;}
}
</xsl:for-each>

new public static string getTableName()
{return c_TableName ;}
new public static string getSelectSQL()
{
return "Select <xsl:for-each select="*">
<xsl:value-of select="normalize-space(@fieldname)" />
<xsl:if test="position() != last()">,</xsl:if>
</xsl:for-each> From " + c_TableName ;
}
new public static string getTypeName()
{
return "<xsl:value-of select="translate(@tablename,'ABCDEFGHIJKLMNOPQRSTUVWXYZ','abcdefghijklmnopqrstuvwxyz')" />";
}
new public static string SearchKey(string strKey)
{
return getSelectSQL() + " Where SEQ =" + strKey ;
}
public override bool SetCommandParams( System.Data.OleDb.OleDbCommand myCmd,bool SetValues )
{
if(myCmd!= null)
{
myCmd.Parameters.Clear ();
<xsl:for-each select="*">
myCmd.Parameters.Add("<xsl:value-of select="normalize-space(@fieldname)" />",System.Data.OleDb.OleDbType.<xsl:if test="@isstring='1'">
VarWChar</xsl:if><xsl:if test="@isstring='0'">Integer</xsl:if>);
</xsl:for-each>
if(SetValues)
{
<xsl:for-each select="*">
myCmd.Parameters[<xsl:value-of select="position()-1" />].Value = m_<xsl:value-of select="@fieldname" /> ; // 字段 <xsl:value-of select="@cname" />
</xsl:for-each>
}
return true;
}
return false;
}
public override bool SetInsertCommand( System.Data.OleDb.OleDbCommand myCmd)
{
if(myCmd != null)
{
myCmd.CommandText ="Insert Into " + c_TableName
+ " ( <xsl:for-each select="*">[<xsl:value-of select="normalize-space(@fieldname)" />]<xsl:if test="position() != last()">,</xsl:if>
</xsl:for-each>) Values (<xsl:for-each select="*">?<xsl:if test="position() != last()">,</xsl:if>
</xsl:for-each>)";
return this.SetCommandParams(myCmd,true);
}
return false;
}
public override bool SetUpdateCommand(System.Data.OleDb.OleDbCommand myCmd)
{
if(myCmd != null)
{
myCmd.CommandText ="Update " + c_TableName
+ " Set <xsl:for-each select="*">[<xsl:value-of select="normalize-space(@fieldname)" />]=? <xsl:if test="position() != last()">,</xsl:if></xsl:for-each> Where SEQ=" + m_SEQ ;
return this.SetCommandParams(myCmd,true);
}
return false;
}
public override bool SelectRS(System.Data.OleDb.OleDbDataReader myReader)
{
try
{
if(myReader != null)
{
if (myReader.FieldCount==5)
{
<xsl:for-each select="*">
m_<xsl:value-of select="@fixname" />
= Convert.To<xsl:if test="@isstring='1'">String</xsl:if><xsl:if test="@isstring='0'">Int32
</xsl:if>(myReader[<xsl:value-of select="position()-1" />]);
</xsl:for-each>
return true;
}
}
}

查看本文来源

&url=http://soft.zhiding.cn/software_zone/2007/0907/495396.shtml&pic=&ralateUid=#495396" class="s_wb" id="weibo">

VIP专区

VIP用户

普通用户

邮件订阅

如果您非常迫切的想了解IT领域最新产品与技术信息，那么订阅至顶网技术邮件将是您的最佳途径之一。

重磅专题

往期文章

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

业界热点: