Need to Get Data From Directly a DataSource So That It Can Use In Code

[MxWade]

Hello,

On many occasions it would be very handy to be able to get data directly from an SQL datasource so that I can manipulate it with VB code in the code behind. My ASP text books mention that the data is stored in a dataset but they do not give any instruction on how to identify the dataset that it is stored in or how to retrieve the data directly from the datasource.

I would appreciate any help on how this can be done.

Thanks

 

[jbenson001]

A dataset is a collection of datatables. For example, if you run a stored procedure tha returns 2 result sets, then your dataset would have 2 datatables, datatable(0) and datatable(1). Most of the time you will only be returning one result set. From there if you need to, you can loop through the rows of the datatable and maniupulate the data as needed. This really should be done on the database side, in SQL.

 

[MxWade]

Thanks for the answer, it helps but I do not think that I understand what I need to know.

Let's say that I have a datasource called DataSource1 which contains a select statement. To make this easy, lets say that the select returns one table that has two columns (A & B) and two records.

Say that I need to put the the data that is contained in column A of the first record into the text field of a label control. How would I do that?

Thanks

 

[Turkbear]

Hi,
here is a code snippet from an app I wrote - it obtains values from a datasoure object and assigns them to a list box..

 <asp:ObjectDataSource ID="RouteDataSource" runat="server" SelectMethod="GetRtNbr" TypeName="Bridge.RouteClass">
  	<SelectParameters>
  		<asp:ControlParameter Name="BKey" ControlID="Rtq" />
  	</SelectParameters>
  			</asp:ObjectDataSource>
    <P><B> Select Route Number</B></BR>
   <asp:ListBox id="ROUTENUM" runat="server"   SelectionMode="Multiple" 
   DataTextField="RTXT" DataValueField="RVAL" 
   	AutoPostBack="True"  DataSourceID="RouteDataSource">
     </asp:ListBox>

The Class and its SQLthat creates the values is :

Public Class RouteClass
        Private dsRoutes As DataSet = New DataSet("RouteDataSet")

         Public Sub New(ByVal BKey As String)
             Dim dbconn As Object
             Dim sqlStr As String
             Dim connectionString As String ="Provider=MSDAORA;Password=<yourpass>;User ID=reporter;Data Source=whsd;Persist Security Info=True;" 
             dbconn=New OleDbConnection(connectionString)
             dbconn.Open()
             Dim MyAdapter As OleDbDataAdapter = New OleDbDataAdapter()
             sqlStr="SELECT ROUTENUM RVAL,ROUTENUM RTXT FROM ROADWAY where trim(BRKEY)||Trim(ON_UNDER) ='" & BKey & "' order by RTXT"
             MyAdapter.SelectCommand = New OleDbCommand(sqlStr,dbconn)
             MyAdapter.Fill (dsRoutes, "Bridge_Routes")
             
        End Sub

        Public Function GetRtNbr() As DataSet
            Return dsRoutes
        End Function
    End Class

This should give you an idea of how to obtain and use data that your datasource object returns.

Hope it helps..





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[jmeckley]

the problem is the datasource control. contrary to what MS demonstrates with their webform 101 tutorials, data access actually has nothing to do with web development. WebForms (html) is merely output which is made human readable by the browser.

data access is is really handled by the ADO.Net framework. Connections, Commands, Readers, Adapters, DataSet/Tables, etc. These are the true objects responsible accessing a database.

The Datasource controls in WebForms are just wrappers around ADO.Net. They only work in the most simple of circumstances.

jbenson001 and I agree that DataSource controls are the most evil invention for WebForms. So instead of drag/drop wizards write the code yourself so you have full control over what's happening. This will give you the ability to inject business logic at the appropiate moments.

I will also plug my FAQ on Database Connection Management. You can access it via my signature below. Taking that concept one step further, David Brion is wrting a series on creating your own DAL using POCOs rather than DataSets. here is the link:

http://davybrion.com/blog/2009/08/build-your-own-data-access-layer-series/

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190

 

[jbenson001]

Yes, as Jason stated, please stay away from the datasource controls. They will only cause confusion instead of making things eaiser. Write the code yourself. There are many benefits in doing so.

 

[MxWade]

Thanks for all of the help on this question. I knew about the ADO method for retrieving data but being new to ASP, I thought that it may be bad form to make use of it. It also seemed like it might be easier if I could pull the data from a datasource.

Another concern that I have is the connection string. When using a datasource, I have read about the benefits of storing the connection string(s) in the web.config file. If I have connection strings in my code behind files, I loose the benefits of having the string in only one place.

Thanks Again - MxW

 

[jmeckley]

If I have connection strings in my code behind files, I loose the benefits of having the string in only one place.

you wouldn't hard code the connection string in the code behind. you would use ConfigurationManager.ConnectionStrings["key"] to pull the information to construct the connection object. How do you think the DataSource control pulls the information from the config file.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190

 

[MxWade]

Thanks Jason - this is another thing that I was not aware of, now I can make use of use it.

MxW

 

[MxWade]

I have been trying to use the ConfigurationManager.ConnectionStrings["key"]that I have been made aware of but I have been unsussessful. The following is from my web.config file (with the login data removed);

<connectionStrings>
<add name="TestCS" connectionString="Data Source=????;Initial Catalog=Test;User ID=UID;Password=PW" providerName="System.Data.SqlClient"/>
</connectionStrings>


I get an error telling me that Value of type 'System.Configuration.ConnectionStringSettings' can not be converted to 'String'. I have tried the following three methods which did not work:

DBConn = New SqlConnection(ConfigurationManager.ConnectionStrings["TestCS"])
DBConn = New SqlConnection(ConfigurationManager.ConnectionStrings["TestCS"].ConnectionStrings)
DBConn = New SqlConnection(ConfigurationManager.ConnectionStrings(1))

If I print to screen:

Response.Write(ConfigurationManager.ConnectionStrings(1))

My output contains the correct connection settings and it looks like it should work. What am I doing wrong?

Thanks,
MxW

 

[jmeckley]

ConfigurationManager.ConnectionStrings["TestCS"] returns a ConnectionStringSettings object, not a string. there are examples of how to use this on the net.

I also make use of this in faq855-7190

also,
Response.Write(ConfigurationManager.ConnectionStrings(1))
is the worst way to debug. if you're going to log use a logging library like log4net. you can dump your logs to file (or other output) and review them without messing around with the actual html generated.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190