I'm new with C# programming and ASP.NET i and i'm doing my final project for graduating. I'm creating an ASP.NET application for managing library in university. So i have situation. I want to search for books(using GridViews), but i need to create joins
because i normalized(to 3rd forms) my tables and i want to show specific columns( not all columns from entity). I say that because i use Entity Framework. Besides i have some textboxes and dropdownlist and i want to take their values so i can use it in my
joins as parameters.
I'm not sure if i'm using the right code because i have some errors. If you have any ideas or better ways please.
If you need more code like aspx or something please tell me. 10x in advance.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.Objects;
using LibraryData;
//using System.Data.Entity;
namespace Library
{
public partial class Search : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Button_Search_Srch_Click(object sender, EventArgs e)
{
LIBRARYEntities context = new LIBRARYEntities();
string SQL_GridViewQuery =
"SELECT tb.title, tb.additional_name, tb.release_date, tb.in_stock, " +
"tbl.language, tbp.publisher, tbp.city, tbc.category, tbg.genre, tban.first_name, tban.last_name " +
"FROM tbl_book tb " +
"INNER JOIN tbl_book_book_author_name tbban on tb.book_id = tbban.book_id " +
"INNER JOIN tbl_book_author_name tban on tbban.book_id = tbban.book_author_name_id " +
"INNER JOIN tbl_book_language tbl on tb.book_language_id = tbl.book_language_id " +
"INNER JOIN tbl_book_publishing tbp on tb.book_publishing_id = tbp.book_publishing_id " +
"INNER JOIN tbl_book_genre tbg on tb.book_genre_id = tbg.book_genre_id " +
"INNER JOIN tbl_book_category tbc on tb.book_category_id = tbc.book_category_id " +
"WHERE tb.title like {0} AND tban.first_name = {1} AND tban.last_name = {2} " +
"AND tbg.genre = {3} AND tbc.category = {4} AND tbl.language = {5} "
;
object[] parameters = { TextBox_SrchBTitle_Srch.Text,
TextBox_FName_Srch.Text,
TextBox_LName_Srch.Text,
DropDownList_Genre_Srch.SelectedValue,
DropDownList_Category_Srch.SelectedValue,
DropDownList_Language_Srch.SelectedValue
};
ObjectResult<GridViewResult> books = context.ExecuteStoreQuery<GridViewResult>(SQL_GridViewQuery, parameters);
if (context != null)
{
GridView_Search_Srch.DataSource = books;
GridView_Search_Srch.DataBind();
GridView_Search_Srch.Visible = true;
}
else
{
Label_NoResult_Srch.Visible = true;
}
public class GridViewResult
{
public string title { get; set; }
public string additional_name { get; set; }
public DateTime release_date { get; set; }
public int in_stock { get; set; }
public string language { get; set; }
public string publisher { get; set; }
public string city { get; set; }
public string category { get; set; }
public string genre { get; set; }
public string first_name { get; set; }
public string last_name { get; set; }
}
}
}
}
public class GridViewResult
{
public string title { get; set; }
public string additional_name { get; set; }
public DateTime release_date { get; set; }
public int in_stock { get; set; }
public string language { get; set; }
public string publisher { get; set; }
public string city { get; set; }
public string category { get; set; }
public string genre { get; set; }
public string first_name { get; set; }
public string last_name { get; set; }
}
}
}
}
after last scope it says:
"Error 2 Type or namespace definition, or end-of-file expected C:\Documents and Settings\Veso\my documents\visual studio 2010\Projects\Library\Library\Search.aspx.cs 77 1 Library"
Thank you but i'v tried this and can't help with joins and parameters. Someone else?
What error have you got now？Any exceptions？
For what i've posted i get 2 errors on compilation:
First: if (context != null) { GridView_Search_Srch.DataSource = books; GridView_Search_Srch.DataBind(); GridView_Search_Srch.Visible = true; }
else { Label_NoResult_Srch.Visible = true; } after last scope it says: "} expected"
Second: public class GridViewResult { public string title { get; set; } public string additional_name { get; set; } public DateTime release_date { get;
set; } public int in_stock { get; set; } public string language { get; set; } public string publisher { get; set; } public string city { get; set; } public
string category { get; set; } public string genre { get; set; } public string first_name { get; set; } public string last_name { get; set; } } } } } after last scope
it says:
"Error 2 Type or namespace definition, or end-of-file expected C:\Documents and Settings\Veso\my documents\visual studio 2010\Projects\Library\Library\Search.aspx.cs 77 1 Library"
Both of the two errors are pointing to something that there's a syntax error with your codes……So please try to modify your codes as this：
Because you cannot put a self-defined class inside another function，the solution is that you should move the class into the namespace—— public partial class Search : System.Web.UI.Page {
//Put the class here……
public class GridViewResult
{
public string title { get; set; }
public string additional_name { get; set; }
public DateTime release_date { get; set; }
public int in_stock { get; set; }
public string language { get; set; }
public string publisher { get; set; }
public string city { get; set; }
public string category { get; set; }
public string genre { get; set; }
public string first_name { get; set; }
public string last_name { get; set; }
}
You are right. I understand my additional class should stay in the public partial class ....
But i want to ask you for one more advice:
As you see i use native sql which complete my task, but i'm using a gridview too. When search for book it show but as header of the gridview, column names are as they are in the Database - "first_name" and so on. I try to change it in the query like: "SELECT
tb.title AS Title..." and it is not working. How can i show names that i want for the columns returned from the query?
P.S The result of the query shows only when i change the AutoGenerateColumns property to true, but then explained what names comes. I try boundfields too - not working.
The result of the query shows only when i change the AutoGenerateColumns property to true, but then explained what names comes. I try boundfields too - not working.
In fact，I still strongly suggest you trying to use by setting AutoGenerateColumns=True，because this will generate all kinds of different kinds of BoundField directly；But if you use BoundFields，your table cannot be dynamcially changed，and that must be a fixed
one……
Veschich
Member
29 Points
279 Posts
Joining tables via Entity Framework and using parameters
Jun 30, 2012 08:47 AM|LINK
Hi everyone,
I'm new with C# programming and ASP.NET i and i'm doing my final project for graduating. I'm creating an ASP.NET application for managing library in university. So i have situation. I want to search for books(using GridViews), but i need to create joins because i normalized(to 3rd forms) my tables and i want to show specific columns( not all columns from entity). I say that because i use Entity Framework. Besides i have some textboxes and dropdownlist and i want to take their values so i can use it in my joins as parameters.
I'm not sure if i'm using the right code because i have some errors. If you have any ideas or better ways please.
If you need more code like aspx or something please tell me. 10x in advance.
using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using System.Data.Objects; using LibraryData; //using System.Data.Entity; namespace Library { public partial class Search : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { } protected void Button_Search_Srch_Click(object sender, EventArgs e) { LIBRARYEntities context = new LIBRARYEntities(); string SQL_GridViewQuery = "SELECT tb.title, tb.additional_name, tb.release_date, tb.in_stock, " + "tbl.language, tbp.publisher, tbp.city, tbc.category, tbg.genre, tban.first_name, tban.last_name " + "FROM tbl_book tb " + "INNER JOIN tbl_book_book_author_name tbban on tb.book_id = tbban.book_id " + "INNER JOIN tbl_book_author_name tban on tbban.book_id = tbban.book_author_name_id " + "INNER JOIN tbl_book_language tbl on tb.book_language_id = tbl.book_language_id " + "INNER JOIN tbl_book_publishing tbp on tb.book_publishing_id = tbp.book_publishing_id " + "INNER JOIN tbl_book_genre tbg on tb.book_genre_id = tbg.book_genre_id " + "INNER JOIN tbl_book_category tbc on tb.book_category_id = tbc.book_category_id " + "WHERE tb.title like {0} AND tban.first_name = {1} AND tban.last_name = {2} " + "AND tbg.genre = {3} AND tbc.category = {4} AND tbl.language = {5} " ; object[] parameters = { TextBox_SrchBTitle_Srch.Text, TextBox_FName_Srch.Text, TextBox_LName_Srch.Text, DropDownList_Genre_Srch.SelectedValue, DropDownList_Category_Srch.SelectedValue, DropDownList_Language_Srch.SelectedValue }; ObjectResult<GridViewResult> books = context.ExecuteStoreQuery<GridViewResult>(SQL_GridViewQuery, parameters); if (context != null) { GridView_Search_Srch.DataSource = books; GridView_Search_Srch.DataBind(); GridView_Search_Srch.Visible = true; } else { Label_NoResult_Srch.Visible = true; } public class GridViewResult { public string title { get; set; } public string additional_name { get; set; } public DateTime release_date { get; set; } public int in_stock { get; set; } public string language { get; set; } public string publisher { get; set; } public string city { get; set; } public string category { get; set; } public string genre { get; set; } public string first_name { get; set; } public string last_name { get; set; } } } } }alvingeorge
Participant
925 Points
203 Posts
Re: Joining tables via Entity Framework and using parameters
Jun 30, 2012 11:43 AM|LINK
Check this link http://www.datasprings.com/resources/articles-information/a-quick-guide-to-using-entity-framework
Veschich
Member
29 Points
279 Posts
Re: Joining tables via Entity Framework and using parameters
Jun 30, 2012 12:26 PM|LINK
Thank you but i'v tried this and can't help with joins and parameters. Someone else?
Veschich
Member
29 Points
279 Posts
Re: Joining tables via Entity Framework and using parameters
Jul 01, 2012 09:22 PM|LINK
I'm really stucked with this. I will appreciate some help.
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: Joining tables via Entity Framework and using parameters
Jul 02, 2012 01:52 AM|LINK
What error have you got now？Any exceptions？
Veschich
Member
29 Points
279 Posts
Re: Joining tables via Entity Framework and using parameters
Jul 02, 2012 02:21 PM|LINK
For what i've posted i get 2 errors on compilation:
First:
if (context != null) { GridView_Search_Srch.DataSource = books; GridView_Search_Srch.DataBind(); GridView_Search_Srch.Visible = true; } else { Label_NoResult_Srch.Visible = true; }after last scope it says: "} expected"
Second:
public class GridViewResult { public string title { get; set; } public string additional_name { get; set; } public DateTime release_date { get; set; } public int in_stock { get; set; } public string language { get; set; } public string publisher { get; set; } public string city { get; set; } public string category { get; set; } public string genre { get; set; } public string first_name { get; set; } public string last_name { get; set; } } } } }after last scope it says:
"Error 2 Type or namespace definition, or end-of-file expected C:\Documents and Settings\Veso\my documents\visual studio 2010\Projects\Library\Library\Search.aspx.cs 77 1 Library"
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: Joining tables via Entity Framework and using parameters
Jul 03, 2012 01:15 AM|LINK
Both of the two errors are pointing to something that there's a syntax error with your codes……So please try to modify your codes as this：
Because you cannot put a self-defined class inside another function，the solution is that you should move the class into the namespace—— public partial class Search : System.Web.UI.Page {
//Put the class here……
public class GridViewResult
{
public string title { get; set; }
public string additional_name { get; set; }
public DateTime release_date { get; set; }
public int in_stock { get; set; }
public string language { get; set; }
public string publisher { get; set; }
public string city { get; set; }
public string category { get; set; }
public string genre { get; set; }
public string first_name { get; set; }
public string last_name { get; set; }
}
protected void Page_Load(object sender, EventArgs e)
{ }
protected void Button_Search_Srch_Click(object sender, EventArgs e)
{
//Here comes your logics……
}
}
Veschich
Member
29 Points
279 Posts
Re: Joining tables via Entity Framework and using parameters
Jul 03, 2012 02:37 PM|LINK
Decker Dong - MSFT
You are right. I understand my additional class should stay in the public partial class ....
But i want to ask you for one more advice:
As you see i use native sql which complete my task, but i'm using a gridview too. When search for book it show but as header of the gridview, column names are as they are in the Database - "first_name" and so on. I try to change it in the query like: "SELECT tb.title AS Title..." and it is not working. How can i show names that i want for the columns returned from the query?
P.S The result of the query shows only when i change the AutoGenerateColumns property to true, but then explained what names comes. I try boundfields too - not working.
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: Joining tables via Entity Framework and using parameters
Jul 04, 2012 01:05 AM|LINK
In fact，I still strongly suggest you trying to use by setting AutoGenerateColumns=True，because this will generate all kinds of different kinds of BoundField directly；But if you use BoundFields，your table cannot be dynamcially changed，and that must be a fixed one……
Veschich
Member
29 Points
279 Posts
Re: Joining tables via Entity Framework and using parameters
Jul 04, 2012 07:15 AM|LINK
Ok. I can use"AutoGenerateColumns" but as i said have 2 problems.
1. I can't change the names of the fields( and i really have to because first_name it's not normal representation for column name etc.).
2. book_id shows( and of course i don't need it, so).
How to handle this 2 problems?