Dataset: dsUser.xsd
This is actually the same dataset we presented in our previous ODBC article, updated to meet current requirements. Figure 1 illustrates the Dataset's design. The schema fields reflect the relevant columns in the referenced database, and will be explicitly mapped onto them in the encapsulation code below.
Figure 1 – The dsSubscriber XML Schema
Encapsulation: subscriberDB.cs
This source code encapsulates the functionality described in the previous section. It resides in the hotquant.data namespace, which is worth knowing when interpreting the code. Doesn't have to reside there, but it does.
HotQuant's web site is standardized on C# .NET. Readers who are more familiar with Visual Basic .NET should have little trouble following along.
// Required assemblies. The one containing Microsoft's ODBC
data
// objects is available for download here. You must also include this
// assembly in the list of project references.
using System;
using System.Data;
using Microsoft.Data.Odbc;
// This is HotQuant's data components namespace.
namespace hotquant.data
{
// This is the ODBC encapsulation class. We use the naming
// convention xxxDB, where xxx is the name of the associated
// Dataset.
public class SubscriberDB
{
// These are the ODBC data objects. We need...
// A Connection object...
protected OdbcConnection ocHQData = new OdbcConnection();
// A Data Adapter...
protected OdbcDataAdapter odaSubscriber = new OdbcDataAdapter();
// A Command Builder. Note that we haven't initialized this one.
// The reason is that the Command Builder constructor takes a
// fully initialized Data Adapter as its argument, and we don't
// have one of those yet. We'll get there.
protected OdbcCommandBuilder ocbSubscriber;
// A Select Command. The Command Builder will use this one to
// generate standard Insert, Update, and Delete functionality.
protected OdbcCommand cmdSubscriberSelect = new OdbcCommand();
// Another Command object, to be used for various scalar
// operations.
private OdbcCommand cmdScalar = new OdbcCommand();
// ... and finally the Dataset. This is why we're here. Note that
// the Dataset is a PUBLIC property! This satisfies one of our
// requirements, i.e. that the class allow direct access to its
// data.
//
// QUESTION: Why the "data" namespace identifier in front
// of "dsSubscriber"?
//
// ANSWER: We're already inside the hotquant namespace, so when
// we type "data" and hit the "." key, the IDE presents us with
// a list of hotquant.data namespace members. In other words,
// it's the same kind of convenience as using "this" for easy
// access to class members.
public data.dsSubscriber userData = new data.dsSubscriber();
// This is the class constructor. You will recognize this code
// from our previous ODBC article. This code provides the logic
// that Microsoft left out of its ODBC data object. Also, any
// structural changes to the associated Dataset should be
// reflected in the table mappings below.
public SubscriberDB()
{
// Set the connection string. In this case, the connection
// is stored in a static string in the Global object, which
// it retrieved from a resource file.
this.ocHQData.ConnectionString = Global.hqConnStr;
// Attach the Command objects to the Connection object.
this.cmdSubscriberSelect.Connection = this.ocHQData;
this.cmdScalar.Connection = this.ocHQData;
// The Data Adapter object is going to use the Select Command
// for standard insert, update, and delete operations, based
// on the output of the Command Builder object. To make this
// happen, we need to introduce the Select Command to the Data
// Adapter...
this.odaSubscriber.SelectCommand = this.cmdSubscriberSelect;
// ... and then generate the Command Builder with the fully
// initialized Data Adapter as its argument.
this.ocbSubscriber = new OdbcCommandBuilder(this.odaSubscriber);
// Define table mappings within the Data Adapter. This is how
// it knows which fields in the database (the Connection
// object) correspond to which fields in the XML schema (the
// Dataset). If the structure of the Dataset changes, this is
// the part that must also change to match it.
this.odaSubscriber.TableMappings.AddRange(
new System.Data.Common.DataTableMapping[]
{
new System.Data.Common.DataTableMapping("Table",
"Subscriber", new System.Data.Common.DataColumnMapping[]
{
new System.Data.Common.DataColumnMapping("Email",
"Email"),
new System.Data.Common.DataColumnMapping("Active",
"Active"),
new System.Data.Common.DataColumnMapping("HTML",
"HTML"),
new System.Data.Common.DataColumnMapping("Survey",
"Survey"),
new System.Data.Common.DataColumnMapping("CreateDate",
"CreateDate")
}) // Note the alternating parentheses & curly braces.
}); // This is easy to screw up if you aren't paying
// attention!
// Now we connect the Dataset object we defined in the
// declarations above to the XML schema file in our data
// directory.
this.userData.DataSetName = "dsSubscriber";
// This setting determines which character set is used to
// perform alphanumeric indexing on the data. In other words,
// it identifies the local alphabet.
this.userData.Locale =
new System.Globalization.CultureInfo("en-US");
} // End of SubscriberDB constructor.
// This is a read-only class property, which returns the number
// of records in the database. This is where the power of the
// encapsulation process really begins to show itself.
public int Count
{
get
{
// Since we are interested in aggregate information
about the
// database, rather than the records themselves,
we can
// bypass the whole Dataset/Data Adapter scheme.
The price
// of the performance boost is that we get to be
responsible
// for opening and closing the data connection.
this.ocHQData.Open();
// Remember that extra Command object? Here is where
we put
// it to work. First we load it with our SQL query...
this.cmdScalar.CommandText = "SELECT COUNT(*) " +
"FROM Subscriber";
// ... and then we execute the query.
int Result = (int)this.cmdScalar.ExecuteScalar();
// Don't forget to close the data connection...
this.ocHQData.Close();
// ... and return the result.
return Result;
} // End of Get block.
} // End of Count property.
// Now we're going to take the Count concept a step further by
// introducing a search criterion. Count returned the number of
// subscribers in the database; Active returns the number of
// ACTIVE subscribers. The only difference is in the WHERE clause
// of the SQL query.
public int Active
{
get
{
this.ocHQData.Open();
this.cmdScalar.CommandText = "SELECT COUNT(*) " +
"FROM Subscriber WHERE Active=1";
int Result = (int)this.cmdScalar.ExecuteScalar();
this.ocHQData.Close();
return Result;
}
}
// Time for some database operations. Because of the way our
// News & Updates interface is organized, it would be convenient
// to have a single method that can accept an e-mail as an
// argument and either insert or update the record, as required.
// This is it.
public void Update(string Email, bool HTML, bool Active)
{
// This time we're going to take advantage of the automation
// provided by the Command Builder object. First, we need to
// define our SQL Select query...
this.cmdSubscriberSelect.CommandText =
"SELECT * FROM Subscriber WHERE Email='" + Email
+ "'";
// ... and then we tell the Command Builder to generate new
// Insert, Update, and Delete queries based on the new select
// query.
this.ocbSubscriber.RefreshSchema();
// Now we can clear the Dataset from any previous searches...
this.userData.Clear();
// ... and refill it based on the new Select query.
odaSubscriber.Fill(this.userData);
// If the user isn't in the database yet...
if (this.userData.Subscriber.Count == 0)
{
// Create a new row in the dataset.
dsSubscriber.SubscriberRow newRow =
(dsSubscriber.SubscriberRow)userData.Subscriber.NewRow();
// Fill the new row.
newRow.Email = Email;
newRow.Active = Active;
newRow.HTML = HTML;
newRow.Survey = false;
newRow.CreateDate = DateTime.Now;
// Add the new row to the data set.
this.userData.Subscriber.Rows.Add(newRow);
// Update the database.
this.odaSubscriber.Update(this.userData);
}
// Otherwise, if the user is in the database...
else
{
// Get the dataset row.
data.dsSubscriber.SubscriberRow theRow =
(data.dsSubscriber.SubscriberRow)userData.Subscriber.Rows[0];
// Fill in any changes.
theRow.Active = Active;
theRow.HTML = HTML;
// Update the database.
this.odaSubscriber.Update(this.userData);
}
} // End of Update method.
// We can't predict everything we might want to do with our
// dataset. Some operations may be clearly unique and thus not
// really worth including in this encapsulation. This is why
// we specified that the encapsulation should allow direct access
// to the associated Dataset, which we did by making the UserData
// member public.
//
// To flesh our our support for this requirement, we need to fill
// the Dataset with database data for outside manipulation, and
// then update the database once manipulation is complete.
//
// This method simply fills the Dataset with the entire contents
// of the database.
public void GetAll()
{
// As we did above, first we define our SQL Select query...
this.cmdSubscriberSelect.CommandText =
"SELECT * FROM Subscriber";
// ... and then we tell the Command Builder to generate new
// Insert, Update, and Delete queries based on the new select
// query.
this.ocbSubscriber.RefreshSchema();
// Then we clear the Dataset of the results of any previous
// searches...
this.userData.Clear();
// ... and refill it based on the new Select query.
odaSubscriber.Fill(this.userData);
} // End of GetAll method.
// As we did with the Count property, we can specify a search
// criterion by adding a WHERE clause to the SQL query. This
// method is identical to the GetAll method, except that it
// returns only ACTIVE subscribers.
public void GetActive()
{
this.cmdSubscriberSelect.CommandText =
"SELECT * FROM Subscriber WHERE Active=1";
this.ocbSubscriber.RefreshSchema();
this.userData.Clear();
odaSubscriber.Fill(this.userData);
} // End of GetActive method.
// Having directly modified the UserData Dataset, the user needs
// a way to update the changes to the database. We chose to
// enable this functionality by overloading the Update function.
// It should be self-explanatory.
public void Update()
{
if (!this.userData.HasChanges()) return;
this.odaSubscriber.Update(this.userData);
} // End of Update method.
// The only requirement still unmet is the encapsulation of the
// clean-up process. Simple enough.
~SubscriberDB()
{
this.ocHQData.Close();
} // End of ~SubscriberDB method.
} // End of SubscriberDB class.
} // End of hotquant.data namespace.
Code Behind: news.aspx.cs
The following code demonstrates the encapsulated functionality in use. Our previous ODBC article only demonstrated the initialization of ODBC functionality, not its use, so readers will just have to accept our assurances that the code below is significantly less complex and easier to read than its predecessor.
// Once again, this is the assembly containing Microsoft's
ODBC data
// objects. Don't forget to include this assembly in the list of
// project references.
using Microsoft.Data.Odbc;
// This assembly supports the bonus explanation below: how we send
// confirmation e-mails from the News & Updates page.
using System.Resources;
// All of our data objects live in the hotquant.data namespace.
using hotquant.data;
// This is HotQuant's home directory namespace. Interesting, but not
// relevant.
namespace hotquant.home
{
// This is the class defining the News & Updates page.
//
// QUESTION: What is the hotquant.Page class, and why haven't we
// told you about it yet?
//
// ANSWER: It isn't relevant to this topic, but it IS mighty
// useful. Hold your horses, and we'll get there in our next
// article.
public class News : hotquant.Page
{
// These are the server controls added in the design view.
protected TextBox txtName;
protected TextBox txtCompany;
protected TextBox txtPosition;
protected TextBox txtEmail;
protected RequiredFieldValidator rfvEmail;
protected RegularExpressionValidator revEmail;
protected Button btnSubscribe;
protected Button btnUnsubscribe;
protected RadioButtonList rbHTML;
// This isn't relevant to the topic, but it is useful. We keep
// the text of our confirmation e-mail in the resource file
// associated with this Web Form, news.aspx.resx. We have to
// create a ResourceManager object in order to access that
// content.
protected ResourceManager rmData =
new ResourceManager("hotquant.home.News",
typeof(News).Assembly);
// This is why we're here. Now that we've encapsulated our ODBC
// connection to the Subscribers database, this one line of code
// is all we need to expose the database for our use.
protected SubscriberDB Subscribers = new SubscriberDB();
// See? No initializations required...
private void Page_Load(object sender, System.EventArgs e)
{
}
// Now for an example of how to use the database. This is the
// event handler for the Subscribe button.
private void btnSubscribe_Click(object sender,
System.EventArgs e)
{
// Validate submission based on the Web Form's validation
// controls.
if (!this.IsValid) return;
// Remember the Update method in the SubscriberDB class? This
// one line checks for a subscriber in the database, adds a new
// record if necessary, and updates the existing one if not.
this.Subscribers.Update(this.txtEmail.Text,
(this.rbHTML.SelectedItem.Value == "HTML"), true);
// Send a confirmation e-mail. Note that we get the content of
// the e-mail from the news.aspx.resx file through the
// ResourceManager object declared above.
SmtpMail.Send(this.rmData.GetString("ConfEmailFrom"),
this.txtEmail.Text,
this.rmData.GetString("ConfSubEmailSubject"),
this.rmData.GetString("ConfSubEmailBody"));
// Redirect to confirmation page.
//
// QUESTION: What's a Context variable?
//
// ANSWER: We'll deal with it in our next article. Rest
// assured, though, that it is VERY useful.
this.Context.Items["SubEmail"] = this.txtEmail.Text;
this.Server.Transfer("confsub.aspx");
}
// Just for completeness, here's the event handler for the
// Unsubscribe button. Note that the Update function called below
// has the wrong number of arguments. YES, we overloaded that
// function more than once and didn't share the code with you.
// Trust us, the code compiles.
private void btnUnsubscribe_Click(object sender,
System.EventArgs e)
{
// Validate submission.
if (!this.IsValid) return;
this.Subscribers.Update(this.txtEmail.Text, false);
// Send a confirmation e-mail.
SmtpMail.Send(this.rmData.GetString("ConfEmailFrom"),
this.txtEmail.Text,
this.rmData.GetString("ConfDeactEmailSubject"),
this.rmData.GetString("ConfDeactEmailBody"));
// Redirect to confirmation page.
this.Context.Items["SubEmail"] = this.txtEmail.Text;
this.Server.Transfer("confdeact.aspx");
}
// All our clean-up code (one whole line) is encapsulated within
// the SubscriberDB class.
private void Page_Unload(object sender, System.EventArgs e)
{
}
} // End of News class.
} // End of hotquant.home namespace.
Comments