Avatar of colje
colje
 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()
ASP

Avatar of undefined
Last Comment
colje

8/22/2022 - Mon
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

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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.
Your help has saved me hundreds of hours of internet surfing.
fblack61
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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
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.