Strategy / direction?

[Graham Blandford]

Hi All, wonder if anyone can help me with some direction...
I'm a VB6er who has been plunged into the VB.NET arena with a requirement to
complete a reasonably small project within a month..

The basics are.. I have two databases, from 3rd-party suppliers for which
there is somewhat common data that I have to 'unite' using a simple
interface, enabling the end-user to report on the combined information from
both.

Both databases are Access; the data is 'open' in one, and the data in the
other is accesible via a dll/reference supplied by the vendor of the
software/database.

My plan is to create a 3rd database - a transaction-store, which essentially
keeps a cross-reference of records in comparable tables in the two 3rd-party
databases. A user-driven function will scan one database, and check for any
new records that don't have a matching record in the transaction-store,
these records are then presented to the user who will go then match them
with records in the other 3rd-party database. These new matches are then
added to the transaction-store database.

Now, in VB6, I could have written some quite offensive code, that would
essentially read the first table, then read the transaction-store for
existence, and added records as necessary....

Looking at VB.NET, I see that we have some wonderful ways to set
relationships in the data programmaticall y. Could I use this to my
advantage? If so, any code examples handy? - nothing too indepth, but any
pointers in the right direction would be greatly appreciated....

Basically, any ideas you guys have, I'd be interested in hearing... as I
say, I'm new to .NET have a number of books to read, but need to start
producing on this pretty quickly and could do with a jump-start..

Thanks,
Graham

[n.b.k]

Re: Strategy / direction?

Hi Graham,
How come you don't want to use SQL statements to combined the data into
a data set that you need at run time? It would be easier then try to
replicate the two into one and then try to keep the data in sync.




"Graham Blandford" <graham.blandfo rd@sympatico.ca > wrote in message
news:zefrc.4150 4$qJ5.1043510@n ews20.bellgloba l.com...[color=blue]
> Hi All, wonder if anyone can help me with some direction...
> I'm a VB6er who has been plunged into the VB.NET arena with a requirement[/color]
to[color=blue]
> complete a reasonably small project within a month..
>
> The basics are.. I have two databases, from 3rd-party suppliers for which
> there is somewhat common data that I have to 'unite' using a simple
> interface, enabling the end-user to report on the combined information[/color]
from[color=blue]
> both.
>
> Both databases are Access; the data is 'open' in one, and the data in the
> other is accesible via a dll/reference supplied by the vendor of the
> software/database.
>
> My plan is to create a 3rd database - a transaction-store, which[/color]
essentially[color=blue]
> keeps a cross-reference of records in comparable tables in the two[/color]
3rd-party[color=blue]
> databases. A user-driven function will scan one database, and check for[/color]
any[color=blue]
> new records that don't have a matching record in the transaction-store,
> these records are then presented to the user who will go then match them
> with records in the other 3rd-party database. These new matches are then
> added to the transaction-store database.
>
> Now, in VB6, I could have written some quite offensive code, that would
> essentially read the first table, then read the transaction-store for
> existence, and added records as necessary....
>
> Looking at VB.NET, I see that we have some wonderful ways to set
> relationships in the data programmaticall y. Could I use this to my
> advantage? If so, any code examples handy? - nothing too indepth, but any
> pointers in the right direction would be greatly appreciated....
>
> Basically, any ideas you guys have, I'd be interested in hearing... as I
> say, I'm new to .NET have a number of books to read, but need to start
> producing on this pretty quickly and could do with a jump-start..
>
> Thanks,
> Graham
>
>[/color]

[William Ryan eMVP]

Re: Strategy / direction?

Hi Graham


"Graham Blandford" <graham.blandfo rd@sympatico.ca > wrote in message
news:zefrc.4150 4$qJ5.1043510@n ews20.bellgloba l.com...[color=blue]
> Hi All, wonder if anyone can help me with some direction...
> I'm a VB6er who has been plunged into the VB.NET arena with a requirement[/color]
to[color=blue]
> complete a reasonably small project within a month..[/color]

Plenty of time for any db app with .NET ;-)[color=blue]
>
> The basics are.. I have two databases, from 3rd-party suppliers for which
> there is somewhat common data that I have to 'unite' using a simple
> interface, enabling the end-user to report on the combined information[/color]
from[color=blue]
> both.[/color]

