I'm fairly new to c#, asp.net and coding in general, and have been following examples online in order to learn. So far so good, well...up until now.
This is the part I'm most concerned about...
In the codebehind, I've tried to set the datasource for the grid based on a method in my BAL.
Here's the codebehind gridsource:
private DataTable GridDataSource()
{
personsBAL pBAL = new personsBAL();
personBO pBO = new personBO();
DataTable dTable = new DataTable();
try
{
pBO.personName = personName.ToString();
pBO.personLast = personLast.ToString();
pBO.genderID = Convert.ToInt32(genderDDL);
pBO.ethnicityID = Convert.ToInt32(ethnicityDDL);
pBO.personAge = int.Parse(personAge.Text);
dTable = pBAL.Search(personName, personLast, genderID, genderDDL, ethnicityDDL, personAge);
}
catch (Exception ee)
{
lblMessage.Text = ee.Message.ToString();
}
finally
{
personBAL = null;
}
return dTable;
}
I'm 100% I did this wrong, but 0% certain how to do this properly. Below I've put in all of the relevant code in the hopes that someone can help me and stop me from continuing to bang my head into the wall.
Thanks in advance.
Stored Procedure:
ALTER PROCEDURE SearchAllPersons
(
@personID int,
@personName varchar(50),
@personLast varchar(50),
@genderID int,
@ethnicityID int,
@personAge int
)
AS
SELECT DISTINCT
person.personID, person.personName, person.personLast, person.genderID, gender.gender, person.ethnicityID,
ethnicity.ethnicity, person.personAge
FROM person INNER JOIN
gender ON person.genderID = gender.genderID INNER JOIN
ethnicity ON person.ethnicityID = ethnicity.ethnicityID
WHERE (person.personName LIKE '%'+ @personName +'%'OR @personName IS NULL) AND (person.personLast LIKE '%' + @personLast + '%' OR
@personLast IS NULL) AND (person.genderID = @genderID OR
0 = @genderID) AND (person.ethnicityID = @ethnicityID OR
0 = @ethnicityID) AND (person.personAge = @personAge OR
0 = @personAge)
RETURN
personsDAL (just the search part)
public DataTable SearchByAll()
{
SqlConnection conn = new SqlConnection(connSTR);
SqlDataAdapter personSearch = new SqlDataAdapter("SearchAllPersons", conn);
SearchAllPersons.SelectCommand.CommandType = CommandType.StoredProcedure;
DataSet dSet = new DataSet();
try
{
SearchAllPersons.Fill(dSet, "personTable");
return dSet.Tables["personTable"];
}
catch
{
throw;
}
finally
{
dSet.Dispose();
SearchAllPersons.Dispose();
conn.Close();
conn.Dispose();
}
}
personsBAL (just the search method)
public DataTable Search(string personName, string personLast, int genderID, int ethnicityID, int personAge)
{
personsDAL pDAL = new personsDAL();
try
{
return Search(personName, personLast, genderID, ethnicityID, personAge);
}
catch
{
throw;
}
finally
{
personsDAL = null;
}
}
personsBO
public class personsBO
{
public personsBO()
{
}
private string personName_BO;
private string personLast_BO;
private int genderID_BO;
private int ethnicityID_BO;
private int personAge_BO;
public string personName
{
get
{
return personName_BO;
}
set
{
if (value == null)
{
throw new Exception("Must type in name");
}
else
{
personName_BO = value;
}
}
}
public string personLast
{
get
{
return personLast_BO;
}
set
{
if (value == null)
{
throw new Exception("Must type in name");
}
else
{
personLast_BO = value;
}
}
}
public int genderID
{
get
{
return genderID_BO;
}
set
{
genderID_BO = value;
}
}
public int ethnicityID
{
get
{
return ethnicityID_BO;
}
set
{
ethnicityID_BO = value;
}
}
public int personAge
{
get
{
return personAge_BO;
}
set
{
personAge_BO = value;
}
}
}
Default.aspx (HTML)
<form id="form1" runat="server">
<div> //in this div...there are the controls for the filters
personNameTextBox
personLastTextBox
ethnicityDropDownList
genderDropDownList
personAgeTextBox
</div>
<div>
<p><a href="Default.aspx">Add Record</a></p>
<asp:Label ID="lblMessage" runat="Server" ForeColor="red" EnableViewState="False"></asp:Label>
<asp:GridView ID="GridView1" runat="server" CellPadding="4" ForeColor="#333333" GridLines="None"
DataKeyNames="PersonID" AutoGenerateEditButton="True" AutoGenerateColumns="False"
OnRowEditing="EditRecord" OnRowUpdating="UpdateRecord" OnRowCancelingEdit="CancelRecord"
OnRowDeleting="DeleteRecord" AllowPaging="True" AllowSorting="true" PageSize="5"
OnPageIndexChanging="ChangePage" OnSorting="SortRecords">
<FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<RowStyle BackColor="#EFF3FB" />
<EditRowStyle BackColor="#2ff1BF" />
<SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
<PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
<HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<AlternatingRowStyle BackColor="White" />
<Columns>
<asp:BoundField DataField="PersonID" HeaderText="Person ID" Visible="false" ReadOnly="True" SortExpression="PersonID" />
<asp:TemplateField HeaderText="First Name" SortExpression="personName">
<ItemTemplate>
<%# Eval("personName") %>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Last Name" SortExpression="personLast">
<ItemTemplate>
<%# Eval("personLast") %>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="genderID" Visible="false">
<ItemTemplate>
<%# Eval("genderID") %>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Gender" SortExpression="gender">
<ItemTemplate>
<%# Eval("gender") %>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="ethnicityID" Visibility="false">
<ItemTemplate>
<%# Eval("ethnicityID") %>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Ethnicity" SortExpression="ethnicity">
<ItemTemplate>
<%# Eval("ethnicity") %>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Age" SortExpression="Age">
<ItemTemplate>
<%# Eval("personAge") %>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Delete?">
<ItemTemplate>
<span önclick="return confirm('Are you sure to Delete?')">
<asp:LinkButton ID="lnBD" runat="server" Text="Delete" CommandName="Delete"></asp:LinkButton>
</span>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
</div>
</form>
OMG FINALLY....
Default.aspx.cs (code behind)
public partial class Default: System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
BindGrid();
}
protected void Button1_Click(object sender, EventArgs e)
{
if (!IsPostBack)
BindGrid();
}
protected void ChangePage(object sender, GridViewPageEventArgs e)
{
GridView1.PageIndex = e.NewPageIndex;
BindGrid();
}
protected void SortRecords(object sender, GridViewSortEventArgs e)
{
DataTable dataTable = GridDataSource();
if (dataTable != null)
{
DataView dataView = new DataView(dataTable);
dataView.Sort = GetSortExpression(e);
GridView1.DataSource = dataView;
GridView1.DataBind();
}
}
#region Private Methods
private void BindGrid()
{
GridView1.DataSource = GridDataSource();
GridView1.DataBind();
}
private DataTable GridDataSource()
{
personsBAL pBAL = new personsBAL();
personBO pBO = new personBO();
DataTable dTable = new DataTable();
try
{
pBO.personName = personName.ToString();
pBO.personLast = personLast.ToString();
pBO.genderID = Convert.ToInt32(genderDDL);
pBO.ethnicityID = Convert.ToInt32(ethnicityDDL);
pBO.personAge = int.Parse(personAge.Text);
dTable = pBAL.Search(personName, personLast, genderID, genderDDL, ethnicityDDL, personAge);
}
catch (Exception ee)
{
lblMessage.Text = ee.Message.ToString();
}
finally
{
personBAL = null;
}
return dTable;
}
private string GetSortExpression(GridViewSortEventArgs e)
{
string sortDirection = string.Empty;
if (ViewState["SortExpression"] != null)
{
if (!ViewState["SortExpression"].ToString().Equals(e.SortExpression.ToLower()))
{
ViewState["SortDirection"] = null;
}
}
if (ViewState["SortDirection"] != null)
{
if (ViewState["SortDirection"].ToString().Equals("ASC"))
{
sortDirection = "DESC";
ViewState["SortDirection"] = "DESC";
}
else
{
sortDirection = "ASC";
ViewState["SortDirection"] = "ASC";
}
}
else
{
ViewState["SortDirection"] = "ASC";
}
ViewState["SortExpression"] = e.SortExpression.ToLower();
return e.SortExpression + " " + sortDirection;
}
#endregion Private Methods
}