esmyth01/GrantsAndServices.cs

## config
<?xml version="1.0"?>

<!--
  For more information on how to configure your ASP.NET application, please visit
  http://go.microsoft.com/fwlink/?LinkId=169433
  -->

<configuration>

    <system.web>
      <compilation debug="true" targetFramework="4.5" />
      <httpRuntime targetFramework="4.5" />
    </system.web>
  <!--here is the connection string to the database.
  In your case instead of localhost use .\sqlexpress-->
  <connectionStrings>
    <add name="BookReviewDbConnectionString"
         connectionString="Data Source=.\sqlexpress;initial catalog=BookReviewDb; integrated security=true"/>
  </connectionStrings>

</configuration>

## default.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Book Review</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <h1>Authors</h1>
        <hr />
        <p>Select an author from the dropdown list to view books from the author.</p>
        <asp:DropDownList ID="AuthorDropDownList" runat="server" AutoPostBack="True" OnSelectedIndexChanged="AuthorDropDownList_SelectedIndexChanged" CssClass="StyleSheet"></asp:DropDownList>
        <asp:GridView ID="BookGridView" runat="server"></asp:GridView>
        <asp:Label ID="ErrorLabel" runat="server" Text=""></asp:Label>
    </div>
    </form>
    <link href="StyleSheet.css" rel="stylesheet" type="text/css" />
</body>
</html>

## default.aspx.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;

public partial class _Default : System.Web.UI.Page
{
    GrantsAndServices gs = new GrantsAndServices();
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
            FillDropDownList();
    }
    protected void AuthorDropDownList_SelectedIndexChanged(object sender, EventArgs e)
    {
        FillGridView();
    }
    protected void FillDropDownList()
    {
        DataTable table = null;
        try
        {
            table = gs.GetAuthors();
        }
        catch (Exception ex)
        {
            ErrorLabel.Text = ex.Message;
        }
        AuthorDropDownList.DataSource = table;
        AuthorDropDownList.DataTextField = "AuthorName";
        AuthorDropDownList.DataValueField = "AuthorKey";
        AuthorDropDownList.DataBind();
    }
    protected void FillGridView()
    {
        int AuthorKey = int.Parse(AuthorDropDownList.SelectedValue.ToString());
        DataTable tbl = null;
        try
        {
            tbl = gs.GetAuthorBooks(AuthorKey);
        }
        catch (Exception ex)
        {
            ErrorLabel.Text = ex.Message;
        }
        BookGridView.DataSource = tbl;
        BookGridView.DataBind();

    }
}

## GrantsAndServices.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
//add these three libraries
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

public class GrantsAndServices
{
    //create connection object
    SqlConnection connect;
	public GrantsAndServices()
	{
        //initialize connection object
        //the connection string is in the web config
        //The config manager lets the code
        //access the web config file
        connect = new SqlConnection(ConfigurationManager.
            ConnectionStrings["BookReviewDbConnectionString"].ToString());
	}

    public DataTable GetAuthors()
    {
        //set up the sql string
        string sql = "Select AuthorKey, AuthorName from Author";
        DataTable tbl;//declare the table
        //initialize the command passing it
        //the sql command and the connection
        SqlCommand cmd = new SqlCommand(sql, connect);
        try
        {
            //we call the ProcessQuery method
            //try catch in case there is an error
            tbl = ProcessQuery(cmd);
        }
        catch(Exception ex)
        {
            //throw it back to the form
            throw ex;
        }
        return tbl;

    }
    public DataTable GetAuthorBooks(int AuthorKey)
    {
        string sql = "SELECT BookTitle, BookEntryDate, BookISBN FROM Book INNER JOIN AuthorBook ON Book.BookKey=AuthorBook.bookkey WHERE AuthorKey=@AuthorKey";
        SqlCommand cmd = new SqlCommand(sql, connect);
        cmd.Parameters.Add("@AuthorKey", AuthorKey);

        DataTable tbl;
        try
        {
            tbl = ProcessQuery(cmd);
        }
        catch (Exception ex)
        {
            throw ex;
        }
        return tbl;
    }


    private DataTable ProcessQuery(SqlCommand cmd)
    {
       //This method processes the queries
        //since the code would be identical in
        //both queries, I refractored it into
        //its own method. No need to repeat more
        //than is necessary
        DataTable table = new DataTable();
        SqlDataReader reader;
        try
        {
            connect.Open();
            reader = cmd.ExecuteReader();
            table.Load(reader);
            connect.Close();
        }
        catch (Exception ex)
        {
            throw ex;
        }
        return table;
    }
}
	<?xml version="1.0"?>

	<!--
	For more information on how to configure your ASP.NET application, please visit
	http://go.microsoft.com/fwlink/?LinkId=169433
	-->

	<configuration>

	<system.web>
	<compilation debug="true" targetFramework="4.5" />
	<httpRuntime targetFramework="4.5" />
	</system.web>
	<!--here is the connection string to the database.
	In your case instead of localhost use .\sqlexpress-->
	<connectionStrings>
	<add name="BookReviewDbConnectionString"
	connectionString="Data Source=.\sqlexpress;initial catalog=BookReviewDb; integrated security=true"/>
	</connectionStrings>

	</configuration>
	<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

	<!DOCTYPE html>

	<html xmlns="http://www.w3.org/1999/xhtml">
	<head runat="server">
	<title>Book Review</title>
	</head>
	<body>
	<form id="form1" runat="server">
	<div>
	<h1>Authors</h1>
	<hr />
	<p>Select an author from the dropdown list to view books from the author.</p>
	<asp:DropDownList ID="AuthorDropDownList" runat="server" AutoPostBack="True" OnSelectedIndexChanged="AuthorDropDownList_SelectedIndexChanged" CssClass="StyleSheet"></asp:DropDownList>
	<asp:GridView ID="BookGridView" runat="server"></asp:GridView>
	<asp:Label ID="ErrorLabel" runat="server" Text=""></asp:Label>
	</div>
	</form>
	<link href="StyleSheet.css" rel="stylesheet" type="text/css" />
	</body>
	</html>
	using System;
	using System.Collections.Generic;
	using System.Linq;
	using System.Web;
	using System.Web.UI;
	using System.Web.UI.WebControls;
	using System.Data;

	public partial class _Default : System.Web.UI.Page
	{
	GrantsAndServices gs = new GrantsAndServices();
	protected void Page_Load(object sender, EventArgs e)
	{
	if (!IsPostBack)
	FillDropDownList();
	}
	protected void AuthorDropDownList_SelectedIndexChanged(object sender, EventArgs e)
	{
	FillGridView();
	}
	protected void FillDropDownList()
	{
	DataTable table = null;
	try
	{
	table = gs.GetAuthors();
	}
	catch (Exception ex)
	{
	ErrorLabel.Text = ex.Message;
	}
	AuthorDropDownList.DataSource = table;
	AuthorDropDownList.DataTextField = "AuthorName";
	AuthorDropDownList.DataValueField = "AuthorKey";
	AuthorDropDownList.DataBind();
	}
	protected void FillGridView()
	{
	int AuthorKey = int.Parse(AuthorDropDownList.SelectedValue.ToString());
	DataTable tbl = null;
	try
	{
	tbl = gs.GetAuthorBooks(AuthorKey);
	}
	catch (Exception ex)
	{
	ErrorLabel.Text = ex.Message;
	}
	BookGridView.DataSource = tbl;
	BookGridView.DataBind();

	}
	}