Multiple Tables as SqlDataSource

Joe Kovac

Hi!

The informations of our customers are saved in two tables:

Customer: ID, CustomerNumber, ... + Person_ID
Person: ID, Name, ...

I want to have an editable GridView displaying all the data and being
able to add, edit and delete customers.

The select is very easy:
SELECT CustomerNumber, Name FROM Customer, Person
WHERE Customer.Person _ID = Person.ID

But what am I to do with the UDATE, DELETE and INSERT?

Is the SQLDataSource the right option?

Creating custom BusinessObjects and custom BusinessObject adapters is
very much work and I found no good sample for such a case, where
everything is supported, including sorting, etc.

Thanks for your help

Joe

May 7 '07 #1

Subscribe Reply

4067

Cowboy \(Gregory A. Beamer\)

When you use the automated goo, you end up pulling from a table (or view)
and .NET attempts to create the INSERT, UPDATE and DELTE for you. This works
extremely well for a single table and many views, but it is not a good
solution if you have complex relationships represented as a denormalized
view of many tables.

In this case, you can still use the different ADO.NET instruments, but you
have to take control of the queries. One of the easiest ways to do this is
to create your own stored procedures for each of the CRUD steps (Create
(INSERT), Read (SELECT), Update and Delete). ADO.NET gives you the ability
to attach sprocs to the data transport objects. I am not sure this is 100%,
but in the cases where it may not be, there is a way to attach an object
that does allow stored procedures.

Can you use SQL commands without encapsulating them in stored procedures?
Sure. But, if you are taking the time to write hand coded SQL, why not take
the step of abstracting your database schema from your code through stored
procedures? :-)

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
http://gregorybeamer.spaces.live.com

*************** *************** ***************
Think outside the box!
*************** *************** ***************
"Joe Kovac" <Jo****@nospam. comwrote in message
news:88******** *************** ***@news.inode. at...

Hi!

The informations of our customers are saved in two tables:

Customer: ID, CustomerNumber, ... + Person_ID
Person: ID, Name, ...

I want to have an editable GridView displaying all the data and being able
to add, edit and delete customers.

The select is very easy:
SELECT CustomerNumber, Name FROM Customer, Person
WHERE Customer.Person _ID = Person.ID

But what am I to do with the UDATE, DELETE and INSERT?

Is the SQLDataSource the right option?

Creating custom BusinessObjects and custom BusinessObject adapters is very
much work and I found no good sample for such a case, where everything is
supported, including sorting, etc.

Thanks for your help

Joe

May 7 '07 #2

Joe Kovac

Cowboy (Gregory A. Beamer) wrote:

When you use the automated goo, you end up pulling from a table (or
view) and .NET attempts to create the INSERT, UPDATE and DELTE for you.
This works extremely well for a single table and many views, but it is
not a good solution if you have complex relationships represented as a
denormalized view of many tables.

In this case, you can still use the different ADO.NET instruments, but
you have to take control of the queries. One of the easiest ways to do
this is to create your own stored procedures for each of the CRUD steps
(Create (INSERT), Read (SELECT), Update and Delete). ADO.NET gives you
the ability to attach sprocs to the data transport objects. I am not
sure this is 100%, but in the cases where it may not be, there is a way
to attach an object that does allow stored procedures.

Can you use SQL commands without encapsulating them in stored
procedures? Sure. But, if you are taking the time to write hand coded
SQL, why not take the step of abstracting your database schema from your
code through stored procedures? :-)

Thanks for your comments. I agree with you. Stored Procedures seem to be
the thing to use.
But maybe there is some BusinessObject-Framework that fits as well as
the SqlDataObject? Does anyone make use of such a thing?

May 7 '07 #3

Similar topics

3121

Multiple Inserts with SQLDataSource

by: Steven Blair | last post by:

I am looking for some advice on the following problem: I am using a GridView and a SQLDataSource. The problem is based around a Room Booking app I am currently writing. When the user makes a booking, an entry must be written to the Booking table, and x amount of entries to the BookingXref(the room keys are held here)

C# / C Sharp

3059

Query data from multiple tables and saving back

by: mimo | last post by:

Hello, I have seen samples on how to pull data from one table and save back to it using the Form View control. How do I pull from multiple tables and save back to multiple tables on one screen? Is there a sample of this somewhere? For example I want to create a Name and an Address from one screen. It has to create a Name Record, a Name Address Record, and a NameAddressJoin Record.

