asked on

ASP.NET - How to read an SQL View into a sqlDataReader using a sqlCommand object

I have created a view in an MS SQL v7 DB and would like to databind the contents to a list on a webform.

The code below works fine for a StoredProcedure but I want to know how to do it for a view.

Can it be done with a sqlDataReader and an sqlCommand ?

SAMPLE CODE
-----------
SqlConnection1.Open()
SqlCommand1.CommandType=System.Data.CommandType.StoredProcedure
SqlCommand1.CommandText = "vEmployees"
objReader = SqlCommand1.ExecuteReader()
lbxEmployees.DataTextField = "Name"
lbxEmployees.DataValueField = "ID"
lbxEmployees.DataSource = objReader
lbxEmployees.DataBind()
objReader.Close()
SqlConnection1.Close()

apollois

I can't give you the exact syntax for ASP.NET, but treat a view exactly the same as you would a table -- just use the view name like a table name.

HTH.

Best Regards,
apollois

ASKER CERTIFIED SOLUTION

Ralf Klatt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

colje

ASKER

Thanks Raiser,

Your sample shows how to use parameters in Stored procs but otherwise is the same as the code snippet I included.

As apollois says views are treated as tables so I get an error with the code I included above.

There are work arounds such as using a stored proc instead of a view or running a select on the view but I am hoping there is simple way to bind a table or view to a list.(without using a DataAdapter)

colje

ASKER

I found that "CommandType.TableDirect" is for returning tables / views. But TableDirect is not supported by the native .NET SQL connection which is why it didn't work for me. You need to use oleDB.

If you would like to post some comments on this I will "Accept as answer" to close this question.

Thanks Apollois and Raiser.

Ralf Klatt

Hi Colje,

... I see what you mean! ... it's all a question of fitting your view into a stored procedure ... can you give any further explanation of what's your intention pointing to a view instead of pointing to sproc?

Best regards, Raisor

colje

ASKER

Raisor,

I am using an OLAP reporting tool (BRIO) to report on the data. With a view I can hide a lot of complex sql (joins / sub-queries) and the view appears in the table list in BRIO like a simple table.

To solve the problem I can use a Stored Proc as well as a view or use an OleDB connection.

Thanks for your continued feedback. Hopefully I can return the favor one day.

Colje.

Ralf Klatt

Hi Colje,

Well, it seems you're closing the circle ... when you've said "you would like to databind the contents to a list on a webform" I thought that best way to it is the way I've shown you in an earlier article (see the example above) ... I don't really see the point in trying to databind a view knowing that a view is nothing else than a complex sql statement (which is the same with sproc) ... there are various possibilities to OUTPUT structured data drom a db through a sproc and bind it to a sql datareader ... a view lines of code and the datareader is presented by a dataview

... but anyway, you'll have to find a way that eases you the most ... me, I didn't work with views since years!

Best regards, Raisor

colje

ASKER

Thanks Raisor

colje

ASKER

The code extract provided by Raisor demonstrates the use of stored procedures with parameters and databinding.

However, note that the TableDirect CommandType returns the table/view to the datareader for an OLEDB connection but TableDirect is not supported by the native .NET SQL connection (SQLConnection). So for SQLConnection you need do a select on the view as a stored proc or pass SQL to the command object using the Text commandType.