导读:
分析数据库结构,自动生成代码
########################### 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
{
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("
?);> 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
{
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
{
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(" ");
this.Response.Write(" ");
this.Response.Write(myDoc.DocumentElement.OuterXml);
}
</script>
文件 dbxmlcfg.xml内容
--------------------------------------------------------------------------------
Provider=SQLOLEDB.1;Password=123456;Persist Security Info=True;User ID=CPR;Initial Catalog=HTIOA;Data Source=192.168.0.124
Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=IssueVision;Data Source=(local)
定义查询表结构使用的SQL语句,
queryfield_驱动程序名称 定义了查询指定表的字段定义的SQL语句,该语句带有一个参数
querytable_驱动程序名称 定义了查询所有表名及其字段个数的SQL语句,该语句没有参数
目前定义了 oracle和ms sql server 的SQL语句
-->
Select TName ,CName ,coltype ,width From Col where tname in (#tablelist) Order by TName,CName
Select TName ,count(*) From Col group by tname Order by TName
'_default_' and systypes.name<>'sysname' and sysobjects.name in (#tablelist) order by sysobjects.name,syscolumns.name]]>
'_default_' and systypes.name<>'sysname' group by sysobjects.name order by sysobjects.name]]>
####################### main.xml ##################################
############################### temp_CSharp.xml 内容 ############################################
-------------- 文件名 .cs -----------------------------
########################## temp_HTML代码.xml #####################################################
数据表 结构
共 个字段
################################ temp_Java_Struct.xml #######################################################################
-------------- 文件名 .java -----------------------------
######################################## temp_VB.xml ############################################################
-------------- 文件名 .cls -----------------------------
####################### temp_表说明文档.xml ##################################
-------------表 的说明文档 个字段
####################### temp_创建表的SQL语句.xml ##################################
-------------创建表 的SQL语句 个字段
####################### temp_选择表使用的SQL语句.xml ##################################
-------------选择表 的SQL语句 个字段
Trackback: http://tb.blog.csdn.net/TrackBack.aspx?PostId=1539902
本文转自
http://blog.csdn.net/21aspnet/archive/2007/03/24/1539902.aspx
########################### 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
{
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("
?);> 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
{
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
{
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(" ");
this.Response.Write(" ");
this.Response.Write(myDoc.DocumentElement.OuterXml);
}
</script>
文件 dbxmlcfg.xml内容
--------------------------------------------------------------------------------
Provider=SQLOLEDB.1;Password=123456;Persist Security Info=True;User ID=CPR;Initial Catalog=HTIOA;Data Source=192.168.0.124
Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=IssueVision;Data Source=(local)
定义查询表结构使用的SQL语句,
queryfield_驱动程序名称 定义了查询指定表的字段定义的SQL语句,该语句带有一个参数
querytable_驱动程序名称 定义了查询所有表名及其字段个数的SQL语句,该语句没有参数
目前定义了 oracle和ms sql server 的SQL语句
-->
Select TName ,CName ,coltype ,width From Col where tname in (#tablelist) Order by TName,CName
Select TName ,count(*) From Col group by tname Order by TName
'_default_' and systypes.name<>'sysname' and sysobjects.name in (#tablelist) order by sysobjects.name,syscolumns.name]]>
'_default_' and systypes.name<>'sysname' group by sysobjects.name order by sysobjects.name]]>
####################### main.xml ##################################
############################### temp_CSharp.xml 内容 ############################################
-------------- 文件名 .cs -----------------------------
//-----------------------------------------------------------------------------
///
/// 数据库表 操作对象
/// 编制: 代码生成器
/// 时间:
///
public class Struct : CommonStruct
{ private const string c_TableName =" ";
// 定义数据库字段变量
string
int
private m_ ;// 字段
// 定义属性 ///
string
int
///
/// 设置/返回数据库字段属性
///
///
public m
{
get{ return m_ ;}
set{ m_ = value ;}
}
new public static string getTableName()
{return c_TableName ;}
new public static string getSelectSQL()
{
return "Select
,
From "+ c_TableName ;
}
new public static string getTypeName()
{
return " ";
}
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 ();
myCmd.Parameters.Add(" ",System.Data.OleDb.OleDbType. VarWChar Integer );
if(SetValues)
{
myCmd.Parameters[ ].Value = m_ ;// 字段
}
return true;
}
return false;
}
public override bool SetInsertCommand( System.Data.OleDb.OleDbCommand myCmd)
{
if(myCmd != null)
{
myCmd.CommandText ="Insert Into "+ c_TableName
+ "( [ ] ,
) Values ( ? ,
)";
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 [ ]=? , 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)
{
m_ = Convert.To String Int32 (myReader[ ]);
return true;
}
}
}
catch
{}
return false;
}
public override bool ToXML(System.Xml.XmlElement myElement)
{
if(myElement != null)
{
myElement.SetAttribute(" ",m_ .ToString() );
return true;
}
return false;
}
public override bool FromXML(System.Xml.XmlElement myElement)
{
try
{
if(myElement != null)
{
m_ = Convert.ToInt32( myElement.GetAttribute(" ") ) ;
return true;
}
}
catch
{}
return false;
}
}// 数据库操作类 Struct 定义结束
########################## temp_HTML代码.xml #####################################################
数据表 结构
共 个字段
字段名 |
类型 |
长度 |
|
|
|
################################ temp_Java_Struct.xml #######################################################################
-------------- 文件名 .java -----------------------------
package com.haitai.emr.struct;
import java.sql.*;
import java.io.*;
/**
* @author 代码生成器 */
public class implements Serializable
{ // 定义数据库字段变量
String
int
private ;// 字段
public static final String SELECT =
"Select
,
From ";
/** @param conn
* @exception SQLException */
public java.sql.PreparedStatement makeInsSt (java.sql.Connection conn) throws SQLException{
PreparedStatement pst=conn.prepareStatement("insert into (
,
)"
+"values( ? , )");
int index=0;
pst.setString(++index,this.get ()); //
return pst;
}
/** @param conn
* @exception SQLException */
public java.sql.PreparedStatement makeUpdSt (java.sql.Connection conn) throws SQLException{
// TODO : implement
PreparedStatement pst=conn.prepareStatement("update set =? , )"
+"where 数据表关键字段名=?");
int index=0;
pst.setString(++index,this.get ()); //
//关键字
pst.setString(++index,this.get数据表关键字段名());//数据表关键字段说明
return pst;
}
public String toString (){
// TODO : implement
return " , ="+ + ;
}
// 读取和修改数据的接口
String
int
public get (){
return ;
}
//@param
public void set ( value){
= value ;
}
} // 类 定义结束
######################################## temp_VB.xml ############################################################
-------------- 文件名 .cls -----------------------------
'******************************************************************************
'**
'** 数据表 [ ]操作的对象
'**
'** 编制:代码生成器
'** 时间:
'**
'******************************************************************************
'** 定义和数据库字段对应的变量 *************************************************************
private const c_TableName As String = " " '** 数据表名称
String
Integer
private m_ As '** 字段
'** 定义数据库字段属性接口 ***************************************************************
Public Property Get TableName() As String
TableName = c_TableName
End Property
String
Integer
'** 数据库字段
Public Property Get m () As
m = m_
End Property
Public Property Let m (Byval Value As )
m_ = m
End Property
'** 获得查询所有数据使用的SQL语句 **
public Function GetBaseSQL() As String
GetBaseSQL ="Select
,
From " & c_TableName
End Function
'** 定义从数据库记录集获得数据的方法 **
Public Function SelectRS(ByVal rs As ADODB.Recordset) As Boolean
On Error GoTo SelectErr
SelectRS = False
m_ = rs.Fields( ).Value '** 字段
SelectRS = True
Exit Function
SelectErr:
SelectRS = False
End Function
####################### temp_表说明文档.xml ##################################
-------------表 的说明文档 个字段
( ) .
####################### temp_创建表的SQL语句.xml ##################################
-------------创建表 的SQL语句 个字段
CREATE TABLE (
( )
,
)
####################### temp_选择表使用的SQL语句.xml ##################################
-------------选择表 的SQL语句 个字段
Select
,
From
Trackback: http://tb.blog.csdn.net/TrackBack.aspx?PostId=1539902
本文转自
http://blog.csdn.net/21aspnet/archive/2007/03/24/1539902.aspx