CODEWITHSUNDEEP
c#sqlsql-server
Exitos
Exitos

Reputation: 29720

How do I encapsulate this data from sql using c#?

I have the script:

select
    fk.name,
    object_name(fk.parent_object_id) 'Parent table',
    c1.name 'Parent column',
    object_name(fk.referenced_object_id) 'Referenced table',
    c2.name 'Referenced column'
from 
    sys.foreign_keys fk
inner join
    sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
inner join
    sys.columns c1 ON fkc.parent_column_id = c1.column_id and c1.object_id = fkc.parent_object_id
inner join
    sys.columns c2 ON fkc.referenced_column_id = c2.column_id and c2.object_id = fkc.referenced_object_id

And I know that I get a result set back with 5 columns. Is there is a slick and efficient way to store this data in a linq type object or an iQueryable object? I want to be able to iterate through it...

Upvotes: 0

Views: 528

Answers (4)

Nps
Nps

Reputation: 1658

You can store this into DataTable. You have add assembly System.Data.Exetensions in your project to used DataTable as IQueryable.

from tbl in dataTable.AsEnumerable()
//where clause
select tbl;

DataView view = tbl.AsDataView();

Upvotes: 0

Andomar
Andomar

Reputation: 238246

For a very lightweight ORM, you can use the DataContext's ExecuteQuery:

class YourRow
{
    public string Col1 { get; set; }
    public string Col2 { get; set; }
    ...
}

// DataContext takes a connection string as parameter
var db = new DataContext("Data Source=myServerAddress;" +
    "Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;");
var rows = db.ExecuteQuery<YourRow>(@"
    select  fk.name,
            object_name(fk.parent_object_id) 'Parent table',
            ...
    ");

If you can store your SQL query in a view, you can drag the view to a DBML file to have LINQ create the wrapper class for you.

Upvotes: 1

Wouter de Kort
Wouter de Kort

Reputation: 39898

If you execute this script by using a DbCommand and a DataReader you can iterate over the rows that are returned and add them to a list of a custom object that will hold the 5 columns.

You can then use Linq To Objects to filter the list even more.

Or you can use an ORM to map your entities to the database. If you create a Database View for your query you can map an entity to the view with for example the Entity Framework and use that to execute further queries.

Upvotes: 0

jrummell
jrummell

Reputation: 43087

There are a few ORMs that use IQueryable: Entity Framework, LINQ to SQL, NHibernate, Subsonic, etc. I recommend trying one out.

Upvotes: 1

Related Questions