I can't tell if the requirement is that these two old dbs are to stay in use
w/ other apps or if you simply need to use them for the data but once you
get them migrated you can say good bye to them forever. Either way you have
a pretty straightforward task. I'll explain below:[color=blue]
>
> Both databases are Access; the data is 'open' in one, and the data in the
> other is accesible via a dll/reference supplied by the vendor of the
> software/database.
>
> My plan is to create a 3rd database - a transaction-store, which[/color]
essentially[color=blue]
> keeps a cross-reference of records in comparable tables in the two[/color]
3rd-party[color=blue]
> databases. A user-driven function will scan one database, and check for[/color]
any[color=blue]
> new records that don't have a matching record in the transaction-store,
> these records are then presented to the user who will go then match them
> with records in the other 3rd-party database. These new matches are then
> added to the transaction-store database.
>
> Now, in VB6, I could have written some quite offensive code, that would
> essentially read the first table, then read the transaction-store for
> existence, and added records as necessary....
>
> Looking at VB.NET, I see that we have some wonderful ways to set
> relationships in the data programmaticall y. Could I use this to my
> advantage? If so, any code examples handy? - nothing too indepth, but any
> pointers in the right direction would be greatly appreciated....[/color]
I'm going to make a few assumptions for the sake of illustration but nothign
would change if the assumptions were changed. Let's assume that both db's
have the same fields/same schemas or if not, that they are similar enough
that mapping them to a new db would be doable.

Remember that the ADO.NET metaphor is that a DataSet is roughly analagous to
a Database. A datatable is roughly analogous to a Table in that db. A
dataview is roughly equivalent to a view in a Db. A Dataset is simply a
collection of db objects, which at a minimum are 0 or more datatables.
DataTables can be bound to each other through DataRelations - these can be
defined at design time w/ Strongly typed datasets or at runtime
http://www.knowdotnet.com/articles/datarelation.html. Now, in general
strong typing is the way to go and STD's are the way to go - however if you
don't know the fields in advance, Strongly Typed Datasets (STD) don't lend
themselves well to this. Anyway, the DataAdapter object is responsible for
marshalling data back and forth. They DONT care where the data came from
and they don't care where it's going as long as the data they are moving
matches the command object for the respective command. So, lets say that
you have 100 records in table1 which has fields A, B, and C. In table 2 you
have fields A,B, C, D and E. You can use one dataadapter to fill a
datatable (dt - which is a table in a dataset dS). Now the select command
for table1 would look like Select A, B, C from Table_1. Then you'd call
fill like this OleDbDataAdapte r1.Fill(ds, dt) Ok, as it stands, the
rowstate of each of the rows is 'unchanged' which means that when we call
update, it will loop through the datatable and for every row with a rowstate
of Added, it will use that row and fire the Insert Command specified in the
adapter. For each rowstate of Modified it will use the Update command. For
each row with a rowstate of Deleted it will use the delete command. Since
we don't have any changes, nothing will happen. However, if we set the
AcceptChangesDu ringFill property of the adapter to false
http://www.knowdotnet.com/articles/datasetmerge.html Then all of the rows
will have a rowstate of Added. Thus, if we call update on another adapter,
each row will be treated just like it was a new row added to the set. So,
if we had OleDbDataAdapte r2 and it's Insert command used only fields A, B,
and C, we could call OleDbDataAdapte r2.Update(ds.Ta bles[0]) //tables 0 is
dt. This would effectively insert each value into the Table2. We could
also create two datatables and perhaps use merge, or add a third DataColumn
so that table1 matched 2 and use all four columns for the update. Also, you
need to use keyed tables for updates (keys on the backend at a minimum) but
you can define your own keys
http://www.knowdotnet.com/articles/dataviewspart2.html and relations that
may or may not exist on the backend. You even have .AutoIncrement
properties and a Seed and everythign.

