* 作者： evlon(阿牛) MSN:
[email protected] QQ:273352165
* 功能： 得用Sql生成的腳本生成C#類
*/
對於SQL Server數據庫,有查詢分析器的幫助.我們可以省下不少的工夫.
如果,在查詢分析器的
選項.腳本中,勾選"將擴展屬性腳本作為對象腳本的一部分"
我們可以生成如下的Create 腳本:
CREATE TABLE [AdminUsers] (
[userid] [int] IDENTITY (1, 1) NOT NULL ,
[loginID] [varchar] (30) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[loginPwd] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[UserName] [nvarchar] (30) COLLATE Chinese_PRC_CI_AS NOT NULL CONSTRAINT [DF_AdminUsers_UserName] DEFAULT (''''),
[IsSuperMan] [bit] NOT NULL CONSTRAINT [DF_AdminUsers_IsSuperMan] DEFAULT (0),
CONSTRAINT [PK_AdminUsers] PRIMARY KEY CLUSTERED
(
[userid]
) ON [PRIMARY]
) ON [PRIMARY]
GO
exec sp_addextendedproperty N''MS_Description'', N''是否超級用戶'', N''user'', N''dbo'', N''table'', N''AdminUsers'', N''column'', N''IsSuperMan''
GO
exec sp_addextendedproperty N''MS_Description'', N''登陸名'', N''user'', N''dbo'', N''table'', N''AdminUsers'', N''column'', N''loginID''
GO
exec sp_addextendedproperty N''MS_Description'', N''登陸密碼'', N''user'', N''dbo'', N''table'', N''AdminUsers'', N''column'', N''loginPwd''
GO
exec sp_addextendedproperty N''MS_Description'', N''用戶系統標識'', N''user'', N''dbo'', N''table'', N''AdminUsers'', N''column'', N''userid''
GO
exec sp_addextendedproperty N''MS_Description'', N''真實姓名'', N''user'', N''dbo'', N''table'', N''AdminUsers'', N''column'', N''UserName''
GO
我們要生成下面的C#類:
/**//// <summary>
/// AdminUsers的摘要說明。
</summary>
public class AdminUsers
{
/**//// <summary>
/// 用戶系統標識/// </summary>
private int userid;
public int Userid
{
get
{ return userid; }
set
{ userid = value; }
}
/**//// <summary>
/// 登陸名/// </summary>
private string loginID;
public string LoginID
{
get
{ return loginID; }
set
{ loginID = value; }
}
/**//// <summary>
/// 登陸密碼/// </summary>
private string loginPwd;
public string LoginPwd
{
get
{ return loginPwd; }
set
{ loginPwd = value; }
}
/**//// <summary>
/// 真實姓名/// </summary>
private string userName;
public string UserName
{
get
{ return userName; }
set
{ userName = value; }
}
/**//// <summary>
/// 是否超級用戶/// </summary>
private bool isSuperMan;
public bool IsSuperMan
{
get
{ return isSuperMan; }
set
{ isSuperMan = value; }
}
}
由於Sql腳本中有我們需要的所有的東西.我們就有了生成類的必要條件.我已經把它用Javascript腳本實現了:
<Html>
<head>
<title>Untitled</title>
<script language=Javascript>
String.prototype.upperFirstChar = function()
{
return this.replace(/^(\w)/,function($1)
{return $1.toUpperCase();});
}
String.prototype.lowerFirstChar = function()
{
return this.replace(/^(\w)/,function($1)
{return $1.toLowerCase();});
}
var ConvertDbTypeToCSType =
{
"bigint":"long",
"binary":"byte[]",
"bit":"bool",
"char":"char",
& "datetime":"DateTime",
"decimal":"decimal",
"float":"float",
"image":"byte[]",
"int":"int",
"money":"string",
"nchar":"char",
"ntext":"string",
"numeric":"int",
"nvarchar":"string",
"real":"string",
"smalldatetime":"DateTime",
"smallint":"int",
"smallmoney":"string",
"sql_variant":"string",
"sysname":"string",
"text":"string",
"timestamp":"DateTime",
"tinyint":"int",
"uniqueidentifIEr":"string",
"varbinary":"byte[]",
"varchar":"string"
};
function CsFileStrea
{
this.ms = new Array();
this.tabNum = 0;
this.getTabs = function()
{
var arr = new Array();
arr.length = this.tabNum + 1;
return arr.join("\t");
}
}
CsFileStream.prototype.append = function()
{
var str = arguments[0];
if(str instanceof Array)
{
str = str.join("");
}
if(this.tabNum > 0)
{
var tabs = this.getTabs();
var tmpStr = str.replace(/\n/g,"\n" + tabs);
this.ms[this.ms.length] = tmpStr;
}
else
{
this.ms[this.ms.length] = str;
}
}
CsFileStream.prototype.addTab = function()
{
this.tabNum ++;
if(this.ms.length > 0)
{
this.ms[this.ms.length - 1] = this.ms[this.ms.length - 1].replace(/\n\t*$/gm,''\n'' + this.getTabs());
}
}
CsFileStream.prototype.delTab = function()
{
this.tabNum --;
if(this.ms.length > 0)
{
this.ms[this.ms.length - 1] = this.ms[this.ms.length - 1].replace(/\n\t*$/gm, ''\n'' + this.getTabs());
}
}
CsFileStream.prototype.toString = function()
{
return this.ms.join("");
}
function buildCsFromSql(strSql)
{
var tableName = strSql.match(/table\s+\[([^\]]+)\]/i)[1];
var column = [];
var rgx = /\[([^\]]+)\]\s+\[([^\]]+)\]/gi;
var arr;
while((arr = rgx.exec(strSql)) != null)
{
column[arr[1]] =
{};
column[arr[1]].name=arr[1];
column[arr[1]].dbtype=arr[2];
column[arr[1]].cstype=ConvertDbTypeToCSType[arr[2]];
}
rgx = /N''MS_Description'',\s+N''([^'']+)''.+N''([^'']+)''$/gim;
while((arr = rgx.exec(strSql)) != null)
{
column[arr[2]].dbdesc=arr[1];
}
var cs = new CsFileStream();
cs.append("/// <summary>\n");
cs.append(["/// ",tableName.upperFirstChar(),"的摘要說明。\n"]);
cs.append("/// </summary>\n");
cs.append(["public class ",tableName.upperFirstChar(),"\n"]);
cs.append("{\n");
cs.addTab();
for(var i in column)
{
//私有成員
cs.append("/// <summary>\n");
cs.append(["/// ",column[i].dbdesc]);
cs.append("/// </summary>\n");
cs.append(["private ",column[i].cstype," ", column[i].name.lowerFirstChar(),";\n"]);
cs.append(["public ",column[i].cstype," ", column[i].name.upperFirstChar(),"\n"]);
cs.append("{\n");
cs.addTab();
cs.append(["get{ return ",column[i].name.lowerFirstChar(),"; }\n"]);
cs.append(["set{ ",column[i].name.lowerFirstChar()," = value; }\n"]);
cs.delTab();
cs.append("}\n");
}
cs.delTab();
cs.append("}\n");
return cs.toString();
}
function go_buildCsFromSql()
{
txtCs.value = buildCsFromSql(txtSql.value);
}
</script>
</head>
<body>
<textarea id=txtSql >
CREATE TABLE [AdminUsers] (
[userid] [int] IDENTITY (1, 1) NOT NULL ,
[loginID] [varchar] (30) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[loginPwd] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[UserName] [nvarchar] (30) COLLATE Chinese_PRC_CI_AS NOT NULL CONSTRAINT [DF_AdminUsers_UserName] DEFAULT (''''),
[IsSuperMan] [bit] NOT NULL CONSTRAINT [DF_AdminUsers_IsSuperMan] DEFAULT (0),
CONSTRAINT [PK_AdminUsers] PRIMARY KEY CLUSTERED
(
[userid]
) ON [PRIMARY]
) ON [PRIMARY]
GO
exec sp_addextendedproperty N''MS_Description'', N''是否超級用戶'', N''user'', N''dbo'', N''table'', N''AdminUsers'', N''column'', N''IsSuperMan''
GO
exec sp_addextendedproperty N''MS_Description'', N''登陸名'', N''user'', N''dbo'', N''table'', N''AdminUsers'', N''column'', N''loginID''
GO
exec sp_addextendedproperty N''MS_Description'', N''登陸密碼'', N''user'', N''dbo'', N''table'', N''AdminUsers'', N''column'', N''loginPwd''
GO
exec sp_addextendedproperty N''MS_Description'', N''用戶系統標識'', N''user'', N''dbo'', N''table'', N''AdminUsers'', N''column'', N''userid''
GO
exec sp_addextendedproperty N''MS_Description'', N''真實姓名'', N''user'', N''dbo'', N''table'', N''AdminUsers'', N''column'', N''UserName''
GO
</textarea>
<input type="button" value="生成" id="doit" onclick="go_buildCsFromSql()">
<textarea id=txtCs >
</textarea>
</body>
</Html>
運行效果如下:
還可以改成Emeditor宏:記得保存
String.prototype.upperFirstChar = function()
{
return this.replace(/^(\w)/,function($1)
{return $1.toUpperCase();});
}
String.prototype.lowerFirstChar = function()
{
return this.replace(/^(\w)/,function($1)
{return $1.toLowerCase();});
}
var ConvertDbTypeToCSType =
{
"bigint":"long",
"binary":"byte[]",
"bit":"bool",
"char":"char",
"datetime":"DateTime",
"decimal":"decimal",
"float":"float",
"image":"byte[]",
"int":"int",
"money":"string",
"nchar":"char",
"ntext":"string",
"numeric":"int",
"nvarchar":"string"
"real":"string",
"smalldatetime":"DateTime",
"smallint":"int",
"smallmoney":"string",
"sql_variant":"string",
"sysname":"string",
"text":"string",
"timestamp":"DateTime",
"tinyint":"int",
"uniqueidentifIEr":"string",
"varbinary":"byte[]",
"varchar":"string"
};
function CsFileStream()
{
this.ms = new Array();
this.tabNum = 0;
this.getTabs = function()
var arr = new Array();
arr.length = this.tabNum + 1;
return arr.join("\t");
}
}
CsFileStream.prototype.append = function()
{
var str = arguments[0];
if(str instanceof Array)
{
str = str.join("");
}
if(this.tabNum > 0)
{
var tabs = this.getTabs();
var tmpStr = str.replace(/\n/g,"\n" + tabs);
this.ms[this.ms.length] = tmpStr;
}
else
{
this.ms[this.ms.length] = str;
}
}
CsFileStream.prototype.addTab = function()
{
this.tabNum ++;
if(this.ms.length > 0)
{
this.ms[this.ms.length - 1] = this.ms[this.ms.length - 1].replace(/\n\t*$/gm,''\n'' + this.getTabs());
}
}
CsFileStream.prototype.delTab = function()
{
this.tabNum --;
if(this.ms.length > 0)
{
this.ms[this.ms.length - 1] = this.ms[this.ms.length - 1].replace(/\n\t*$/gm, ''\n'' + this.getTabs());
}
}
CsFileStream.prototype.toString = function()
{
return this.ms.join("");
}
function buildCsFromSql(strSql)
{
var tableName = strSql.match(/table\s+\[([^\]]+)\]/i)[1];
var column = [];
var rgx = /\[([^\]]+)\]\s+\[([^\]]+)\]/gi;
var arr;
while((arr = rgx.exec(strSql)) != null)
{
column[arr[1]] =
{};
column[arr[1]].name=arr[1];
column[arr[1]].dbtype=arr[2];
column[arr[1]].cstype=ConvertDbTypeToCSType[arr[2]];
}
rgx = /N''MS_Description'',\s+N''([^'']+)''.+N''([^'']+)''$/gim;
while((arr = rgx.exec(strSql)) != null)
{
column[arr[2]].dbdesc=arr[1];
}
var cs = new CsFileStream();
cs.append("/// <summary>\n");
cs.append(["/// ",tableName.upperFirstChar(),"的摘要說明。\n"]);
cs.append("/// </summary>\n");
cs.append(["public class ",tableName.upperFirstChar(),"\n"]);
cs.append("{\n");
cs.addTab();
for(var i in column)
{
//私有成員
cs.append("/// <summary>\n");
cs.append(["/// ",column[i].dbdesc]);
cs.append("/// </summary>\n");
cs.append(["private ",column[i].cstype," ", column[i].name.lowerFirstChar(),";\n"]);
cs.append(["public ",column[i].cstype," ", column[i].name.upperFirstChar(),"\n"]);
cs.append("{\n");
cs.addTab();
cs.append(["get{ return ",column[i].name.lowerFirstChar(),"; }\n"]);
cs.append(["set{ ",column[i].name.lowerFirstChar()," = value; }\n"]);
cs.delTab();
cs.append("}\n");
}
cs.delTab();
cs.append("}\n");
return cs.toString();
}
function go_buildCsFromSql()
{
txtCs.value = buildCsFromSql(txtSql.value);
}
try
{
document.selection.SelectAll();
document.selection.Text = buildCsFromSql(document.selection.Text);
document.selection.Collapse();
document.ConfigName = "C#";
}
catch(e)
{
alert("Sql的格式不正確");
}
