LINQ Query to retrieve Data from multiple Database tables
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 13 of 13

Thread: LINQ Query to retrieve Data from multiple Database tables

  1. #1
    Join Date
    Jan 2011
    Posts
    49

    LINQ Query to retrieve Data from multiple Database tables

    Hey there yet again.

    I´m still crushing my teeth at using LINQ in C# correctly..
    right now i´m trying to create a view or temporary table to retrieve the data from my 3 tables where The ID == REF_ID.
    i know i got to use the JOIN in some way, but don´t really get yet, how to do that.
    I got 3 tables
    Rezepttabelle (parent table with RezeptID column)
    Kategorien (child table with REF_RezeptID column)
    Zutaten (child table with REF_RezeptID column)

    I want to get all the data joined where REF_RezeptID = RezeptID and display that data in a datagridview on my form1.

    Code:
            /// <summary>
            /// 
            /// </summary>
            public void Rezept_load()
            {
                //a new connection to the database is created.
                SqlConnection con = new SqlConnection();
                //connection string to the database
                con.ConnectionString =
                         @"Data Source=vm-msdb01\fme;Initial Catalog=Lisa;Persist Security Info=True;User ID=sa;Password=fmefme";
                //connection opened
                con.Open();
    
                //the values from the various textboxes are added to the main table.
                //the id is created automatically.
                SqlCommand cmd = new SqlCommand("");
                
                try
                {
                    cmd.ExecuteNonQuery();
                }
    
                //any errors occuring are caught here
                catch (SqlException ex)
                {
                    throw new Exception(ex.ToString());
                }
    
                //the connection is closed
                finally
                {
                    con.Close();
                }
            }
    that&#180;s my code so far, as u can see, the sql query is empty, for i have no idea how to make my statement...
    maybe you can help me, i really hope so

    Greetings and Cheers

    Cheers
    Last edited by Cheers1337; July 4th, 2011 at 05:04 AM. Reason: Please ignore the // comments, they´re not up to date!

  2. #2
    Join Date
    Jun 2011
    Location
    Office: Guilford, VT & Northfield, MA
    Posts
    10

    Re: LINQ Query to retrieve Data from multiple Database tables

    Try this:


    /// <summary>
    ///
    /// </summary>
    public void Rezept_load()
    {
    //a new connection to the database is created.
    SqlConnection con = new SqlConnection();
    //connection string to the database
    con.ConnectionString =
    @"Data Source=vm-msdb01\fme;Initial Catalog=Lisa;Persist Security Info=True;User ID=sa;Password=fmefme";
    //connection opened
    con.Open();


    string sqlstate = "SELECT Rezepttabelle.RezeptID, Kategorien.REF_RezeptID, Zutaten.REF_RezeptID FROM Rezepttabelle, Kategorien JOIN Zutaten AS Ref_RezeptID = RezeptId"

    //the values from the various textboxes are added to the main table.
    //the id is created automatically.
    SqlCommand cmd = new SqlCommand(sqlstate, con);

    try
    {
    //then here put it in a table or display in the gridview
    cmd.ExecuteNonQuery();
    }

    //any errors occuring are caught here
    catch (SqlException ex)
    {
    throw new Exception(ex.ToString());
    }

    //the connection is closed
    finally
    {
    con.Close();
    }
    }
    Need a new website? Need custom software developed? IT consultation? Visit www.irnsystems.com to find out more details.

  3. #3
    Join Date
    Jan 2011
    Posts
    49

    Re: LINQ Query to retrieve Data from multiple Database tables

    Hey, good start i guess, but i get an "incorrect syntax near =" error. something is wrong

    //then here put it in a table or display in the gridview
    cmd.ExecuteNonQuery();

    and how do u mean that? my sqlstatement is not on form one, how do i access the datagridview there ? ok, i guess i could work this out on my own. but dont i have to create a new table to store my joined and selected data in ? or maybe a view?
    when i simply join my tables where ref-rezeptid, it will give me ALL my data, cause all recipes have mathcing entrys in the other tables so i think i need a second way of identification, right? i think i have to rebuild my whole tables

  4. #4
    Join Date
    Jun 2011
    Location
    Office: Guilford, VT & Northfield, MA
    Posts
    10

    Re: LINQ Query to retrieve Data from multiple Database tables

    So basically you want all the records that match in table Kategorien, and table Zutaten where it equals all the records in table Rezepttabelle?


    parent table:
    1.dog
    2.cat
    3.bird


    table child:
    1.dog
    2.reptile
    3.bird

    table child 2:
    1.dog
    2.monkey
    3.fish



    new table:?
    1.dog



    or new table:?
    1.dog
    2.bird

    after we figure this out, then I will show you how to acces the sql statement from another place on the program.
    Need a new website? Need custom software developed? IT consultation? Visit www.irnsystems.com to find out more details.

  5. #5
    Join Date
    Jan 2011
    Posts
    49

    Re: LINQ Query to retrieve Data from multiple Database tables

    i&#180;ll try to explain my tables ^^
    i have a table of recipes. it contains the name, the time, the price, and the automatically generated id.
    second i have a table containing categorys. each recipe can have up to 4 categorys. they reference by the ref_id from the first table.
    third i have a table of ingredients. it contains the name of the ingredient, the amount and the "type of amount" (like kilogram, gramm, litres etc.) each recipe of course has multiple ingredients. they are also referenced my the ref_id from the main table.
    my mistake in the first place is, that i built 1-n tables. now when i put my string in to show ALL information for ONE recipe (the one, which name is written in the textbox IDNameBox.

    Code:
    GetData("SELECT DISTINCT Name, Zeit, Preis, Anleitung, Zutat, Menge, Mengentyp, Kategorie " +
                        "FROM Rezepttabelle " +
                        "LEFT JOIN Zutaten ON Zutaten.REF_RezeptID = Rezepttabelle.RezeptID " +
                        "LEFT JOIN Kategorien ON Kategorien.REF_RezeptID = Zutaten.REF_RezeptID " +
                        "WHERE Rezepttabelle.Name = ('" + IDNameBox.Text + "')");
    the output is messed up
    i get each unique line, but for every combination of categorys and ingredients possible.

    u know how i mean? so i guess i need to rebuild my tables first, but i still got no idea how...

    for now i can insert into my 3 tables, i can reload, i can delete.

    help, thx in advance

    Lisa
    Last edited by Cheers1337; July 12th, 2011 at 05:31 AM.

  6. #6
    Join Date
    Jan 2011
    Posts
    49

    Re: LINQ Query to retrieve Data from multiple Database tables

    ah, i forgot to post my GetData method...

    Code:
            /// <summary>
            /// 
            /// </summary>
            /// <param name="selectCommand"></param>
            private void GetData(string selectCommand)
            {
                try
                {
                    String connectionString =
                    @"Data Source=vm-msdb01\fme;Initial Catalog=Lisa;Persist Security Info=True;User ID=sa;Password=fmefme";
    
                    // Create a new data adapter based on the specified query.
                    dataAdapter = new SqlDataAdapter(selectCommand, connectionString);
    
                    // Create a command builder to generate SQL update, insert, and
                    // delete commands based on selectCommand. These are used to
                    // update and manipulate the database.
                    SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dataAdapter);
    
                    // Populate a new data table and bind it to the BindingSource.
                    DataTable table = new DataTable();
                    table.Locale = CultureInfo.InvariantCulture;
                    dataAdapter.Fill(table);
                    bindingSource1.DataSource = table;
                }
    
                //any errors occuring are caught here
                catch (SqlException ex)
                {
                    throw new Exception(ex.ToString());
                }
    
                //the connection is closed
                finally
                {
    
                }
            }
    cheers

  7. #7
    Join Date
    Jun 2011
    Location
    Office: Guilford, VT & Northfield, MA
    Posts
    10

    Re: LINQ Query to retrieve Data from multiple Database tables

    You want to display all the recipe in the textbox?

    I made a demo of tables, on how I understood on what your looking for.
    Attached Images Attached Images    
    Need a new website? Need custom software developed? IT consultation? Visit www.irnsystems.com to find out more details.

  8. #8
    Join Date
    Jan 2011
    Posts
    49

    Re: LINQ Query to retrieve Data from multiple Database tables

    i want to display the categories in a gridview, the ingredients in a grieview and the description in a textbox. further i want the user to be able to store upload picture and save it and display that in a picture box.
    i have a page where all the recommendet data can be inserted by the user and stored in the right tables in the database.
    but:
    i have recipe with ID 1, the name, time, cost etc. and 3 categorys let&#180;s say mexican food, supper, vegetarian. and the ingredients 1 piece of broccoli, 1 cup of soy sauce and 1 tortilla.
    they all have the REF_ID 1.
    now when i tell my database to retrieve everything from all 3 tables that has the REF_ID 1 it gives me all kinds of combination.
    there&#180;s combination broccoli mexican, broccoli vegetarian, brocolli supper, tortilla mexican, tortilla vegetarian, tortilla supper etc.
    he gives me all the data that has the ref_id in common...
    dunno how to fix that

    but now i got another problem anyway. i tried to make it possible for the user to upload a picture. and convet that to a bit array. store that in the database and when i retrieve it, convert it back to image and display it.

    here my code

    Code:
            /// <summary>
            /// 
            /// </summary>
            public void Image_save()
            {
                //through the instruction below, we save the
                //image to byte in the object "stream".
                //pictureBox1.Image.Save(stream, System.Drawing.Imaging.ImageFormat.Jpeg);
    
                SqlConnection con = new SqlConnection
                                   (@"Data Source=vm-msdb01\fme;Initial Catalog=Lisa;Persist Security Info=True;User ID=sa;Password=fmefme");
    
                //connection opened
                con.Open();
    
                //Below is the most important part, actually you are
                //transferring the bytes of the array
                //to the pic which is also of kind byte[]
                SqlCommand cmd = new SqlCommand("INSERT INTO Imagesstore (OriginalPath, ImageData, REF_Name, REF_RezeptID) VALUES (@path, @pic, @refname, @refid)", con);
                cmd.Parameters.Add(new SqlParameter("@path", SqlDbType.VarChar)).Value = this.OriginalPath;
                cmd.Parameters.Add(new SqlParameter("@pic", SqlDbType.Image)).Value = this.ImageData;
                cmd.Parameters.Add(new SqlParameter("@refname", SqlDbType.VarChar, 20)).Value = getIDName();
                cmd.Parameters.Add(new SqlParameter("@refid", SqlDbType.Int)).Value = RetrieveID();
    
                //the command is executed.
                try
                {
                    //cmd.Parameters.AddWithValue("@pic", ImageData);
                    cmd.ExecuteNonQuery();
                }
    
                //any errors occuring are caught here
                catch (SqlException ex)
                {
                    throw new Exception(ex.ToString());
                }
    
                //the connection is closed
                finally
                {
                    con.Close();
                }
            }
    this.ImageData is a byteArray.

    the conversion is here

    Code:
                MemoryStream stream = new MemoryStream();
                //through the instruction below, we save the
                //image to byte in the object "stream".
    
                //Below is the most important part, actually you are
                //transferring the bytes of the array
                //to the pic which is also of kind byte[]
                stream.Position = 0;
                byte[] pic = stream.ToArray();
    
                Bild.ImageData = pic;
    
                Bild.Image_save();
    Code:
            /// <summary>
            /// 
            /// </summary>
            public void Image_load()
            {
                SqlConnection con = new SqlConnection
                                 (@"Data Source=vm-msdb01\fme;Initial Catalog=Lisa;Persist Security Info=True;User ID=sa;Password=fmefme");
                SqlCommand cmd = new SqlCommand
                                    ("SELECT ImageData FROM Imagesstore WHERE OriginalPath = '" + this.OriginalPath + "'", con);
                //for retrieving the image field in SQL SERVER EXPRESS
                //Database you should first bring
                //that image in DataList or DataTable
                //then add the content to the byte[] array.
                //That's ALL!
                con.Open();
                try
                {
                        SqlDataAdapter dp = new SqlDataAdapter(cmd);
                        DataSet ds = new DataSet("Imagesstore");
                        dp.Fill(ds, "Imagesstore");
    
                        DataRow myRow = ds.Tables["Imagesstore"].Rows[0];
    
                        byte[] MyData = (byte[])cmd.ExecuteScalar();
    
                        MemoryStream stream = new MemoryStream(MyData);
                        stream.Position = 0;
                        this.Picture = Image.FromStream(stream);
                }
    
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
    
                finally
                {
                    con.Close();
                }
            }
    this.Picture is an image.

    but when i try to retrieve the byteArray and convert it back to image here

    Code:
     MemoryStream stream = new MemoryStream(MyData);
                        stream.Position = 0;
                        this.Picture = Image.FromStream(stream);
    i get an error that the parameter is not valid...
    i&#180;ve been googling all day
    pity u live in the states, the difference in time is not helpful when i&#180;m hoping for an answer :P

    cheers

  9. #9
    Join Date
    Jun 2011
    Location
    Office: Guilford, VT & Northfield, MA
    Posts
    10

    Re: LINQ Query to retrieve Data from multiple Database tables

    Ok before we go on, you want the categories displayed
    in a gridview, ingredients in another gridview?

    With the picture upload thing, you can let the user upload the image onto the server, and then store the url in the database.
    Need a new website? Need custom software developed? IT consultation? Visit www.irnsystems.com to find out more details.

  10. #10
    Join Date
    Jan 2011
    Posts
    49

    Re: LINQ Query to retrieve Data from multiple Database tables

    okay, solved my picture problem partly.

    but somehow i&#180;m able to retrieve and display my picture on one form, but not on the other. even though i use the same method...

    on this form it&#180;s working

    on form "create new recipe"

    Code:
            /// <summary>
            /// 
            /// </summary>
            /// <param name="sender"></param>
            /// <param name="e"></param>
            private void vorschauButton_Click(object sender, EventArgs e)
            {
                Bild.Image_load();
                pictureBox1.Image = Bild.Picture;
            }
    in my Images Class

    Code:
            /// <summary>
            /// 
            /// </summary>
            public void Image_load()
            {
                SqlConnection con = new SqlConnection
                     (@"Data Source=vm-msdb01\fme;Initial Catalog=Lisa;Persist Security Info=True;User ID=sa;Password=fmefme");
                //for retrieving the image field in SQL SERVER EXPRESS
                //Database you should first bring
                //that image in DataList or DataTable
                //then add the content to the byte[] array.
                //That's ALL!
                con.Open();
                //Initialize SQL adapter.
                SqlDataAdapter ADAP = new SqlDataAdapter("SELECT ImageData FROM Imagesstore " +
                                                         "WHERE OriginalPath = '" + this.OriginalPath + "'", con);
                try
                {
                    //Initialize Dataset.
                    DataSet DS = new DataSet();
                    //Fill dataset with ImagesStore table.
                    ADAP.Fill(DS, "Imagesstore");
                    //Fill Grid with dataset.
                    this.DT = DS.Tables["Imagesstore"];
                    DataRow myRow;
                    myRow = DS.Tables["Imagesstore"].Rows[0];
                    byte[] MyData = new byte[0];
    
                    MyData = (byte[])myRow["ImageData"];
                    
    
                    MemoryStream stream = new MemoryStream(MyData);
                    //With the code below, you are in fact converting the byte array of image
                    //to the real image.
                    this.Picture = Image.FromStream(stream);
    
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString());
                }
                finally
                {
                    con.Close();
                }
            }
    on this form it isn&#180;t working
    my display recipe form

    Code:
            /// <summary>
            /// 
            /// </summary>
            /// <param name="sender"></param>
            /// <param name="e"></param>
            private void Rezept_anzeigen_Load(object sender, EventArgs e)
            {
                Rezept.Rezept_load();
                        label3.Text = Rezept.Name;
                        textBox1.Text = Rezept.Anleitung;
    
                Zutat.Zutaten_load();
                      dataGridView1.DataSource = Zutat.DT;
    
                Kategorien.Kategorien_load();
                      dataGridView2.DataSource = Kategorien.DT;
    
                Bild.Image_load();
                      pictureBox1.Image = Bild.Picture;
                    
            }
    no idea why...
    i use normal picture boxes in both. on the second form i get the error

    There is no row at position 0.

    one problem after the other

  11. #11
    Join Date
    Jan 2011
    Posts
    49

    Re: LINQ Query to retrieve Data from multiple Database tables

    Quote Originally Posted by DemSamPro View Post
    Ok before we go on, you want the categories displayed
    in a gridview, ingredients in another gridview?

    With the picture upload thing, you can let the user upload the image onto the server, and then store the url in the database.
    yep. 2 gridviews, and most other information of the recipe in a textbox or labels. and the picture in a picture box.

    i think i got the gridviews working now. but not the rest

    Code:
                Rezept.Rezept_load();
                        label3.Text = Rezept.Name;
                        textBox1.Text = Rezept.Anleitung;
    
                Zutat.Zutaten_load();
                      dataGridView1.DataSource = Zutat.DT;
    
                Kategorien.Kategorien_load();
                      dataGridView2.DataSource = Kategorien.DT;
    
                Bild.Image_load();
                      pictureBox1.Image = Bild.Picture;
    this doesn´t seem to retrieve me the right information from the databases...

  12. #12
    Join Date
    Jan 2011
    Posts
    49

    Re: LINQ Query to retrieve Data from multiple Database tables

    ok, solved that too had the wrong query -.-

    but i still need to retrieve the rest of the information, i&#180;ll look over my other loading methods again

    sometimes telling ur problem is already half the solution
    Last edited by Cheers1337; July 13th, 2011 at 09:39 AM.

  13. #13
    Join Date
    Jan 2011
    Posts
    49

    Re: LINQ Query to retrieve Data from multiple Database tables

    ok, no idea what&#180;s going wrong

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Azure Activities Information Page

Windows Mobile Development Center


Click Here to Expand Forum to Full Width

This is a CodeGuru survey question.


Featured


HTML5 Development Center