|
Subject:
|
Beginning Visual C# Exercises - Chapter 19 Answers
|
|
Posted By:
|
seblake
|
Post Date:
|
10/7/2004 5:46:31 PM
|
Add ADO.NET and SQL Server namespace directives to top for all following...
using System.Data;
using System.Data.SqlClient;
1. In Main() method...
// Specify SQL Server specific connection string
SqlConnection thisConnection = new SqlConnection(
@"Data Source=(local);Integrated Security=SSPI;Initial Catalog=northwind");
// Open connection
thisConnection.Open();
// Create command for this connection
SqlCommand thisCommand = thisConnection.CreateCommand();
// Specify SQL query for this command
thisCommand.CommandText = "SELECT EmployeeID, LastName from Employees";
// Execute DataReader for specified command
SqlDataReader thisReader = thisCommand.ExecuteReader();
//While there are rows to read
while (thisReader.Read())
{
// Output ID and name columns
Console.WriteLine("\t{0}\t{1}", thisReader["EmployeeID"], thisReader["LastName"]);
}
// Close reader & connection
thisReader.Close();
thisConnection.Close();
2. Replace assignment in "UpdatingData" class Main()...
// thisDataSet.Tables["Customers"].Rows[9]["CompanyName"] = "Acme, Inc.";
// Toggle between these two company names for exercise
string companyNameAcme = "Acme, Inc.";
string companyNameBottom = "Bottom-Dollar Markets";
// Change data in Customers table, row 9, CompanyName column
if ((string)thisDataSet.Tables["Customers"].Rows[9]["CompanyName"] == companyNameAcme)
{
thisDataSet.Tables["Customers"].Rows[9]["CompanyName"] = companyNameAcme;
}
else
{
thisDataSet.Tables["Customers"].Rows[9]["CompanyName"] = companyNameBottom;
}
3. In Main(), ask for CustomerID then verify that it exists...
Console.WriteLine("Enter five character upper case Customer ID");
string theCustomerID = Console.ReadLine();
if (theCustomerID.Length == 5)
{
// Specify SQL Server specific connection string
SqlConnection thisConnection = new SqlConnection(
@"Data Source=(local);Integrated Security=SSPI;Initial Catalog=northwind");
// Create DataSet to contain related data tables, rows, and columns
DataSet thisDataSet = new DataSet();
// Create DataAdapter object
SqlDataAdapter thisAdapter = new SqlDataAdapter(
"SELECT CustomerID, CompanyName From Customers", thisConnection);
thisAdapter.Fill(thisDataSet, "Customers");
// Set up keys object for defining primary key
DataColumn[] keys = new DataColumn[1];
keys[0] = thisDataSet.Tables["Customers"].Columns["CustomerID"];
thisDataSet.Tables["Customers"].PrimaryKey = keys;
DataRow findRow = thisDataSet.Tables["Customers"].Rows.Find(theCustomerID);
if (findRow == null)
{
// Advise user his input is bogus
Console.WriteLine("Your requested customer ID '" + theCustomerID + "' not found");
}
else
{
// Advise user his input found a customer
Console.WriteLine(theCustomerID + " represents NorthWind customer {0}",
findRow["CompanyName"]);
}
}
else
{
// Advise user his input is dismal
Console.WriteLine("Your input was not valid to commence search.");
}
4. Code within the MAIN() function...
Note that Orders SQL table has three fields with data constraints
// Specify SQL Server specific connection string
SqlConnection thisConnection = new SqlConnection(
@"Data Source=(local);Integrated Security=SSPI;Initial Catalog=northwind");
// Create DataAdapter object
SqlDataAdapter thisAdapter = new SqlDataAdapter(
"SELECT CustomerID,EmployeeID,OrderDate,RequiredDate," +
"ShipVia,Freight,ShipName,ShipAddress,ShipCity,ShipRegion," +
"ShipPostalCode,ShipCountry From Orders", thisConnection);
// Create the CommandBuilder object to build SQL commands
SqlCommandBuilder thisBuilder = new SqlCommandBuilder(thisAdapter);
// Create DataSet to contain related data tables, rows, and columns
DataSet thisDataSet = new DataSet();
// Fill DataSet using query defined previously for DataAdapter
thisAdapter.Fill(thisDataSet, "Orders");
// Observe CustomerID foreign key constraint in Orders
string customerZacheryZithers = "ZACZI";
// Observe EmployeeID foreign key constraint in Orders
int employeeSteveBuchanan = 5;
// Observe ShipVia foreign key constraint in Orders
int shipperUnitedPackage = 2;
// New row #1
DataRow rowOrders01 = thisDataSet.Tables["Orders"].NewRow();
rowOrders01["CustomerID"] = customerZacheryZithers;
rowOrders01["EmployeeID"] = employeeSteveBuchanan;
rowOrders01["OrderDate"] = Convert.ToDateTime("2004/08/30");
rowOrders01["RequiredDate"] = Convert.ToDateTime("2004/09/15");
rowOrders01["ShipVia"] = shipperUnitedPackage;
rowOrders01["Freight"] = Convert.ToDecimal("17.17");
rowOrders01["ShipName"] = "The Shopping Center";
rowOrders01["ShipAddress"] = "123 East Michigan Avenue";
rowOrders01["ShipCity"] = "Paw Paw";
rowOrders01["ShipRegion"] = "MI";
rowOrders01["ShipPostalCode"] = "49079";
rowOrders01["ShipCountry"] = "USA";
thisDataSet.Tables["Orders"].Rows.Add(rowOrders01);
// New row #2
DataRow rowOrders02 = thisDataSet.Tables["Orders"].NewRow();
rowOrders02["CustomerID"] = customerZacheryZithers;
rowOrders02["EmployeeID"] = employeeSteveBuchanan;
rowOrders02["OrderDate"] = Convert.ToDateTime("2004/09/21");
rowOrders02["RequiredDate"] = Convert.ToDateTime("2004/10/15");
rowOrders02["ShipVia"] = shipperUnitedPackage;
rowOrders02["Freight"] = Convert.ToDecimal("10.12");
rowOrders02["ShipName"] = "Jewel Supermarkets";
rowOrders02["ShipAddress"] = "123 West Main Street";
rowOrders02["ShipCity"] = "Dyer";
rowOrders02["ShipRegion"] = "IN";
rowOrders02["ShipPostalCode"] = "47923";
rowOrders02["ShipCountry"] = "USA";
thisDataSet.Tables["Orders"].Rows.Add(rowOrders02);
// Write to SQL table
try
{
thisAdapter.Update(thisDataSet, "Orders");
}
catch (System.Data.SqlClient.SqlException e)
{
// This is how I found out about the constraints
Console.WriteLine("Exception is {0}", e.Message);
return;
}
// Now display orders within last six months; DATEDIFF() is T-SQL function
SqlCommand thisCommand = thisConnection.CreateCommand();
thisCommand.CommandText =
"SELECT OrderID, RequiredDate, ShipName, ShipAddress, " +
"RTRIM(ShipCity) + ', ' + ShipRegion AS ShipLocale " +
"FROM Orders WHERE CustomerID='" + customerZacheryZithers + "' AND " +
"(DATEDIFF(day, OrderDate, GETDATE()) <= 180)";
thisConnection.Open();
// Load result set into the data reader
SqlDataReader thisReader = thisCommand.ExecuteReader();
while (thisReader.Read())
{
Console.WriteLine("{0}\t{1}\n{2}\n{3}\n{4}\n",
thisReader["OrderID"], thisReader["RequiredDate"],
thisReader["ShipName"], thisReader["ShipAddress"],
thisReader["ShipLocale"]);
}
thisReader.Close();
thisConnection.Close();
Console.ReadLine();
5. Code within the MAIN() function...
// Specify SQL Server specific connection string
SqlConnection thisConnection = new SqlConnection(
@"Data Source=(local);Integrated Security=SSPI;Initial Catalog=northwind");
// Create DataSet to contain related data tables, rows, and columns
DataSet thisDataSet = new DataSet();
// Set up DataAdapter objects for each table and fill
SqlDataAdapter supplierAdapter = new SqlDataAdapter(
"SELECT SupplierID, CompanyName, ContactName from Suppliers",
thisConnection);
supplierAdapter.Fill(thisDataSet, "Suppliers");
SqlDataAdapter productAdapter = new SqlDataAdapter(
"SELECT SupplierID, CategoryID, ProductName from Products",
thisConnection);
productAdapter.Fill(thisDataSet, "Products");
SqlDataAdapter categoryAdapter = new SqlDataAdapter(
"SELECT CategoryID, CategoryName, Description from Categories",
thisConnection);
categoryAdapter.Fill(thisDataSet, "Categories");
// Set up DataRelation between Products and Suppliers tables
DataRelation SupplierProductRel = thisDataSet.Relations.Add("SuppProducts",
thisDataSet.Tables["Suppliers"].Columns["SupplierID"],
thisDataSet.Tables["Products"].Columns["SupplierID"]);
// Set up DataRelation between Products and Categories tables
DataRelation ProductCategoriesRel = thisDataSet.Relations.Add("ProdCategories",
thisDataSet.Tables["Categories"].Columns["CategoryID"],
thisDataSet.Tables["Products"].Columns["CategoryID"]);
// Print suppliers nested products and their categories
foreach (DataRow suppRow in thisDataSet.Tables["Suppliers"].Rows)
{
Console.WriteLine("Supplier: " + suppRow["CompanyName"]);
Console.WriteLine(" Contact: " + suppRow["ContactName"]);
foreach (DataRow prodRow in suppRow.GetChildRows(SupplierProductRel))
{
Console.WriteLine("\tProduct: " + prodRow["ProductName"]);
foreach (DataRow categoryRow in prodRow.GetParentRows(ProductCategoriesRel))
{
Console.WriteLine("\t Category: " + categoryRow["CategoryName"]);
Console.WriteLine("\t Details: " + categoryRow["Description"] + "\n");
}
}
}
Console.ReadLine();
6. Insert following just before or after the FOREACH loop region of Exercise 05...
SupplierProductRel.Nested = true;
// Product table is child in both relationships preventing further nesting
//ProductCategoriesRel.Nested = true;
thisDataSet.WriteXml(@"d:\temp\exercise06.xml");
Console.WriteLine("XML table has been written!");
Console.ReadLine();
7. Modify SQL command in "ManyRelations" with a "WHERE" clause...
-- AND --
use overload of "DATACOLLECTION.ADD()" to ignore constraint
from unmatched Orders.CustomerID values
// Set up DataAdapter objects for each table and fill
// SqlDataAdapter custAdapter = new SqlDataAdapter(
"SELECT * from Customers", thisConnection);
//Pull only customers whose names begin A through M
SqlDataAdapter custAdapter = new SqlDataAdapter(
"SELECT * from Customers WHERE LEFT(CustomerID, 1) <= 'M'",
thisConnection);
custAdapter.Fill(thisDataSet, "Customers");
SqlDataAdapter orderAdapter = new SqlDataAdapter(
"SELECT * from Orders",
thisConnection);
orderAdapter.Fill(thisDataSet, "Orders");
// Set up DataRelation between customers and orders tables
DataRelation custOrderRel = thisDataSet.Relations.Add("CustOrders",
thisDataSet.Tables["Customers"].Columns["CustomerID"],
thisDataSet.Tables["Orders"].Columns["CustomerID"])
DataRelation custOrderRel = thisDataSet.Relations.Add("CustOrders",
thisDataSet.Tables["Customers"].Columns["CustomerID"],
thisDataSet.Tables["Orders"].Columns["CustomerID"],
false);
8. Replace the SQL command in "ShowSQL" as requested...
// Set up DataAdapter objects for each table and fill
//SqlDataAdapter thisAdapter = new SqlDataAdapter(
"SELECT CustomerID from Customers", thisConnection);
SqlDataAdapter thisAdapter = new SqlDataAdapter(
"SELECT * from Customers", thisConnection);
... Then run application and observe
|
|
Reply By:
|
bmains
|
Reply Date:
|
10/8/2004 5:05:44 PM
|
Are you posting this as a correction?
|
|