Anyway, you can easily use both datasources to fill a datatable (yes, you
can fill one datatable with two different adapters from two (or more)
different datasources as long as the schemas match (or have something in
common - you can make them be exact or you can allow it to be forgiving to
some degreee). Now, remember that rowstate is everything. So you have all
of this data (remember the AcceptChangesDu ringFill should be false if you
want these rows to be inserted into the destination) from two separate
sources in one or two datatables (say one for this). Now you can call
OleDbDataAdapte r3.Update(ds.Ta bles[0]) or
OleDbDataAdapte r3.Update(ds.Ta bles[0]); then call
OleDbDataAdapte r3.Update(ds.Ta bles[1]); if we used two tables instead of
one. If you configured everything correctly, you'll have all of your data
and the modifications you made to it in the destination datasource.

This end source can be XML (you can use
DataSetName.Wri teXML(@"Path:\f ile.xml"); to write out your data and the
inverse dataSetName.Rea dXML(@"Path:\fi le.xml"); to deserialize things

If you look at the Data Access section of www.knowdotnet.com
http://www.knowdotnet.com/dataaccess.html I've written a fair amount of
stuff (I mention it b/c it's mine but that's in no way implying it's a
definitive site).

Bill Vaughn's www.betav.com has some of the best articles ever written on
ADO.NET, pure gold. You'll find his book(s) there too which is in a first
place tie for best ADO.NET book written. The other book is David Sceppa's
ADO.NET Core Reference. Don't leave home without either of them.

Another thing you may want to look into is System.XML and XML web services.
XML isn't the fastest way to access data but it's powerful and if you aren't
resource constrained, it offers some killer solutions.

There are many sites like www.c-sharpcorner.com that have some great
DataAccess content, of course www.dotnetjunkies.com / www.sqljunkies.com ,
www.gotdotnet.com , www.brains-n-brawn.com just to name a few. Also there
is a ton of great stuff in this NG. This is without a doubt my favorite NG
and it's because the topics are always interesting and there's some really
amazingly talented folks here.

Hopefully this is enough to get you started but if you need any help or have
any questions, please don't hesitate to ask.

Cheers,

Bill



[color=blue]
>
> Basically, any ideas you guys have, I'd be interested in hearing... as I
> say, I'm new to .NET have a number of books to read, but need to start
> producing on this pretty quickly and could do with a jump-start..
>
> Thanks,
> Graham
>
>[/color]

--
W.G. Ryan MVP Windows - Embedded

https://www.devbuzz.com

https://www.knowdotnet.com

http://www.msmvps.com/williamryan/

[guy]

RE: Strategy / direction?

Hi,
Well you could thump your bosses for being unreasonable;-)
moving from VB6 to VB.Net s a *** big *** change (but definitely worth it!)
as nbk implies do as much as you can at the database level, and if performance is critical use a datareader not datasets - but this depends to some degree on wether it is a Winforms or Webforms app...
so much to learn...
As I say a BIG change and lots of fun(?) learning.
probabaly the most iportant thing is to get the idea across to your bosses the idea that VB6-VB.NET is NOTHING like VB5-> VB6

hth guy

[Graham Blandford]

Re: Strategy / direction?

Thanks for your help guys.
Lots of useful things in there for me to investigate...

The .NET thing has been thrown into the mix purely because the dll/reference
for accessing one of the databases is a .NET component and can't be used in
VB6 - the database is locked up by the 3rd-party company who won't allow any
direct access to their db... so.. read only it is....hence the requirement
to develop in .NET and the aggresive time-frame to learn...

Once again, thanks for the insights and I'm sure you'll be seeing my posts
more regularly in the very near-future.... ;)

Graham





"Graham Blandford" <graham.blandfo rd@sympatico.ca > wrote in message
news:zefrc.4150 4$qJ5.1043510@n ews20.bellgloba l.com...[color=blue]
> Hi All, wonder if anyone can help me with some direction...
> I'm a VB6er who has been plunged into the VB.NET arena with a requirement[/color]
to[color=blue]
> complete a reasonably small project within a month..
>
> The basics are.. I have two databases, from 3rd-party suppliers for which
> there is somewhat common data that I have to 'unite' using a simple
> interface, enabling the end-user to report on the combined information[/color]
from[color=blue]
> both.
>
> Both databases are Access; the data is 'open' in one, and the data in the
> other is accesible via a dll/reference supplied by the vendor of the
> software/database.
>
> My plan is to create a 3rd database - a transaction-store, which[/color]
essentially[color=blue]
> keeps a cross-reference of records in comparable tables in the two[/color]
3rd-party[color=blue]
> databases. A user-driven function will scan one database, and check for[/color]
any[color=blue]
> new records that don't have a matching record in the transaction-store,
> these records are then presented to the user who will go then match them
> with records in the other 3rd-party database. These new matches are then
> added to the transaction-store database.
>
> Now, in VB6, I could have written some quite offensive code, that would
> essentially read the first table, then read the transaction-store for
> existence, and added records as necessary....
>
> Looking at VB.NET, I see that we have some wonderful ways to set
> relationships in the data programmaticall y. Could I use this to my
> advantage? If so, any code examples handy? - nothing too indepth, but any
> pointers in the right direction would be greatly appreciated....
>
> Basically, any ideas you guys have, I'd be interested in hearing... as I
> say, I'm new to .NET have a number of books to read, but need to start
> producing on this pretty quickly and could do with a jump-start..
>
> Thanks,
> Graham
>
>[/color]

