【.NET】通过传入表名操作数据库

两种思路：

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数组，其他方法在之后实际使用后做进一步补充