Linq统一的SQL Getter[英] Unified SQL getter with LINQ

本文是小编为大家收集整理的关于Linq统一的SQL Getter的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

我得到了许多具有相同设计的SQL表格 - 所有标识和两个字符串字段具有相同的名称.我不想编写一组函数来从这些表中获取值,我想将表作为参数的一个过程.但是,当我开始检索数据时,它说"无法转换类型的bla-bla-bla".它需要直接通过的类型,这就是我要避免的.该怎么办?

/*
defined tables: 

create table tableA 
(
  id int identity not null,
  type_code nvarchar(50) not null,
  type_description nvarchar(1000) not null
)

same SQL for tableB and tableC

tableA, tableB, tableC
*/

void getAnyId( Table tbl, string codeFilter)
{
   var p=(tableA)tbl;   // HERE I GET EXCEPTION !!!
   var id = p.Where( r=> r.code == codeFilter);
   if( id.Count() != 1 )
       return null;
   return id.id;
}

另一个示例:

    public Dictionary<string,string> readDataSchemeTypes( tvbaseDataContext dc )
    {
        Dictionary<string,string> ds = new Dictionary<string,string>();

        foreach( var ast in dc.tableA)
            ds.Add( ast.type_code, ast.type_description );

        return ds;
    }

这起作用,但我需要一组功能,每个表都一个.

public Dictionary<string, string> readAnySchemeTypes<T>(System.Data.Linq.Table<T> table) where T:System.Data.Linq.ITable
{
    Dictionary<string, string> ds = new Dictionary<string, string>();

    foreach (var ast in table)
        ds.Add(ast.type_code, ast.type_description); // type_code and type_description are not defined for type T

    return ds;
}

此示例不编译.

推荐答案

第一个机会:您可以使用动态SQL传递每个查询的表名.但这应该很痛苦,因为您丢失了Linq的类型安全. 例如:

Create procedure s_ProcTable
@TableName varchar(128)
as

declare @sql varchar(4000)
    select @sql = 'select count(*) from [' + @TableName + ']'
    exec (@sql)
go

再次,请小心动态SQL.在运行

之前,您将看不到任何错误

第二次机会:让您的方法通用.因此,您只需要指定每个呼叫所需的类型,而不是重写整个方法所需的类型.

本文地址:https://www.itbaoku.cn/post/1557139.html

问题描述

I got many different SQL tables with the same design - all have identity and two string fields with the same names. I do not want to write a set of functions to get values from these tables, i want to have one procedure with the table as a parameter. But when i start to retrieve data it says "can not convert type bla-bla-bla". It needs the type to be directly passed and thats what i want to avoid. What to do?

/*
defined tables: 

create table tableA 
(
  id int identity not null,
  type_code nvarchar(50) not null,
  type_description nvarchar(1000) not null
)

same SQL for tableB and tableC

tableA, tableB, tableC
*/

void getAnyId( Table tbl, string codeFilter)
{
   var p=(tableA)tbl;   // HERE I GET EXCEPTION !!!
   var id = p.Where( r=> r.code == codeFilter);
   if( id.Count() != 1 )
       return null;
   return id.id;
}

Another example:

    public Dictionary<string,string> readDataSchemeTypes( tvbaseDataContext dc )
    {
        Dictionary<string,string> ds = new Dictionary<string,string>();

        foreach( var ast in dc.tableA)
            ds.Add( ast.type_code, ast.type_description );

        return ds;
    }

This works but i need a set of functions, one per each table.

public Dictionary<string, string> readAnySchemeTypes<T>(System.Data.Linq.Table<T> table) where T:System.Data.Linq.ITable
{
    Dictionary<string, string> ds = new Dictionary<string, string>();

    foreach (var ast in table)
        ds.Add(ast.type_code, ast.type_description); // type_code and type_description are not defined for type T

    return ds;
}

This example doesn't compile.

推荐答案

First chance: you can use dynamic SQL to pass table name for each query. But it should be painful because you loss the type-safe of LINQ. For example:

Create procedure s_ProcTable
@TableName varchar(128)
as

declare @sql varchar(4000)
    select @sql = 'select count(*) from [' + @TableName + ']'
    exec (@sql)
go

Once again, be careful with Dynamic SQL. You cannot see any error until you run it

Second chance: let your method generic. So you just have to specify the type needed for each call, not necessary to re-write the whole method.