display View output in DataGridView

[viepia]

Hi,
How do I go from a text file containing:

CREATE VIEW MyView
AS
SELECT * FROM MyTable

where my VS2008 LINQ application knows the database MyView is targeting ==> to a
DataGridView.DataSource? The database had dozens of tables; my application needs to
handle any VIEW possible; in use there will be hundreds of these text file VIEWs.
I don't want to do any more parsing of the text files than necessary.

One way I thought to do this is to install the VIEW selected by the user is by ALTER
VIEWing a fixed name VIEW already installed on SQL Server 2005. My application would have
a fixed name UDF dragged onto the DBML referencing the fixed name VIEW; the UDF is set as
the DataSource for a DataGridView The problem with this is the early binding done by the
MSLinqToSQLGenerator when an UDF is dragged to the DBML: XML in the DBML file and C# code
in MyDatabaseClasses.designer.cs that sees through the UDF and the View to the underlying
MyTable. Even though there is no change in its code ALTER FUNCTION also has to be run
for the UDF. For this to work I need to find out to dynamically delete/drag the UDF
from/to the DBML or find some other way to force an equivalent action, maybe by using the
MSLinqToSQLGenerator in runtime; ALTER VIEW and ALTER FUNCTION are easy with
SqlCommand.ExecuteNonQuery. I can dynamically refresh the DataGridView by setting
DataGridView.DataSource to null, then reattaching the UDF, then Refresh, DoEvents (or
maybe Refresh, DoEvents are all that is necessary, I can't know until I know how to
dynamically delete/drag the UDF).

Thanks,
Viepia

 

[Marc Gravell]

One way I thought to do this is to install the VIEW selected by the user is by ALTER

VIEWing a fixed name VIEW already installed on SQL Server 2005.

Changing one already installed would be very confusing (not to mention
"thread race"), and hard frmo a security perspective. It would be
better to create a temporary view for the user - or lose the "VIEW"
completely and just use the SELECT. But this isn't really LINQ -
you're juts talking raw commands now

the UDF is set as the DataSource for a DataGridView.

As you've found - this isn't going to work. LINQ (and other ORM tools)
demand the schema is well known.

Basically, you can't use LINQ for this. I recommend just SqlCommand
and the SELECT, since the VIEW and UDF aren't helping you at all. This
might be one of the rare occasions when I suggest a DataTable as the
data-store. I'm not a fan of DataTable, but it might do the job nicely
here...

Marc

 

[viepia]

Using a DataTable made it easy, thanks very much.
Viepia