Adventure Works Access Customer Information

Question

I'm doing a project using the AdventureWorks database and I'm trying to access the database so that the users can change their account information. The users for the website are set up using the ASP.NET Configuration Manager, so I'm not really sure how to go about it. I have a GetCustomer Select method set up to fetch (at this point) just the first and last name of the customers in the Person.Contact table.

[DataObject(true)]
public static class CustomerDB
{
    [DataObjectMethod(DataObjectMethodType.Select)]
    public static List<Customer> GetCustomer()
    {
    List<Customer> CustomerList = new List<Customer>();
    SqlConnection con = new SqlConnection(GetConnectionString());


    string sel = "SELECT Person.Contact.FirstName, Person.Contact.LastName " + 
    "FROM Person.Contact " +
    "JOIN Sales.Individual ON Person.Contact.ContactID = Sales.Individual.ContactID " + 
    "JOIN Sales.Customer ON Sales.Individual.CustomerID = Sales.Customer.CustomerID " +
    "WHERE Sales.Customer.CustomerType = Sales.Individual " +
    "ORDER BY Person.Contact.LastName, Person.Contact.FirstName";

    SqlCommand cmd = new SqlCommand(sel, con);
    con.Open();
    SqlDataReader rdr =
    cmd.ExecuteReader(CommandBehavior.CloseConnection);
    Customer customer;
    while (rdr.Read())
    {
        customer = new Customer();
        customer.FirstName = rdr["FirstName"].ToString();
        customer.LastName = rdr["FirstName"].ToString();
        CustomerList.Add(customer);
    }
    rdr.Close();
    return CustomerList;
}
private static string GetConnectionString()
{
    return ConfigurationManager.ConnectionStrings
    ["AdventureWorksString"].ConnectionString;
}

But I'm getting the error saying: "Invalid object name 'Person.Contact'." which makes me think it's the query that is the problem. The query I'm using is based on one of the sample scenarios on the Msdn website. If someone could point me in the right direction in terms of which tables to use to get the customer info so a user would be able to change their address and password, I'd be very grateful!

Answer 1

I found the Person.Contact as well as Sales.Individual tables from the below steps. Hope this helps.

1. Download the MSFTDBProdSamples.zip from https://archive.codeplex.com/?p=msftdbprodsamples.

2. Unzip this subfolder \sourceCode\sourceCode.zip\Katmai_October2009_Refresh3\AdventureWorks OLTP.

3. Open the instawdb.sql from SQL Server Management Studio in SQLCMD Mode.

4. Make sure to change these line accordingly.

:setvar SqlSamplesDatabasePath "C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\"

:setvar SqlSamplesSourceDataPath "C:\Temp\" -- Copy the AdventureWorks OLTP subfolder here

5. Click Execute.