Start Free Trial
Come for the solution, stay for everything else.
Start Free Trial
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 ?
SqlCommand1.CommandText = "vEmployees"
objReader = SqlCommand1.ExecuteReader(
d = "ID"
lbxEmployees.DataSource = objReader
8/22/2022 - Mon
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.
ASKER CERTIFIED SOLUTION
Log in or sign up to see answer
Become an EE member today
7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
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.
See how we're fighting big data
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
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)
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.
... 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
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.
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
to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
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.
Plans and Pricing
Certified Expert Program
© 1996-2023 Experts Exchange, LLC. All rights reserved. Covered by US Patent