public override DataSet Clone()
{
dsCustOrderHist cln = ((dsCustOrderHist)(base.Clone()));
cln.InitVars();
return(cln);
}
public CustomerAndOrderHistoryInfo GetCustomerOrderHistory(string strCustID)
{
// Attempt to connect to the local SQL server instance, and a local
// MSDE installation (with Northwind).
bool IsConnecting = true;
CustomerAndOrderHistoryInfo cohi = null;
while (IsConnecting)
{
// The SqlConnection class allows you to communicate with SQL Server.
// The constructor accepts a connection string an argument. This
// connection string uses Integrated Security, which means that you
// must have a login in SQL Server, or be part of the Administrators
// group for this to work.
SqlConnection scnnNW = new SqlConnection(strConn);
// A SqlCommand object is used to execute the SQL commands.
SqlCommand scmd = new SqlCommand("CustOrderHist", scnnNW);
// Create an instance of the custom return type.
cohi = new CustomerAndOrderHistoryInfo();
try
{
// Tell the Command object that the text you passed when creating
// the object was for a stored procedure instead of ad hoc SQL.
scmd.CommandType = CommandType.StoredProcedure;
// Add a SqlParamter object to pass to the stored procedure.
scmd.Parameters.Add(new SqlParameter("@CustomerID",
SqlDbType.NChar, 5)).Value = strCustID;
// A SqlDataAdapter uses the SqlCommand object to fill a DataSet.
SqlDataAdapter sda = new SqlDataAdapter(scmd);
// Create an instance of the typed DataSet.
dsCustOrderHist dsOrderHistory = new dsCustOrderHist();
// Unlike generic DataSets, When filling a typed Dataset you must
// pass the DataTable (or DataTableName, e.g., this is acceptable
// also: sda.Fill(dsOrderHistory, dsOrderHistory.Tables(0).TableName).
// if you just pass the typed Dataset instance there will be no
// runtime error but you'll have no results in the DataTable, which
// could be quite puzzling if you know (using SQL Server Profiler)
// that the SQL executed and returned data. Also, notice that the
// table takes the same name the stored procedure.
sda.Fill(dsOrderHistory.CustOrderHist);
// Data has been successfully retrieved, so break out of the loop
// and close the status form.
IsConnecting = false;
// Assign the filled, typed Dataset to the Orders field of the custom
// type.
cohi.Orders = dsOrderHistory;
// Change the Command text to retrieve the company name.
scmd.CommandText =
"SELECT CompanyName " +
"FROM Customers " +
"WHERE CustomerID = @CustomerID";
// Change the CommandType to ad hoc SQL.
scmd.CommandType = CommandType.Text;
// Open a connection and execute the new SQL statement. Use
// ExecuteScalar() for performance gains because you know you
// are only interested in the first column of the first row
// of data returned. (In this case that is all that is returned
// anyway.) Only set the CompanyName property if a name is found.
// Without this you generate an exception for a bad Customer ID.
scnnNW.Open();
object objReturnVal = scmd.ExecuteScalar();
if (objReturnVal != System.DBNull.Value)
{
cohi.CompanyName = objReturnVal.ToString();
}
}
catch (SqlException expSql)
{
throw new Exception(expSql.Message);
}
catch (Exception exp)
{
if (strConn == SQL_CONNECTION_STRING)
{
// Couldn't connect to SQL Server. Now try MSDE.
strConn = MSDE_CONNECTION_STRING;
}
else
{
// Unable to connect to SQL Server or MSDE
throw new Exception(exp.Message);
}
}
finally
{
// Close the connection.
scnnNW.Close();
}
}
return(cohi);
}
