代码生成器增加几个生成模式
1 JSON转表
2 DAL中间层类
3 VFP前端
其中中间层类搭配VFP前端可以实现中间层模式下的一键SAVE
VFP类增加了qiyu_image, qiyu_treeview类
qiyu_image实现在GRID显示图片
qiyu_treeview实现了无限级类
qiyu_pagination实现了分页
数据表结构
代码语言：javascript复制/*
Navicat Premium Data Transfer
Source Server : 127.0.0.1
Source Server Type : SQL Server
Source Server Version : 10501600
Source Host : 127.0.0.1,3300:1433
Source Catalog : qiyutest
Source Schema : dbo
Target Server Type : SQL Server
Target Server Version : 10501600
File Encoding : 65001
Date: 16/11/2023 10:00:08
*/
-- ----------------------------
-- Table structure for pagedata
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[pagedata]') AND type IN ('U'))
DROP TABLE [dbo].[pagedata]
GO
CREATE TABLE [dbo].[pagedata] (
[id] int IDENTITY(1,1) NOT NULL,
[name] varchar(50) COLLATE Chinese_PRC_CI_AS NOT NULL
)
GO
ALTER TABLE [dbo].[pagedata] SET (LOCK_ESCALATION = TABLE)
GO
-- ----------------------------
-- Records of pagedata
-- ----------------------------
SET IDENTITY_INSERT [dbo].[pagedata] ON
GO
INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'1', N'1')
GO
INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'2', N'2')
GO
INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'3', N'3')
GO
INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'4', N'4')
GO
INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'5', N'5')
GO
INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'6', N'6')
GO
INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'7', N'7')
GO
INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'8', N'8')
GO
INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'9', N'9')
GO
INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'10', N'10')
GO
INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'11', N'11')
GO
INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'12', N'12')
GO
INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'13', N'13')
GO
INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'14', N'14')
GO
INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'15', N'15')
GO
INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'16', N'16')
GO
INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'18', N'17')
GO
INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'19', N'18')
GO
INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'20', N'1')
GO
INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'21', N'1')
GO
INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'22', N'1')
GO
INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'23', N'1')
GO
INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'24', N'1')
GO
INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'25', N'1')
GO
INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'26', N'1')
GO
INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'27', N'1')
GO
INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'28', N'1')
GO
INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'29', N'1')
GO
INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'30', N'1')
GO
INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'31', N'1')
GO
INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'32', N'1')
GO
INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'33', N'1')
GO
INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'34', N'1')
GO
INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'35', N'1')
GO
INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'36', N'1')
GO
INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'37', N'1')
GO
INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'38', N'1')
GO
INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'39', N'1')
GO
INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'40', N'1')
GO
INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'41', N'1')
GO
INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'42', N'1')
GO
INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'43', N'1')
GO
INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'44', N'1')
GO
INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'45', N'1')
GO
INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'46', N'1')
GO
INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'47', N'1')
GO
INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'48', N'1')
GO
INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'49', N'1')
GO
INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'50', N'1')
GO
INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'51', N'1')
GO
INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'52', N'1')
GO
INSERT INTO [dbo].[pagedata] ([id], [name]) VALUES (N'53', N'1')
GO
SET IDENTITY_INSERT [dbo].[pagedata] OFF
GO
-- ----------------------------
-- Table structure for treedata
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[treedata]') AND type IN ('U'))
DROP TABLE [dbo].[treedata]
GO
CREATE TABLE [dbo].[treedata] (
[id] int IDENTITY(1,1) NOT NULL,
[name] varchar(50) COLLATE Chinese_PRC_CI_AS NOT NULL,
[fid] int NULL
)
GO
ALTER TABLE [dbo].[treedata] SET (LOCK_ESCALATION = TABLE)
GO
-- ----------------------------
-- Records of treedata
-- ----------------------------
SET IDENTITY_INSERT [dbo].[treedata] ON
GO
INSERT INTO [dbo].[treedata] ([id], [name], [fid]) VALUES (N'1', N'ttt ', N'0')
GO
INSERT INTO [dbo].[treedata] ([id], [name], [fid]) VALUES (N'2', N'12312 ', N'1')
GO
INSERT INTO [dbo].[treedata] ([id], [name], [fid]) VALUES (N'5', N'测试', N'2')
GO
INSERT INTO [dbo].[treedata] ([id], [name], [fid]) VALUES (N'6', N'我是子烊', N'5')
GO
SET IDENTITY_INSERT [dbo].[treedata] OFF
GO
-- ----------------------------
-- Table structure for treedata1
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[treedata1]') AND type IN ('U'))
DROP TABLE [dbo].[treedata1]
GO
CREATE TABLE [dbo].[treedata1] (
[id] int IDENTITY(1,1) NOT NULL,
[name] varchar(50) COLLATE Chinese_PRC_CI_AS NOT NULL
)
GO
ALTER TABLE [dbo].[treedata1] SET (LOCK_ESCALATION = TABLE)
GO
-- ----------------------------
-- Records of treedata1
-- ----------------------------
SET IDENTITY_INSERT [dbo].[treedata1] ON
GO
INSERT INTO [dbo].[treedata1] ([id], [name]) VALUES (N'1', N'6677 ')
GO
INSERT INTO [dbo].[treedata1] ([id], [name]) VALUES (N'2', N'687 ')
GO
INSERT INTO [dbo].[treedata1] ([id], [name]) VALUES (N'3', N'8909 ')
GO
SET IDENTITY_INSERT [dbo].[treedata1] OFF
GO
-- ----------------------------
-- Table structure for treepathdata
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[treepathdata]') AND type IN ('U'))
DROP TABLE [dbo].[treepathdata]
GO
CREATE TABLE [dbo].[treepathdata] (
[id] int IDENTITY(1,1) NOT NULL,
[name] varchar(50) COLLATE Chinese_PRC_CI_AS NOT NULL,
[path] varchar(250) COLLATE Chinese_PRC_CI_AS NOT NULL,
[fid] int NULL
)
GO
ALTER TABLE [dbo].[treepathdata] SET (LOCK_ESCALATION = TABLE)
GO
-- ----------------------------
-- Records of treepathdata
-- ----------------------------
SET IDENTITY_INSERT [dbo].[treepathdata] ON
GO
SET IDENTITY_INSERT [dbo].[treepathdata] OFF
GO
-- ----------------------------
-- procedure structure for SelectBase1
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[SelectBase1]') AND type IN ('FN', 'FS', 'FT', 'IF', 'TF'))
DROP PROCEDURE[dbo].[SelectBase1]
GO
CREATE PROCEDURE [dbo].[SelectBase1]
@PageIndex int,
@PageSize int,
@TableName nvarchar(4000),
@Where nvarchar(2000)='',
@rowcount int output
as
Declare @intStart int
Declare @intEnd int
declare @Column1 varchar(32)
Declare @Sql nvarchar(2000), @WhereR nvarchar(1000), @OrderBy nvarchar(1000)
set @rowcount=0
set nocount on
if CHARINDEX('order by', @Where)>0
begin
set @WhereR=substring(@Where, 1, CHARINDEX('order by',@Where)-1) --取得条件
set @OrderBy=substring(@Where, CHARINDEX('order by',@Where), Len(@Where)) --取得排序方式(order by 字段 方式)
if @WhereR<>''
begin
set @WhereR=' and ' @WhereR
end
end
else
begin
set @WhereR=@Where
if @WhereR<>''
begin
set @WhereR=' and ' @WhereR
end
set @OrderBy=''
end
if @WhereR<>'' --说明有Where
begin
set @Where=' and ' @Where
end
print 'where处理完成' @Where
print @WhereR
print @OrderBy
set @Sql='SELECT @rowcount=count(*) from ' cast(@TableName as varchar(4000)) ' where 1=1 ' @WhereR
exec sp_executeSql @Sql,N'@rowcount int output',@rowcount output
print '获取总行数' @Sql
print @rowcount
if @PageIndex=0 and @PageSize=0 --不进行分页,查询所有数据列表
begin
set @Sql='SELECT * from ' cast(@TableName as varchar(4000)) ' where 1=1 ' @Where
end
else --进行分页查询数据列表
print '开始分页查询'
begin
set @intStart=(@PageIndex-1)*@PageSize 1;
set @intEnd=@intStart @PageSize-1
set @Column1=col_name(object_id(@tableName),1)
if @Column1 is null begin set @Column1='ID' end
set @Sql='Create table #tem(tempID int identity(1,1) not null,Row Varchar(100)) '
set @Sql=@Sql 'insert #tem(Row) select ' @Column1 ' from ' @TableName ' where 1=1 ' @Where
set @Sql=@Sql ' select * from ' @TableName ' left join #tem on ' @Column1 '=#tem.Row '
set @Sql=@Sql ' where #tem.tempID between ' cast(@intStart as varchar) ' and ' cast(@intEnd as varchar) ' Order by tempID'
end
print @Sql
exec sp_executeSql @Sql
--return @rowcount
set nocount off
GO
-- ----------------------------
-- procedure structure for SelectBase
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[SelectBase]') AND type IN ('FN', 'FS', 'FT', 'IF', 'TF'))
DROP PROCEDURE[dbo].[SelectBase]
GO
CREATE PROCEDURE [dbo].[SelectBase]
@PageIndex int,
@PageSize int,
@TableName nvarchar(4000),
@Where nvarchar(2000)='',
@rowcount int output
as
Declare @intStart int
Declare @intEnd int
declare @Column1 varchar(32)
Declare @Sql nvarchar(2000), @WhereR nvarchar(1000), @OrderBy nvarchar(1000)
set @rowcount=0
set nocount on
if CHARINDEX('order by', @Where)>0
begin
set @WhereR=substring(@Where, 1, CHARINDEX('order by',@Where)-1) --取得条件
set @OrderBy=substring(@Where, CHARINDEX('order by',@Where), Len(@Where)) --取得排序方式(order by 字段 方式)
if @WhereR<>''
begin
set @WhereR=' and ' @WhereR
end
end
else
begin
set @WhereR=@Where
if @WhereR<>''
begin
set @WhereR=' and ' @WhereR
end
set @OrderBy=''
end
if @WhereR<>'' --说明有Where
begin
set @Where=' and ' @Where
end
print 'where处理完成' @Where
print @WhereR
print @OrderBy
set @Sql='SELECT @rowcount=count(*) from ' cast(@TableName as varchar(4000)) ' where 1=1 ' @WhereR
exec sp_executeSql @Sql,N'@rowcount int output',@rowcount output
print '获取总行数' @Sql
print @rowcount
if @PageIndex=0 and @PageSize=0 --不进行分页,查询所有数据列表
begin
set @Sql='SELECT * from ' cast(@TableName as varchar(4000)) ' where 1=1 ' @Where
end
else --进行分页查询数据列表
print '开始分页查询'
begin
set @intStart=(@PageIndex-1)*@PageSize 1;
set @intEnd=@intStart @PageSize-1
set @Column1=col_name(object_id(@tableName),1)
if @Column1 is null begin set @Column1='ID' end
set @Sql='Create table #tem(tempID int identity(1,1) not null,Row Varchar(100)) '
set @Sql=@Sql 'insert #tem(Row) select ' @Column1 ' from ' @TableName ' where 1=1 ' @Where
set @Sql=@Sql ' select * from ' @TableName ' left join #tem on ' @Column1 '=#tem.Row '
set @Sql=@Sql ' where #tem.tempID between ' cast(@intStart as varchar) ' and ' cast(@intEnd as varchar) ' Order by tempID'
end
print @Sql
exec sp_executeSql @Sql
--return @rowcount
set nocount off
GO
-- ----------------------------
-- Auto increment value for pagedata
-- ----------------------------
DBCC CHECKIDENT ('[dbo].[pagedata]', RESEED, 53)
GO
-- ----------------------------
-- Primary Key structure for table pagedata
-- ----------------------------
ALTER TABLE [dbo].[pagedata] ADD CONSTRAINT [PK_pagedata] PRIMARY KEY CLUSTERED ([id])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO
-- ----------------------------
-- Auto increment value for treedata
-- ----------------------------
DBCC CHECKIDENT ('[dbo].[treedata]', RESEED, 6)
GO
-- ----------------------------
-- Primary Key structure for table treedata
-- ----------------------------
ALTER TABLE [dbo].[treedata] ADD CONSTRAINT [PK_treedata] PRIMARY KEY CLUSTERED ([id])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO
-- ----------------------------
-- Auto increment value for treedata1
-- ----------------------------
DBCC CHECKIDENT ('[dbo].[treedata1]', RESEED, 3)
GO
-- ----------------------------
-- Primary Key structure for table treedata1
-- ----------------------------
ALTER TABLE [dbo].[treedata1] ADD CONSTRAINT [PK_treedata1] PRIMARY KEY CLUSTERED ([id])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO
-- ----------------------------
-- Auto increment value for treepathdata
-- ----------------------------
DBCC CHECKIDENT ('[dbo].[treepathdata]', RESEED, 1)
GO
-- ----------------------------
-- Primary Key structure for table treepathdata
-- ----------------------------
ALTER TABLE [dbo].[treepathdata] ADD CONSTRAINT [PK_treepathdata] PRIMARY KEY CLUSTERED ([id])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO
