Database Linking

How to link several databases together using VB.NET

Page 1 of 1

8 Replies - 1679 Views - Last Post: 27 October 2010 - 07:02 AM Rate Topic: -----

#1 bdabaum  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 22-October 10

Database Linking

Posted 25 October 2010 - 04:24 PM

I have an interesting project. I've been tasked to create utility in VB.Net. So far from what I can see, I will need two tables produced from data in other databases. The mockup was done in Access which has a unique way of treating all tables as local tables that can interact with one another without any serious side effects. Simply put, I can add three tables from three databases and create a relationship and query against them without any problems.

I'm not allowed to write or create items on the Server, so I need to do everything within VB.NET. I've created a local MDF file that will be included in the project installation, but as I'm new to .Net. I'm not sure if this is the best practice.

I was excited at the prospect that I might be able to avoid having an attached data source when I realized I could create a DataTable and call data from it. When the application shuts down, the table would disappear and the footprint would be virtually 0. But now rather than just selecting data from that table I need to now delete and insert data to the Datatable object based on AdHoc selections on the form.

Can you execute queries on a DataTable object? If so could you point me to some tutorials/examples?

Is This A Good Question/Topic? 0
  • +

Replies To: Database Linking

#2 demausdauth  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 175
  • View blog
  • Posts: 635
  • Joined: 03-February 10

Re: Database Linking

Posted 25 October 2010 - 05:37 PM

I think your best bet will be to use a DataSet object and then create DataRelations between the separate tables in the DataSet.

Here's some links to read up on.

DataSet
DataRelations
DataTable Constraints
Was This Post Helpful? 0
  • +
  • -

#3 _HAWK_  Icon User is offline

  • Master(Of Foo)
  • member icon

Reputation: 1043
  • View blog
  • Posts: 4,054
  • Joined: 02-July 08

Re: Database Linking

Posted 25 October 2010 - 05:40 PM

You can use LINQ on your dataset.
LINQ with Dataset.
Was This Post Helpful? 0
  • +
  • -

#4 motcom  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 290
  • View blog
  • Posts: 1,371
  • Joined: 16-June 08

Re: Database Linking

Posted 25 October 2010 - 11:57 PM

Hi,

A Data Table will get filled by Data from a database, be it on a Server or whatever PC.

It's a in memory representation of the Data that you querried from the Database on your PC. The idea is that it is a disconnected record set, You pull your data, disconnect from the server, manipulate the data in memory on your PC and then you connect to the database again to save the changes you made. In a multi user environment, you would have to check if another user has not worked on the data whilst you were working on it. That is called concurrency handling.

So to your question, you can have a database deployed on a server and without creating things (i don't know what things though) on your server (well you are but only on the database) you can do all the things you wanted to do in VB.Net or C#. If it is a multi user program, then a server will be a good option to host your database. If not, you can install sql express or Access and deploy the database with your application. Just be carefull that you do not overwrite the database files.
Was This Post Helpful? 0
  • +
  • -

#5 bdabaum  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 22-October 10

Re: Database Linking

Posted 26 October 2010 - 06:00 AM

View Posthawkvalley1, on 25 October 2010 - 04:40 PM, said:

You can use LINQ on your dataset.
LINQ with Dataset.


That was my first idea when they brought the project to me. But they won't upgrade my VS version. I have a "privledge" of being able to use the version I have... :wheelchair: So I'm crippled when it comes to using anything above .Net 2.0.

Thanks everyone for your thoughts. I'll work my way through the links and see if I can get myself up to speed.
Was This Post Helpful? 0
  • +
  • -

#6 bdabaum  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 22-October 10

Re: Database Linking

Posted 26 October 2010 - 06:15 AM

View Postmotcom, on 25 October 2010 - 10:57 PM, said:

Hi,

A Data Table will get filled by Data from a database, be it on a Server or whatever PC.

It's a in memory representation of the Data that you querried from the Database on your PC. The idea is that it is a disconnected record set, You pull your data, disconnect from the server, manipulate the data in memory on your PC and then you connect to the database again to save the changes you made. In a multi user environment, you would have to check if another user has not worked on the data whilst you were working on it. That is called concurrency handling.

So to your question, you can have a database deployed on a server and without creating things (i don't know what things though) on your server (well you are but only on the database) you can do all the things you wanted to do in VB.Net or C#. If it is a multi user program, then a server will be a good option to host your database. If not, you can install sql express or Access and deploy the database with your application. Just be carefull that you do not overwrite the database files.


Okay. I'm going to need to learn about concurrency handling then. Are you saying that if I'm running this application from a network and two or more people are using it, then the in memory data tables are going to get mixed up? I thought once the application was open each instance of the form would be a new instance and therefore would create new instances of the in memory datatables. Is this not correct?
Was This Post Helpful? 0
  • +
  • -

#7 demausdauth  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 175
  • View blog
  • Posts: 635
  • Joined: 03-February 10

Re: Database Linking

Posted 26 October 2010 - 06:21 AM

In a nutshell....

You get new instances of the forms but let's say that you open Form1 and it pulls in Table1 data, Joe opens Form1 on his application and it pulls in Table1 data. You make a change to Row3 and save the changes, Joe's Table1 data is the same as it was when he loaded the form. Now if he makes a change to Row3 then he will end up overwriting your changes. This is where concurrency handling comes into play.
Was This Post Helpful? 0
  • +
  • -

#8 bdabaum  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 22-October 10

Re: Database Linking

Posted 26 October 2010 - 07:39 AM

Not a problem. Nobody is allowed to change the data in Table1. :bananaman:
It's a reporting utility. If any data is changed. My head would be on a platter. That's why I thought of using the in Memory tables. I need to organize the data without ruining the production data.(I'm new to .NET so I won't be offended if someone says, I'm stupid for doing it this way, but if you do please include an alternate solution.)

It may be even as easy as filtering the data that is in the DataTable object. Once the stored procedure returns the data that I put in the DataTable, I may need to remove all rows where ItemNumber is Rxx or something like that. Then later on, I may need to further filter out Source = A, C, and D.
Was This Post Helpful? 0
  • +
  • -

#9 bdabaum  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 22-October 10

Re: Database Linking

Posted 27 October 2010 - 07:02 AM

So I got the dataset created, but I can't seem to query two different tables in the dataset. I have a table Parts and a table OrderTime. Each part has a wait time that is stored in the OrderTime table. OrderTime comes from database2 and Parts is on database1. I thought that adding the tables to the same dataset and adding the relationships would let me query them from the dataset. But I still have no luck. Any examples of datasets that use tables from different databases?
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1