how to use TableAdapterManager and related datatables in combination with SQL queries

Question

i have a question on how to work properly with related datatables and how to actualize them properly in the backend source database.

the situation is following:

1. I have a SQL database (SQL Server) which is the basic data source. In here the tables can have relation with each other via foreign keys.

2. this SQL database i use to fill datatables via SQL queries. The resulting datatables i use to visualize the information on the formular

var query = new SqlCommand(_sqlquery, connection);
var sqlAdapter = new SqlDataAdapter(query);
sqlAdapter.Fill(dataTable);

...

Here i am using sql queries to get the needed information into my datatables for example:

SELECT * FROM [order] INNER JOIN customer ON [order].customerID = customer.customerID;

So far so good the the visualization in the datagrid is working well.

BUT the problems are rising when trying to save modifications on the visualized data in the datagrid back in the source sql database.

I searched a lot in the internet and the solution seems to be the usage of TableAdapterManager. The problem is that i don't really understand how to connect my code i have already to visualize the data with the TableAdapterManager which can actualize the data in the sql database.

Answer 1

Let me add another answer - it will be easy to explain step by step. I will edit/add more details when it needed.

1. start from creating new dataset and add datatables which you need. Add new tableadapter - it will also create datatable based on your or default query. Tableadapter may have many queries - they used to fill the datatable from db.

Start from simple query:

select * from customers

or

select *, computedField1 = 'abc', computedField2 = 123 
from customers 
where 1=0

This "first" query is "fake" ("where 1=0" tell you that it will never be used) and declarative, from single table, no params. Usually you never retrieve ALL data w/o parameters or joins. This query "allows" the designer to create structure and update statements, recognise primary key.

Then add another queries:

FillById

select * 
from customers 
where custId = @custId

FillByName

select * 
from customers 
where custname like (@ss + '%')

FillByRecent

select cc.*
from customers cc
inner join orders oo on cc.custId = oo.custId   // get only customers with orders
where oo.orderdate > @dtFrom

You will call them in code:

taCustomer.FillByName(ds.customers, "AAA");    // all started from "AAA"

Do the same for orders

2. Create relation

Click on gray space before column custId in Customer - it will select column. Drag selected and drop on custId in Orders

3. Add do form

After compile you dataset will be added to "Data Sources" window. Select your form, drag customer from "Data Sources" window to your form - it will create grig and add components to form: dataset, binding source, tableadapter.

Add orders too.

Look at the property "datasource" and "datamember" in the grids, and binding sources:

• bsCustomer will refer to ds - customer
• bsOrders will refer to customer - customers_orders_relation

4. Load / Update

in form_load() call

taOrders.FillBySomething(ds.orders, ...)
taCustomer.FillByAnotherSomething(ds.customers, ...); 

under Save button

taOrders.Update(ds.orders); // will update only modified content
taCustomer.Update(ds.customers);

Answer 2

You use join. Its combine result from 2 tables which is good for view, but confusing for update (but it still possible). Your result will be as follow. What do you expect if user will do such modification:

ord1 cust1   --> cust1a
ord2 cust1   --> cust1b
ord3 cust2
ord4 cust2

usually if need updates, create 2 tables dtOrders and dtCustomers and set relation between them in c# (I like to use designer: add Dataset, add tableAdapters for orders and customers, drag customerId from one table to another to set relation). Drop on the form Dataset, Tableadapters, bindingSources, and cofigure orderDatasource to get data from customerDatasource->yourRelation. get data for each table.

Note. Using Dataset and tableadapters is more preferable for winform applications. For web usually used more lightweight constructions, to avoid recreation havy objects on each request.

customerTableAdapter.Fill(ds.dtCustomers);
ordersTableAdapter.Fill(ds.dtOrders);

Then you can display 2 grids (master-details) or one grig with using relation. In your datatables you will not have duplicated customers do updates will not be confusing.

TableAdapterManager may be used if you need update tables in one transaction. If it is not important you may do updates without TableAdapterManager.

---

in other way you may use single table adapter with join and create your own update statemtes as many as you need:

ta.updateOrderMethod1(row1)     // Update orders set ... where ordId = @ordId
ta.updateOrderMethod2(row1)     // spUpdateOrder(ordId = @ordId)
ta.updateCustomer(row1)         // Update customer set ... where customerId = @customerId
ta.updateCustomerNameOnly(row1) // Update customer set customerName=@customerName where customerId = @customerId