利用Sql脚本生成C#类

最新推荐文章于 2022-01-05 14:52:48 发布

weixin_34037515

最新推荐文章于 2022-01-05 14:52:48 发布

阅读量227

点赞数

文章标签： c# 数据库 javascript ViewUI

/*
* 作者： evlon(阿牛) MSN:niukl@msn.com 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]

exec sp_addextendedproperty N'MS_Description', N'是否超级用户', N'user', N'dbo', N'table', N'AdminUsers', N'column', N'IsSuperMan'

exec sp_addextendedproperty N'MS_Description', N'登陆名', N'user', N'dbo', N'table', N'AdminUsers', N'column', N'loginID'

exec sp_addextendedproperty N'MS_Description', N'登陆密码', N'user', N'dbo', N'table', N'AdminUsers', N'column', N'loginPwd'

exec sp_addextendedproperty N'MS_Description', N'用户系统标识', N'user', N'dbo', N'table', N'AdminUsers', N'column', N'userid'

exec sp_addextendedproperty N'MS_Description', N'真实姓名', N'user', N'dbo', N'table', N'AdminUsers', N'column', N'UserName'

我们要生成下面的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>

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

}

</script>

</head>

<body>

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]

exec sp_addextendedproperty N'MS_Description', N'是否超级用户', N'user', N'dbo', N'table', N'AdminUsers', N'column', N'IsSuperMan'

exec sp_addextendedproperty N'MS_Description', N'登陆名', N'user', N'dbo', N'table', N'AdminUsers', N'column', N'loginID'

exec sp_addextendedproperty N'MS_Description', N'登陆密码', N'user', N'dbo', N'table', N'AdminUsers', N'column', N'loginPwd'

exec sp_addextendedproperty N'MS_Description', N'用户系统标识', N'user', N'dbo', N'table', N'AdminUsers', N'column', N'userid'

exec sp_addextendedproperty N'MS_Description', N'真实姓名', N'user', N'dbo', N'table', N'AdminUsers', N'column', N'UserName'

</textarea>

</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的格式不正确");

}