Create controls to relate two tables

  • (3 Pages)
  • +
  • 1
  • 2
  • 3

42 Replies - 3979 Views - Last Post: 11 October 2010 - 02:44 AM Rate Topic: -----

#1 nighttrain  Icon User is online

  • D.I.C Regular

Reputation: 6
  • View blog
  • Posts: 330
  • Joined: 22-September 10

Create controls to relate two tables

Posted 23 September 2010 - 01:16 AM

Hi there !

That's my first post on this forum. I need Your help because i got problem with C# and MySQL. So.. I got an application which connect to MySQL base. This base has 3 tables. First table includes informations about computers and the second table has got information about programs. The Third table has got relations with first and second table (connects by id's). For example i got computer from first table with id - 4 number and i want to make relation it with program Office which has id 42 in table programs. So i make relation in third table 4 - 42. OK.

Now i got problem how to make this relation in C# Visual Studio 2008. I don't know what should i use to connect thats stuff. What controls to use?
I wonder about mb some combobox which download names from this tables, put them to comboboxes and just select it and press some button to make relations.

This is my tables:

1 table: "computers": (id, name_of_computer, ip, mac)
2 table: "programs" : (id_computer, name_of_program, version)
3 table: "rel_comp_programs: (computer_id, program_id, id)

thx for all help..

Is This A Good Question/Topic? 0
  • +

Replies To: Create controls to relate two tables

#2 FlashM  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 382
  • View blog
  • Posts: 1,195
  • Joined: 03-December 09

Re: Create controls to relate two tables

Posted 23 September 2010 - 01:29 AM

This really depends on how you're connecting to your database...
If you're manually connection to your database and using raw SQL queries to get your data from DB, then it goes something like this:

SELECT *
FROM table a inner join table b on a.id = b.id
WHERE some condition = some value...

If you're using any ORM systems like NHibernate or DLINQ, then there is a completely different story.

I also suggest you browse our extensive tutorial section for help... There are numerous examples on how to do it...
Was This Post Helpful? 0
  • +
  • -

#3 nighttrain  Icon User is online

  • D.I.C Regular

Reputation: 6
  • View blog
  • Posts: 330
  • Joined: 22-September 10

Re: Create controls to relate two tables

Posted 23 September 2010 - 02:27 AM

hello thx for fast reply. I connect to my base by MySQL Connector.

This post has been edited by nighttrain: 23 September 2010 - 02:27 AM

Was This Post Helpful? 0
  • +
  • -

#4 FlashM  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 382
  • View blog
  • Posts: 1,195
  • Joined: 03-December 09

Re: Create controls to relate two tables

Posted 23 September 2010 - 02:35 AM

Are you writting the code yourself or using wizards to connect to database? If you could provide some of your code it would be much easier to help you...
Was This Post Helpful? 0
  • +
  • -

#5 Guest_nighttrain*


Reputation:

Re: Create controls to relate two tables

Posted 23 September 2010 - 02:45 AM

i connect manually to my database.
Here's an example of connect:

private void OdczytajDane2()             // METODA ODCZYTUJĄCA DO LISTBOX ID DANYCH
    {
        listBox2.Items.Clear();

        conDatabase = new MySqlConnection("SERVER=localhost; DataBase=komputery; UserId=root; PWD=pass;");
        conDatabase.Open();

        zapytania = conDatabase.CreateCommand();

        zapytania.CommandText = ("SELECT nazwa_programu FROM oprogramowanie ORDER By nazwa_programu ");
        zapytania.ExecuteNonQuery();

        MySqlDataReader dr = zapytania.ExecuteReader(Commandbehavior.CloseConnection);

        while (dr.Read())
        {
            string nazwa_programu = Convert.ToString(dr["nazwa_programu"]);
            this.listBox2.Items.Add(nazwa_programu);
        }

        conDatabase.Close();
    }


Was This Post Helpful? 0

