Unified SQL getter with LINQ

Question

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.

Selwin

can you post code for tableA class, do tableA/B/C classes have the same base class? – Shafer

Is tableA an actual CLR type? Do you need the typecast at all? – Oblong

You just can't make it with linq2sql, you can make it with casting your tables to IEnumerable, but you will lost performance. Better way is just copy this (same) code for different tables, because if you can see it the same it really isn't the same, every "same" field isn't the same in sql. – Dolph

Added SQL definition and another sample code. – Selwin

Answer 1

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.

Stardom

you can use dynamic SQL to pass table name for each query. - Can you please post sample code describing what you mean? – Selwin

I added an example, you then can call this stored procedure with table name as parameter. Or you can use ad-hoc query, not stored procedure – Stardom

The only addition, you have to add the string SET FMTONLY OFF; into stored procedure body, or LINQ would miss return type to integer. – Selwin