?
Solved

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

Posted on 2003-02-18
9
Medium Priority
?
732 Views
Last Modified: 2012-06-21
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()
0
Comment
Question by:colje
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
9 Comments
 
LVL 10

Expert Comment

by:apollois
ID: 7979834
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
0
 
LVL 15

Accepted Solution

by:
Ralf Klatt earned 750 total points
ID: 7981333
Hi Colje,

I think you can get an idea of how to implement sprocs to use with a datalist by analysing the folowing extract from the ASPNetPortal application!

Best regards, Raisor

Create a vb.class with the following (ex.:) function:

        Public Function GetTopLevelMessages(ByVal moduleId As Integer) As SqlDataReader

            ' Create Instance of Connection and Command Object
            Dim myConnection As New SqlConnection(ConfigurationSettings.AppSettings("connectionString"))
            Dim myCommand As New SqlCommand("GetTopLevelMessages", myConnection)

            ' Mark the Command as a SPROC
            myCommand.CommandType = CommandType.StoredProcedure

            ' Add Parameters to SPROC
            Dim parameterModuleId As New SqlParameter("@ModuleId", SqlDbType.Int, 4)
            parameterModuleId.Value = moduleId
            myCommand.Parameters.Add(parameterModuleId)

            ' Execute the command
            myConnection.Open()
            Dim result As SqlDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection)

            ' Return the datareader
            Return result

        End Function


Call this function from your aspx.vb file and bind it the following (ex.:) way:

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

            If Page.IsPostBack = False Then
                BindList()
            End If

        End Sub

Sub BindList()

            ' Obtain a list of discussion messages for the module
            ' and bind to datalist
            Dim discuss As New ASPNetPortal.DiscussionDB()

            TopLevelList.DataSource = discuss.GetTopLevelMessages(ModuleId)
            TopLevelList.DataBind()

        End Sub

The (ex.:) presentation interface could look like this:

<%-- discussion list --%>
<asp:DataList id="TopLevelList" width="98%" ItemStyle-Cssclass="Normal" DataKeyField="Parent" runat="server">
      <ItemTemplate>
            <asp:ImageButton id="btnSelect" ImageUrl='<%# NodeImage(Cint(DataBinder.Eval(Container.DataItem, "ChildCount"))) %>' CommandName="select" runat="server" />
            <asp:hyperlink Text='<%# DataBinder.Eval(Container.DataItem, "Title") %>' NavigateUrl='<%# FormatUrl(CInt(DataBinder.Eval(Container.DataItem, "ItemID"))) %>' Target="_new" runat="server" />,
            from
            <%# DataBinder.Eval(Container.DataItem,"CreatedByUser") %>
            , posted
            <%# DataBinder.Eval(Container.DataItem,"CreatedDate", "{0:g}") %>
      </ItemTemplate>
      <SelectedItemTemplate>
            <asp:ImageButton id="btnCollapse" ImageUrl="~/images/minus.gif" runat="server" CommandName="collapse" />
            <asp:hyperlink Text='<%# DataBinder.Eval(Container.DataItem, "Title") %>' NavigateUrl='<%# FormatUrl(CInt(DataBinder.Eval(Container.DataItem, "ItemID"))) %>' Target="_new" runat="server" />,
            from
            <%# DataBinder.Eval(Container.DataItem,"CreatedByUser") %>
            , posted
            <%# DataBinder.Eval(Container.DataItem,"CreatedDate", "{0:g}") %>
            <asp:DataList id="DetailList" ItemStyle-Cssclass="Normal" datasource="<%# GetThreadMessages() %>" runat="server">
                  <ItemTemplate>
                        <%# DataBinder.Eval(Container.DataItem, "Indent") %>
                        <img src="<%=Request.ApplicationPath%>/images/1x1.gif" height="15">
                        <asp:hyperlink Text='<%# DataBinder.Eval(Container.DataItem, "Title") %>' NavigateUrl='<%# FormatUrl(CInt(DataBinder.Eval(Container.DataItem, "ItemID"))) %>' Target="_new" runat="server" />,
                        from
                        <%# DataBinder.Eval(Container.DataItem,"CreatedByUser") %>
                        , posted
                        <%# DataBinder.Eval(Container.DataItem,"CreatedDate", "{0:g}") %>
                  </ItemTemplate>
            </asp:DataList>
      </SelectedItemTemplate>
</asp:DataList>
0
 
LVL 2

Author Comment

by:colje
ID: 7984545
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)
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 2

Author Comment

by:colje
ID: 7985084
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.
0
 
LVL 15

Expert Comment

by:Ralf Klatt
ID: 7989437
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
0
 
LVL 2

Author Comment

by:colje
ID: 8006081
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.
0
 
LVL 15

Expert Comment

by:Ralf Klatt
ID: 8006771
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
0
 
LVL 2

Author Comment

by:colje
ID: 8013510
Thanks Raisor
0
 
LVL 2

Author Comment

by:colje
ID: 8013561
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.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question