懒人的懒做法
班门弄斧一下
--创建一个处理名称的函数
--将数据库中字段名为 news_type_xx 格式的转换为 实体名为 NewsTypeXxxIF OBJECT_ID('f_split')IS NOT NULL
DROP FUNCTION dbo.f_split
GO
CREATE FUNCTION f_split(@s VARCHAR(1000))
RETURNS VARCHAR(1000)
AS
BEGIN
DECLARE @str VARCHAR(1000),@split VARCHAR(100)
SELECT @s=@s+'_',@str=''
WHILE CHARINDEX('_',@s)>0
BEGIN
SET @split=left(@s,CHARINDEX('_',@s))
SET @str=@str+UPPER(left(@split,1))+right(@split,LEN(@split)-1)
SET @s=STUFF(@s,1,CHARINDEX('_',@s),'')
END
RETURN REPLACE(LEFT(@str,LEN(@str)-1),'_','')
END
GO
--创建存储过程
IF OBJECT_ID('c#','p')IS NOT NULL
DROP PROC c#
GO
CREATE PROC c#(@ VARCHAR(100))
AS
DECLARE @i INT,@j INT, @a VARCHAR(20), @s VARCHAR(8000),@g VARCHAR(30)
DECLARE @t TABLE(id INT,[name] VARCHAR(100),[type] VARCHAR(20),[value] VARCHAR(200))
SET @i=1
SET @a='/// </summary>'
SET @g=' { get; set; }'
SET @s=@a+CHAR(10)+'/// '
INSERT INTO @t
SELECT id=ROW_NUMBER() OVER(ORDER BY c.[name]),
CAST(c.[name] AS VARCHAR(100)) AS [name] ,
CAST(t.[name] AS VARCHAR(30)) AS [type] ,
CAST(ISNULL(e.[value],'') AS VARCHAR(200)) AS [value]--字段说明
FROM SYS.COLUMNS c
LEFT JOIN SYS.TYPES t
ON c.user_type_id=t.user_type_id
LEFT JOIN SYS.EXTENDED_PROPERTIES e
ON e.class=1 AND c.[object_id]=e.major_id AND c.column_id=e.minor_id
WHERE c.[object_id]= OBJECT_ID(''+@+'')
SELECT @j=MAX(id) FROM @t
WHILE @i<=@j
BEGIN
SELECT @s=@s + [value] FROM @t WHERE id=@i
SET @s=@s+CHAR(10)+@a+CHAR(10)
SELECT @s=@s+'public'+CHAR(32)+CASE [type] WHEN 'datetime' THEN 'DateTime'
WHEN 'varchar' THEN 'String'
WHEN 'int' THEN 'Int32'
WHEN 'bit' THEN 'Boolean'
ELSE 'String' END--常用的几个数据类型,根据需要可扩充
+CHAR(32) FROM @t WHERE id=@i
SELECT @s=@s+ dbo.F_split([name]) --调用函数
FROM @t WHERE id=@i
SET @s=@s+@g+CHAR(10)+CHAR(10)
SET @s=@s+@a+CHAR(10)++'/// '
SET @i=@i+1
END
print @S
GO
--测试
EXEC C# 'moa_news'
--结果/// </summary>
/// 是否提醒1为提醒,0为不提醒,默认0
/// </summary>
public Boolean IsRemind { get; set; }/// </summary>
/// 是否置顶1为置顶,0为不置顶,默认0
/// </summary>
public Boolean IsTop { get; set; }/// </summary>
/// 是否可见1为可见0为不可见,默认可见
/// </summary>
public Boolean IsVisible { get; set; }/// </summary>
/// 新闻发布人
/// </summary>
public String NewsAuthor { get; set; }/// </summary>
/// 新闻内容
/// </summary>
public String NewsContent { get; set; }/// </summary>
/// 新闻发布时间
/// </summary>
public DateTime NewsCreateDate { get; set; }/// </summary>
/// 附件地址
/// </summary>
public String NewsEnclosurePath { get; set; }/// </summary>
/// 附件名称
/// </summary>
public String NewsEnclosureTitle { get; set; }/// </summary>
/// 新闻生效时间
/// </summary>
public DateTime NewsFromDate { get; set; }/// </summary>
/// 新闻编号
/// </summary>
public Int32 NewsId { get; set; }/// </summary>
/// 可阅读该新闻的机构列表,以“,”分隔
/// </summary>
public String NewsOrganiztionList { get; set; }/// </summary>
/// 可阅读该新闻的角色列表,以“,”分隔
/// </summary>
public String NewsRoleList { get; set; }/// </summary>
/// 新闻标题
/// </summary>
public String NewsTitle { get; set; }/// </summary>
/// 新闻失效时间
/// </summary>
public DateTime NewsToDate { get; set; }/// </summary>
///
/// </summary>
public Int32 NewsType { get; set; }/// </summary>
/// 可阅读该新闻的人员列表,以“,”分隔
/// </summary>
public String NewsUserList { get; set; }
班门弄斧一下
--创建一个处理名称的函数
--将数据库中字段名为 news_type_xx 格式的转换为 实体名为 NewsTypeXxxIF OBJECT_ID('f_split')IS NOT NULL
DROP FUNCTION dbo.f_split
GO
CREATE FUNCTION f_split(@s VARCHAR(1000))
RETURNS VARCHAR(1000)
AS
BEGIN
DECLARE @str VARCHAR(1000),@split VARCHAR(100)
SELECT @s=@s+'_',@str=''
WHILE CHARINDEX('_',@s)>0
BEGIN
SET @split=left(@s,CHARINDEX('_',@s))
SET @str=@str+UPPER(left(@split,1))+right(@split,LEN(@split)-1)
SET @s=STUFF(@s,1,CHARINDEX('_',@s),'')
END
RETURN REPLACE(LEFT(@str,LEN(@str)-1),'_','')
END
GO
--创建存储过程
IF OBJECT_ID('c#','p')IS NOT NULL
DROP PROC c#
GO
CREATE PROC c#(@ VARCHAR(100))
AS
DECLARE @i INT,@j INT, @a VARCHAR(20), @s VARCHAR(8000),@g VARCHAR(30)
DECLARE @t TABLE(id INT,[name] VARCHAR(100),[type] VARCHAR(20),[value] VARCHAR(200))
SET @i=1
SET @a='/// </summary>'
SET @g=' { get; set; }'
SET @s=@a+CHAR(10)+'/// '
INSERT INTO @t
SELECT id=ROW_NUMBER() OVER(ORDER BY c.[name]),
CAST(c.[name] AS VARCHAR(100)) AS [name] ,
CAST(t.[name] AS VARCHAR(30)) AS [type] ,
CAST(ISNULL(e.[value],'') AS VARCHAR(200)) AS [value]--字段说明
FROM SYS.COLUMNS c
LEFT JOIN SYS.TYPES t
ON c.user_type_id=t.user_type_id
LEFT JOIN SYS.EXTENDED_PROPERTIES e
ON e.class=1 AND c.[object_id]=e.major_id AND c.column_id=e.minor_id
WHERE c.[object_id]= OBJECT_ID(''+@+'')
SELECT @j=MAX(id) FROM @t
WHILE @i<=@j
BEGIN
SELECT @s=@s + [value] FROM @t WHERE id=@i
SET @s=@s+CHAR(10)+@a+CHAR(10)
SELECT @s=@s+'public'+CHAR(32)+CASE [type] WHEN 'datetime' THEN 'DateTime'
WHEN 'varchar' THEN 'String'
WHEN 'int' THEN 'Int32'
WHEN 'bit' THEN 'Boolean'
ELSE 'String' END--常用的几个数据类型,根据需要可扩充
+CHAR(32) FROM @t WHERE id=@i
SELECT @s=@s+ dbo.F_split([name]) --调用函数
FROM @t WHERE id=@i
SET @s=@s+@g+CHAR(10)+CHAR(10)
SET @s=@s+@a+CHAR(10)++'/// '
SET @i=@i+1
END
print @S
GO
--测试
EXEC C# 'moa_news'
--结果/// </summary>
/// 是否提醒1为提醒,0为不提醒,默认0
/// </summary>
public Boolean IsRemind { get; set; }/// </summary>
/// 是否置顶1为置顶,0为不置顶,默认0
/// </summary>
public Boolean IsTop { get; set; }/// </summary>
/// 是否可见1为可见0为不可见,默认可见
/// </summary>
public Boolean IsVisible { get; set; }/// </summary>
/// 新闻发布人
/// </summary>
public String NewsAuthor { get; set; }/// </summary>
/// 新闻内容
/// </summary>
public String NewsContent { get; set; }/// </summary>
/// 新闻发布时间
/// </summary>
public DateTime NewsCreateDate { get; set; }/// </summary>
/// 附件地址
/// </summary>
public String NewsEnclosurePath { get; set; }/// </summary>
/// 附件名称
/// </summary>
public String NewsEnclosureTitle { get; set; }/// </summary>
/// 新闻生效时间
/// </summary>
public DateTime NewsFromDate { get; set; }/// </summary>
/// 新闻编号
/// </summary>
public Int32 NewsId { get; set; }/// </summary>
/// 可阅读该新闻的机构列表,以“,”分隔
/// </summary>
public String NewsOrganiztionList { get; set; }/// </summary>
/// 可阅读该新闻的角色列表,以“,”分隔
/// </summary>
public String NewsRoleList { get; set; }/// </summary>
/// 新闻标题
/// </summary>
public String NewsTitle { get; set; }/// </summary>
/// 新闻失效时间
/// </summary>
public DateTime NewsToDate { get; set; }/// </summary>
///
/// </summary>
public Int32 NewsType { get; set; }/// </summary>
/// 可阅读该新闻的人员列表,以“,”分隔
/// </summary>
public String NewsUserList { get; set; }
解决方案 »
- 为什么循环不终止呢?
- 只有资深C#专家才能解答的问题。在线等。分不是问题,问题是要回答准确。
- VC到C#,变量到对象。。
- NavigateUrl链接格式问题
- .net2.0里membership权限问题
- Team Foundation Serve. check in 出错,提示:无法将数据写入传输连接:句柄无效
- asp.net 1.1中能不能实现点击关闭网页页面?
- 请间怎样用C#读取unicode文件
- 关于checkbox的问题请教
- 求一个SQL存储过程
- 大家看看这段代码怎么改!急!怎么把“categoryID”传给页面
- 急,急,未处理的“System.Data.SqlClient.SqlException”类型的异常出现在 system.data.dll 中。
这个完全是自己写这个玩的,没啥优势
GO
CREATE TABLE tb(id VARCHAR(3),pid VARCHAR(3),[name] VARCHAR(10))
GO
INSERT INTO tb SELECT '001',NULL,'河南省'
UNION ALL SELECT '002','001','洛阳市'
UNION ALL SELECT '003','001','新乡市'
UNION ALL SELECT '004','002','栾川县'
UNION ALL SELECT '005','003','长垣县'
UNION ALL SELECT '006','002','孟津县'
UNION ALL SELECT '007','004','冷水乡'
UNION ALL SELECT '008','004','叫河乡'
UNION ALL SELECT '009','008','A村'
UNION ALL SELECT '010','008','B村'
GO
/*
1、查询指定节点及其所有子节点
--如:已知 栾川县
--得到结果id pid name
---- ---- ----------
004 002 栾川县
007 004 冷水乡
008 004 叫河乡
009 008 A村
010 008 B村
*/
--*************************查询开始*********************
--SQL2005 使用CTE
DECLARE @ VARCHAR(10)
SET @='栾川县'
;WITH XiaoAi AS
(
SELECT id FROM tb WHERE [name]=@
UNION ALL
SELECT t.id FROM XiaoAi AS a INNER JOIN tb AS t ON a.id=t.pid
)
SELECT t.* FROM XiaoAi AS a LEFT JOIN tb AS t ON t.id=a.id--SQL 2000 使用函数
IF OBJECT_ID('dbo.XiaoAi') IS NOT NULL DROP FUNCTION dbo.XiaoAi
GO
CREATE FUNCTION dbo.XiaoAi(@ VARCHAR(20))
RETURNS @t TABLE(id VARCHAR(3), pid VARCHAR(3), [name] VARCHAR(20),Level INT)
AS
BEGIN
DECLARE @level INT
SET @level=1
INSERT INTO @t SELECT *,@level FROM tb WHERE [name]=@
WHILE(@@ROWCOUNT>0)
BEGIN
SET @level=@level+1
INSERT INTO @t SELECT t.*,@level FROM tb AS t,@t AS a WHERE a.id=t.pid AND a.level=@level-1
END
RETURN
END
GO
--调用函数
SELECT id, pid,[name] from dbo.XiaoAi('栾川县')
--****************************查询结束*************************************
/*
2、查询指定节点及其所有父节点
如:已知 栾川县
得到以下结果
id pid name
---- ---- ----------
001 NULL 河南省
002 001 洛阳市
004 002 栾川县
*/
----------------------测试开始-------------------------------------------------
DECLARE @s VARCHAR(10)
SET @s='栾川县'
SELECT id, pid,[name] INTO # FROM tb WHERE [name]=@s
WHILE @@ROWCOUNT>0
BEGIN
INSERT INTO # SELECT t.id, t.pid,t.[name] FROM tb AS t
INNER JOIN # AS a ON t.id=a.pid AND t.id NOT IN(SELECT ID FROM #)
END
SELECT * FROM # ORDER BY ID
-----------------------测试结束-------------------------------------------------