general SqlDataSource questions

[djc]

I had previously (in asp.net 1.1 and asp.net web matrix) just done this to
populate a listbox with data from a database:

------------ this was from the page load event ----------------
'fill recipient address list box
Dim cnn As SqlConnection
cnn = New SqlConnection(Application("cnn").ToString())

Dim cmdGetAllRecipientAddresses As SqlCommand = New
SqlCommand("GetAllRecipientAddresses", cnn)
cmdGetAllRecipientAddresses.CommandType = CommandType.StoredProcedure

cnn.Open()

Me.lstAvailableRecipients.DataSource =
cmdGetAllRecipientAddresses.ExecuteReader(CommandBehavior.CloseConnection)
Me.lstAvailableRecipients.DataTextField = "EmailAddress"
Me.lstAvailableRecipients.DataValueField = "EmailAddress"
Me.lstAvailableRecipients.DataBind()
--------------------------------------------------------------

Now I'm playing with 2.0 and VS 2005 and see this 'SqlDataSource' class.
1) Is this now the preferred/suggested method for binding data to
listboxes/datagrids etc...?

2) I'm not clear on its intended scope of usage: for instance if I have
several different databound controls in my asp.net page would I drag one
SqlDataSource from the toolbox for each of those controls? or would I use
just one and programmatically adjust properties (esentially the store
procedure I want to run)?

I'm just starting a new project (only hobby) and didn't want to get to far
into it using the method I was already familiar with if this new method is
preferred. This is mainly just hobby for me but I still like to follow
recommended practices. Any input would be appreciatd.

 

[RobinS]

It's better to use strongly-typed datasets when doing
data binding. The dataset is what links to SQLServer.
That way, if you ever want to change databases, you only have to
change the dataset definition, and all of your code will
still work. I'd check out "Data Binding with Windows
Forms 2.0" by Brian Noyes. It's a great book.

For basic info, I'd check out Linda Liu's message posted
in microsoft.public.dotnet.general with the title
"Fw: VS2005,VB,BindingNavigator: programmatically moving to record".
She covers the basic info.Here's the link to the message
on Google.

http://groups.google.com/group/micr...indingNavigator&rnum=1&hl=en#5a54bd16ded65b78

Robin S.

 