ASP.NET

2613

use same DetailsView for multiple purposes?

by: Dabbler | last post by:

In some cases I would like to allow a user to select a row from a GridView to update in another page using a DetailsView form (passing the key in QueryString). In other cases I would like to use the same DetailsView form to allow general edit/insert/delete functions with Paging=true. I can't figure out how to change the select statement from one case to the other, e.g. if I have an SqlDataSource with:

ASP.NET

1784

Binding multiple CheckBoxList controls to one sqlDataSource

by: Nam | last post by:

I want to use the same sqlDataSource for two CheckBoxList controls in ASP.NET 2.0. For simplicity, I am using the case of only two CheckBoxList controls in the following example: My sqlDataSource is bound to a table in my database. The table has four columns, say, col1_ID, col1_value and col2_ID, col2_value. The DataValueField and DataTextField values for CheckBoxList1 are bound to col1_ID and col1_value respectively. And, for...

C# / C Sharp

1765

SqlDataSource multiple table insert

by: Steven Blair | last post by:

Hi, Would be grateful if anyone with a high understanding of the SqlDataSource and FormView could have a look at my example and my problems. This has really been puzzling me for some time, and would like and expert just to confirm what I am doing is the best approach. Application example:

C# / C Sharp

2739

Help: Using Multiple Checkboxes in a Checkboxlist to filter results in a gridview in ASP.NET 2.0

by: joebob8000 | last post by:

This seems like a simple task, but my 6 year old roots in classic ASP must be causing me trouble with my current problem. I am looking to provide a search for users in which they can select multiple categores (check boxes) and view only the results in those categories in a gridView control. My goal is to do so for movies, so an example would be if the user checks comedy and childrens then they would only get childrens comedies and the...

ASP.NET

5170

.net/oracle multiple select parameter problem

by: rosie2006 | last post by:

Environment: .net2.0 over oracle Problem? a simple select statement within a DetailsView that gets two parameter values from a GridView SelectedValue. It might be valuable to understand that to pull a single record from this particular table requires two parameters. What follows defies understanding. The desired query: <asp:SqlDataSource .....

Oracle Database

6127

SqlDataSource and multiple recordsets

by: =?Utf-8?B?TWFkaXNvbg==?= | last post by:

Hi all, Could I use the sqldatasource with stored-procedure to return multiple recordset? I did not want to have any sqldatasourec in the one page just to return the record back to dropdownlists. In my stored procedure will have 2 select statement. If it can be done how I refer to each dropdownlist to correct recordset. Please advise. Thanks.

ASP.NET

1339

Multiple Tables in SQLDataSource

by: suganya | last post by:

Hi I have a GridView in my Page. I have to bind multiple tables into the GridView using SQLDataSource. For that I have given the coding as <asp:GridView ID="Search_GridView" runat="server" Style="z-index: 100; left: 2px; position: absolute; top: 270px" AutoGenerateColumns="False" OnSelectedIndexChanged="Search_GridView_SelectedIndexChanged" CellPadding="20" Width="720px" AllowPaging="True"...

.NET Framework

8265

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...

General

8196

Changing the language in Windows 10

by: Hystou | last post by:

Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...

Windows Server

8504

Discussion: How does Zigbee compare with other wireless protocols in smart home applications?

by: tracyyun | last post by:

Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...

General

6125

Access Europe - Using VBA to create a class based on a table - Wed 1 May

by: isladogs | last post by:

The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...

Microsoft Access / VBA

5574

Couldn’t get equations in html when convert word .docx file to html file in C#.

by: conductexam | last post by:

I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...

C# / C Sharp

4092

Trying to create a lan-to-lan vpn between two differents networks

by: TSSRALBI | last post by:

Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...

Networking - Hardware / Configuration

2625

transfer the data from one system to another through ip address

by: 6302768590 | last post by:

Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

C# / C Sharp

1808

How to add payments to a PHP MySQL app.

by: muto222 | last post by:

How can i add a mobile payment intergratation into php mysql website.

PHP

1511

Comprehensive Guide to Website Development in Toronto: Expert Insights from BSMN Consultancy

by: bsmnconsultancy | last post by:

In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

General