C# query using a generic Type

Question

I am trying to make a generic function to do a database call (see code below). I have put the function in a separate solution, so that i can use it in different projects.

the line:

var data =  d.Database.SqlQuery<T> (sql).First();

gives me the error:

Invalid object name 'VM_MailData'

(VM_MailData is the type I add as generic type T)

public static void ProcessData<T>(string Group, int Id)
{
   string ConnectionString = "SomeConnectionStringName";
   string sql = "select top 1 * from " + (typeof (T).Name) + " where " + Group + "Id = " + Id + ";";             
   DbContext d = new DbContext(ConnectionString);
   var data =  d.Database.SqlQuery<T> (sql).First();
   //Do some stuff with the data...
   html = "some tekst...";
   foreach (var sourceProperty in data.GetType().GetProperties())
   {
    html = html.Replace("{#" + sourceProperty.Name + "#}",    sourceProperty.GetValue(data, new object[] { }) == null ? "" : sourceProperty.GetValue(data, new object[] { }).ToString());
    //enter code here

   }
}

Does the table exist in the database? Is the naming convention case sensitive? — Icepickle
– Icepickle, Commented Jan 22, 2016 at 11:21
Where does Group come from, and do you know about SQL Injection? — James Thorpe
– James Thorpe, Commented Jan 22, 2016 at 11:21
icepickle The doesnt reach the database, but the sql is correct and the query does return a record. james-thorpe SQL injection is no issue this is only a simle code example the real code deals with all of these problems. — Martijn
– Martijn, Commented Jan 22, 2016 at 11:26
"Invalid object name 'VM_MailData'" would seem to indicate that that table/view/whatever doesn't exist in your database. Are you sure that your tables/views/whatevers exactly match your type names? — James Thorpe
– James Thorpe, Commented Jan 22, 2016 at 11:28

Alex Art. · Accepted Answer · 2016-01-22 12:08:11Z

1

You most probably need to call ProcessData<T>(string Group, int Id) with some base type of VM_MailData. I assume that VM stands for "view model" and you have something like

public class MailData
{
} 

public class VM_MailData : MailData
{
}

Where MailData class is actually the one that has a corresponding table. So instead of calling ProcessData<VM_MailData >("some group", 1) you need to call ProcessData<MailData>("some group", 1).

P.S. You really should use parameterized queries to avoid SQL Injection!

answered Jan 22, 2016 at 12:08

Alex Art.

8,7813 gold badges32 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mihail Shishkov · Accepted Answer · 2016-01-22 11:39:20Z

I can see where you're heading :) so... You have to create a map (dictionary) that maps from Type to appropriate table name. You can built that using some kind of convention based on the name of the type or the tables.

Use those to get available tables and views in the dabatase

SELECT SCHEMA_NAME(schema_id) as SchemaName, name as Name FROM sys.tables
SELECT SCHEMA_NAME(schema_id) as SchemaName, name as Name FROM sys.views

You can use the following script to get the primary key column of the table.

DECLARE @origin_table_name AS VARCHAR(50)

SET @origin_table_name = 'Your_table_name_goes_here'

    SELECT
        s.name AS TABLE_SCHEMA
      , t.name AS TABLE_NAME
      , c.name AS COLUMN_NAME
      , k.name AS CONSTRAINT_NAME
      , ic.key_ordinal AS ORDINAL_POSITION
    FROM
        sys.key_constraints AS k
    JOIN sys.tables AS t ON t.object_id = k.parent_object_id
    JOIN sys.schemas AS s ON s.schema_id = t.schema_id
    JOIN sys.index_columns AS ic ON ic.object_id = t.object_id
                                    AND ic.index_id = k.unique_index_id
    JOIN sys.columns AS c ON c.object_id = t.object_id
                             AND c.column_id = ic.column_id
    WHERE
        k.type = 'PK'
        AND t.name = @origin_table_name

Good luck

Collectives™ on Stack Overflow

C# query using a generic Type

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related