[Pete Wright]

Re: Strategy / direction?

What you are saying then is that the third party has supplied a .NET
assembly (the DLL). This isn't something I know how to do off the top of my
head but I do know that you can quite easily produce a COM wrapper around a
..NET assembly. If it would help your time to market this might be a better
option; producing a COM wrapper around the assembly would for the time being
at least allow you to return to the development environment you love and
write code as you know how.

Just a thought

--
--------------------------------------------------------
Peter Wright (www.petewright.org)
Author of ADO.NET Novice To Pro
From Apress. www.apress.com


"Graham Blandford" <graham.blandfo rd@sympatico.ca > wrote in message
news:0Pnrc.8348 0$325.1851945@n ews20.bellgloba l.com...[color=blue]
> Thanks for your help guys.
> Lots of useful things in there for me to investigate...
>
> The .NET thing has been thrown into the mix purely because the[/color]
dll/reference[color=blue]
> for accessing one of the databases is a .NET component and can't be used[/color]
in[color=blue]
> VB6 - the database is locked up by the 3rd-party company who won't allow[/color]
any[color=blue]
> direct access to their db... so.. read only it is....hence the requirement
> to develop in .NET and the aggresive time-frame to learn...
>
> Once again, thanks for the insights and I'm sure you'll be seeing my posts
> more regularly in the very near-future.... ;)
>
> Graham
>
>
>
>
>
> "Graham Blandford" <graham.blandfo rd@sympatico.ca > wrote in message
> news:zefrc.4150 4$qJ5.1043510@n ews20.bellgloba l.com...[color=green]
> > Hi All, wonder if anyone can help me with some direction...
> > I'm a VB6er who has been plunged into the VB.NET arena with a[/color][/color]
requirement[color=blue]
> to[color=green]
> > complete a reasonably small project within a month..
> >
> > The basics are.. I have two databases, from 3rd-party suppliers for[/color][/color]
which[color=blue][color=green]
> > there is somewhat common data that I have to 'unite' using a simple
> > interface, enabling the end-user to report on the combined information[/color]
> from[color=green]
> > both.
> >
> > Both databases are Access; the data is 'open' in one, and the data in[/color][/color]
the[color=blue][color=green]
> > other is accesible via a dll/reference supplied by the vendor of the
> > software/database.
> >
> > My plan is to create a 3rd database - a transaction-store, which[/color]
> essentially[color=green]
> > keeps a cross-reference of records in comparable tables in the two[/color]
> 3rd-party[color=green]
> > databases. A user-driven function will scan one database, and check for[/color]
> any[color=green]
> > new records that don't have a matching record in the transaction-store,
> > these records are then presented to the user who will go then match them
> > with records in the other 3rd-party database. These new matches are then
> > added to the transaction-store database.
> >
> > Now, in VB6, I could have written some quite offensive code, that would
> > essentially read the first table, then read the transaction-store for
> > existence, and added records as necessary....
> >
> > Looking at VB.NET, I see that we have some wonderful ways to set
> > relationships in the data programmaticall y. Could I use this to my
> > advantage? If so, any code examples handy? - nothing too indepth, but[/color][/color]
any[color=blue][color=green]
> > pointers in the right direction would be greatly appreciated....
> >
> > Basically, any ideas you guys have, I'd be interested in hearing... as I
> > say, I'm new to .NET have a number of books to read, but need to start
> > producing on this pretty quickly and could do with a jump-start..
> >
> > Thanks,
> > Graham
> >
> >[/color]
>
>[/color]