-
July 4th, 2011, 05:03 AM
#1
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´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 LisaWvL; July 4th, 2011 at 05:04 AM.
Reason: Please ignore the // comments, they´re not up to date!
-
July 7th, 2011, 10:57 AM
#2
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.
-
July 11th, 2011, 02:18 AM
#3
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
-
July 11th, 2011, 02:43 PM
#4
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.
-
July 12th, 2011, 01:45 AM
#5
Re: LINQ Query to retrieve Data from multiple Database tables
i´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 LisaWvL; July 12th, 2011 at 05:31 AM.
-
July 12th, 2011, 01:47 AM
#6
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
-
July 12th, 2011, 07:59 PM
#7
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.
Need a new website? Need custom software developed? IT consultation? Visit www.irnsystems.com to find out more details.
-
July 13th, 2011, 01:50 AM
#8
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´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´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´ve been googling all day
pity u live in the states, the difference in time is not helpful when i´m hoping for an answer :P
cheers
-
July 13th, 2011, 06:42 AM
#9
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.
-
July 13th, 2011, 06:43 AM
#10
Re: LINQ Query to retrieve Data from multiple Database tables
okay, solved my picture problem partly.
but somehow i´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´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´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
-
July 13th, 2011, 06:46 AM
#11
Re: LINQ Query to retrieve Data from multiple Database tables
Originally Posted by DemSamPro
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...
-
July 13th, 2011, 06:52 AM
#12
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´ll look over my other loading methods again
sometimes telling ur problem is already half the solution
Last edited by LisaWvL; July 13th, 2011 at 09:39 AM.
-
July 13th, 2011, 09:01 AM
#13
Re: LINQ Query to retrieve Data from multiple Database tables
ok, no idea what´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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|