none
How best to create a temporary view? RRS feed

  • Question

  • Hi all;

    I need to do the equivalent of creating a view where I can then run selects against that view. At present I create the view with each connection and then drop it before closing. Is there a better way to do this, both for a question of faster queries and where the drop occurs automatically?

    thanks - dave


    Very funny video - What's your Metaphor?
    Thursday, January 27, 2011 6:38 PM

All replies

  • Hi DavidThi808,

    Welcome to MSDN forums.

    A view is nothing but a virtual table, you can handle it as a table by Ado.net. I'd like to say you can use EF to handle views, here is a link:http://smehrozalam.wordpress.com/2009/08/12/entity-framework-creating-a-model-using-views-instead-of-tables/

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, January 28, 2011 8:33 AM
    Moderator
  • Hi;

    Thank you, that approach I know about. I was wondering if there is a way to create a temporary table that I can then run selects against.

    thanks - dave


    Very funny video - What's your Metaphor?
    Saturday, January 29, 2011 6:46 PM
  • Hi DavidThi808,

    My bad understanding. You can use LINQ to DataSet and you can refer here: http://msdn.microsoft.com/en-us/library/bb386921.aspx

    I'd like to say garbage collector will clear your temporary object.

    Thanks for understanding.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, February 1, 2011 7:48 AM
    Moderator
  • Hi Dave,

    Could you please tell us how is the problem now? 

    If you need any further assistance, please feel free to let us know.

    Have a nice weekend!


    Michael Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, February 4, 2011 3:20 AM
    Moderator
  • Hi;

    First off sorry for the delay - we've been pounded with a bunch of urgent issues (and a lot of snow too).

    I can't use DataTable or anything like that because our solution needs to run on Java as well as .NET. So we need standard SQL I think. Which leads to the question if I have an SQL statement like:

    SELECT LastName, TerritoryDescription FROM (Northwind.dbo.Employees INNER JOIN Northwind.dbo.EmployeeTerritories ON Northwind.dbo.Employees.EmployeeID = Northwind.dbo.EmployeeTerritories.EmployeeID) INNER JOIN Northwind.dbo.Territories ON Northwind.dbo.EmployeeTerritories.TerritoryID = Northwind.dbo.Territories.TerritoryID
    

    What about I do:

    select TerritoryDescription from (SELECT LastName, TerritoryDescription FROM (Northwind.dbo.Employees INNER JOIN Northwind.dbo.EmployeeTerritories ON Northwind.dbo.Employees.EmployeeID = Northwind.dbo.EmployeeTerritories.EmployeeID) INNER JOIN Northwind.dbo.Territories ON Northwind.dbo.EmployeeTerritories.TerritoryID = Northwind.dbo.Territories.TerritoryID) as xyz where LastName like 'B%' order by TerritoryDescription
    

    I can put my original select inside the parens and pull any set of tables from it along with any where & order by clause at the end.

    Should this work?

    thanks - dave


    Very funny video - What's your Metaphor?
    Wednesday, February 9, 2011 3:50 AM
  • Hi Dave,

    I think it is clear for me now. To be frank, I'm not familiar with JAVA, but I know it also supports List and .net also support List<object>.

    You should code your method to select the records out.

    By the way, If you want to let your code run in different platform, you should use WCF and communication with service.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, February 9, 2011 8:53 AM
    Moderator
  • Pulling a list won't work because I want to run a sql query against this temporary view.

    thanks - dave


    Very funny video - What's your Metaphor?
    Wednesday, February 9, 2011 5:29 PM
  • Hi Dave,

    Thanks for your feedback.

    In .NET you can use LINQ to query IEnumerable list, it may hard in Java. I'm afraid that I couldn't find a good way to handle your scenario.

    By the way, I am not sure about Store procedure or View in database can meet your needs.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, February 10, 2011 1:22 AM
    Moderator
  • Hi;

    There is a giant problem with your suggestion. If the first select returns 1 million rows, and the second select pulls 2 rows from the 1 million - your approach would require reading 1 million rows from the database into memory, then iterating through those rows.

    If I can make it all 1 select up on the database, then only 2 rows need to be read.

    thanks - dave


    Very funny video - What's your Metaphor?
    Thursday, February 10, 2011 3:45 PM
  • Hi Dave,

    My bad suggestion. I think you can just run your T-SQL in your database.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, February 15, 2011 1:55 AM
    Moderator