[Miha Markic [MVP C#]]

I had previously (in asp.net 1.1 and asp.net web matrix) just done this to
populate a listbox with data from a database:

------------ this was from the page load event ----------------
'fill recipient address list box
Dim cnn As SqlConnection
cnn = New SqlConnection(Application("cnn").ToString())

Dim cmdGetAllRecipientAddresses As SqlCommand = New
SqlCommand("GetAllRecipientAddresses", cnn)
cmdGetAllRecipientAddresses.CommandType = CommandType.StoredProcedure

cnn.Open()

Me.lstAvailableRecipients.DataSource =
cmdGetAllRecipientAddresses.ExecuteReader(CommandBehavior.CloseConnection)
Me.lstAvailableRecipients.DataTextField = "EmailAddress"
Me.lstAvailableRecipients.DataValueField = "EmailAddress"
Me.lstAvailableRecipients.DataBind()
--------------------------------------------------------------

Now I'm playing with 2.0 and VS 2005 and see this 'SqlDataSource' class.
1) Is this now the preferred/suggested method for binding data to
listboxes/datagrids etc...?

Sort of. The problem with this approach is mixing UI and data layer (hence
also business layer).
So, if you are into serious stuff you might prefer another approach. Perhaps
using ObjectDataSource as it gives you more control.

 

[Beemer Biker]

It's better to use strongly-typed datasets when doing
data binding. The dataset is what links to SQLServer.
That way, if you ever want to change databases, you only have to
change the dataset definition, and all of your code will
still work. I'd check out "Data Binding with Windows
Forms 2.0" by Brian Noyes. It's a great book.

For basic info, I'd check out Linda Liu's message posted
in microsoft.public.dotnet.general with the title
"Fw: VS2005,VB,BindingNavigator: programmatically moving to record".
She covers the basic info.Here's the link to the message
on Google.

http://groups.google.com/group/micr...indingNavigator&rnum=1&hl=en#5a54bd16ded65b78

Robin S.

Unless I am mistaken, you are essentially ruling out using SqlDataSource and
the GridView as that control seems to take only SqlDataSources.

Looking at GridView's properties, all I see under "data" is DataSourceID.
Clicking and then creating a new "DataSource" shows 5 application to get
data from: Access, Database (SqlDataSource), Object, Site Map and XML file.
These options are the only ones available in the toolbox under "data".
Comeing here to VS8 and DOTNET from CBuilder6 I would have expected to see
the following
http://stateson.net/pub/db_stuff/cb6_ado_db.jpg

-DB Grid ( Borland, LMD, DevExpress, ComponentOne, IOComp)
+ Grid takes DataSource
- + DataSource take any DataSet, say "ADODataSet"
- - + DataSet takes any number of ADO objects, say "ADOConnection"
- - - - ADOConnection has provider and string or datalink file

On the VS8 "Component Designer" page I do see some stuff:
http://stateson.net/pub/db_stuff/vs8_web_db.jpg
We are using postgresql so their objects show up. But neither theirs nor
Microsofts show up in the aspx designer where the GridView is located.
Conversly, there is no grid in the component designer.

 

[RobinS]

Unless I am mistaken, you are essentially ruling out using SqlDataSource
and the GridView as that control seems to take only SqlDataSources.

Looking at GridView's properties, all I see under "data" is DataSourceID.
Clicking and then creating a new "DataSource" shows 5 application to get
data from: Access, Database (SqlDataSource), Object, Site Map and XML
file. These options are the only ones available in the toolbox under
"data". Comeing here to VS8 and DOTNET from CBuilder6 I would have
expected to see the following
http://stateson.net/pub/db_stuff/cb6_ado_db.jpg

-DB Grid ( Borland, LMD, DevExpress, ComponentOne, IOComp)
+ Grid takes DataSource
- + DataSource take any DataSet, say "ADODataSet"
- - + DataSet takes any number of ADO objects, say "ADOConnection"
- - - - ADOConnection has provider and string or datalink file

On the VS8 "Component Designer" page I do see some stuff:
http://stateson.net/pub/db_stuff/vs8_web_db.jpg
We are using postgresql so their objects show up. But neither theirs nor
Microsofts show up in the aspx designer where the GridView is located.
Conversly, there is no grid in the component designer.

Fair enough. I'm not doing web development, I'm doing Windows
Forms apps. However, I believe there still needs to be some
separation of your data layer from your business layer
and your presentation layer.

With WindowsForms, you can create a DataSet using the
DataSet Designer, then create a DataSource that accesses the
DataSet.

Then you can drag the entries from the DataSource window
onto the form, and it will create the bindings and the
controls for you. Before dragging, you can select the
type of control to use. It will do a DataViewGrid if your
DataSource is a table of some kind.

Are you telling me the grid control for web development
in Visual Studio only allows you to bind it to a specific
SQLServer database, and no other data source?

If that's the case, I would create a business object layer
and bind it to generic list of business objects.

Good luck.
Robin S.

 

[Beemer Biker]

Fair enough. I'm not doing web development, I'm doing Windows
Forms apps. However, I believe there still needs to be some
separation of your data layer from your business layer
and your presentation layer.

With WindowsForms, you can create a DataSet using the
DataSet Designer, then create a DataSource that accesses the
DataSet.

Then you can drag the entries from the DataSource window
onto the form, and it will create the bindings and the
controls for you. Before dragging, you can select the
type of control to use. It will do a DataViewGrid if your
DataSource is a table of some kind.

Are you telling me the grid control for web development
in Visual Studio only allows you to bind it to a specific
SQLServer database, and no other data source?

I do not know how to bind the grid to anything other than SqlDataSource.
There is undoubtedly a way, I just havent discovered it in the two weeks I
have been working on this project. I have enough trouble learning the
difference between normal, offset and absolute positioning on web pages. It
is very aggrevating to try to move an object a very tiny amount and have it
totally disappear off the panel because it switched to absolute positioning
all on its own.

It would seem to me that I should be able to get the microsoft gridview to
use the SqlConnection. PostgreSql provides a VS8 wizard to build a dataset.
I have not figured out how it works and even if I did build a dataset, the
GridView does not seem to take it using any of the datasource objects that
are available. I see a tool called ObjectDataSource in the toolbox. The
grid takes that, but I do not see how to associated a postgresql dataset
with that object. I think that is the business objects you mention below.

 

[RobinS]

Sorry to hear about all the trouble you're having.
Not that it helps, but you have my sympathy.

A business object layer is usually comprised of
classes defining your business objects, like
Customers, Orders, etc. Then you can make a
business object that is comprised of a list
of one of your business objects, resulting
in a list of Customers, list of Orders, etc.

Then you can create a data source that points
to the list of objects, and then you can
bind it to a grid. This is probably what
they are talking about.

This 3-layer approach is beneficial in the long
run, but can be a pain in the a** when you first
start a project. ;-)

Best of luck.
Robin S.