两种思路:
1.通过SQL语句查询到表名下的列名,将通过程序获取列名为string集合(通过SQL获取查询结果的DataSet或DataTable),遍历集合拼接SQL语句
获取列名SQL:
SELECT
Columns = CONVERT (VARCHAR(100), a.name),
TableName = CONVERT (VARCHAR(50), d.name),
FieldType = CONVERT (VARCHAR(50), b.name),
DataBaseName = 'dbo',
Trig = CONVERT (VARCHAR(50), h.name),
remark = CONVERT (
VARCHAR (50),
isnull(g.[value], '')
)
FROM
dbo.syscolumns a
LEFT JOIN dbo.systypes b ON a.xusertype = b.xusertype
INNER JOIN dbo.sysobjects d ON a.id = d.id
AND d.xtype = 'U'
AND d.name <> 'dtproperties'
LEFT JOIN dbo.syscomments e ON a.cdefault = e.id
LEFT JOIN sys.extended_properties g ON a.id = g.major_id
AND a.colid = g.minor_id
LEFT JOIN sys.extended_properties f ON d.id = f.major_id
AND f.minor_id = 0
LEFT JOIN sysobjects h on a.id = h.parent_obj and h.xtype='TR'
WHERE
d.name = 'DataInput_Land'--设置查询的表名
过程
CREATE PROCEDURE [dbo].[getTableInfo]
@tableName VARCHAR(1000)
AS
BEGIN
SELECT
Columns = CONVERT (VARCHAR(100), a.name),
TableName = CONVERT (VARCHAR(50), d.name),
FieldType = CONVERT (VARCHAR(50), b.name),
DataBaseName = 'dbo',
Trig = CONVERT (VARCHAR(50), h.name),
remark = CONVERT (
VARCHAR (50),
isnull(g.[value], '')
)
FROM
dbo.syscolumns a
LEFT JOIN dbo.systypes b ON a.xusertype = b.xusertype
INNER JOIN dbo.sysobjects d ON a.id = d.id
AND d.xtype = 'U'
AND d.name <> 'dtproperties'
LEFT JOIN dbo.syscomments e ON a.cdefault = e.id
LEFT JOIN sys.extended_properties g ON a.id = g.major_id
AND a.colid = g.minor_id
LEFT JOIN sys.extended_properties f ON d.id = f.major_id
AND f.minor_id = 0
LEFT JOIN sysobjects h on a.id = h.parent_obj and h.xtype='TR'
WHERE
d.name = @tableName--设置查询的表名
RETURN
END
2.通过获取程序集列表,查找获取model程序集,通过“程序集名.类名”查找并获取Type对象,做需要的操作(通过字段名拼接SQL字符串操作)
程序代码:
//以下代码可添加在webapi或方法中需要的位置
Assembly assembly = null;
//获取程序集数组
Assembly[] assemblyArray = AppDomain.CurrentDomain.GetAssemblies();
//查找MODEL程序集,查找到后获取程序集并跳出
foreach (Assembly item in assemblyArray) {
string s = item.FullName;
if (item.GetName().ToString().Contains("RisunFactoryLibrary")) {
assembly = item;
break;
}
}
//通过 程序集名.类名 获取Type对象
Type temp = assembly.GetType("RisunFactoryLibrary." + TableName,true,true);
//通过Type对象获取类名
string name = temp.Name;
//通过Type对象获取类字段集合(公共属性)
var myField = temp.GetProperties().ToList();
//遍历类字段集合
foreach (var item in myField) {
//业务逻辑
}
需要注意的是通过程序集获取的方法首先model类和数据库需一一对应,其次其中 GetProperties方法只能获取该类型的公共属性(public)如果没有公共属性成员则返回空的Type数组,其他方法在之后实际使用后做进一步补充