DataSet/Binding Help

dsk96m

Well-known member
Joined
Jan 11, 2013
Messages
173
Programming Experience
1-3
I am trying to convert a vb6 program to vb.net. I don't think I fully understand the whole dataset/binding.

So my sql code is this:
VB.NET:
SELECT   CASE 
             WHEN e.empl_middle_init != ' ' 
                  THEN e.empl_last_name||', '||e.empl_first_name||' '||e.empl_middle_init||'.' 
                  ELSE e.empl_last_name||', '||e.empl_first_name 
             END Employee,e.empl_id,e.empl_empl_id,p.ppcd_cd TYPE,c.clas_desc ORG,
         o.orgn_cd ORGN,e.empl_pacc_id Prim,e.empl_cctr_id BC,e.empl_entity Entity,
         e.empl_sep_dt SepDT,e.empl_type_id,e.empl_oref,e.empl_sect,e.empl_ppcd,
         NVL(h.min_pp,0) min_pp,NVL(h.max_pp,0) max_pp,e.empl_job_cd JobCode 
FROM     
fset.tfsetempl e LEFT JOIN fset.tfsetorgn o ON o.orgn_oref = e.empl_oref 
INNER JOIN 
fset.tfsetppcd p ON e.empl_ppcd=p.ppcd_pref 
LEFT JOIN 
fset.tfsetclas c ON o.orgn_clas = c.clas_cref 
LEFT JOIN 
(SELECT a.hist_empl_id empl_id,MIN(hist_pp) min_pp,MAX(hist_pp) max_pp FROM fset.tfsethist a WHERE a.hist_yr = 2013 GROUP BY a.hist_empl_id) h ON e.empl_id=h.empl_id 
where (e.empl_sep_dt is null or e.empl_sep_dt > to_date('12/31/2012','mm/dd/yyyy')) 
ORDER BY 1 ASC

Data from this was used to populate a combobox.

So in visual studio 2010, in my project i went to add new data source, followed the wizard and added the tables used in the above sql to create the data set. So in the designer I have the 5 tables. how do I make the dataset equal to the above? What am I missing?

Thanks
 

IanRyder

Well-known member
Joined
Sep 9, 2012
Messages
1,130
Location
Healing, NE Lincs, UK
Programming Experience
10+
Hi,

The bit that you are missing is that your SQL query should be created as a View in your backend database. You can then use the VS2010 wizard to attach your view, as you do tables, to your Dataset. To then populate a ComboBox, you would set it's DataSource to the name of the View, it's DisplayMember to the Field to be displayed in the ComboBox and then set it's ValueMember to the Field to be returned when reading the ComboBox.SelectedValue.ToString property.

Hope that helps.

Cheers,

Ian
 

cjard

Well-known member
Joined
Apr 25, 2006
Messages
7,052
Programming Experience
10+
Not necessarily but it usually helps to make it a view (a select query that is saved in a db and is combined onthe fly into whatever query it is joined into/used in, before the query is run) simply because on really complicated queries, the dataset query wizard falls over


You seem to have missed the point that a dataset/datatables are an ABSTRACTION of the data in the database; a datatable doesnt HAVE to exactly represent an exact database table, including all its columns
You DONT do queries on a dataset full of datatables
A dataset IS NOT a relational database


If I want a dataset that contains a single datatable that is of people's latest addresses, and this is stored in two tables in the actual DB (lame example but let's run with it):

tblPerson: PersonID, PersonName, AddressID
tblAddress: AddressID,AddressData, LivedThereUntilDate

Then I pop open the query designer (tableadapter wizard)
SELECT PersonName, AddressData
FROM tblPerson p INNER JOIN tblAddress a ON p.AddressID = a.AddressID
WHERE a.LivedThereUntil = null


And thats it. I'll get a datatable that has 2 columns and is a read only report of all people

If I wanted to create and do updates and inserts, that's different. I would have each table as a datatable, and a datarelation between them. Upon making a new person row, Iwould need to store that, because when I make a new address row, the constructor would demand that I add the person row first, then save it first, then save the address row

So you might say why have the single table of people addresses at all? WHy not just download a million people and 2 million addresses into the client, then count all the people whose name starts with P and whose past addresses have been new york...
Why not? because thats not what datasets are for, that's what databases are for. Databases are on powerful servers, you ask for exactly and only the data you want and it is delivered, formatted, summedand filtered in exactly the right way, so you can work with it and only a few rows come over the network

Repeat until you're clear as crystal: a dataset is not a database, a datatable is not a database table. they're both just a convenient way of specifying objects that represent some of the data i hold in adatabase. a database is where I do the hard work :D
 
Top Bottom