#6 FlashM  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 382
  • View blog
  • Posts: 1,195
  • Joined: 03-December 09

Re: Create controls to relate two tables

Posted 23 September 2010 - 04:47 AM

All you need to do is to write your SQL query for selecting the records you want to display:


COMPUTER TABLE:

id
name
ip
mac

PROGRAM TABLE:
id
name
version

COMPUTER_PROGRAM TABLE:
computer_id
program_id

Get all computers that contain certain program:
SELECT c.name, c.ip, c.mac
FROM (computer c inner join computer_program cp on c.id = cp.id_computer) inner join program p on p.id = cp.id_program
WHERE p.id = 2


Get all programs that are installed on certain computer

SELECT p.name, p.version
FROM (computer c inner join computer_program cp on c.id = cp.id_computer) inner join program p on p.id = cp.id_program
WHERE c.id = 2

This post has been edited by FlashM: 23 September 2010 - 04:48 AM

Was This Post Helpful? 0
  • +
  • -

#7 tlhIn`toq  Icon User is online

  • Please show what you have already tried when asking a question.
  • member icon

Reputation: 5515
  • View blog
  • Posts: 11,824
  • Joined: 02-June 10

Re: Create controls to relate two tables

Posted 23 September 2010 - 07:48 AM

View Postnighttrain, on 23 September 2010 - 12:16 AM, said:

Hi there !

That's my first post on this forum. I need Your help because i got problem with C# and MySQL. So.. I got an application which connect to MySQL base. This base has 3 tables. First table includes informations about computers and the second table has got information about programs. The Third table has got relations with first and second table (connects by id's). For example i got computer from first table with id - 4 number and i want to make relation it with program Office which has id 42 in table programs. So i make relation in third table 4 - 42. OK.

Now i got problem how to make this relation in C# Visual Studio 2008. I don't know what should i use to connect thats stuff. What controls to use?
I wonder about mb some combobox which download names from this tables, put them to comboboxes and just select it and press some button to make relations.

This is my tables:

1 table: "computers": (id, name_of_computer, ip, mac)
2 table: "programs" : (id_computer, name_of_program, version)
3 table: "rel_comp_programs: (computer_id, program_id, id)

thx for all help..


Sometimes just learning out to look for help is the biggest help of all.

Google with the language you are using and the keywords of the issue:
"C# mysql tutorial"
And you will get a lot of good, relevant hits.
Was This Post Helpful? 0
  • +
  • -

#8 nighttrain  Icon User is online

  • D.I.C Regular

Reputation: 6
  • View blog
  • Posts: 330
  • Joined: 22-September 10

Re: Create controls to relate two tables

Posted 23 September 2010 - 11:27 PM

SELECT c.name, c.ip, c.mac FROM (computer c inner join computer_program cp on c.id = cp.id_computer) inner join program p on p.id = cp.id_program WHERE p.id = 2



u mean c.name it's in my case computers.name right?

and what's computer c inner join ?
Was This Post Helpful? 0
  • +
  • -

#9 FlashM  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 382
  • View blog
  • Posts: 1,195
  • Joined: 03-December 09

Re: Create controls to relate two tables

Posted 23 September 2010 - 11:42 PM

No... This is the short notation for the name of your database table... You could write it the long way like that:

SELECT computer.name, computer.ip, computer.mac 
FROM (computer inner join computer_program on computer.id = computer_program.id_computer) inner join program on program .id = computer_program.id_program 
WHERE program.id = 2


What keyword 'join' actually does is it joins two of your database tables and 'inner join' is a special way of joining your tables... As I can see you have practically very little understanding of SQL query language, so I suggest you start learning it as soon as possible... It's very easy and you can learn the basics in few hours or even less... And you need to understand the SQL if you'll ever become software developer (although SQL is rarely used today since there are some really cool ORM tools available), but every developer needs to understand it and it is a must. If you need any further information on that, feel free to ask. Happy coding...
Was This Post Helpful? 1
  • +
  • -

#10 nighttrain  Icon User is online

  • D.I.C Regular

Reputation: 6
  • View blog
  • Posts: 330
  • Joined: 22-September 10

Re: Create controls to relate two tables

Posted 24 September 2010 - 01:13 AM

ok i think i understand that
SELECT c.name, c.ip, c.mac FROM (computer c inner join computer_program cp on c.id = cp.id_computer) inner join program p on p.id = cp.id_program WHERE p.id = 2


but i think i don't need c.ip and c.mac so i clear query from it

now i wonder how to use objects in C# (what code add to what object). How will You do it best and easy way to use it for users?
I will really pleasure for any example code :)

best regards FlashM ~
Was This Post Helpful? 0
  • +
  • -

#11 FlashM  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 382
  • View blog
  • Posts: 1,195
  • Joined: 03-December 09

Re: Create controls to relate two tables

Posted 24 September 2010 - 01:44 AM

Could you be more specific about your question... What exactly did you have in mind? What objects? Do you have any code written that we could see... It would be much easier to help you...
Was This Post Helpful? 0
  • +
  • -

#12 nighttrain  Icon User is online

  • D.I.C Regular

Reputation: 6
  • View blog
  • Posts: 330
  • Joined: 22-September 10

Re: Create controls to relate two tables

Posted 24 September 2010 - 04:16 AM

For example if i would to make it by the 2 Comboboxes

->First combobox will have list of computers from table computers which will download from that database and fill this combobox by this data by computer_name
->Second combobox will have list of programs from table programs which will download from database and fill this combobox by this data by program_name

then if i select from first ComboBox list- a computer and select from second ComboBox list - a program
i can press button to make relation of this computer and program and update third database computer_program.

adioS
Was This Post Helpful? 0
  • +
  • -

#13 FlashM  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 382
  • View blog
  • Posts: 1,195
  • Joined: 03-December 09

Re: Create controls to relate two tables

Posted 24 September 2010 - 04:37 AM

why wouldn't you have a combobox populated with computers from DB and then have a listbox of programs populated from DB - then you could have multi-program select on your listbox and then send a multi selection to your third table in DB? I'd find that more user friendly then if user had to save each program for specific computer at a time? By the way, did you try to do it yourself? Could you post some your sample code?
Was This Post Helpful? 0
  • +
  • -

#14 Guest_nighttrain*


Reputation:

Re: Create controls to relate two tables

Posted 24 September 2010 - 05:58 AM

It's a great idea Flash. I must work out on it and soon i will try and publish here some sample code of this case. It's 100% more user friendly than i wrote. I think in future how to be best to erase that's relations :). Hey
Was This Post Helpful? 0

#15 FlashM  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 382
  • View blog
  • Posts: 1,195
  • Joined: 03-December 09

Re: Create controls to relate two tables

Posted 24 September 2010 - 06:12 AM

Here's a cookie to get you started... :-)

public Form3()
{
    InitializeComponent();

    //Get your applications from DB:
    string[] applications = { "WinXP", "Windows Vista", "Windows 7", "Linux", "Some Application" };

    applicationsListbox.Items.AddRange(applications); //add programs to listbox
    applicationsListbox.SelectionMode = SelectionMode.MultiSimple; //enables multi select
}

private void btnGetListboxSelection_Click(object sender, EventArgs e)
{
    //Get all selected applications from listbox
    ListBox.SelectedObjectCollection selectedItems = applicationsListbox.SelectedItems;

    if (selectedItems.Count > 0)
    {
        //Loop through all the selected applicatoins in listbox
        foreach (string application in selectedItems)
        {
            //Save selected application to database
            Console.WriteLine(application);
        }
    }
}


Was This Post Helpful? 1
  • +
  • -

  • (3 Pages)
  • +
  • 1
  • 2
  • 3