ASP.NET C# Programmer Kerala: Advanced Filtering with Relationships

ASP.NET C# Programmer Kerala: Advanced Filtering with Relationships

DataView Advanced Filtering with Relationships in C# And ASP.NET

The DataView allows for some surprisingly complex filter expressions. One of its little-known features is the ability to filter rows based on relationships. For example, you could display categories that contain more than 20 products, or you could display customers who have made a certain
number of total purchases. In both of these examples, you need to filter one table based on the
information in a related table.

To create this sort of filter string, you need to combine two ingredients:

• A table relationship that links two tables.
• An aggregate function such as AVG(), MAX(), MIN(), or COUNT(). This function acts on the
data in the related records.

For example, suppose you’ve filled a DataSet with the Categories and Products tables and
defined this relationship:

// Define the relationship between Categories and Products.
DataRelation relat = new DataRelation("CatProds",
ds.Tables["Categories"].Columns["CategoryID"],
ds.Tables["Products"].Columns["CategoryID"]);
// Add the relationship to the DataSet.
ds.Relations.Add(relat);

You can now filter the display of the Categories table using a filter expression based on the
Products table. For example, imagine you want to show only category records that have at least one
product worth more than $50. To accomplish this, you use the COUNT() function, along with the
name of the table relationships (CatProds).
Here’s the filter string you need:

MAX(Child(CatProds).UnitPrice) > 50

And here’s the code that applies this filter string to the DataView:

DataView view1 = new DataView(ds.Tables["Categories"]);
view1.RowFilter = "MAX(Child(CatProds).UnitPrice) > 50";
GridView1.DataSource = view1;

The end result is that the GridView shows only the categories that have a product worth more
than $50.

Filtering with a DataView

You can a DataView to apply custom filtering so that only certain rows are included in the
display. To accomplish this feat, you use the RowFilter property. The RowFilter property acts like a WHERE clause in a SQL query. Using it, you can limit results using logical operators (such as <, >, and =) and a wide range of criteria. Bellow the most common filter operators.

Filter Operators

Operator Description
<, >, <=, and >= Performs comparison of more than one value. These comparisons can be
numeric (with number data types) or alphabetic dictionary comparisons
(with string data types).
<> and = Performs equality testing.
NOT Reverses an expression. Can be used in conjunction with any other clause.
BETWEEN Specifies an inclusive range. For example, “Units BETWEEN 5 AND 15”
selects rows that have a value in the Units column from 5 to 15.
IS NULL Tests the column for a null value.
IN(a,b,c) A short form for using an OR clause with the same field. Tests for equality
between a column and the specified values (a, b, and c).
LIKE Performs pattern matching with string data types.
+ Adds two numeric values or concatenates a string.
- Subtracts one numeric value from another.
* Multiplies two numeric values.
/ Divides one numeric value by another.
% Finds the modulus (the remainder after one number is divided by another).
AND Combines more than one clause. Records must match all criteria to be
displayed.
OR Combines more than one clause. Records must match at least one of the
filter expressions to be displayed.
The following example page includes three GridView controls. Each one is bound to the same
DataTable but with different filter settings.

string connectionString =
WebConfigurationManager.ConnectionStrings["Northwind"].ConnectionString;
SqlConnection con = new SqlConnection(connectionString);
string sql = "SELECT ProductID, ProductName, UnitsInStock, UnitsOnOrder, " +
Discontinued FROM Products";
SqlDataAdapter da = new SqlDataAdapter(sql, con);
DataSet ds = new DataSet();
da.Fill(ds, "Products");
// Filter for the Chocolade product.
DataView view1 = new DataView(ds.Tables["Products"]);
view1.RowFilter = "ProductName = 'Chocolade'";
GridView1.DataSource = view1;

// Filter for products that aren't on order or in stock.
DataView view2 = new DataView(ds.Tables["Products"]);
view2.RowFilter = "UnitsInStock = 0 AND UnitsOnOrder = 0";
GridView2.DataSource = view2;
// Filter for products starting with the letter P.
DataView view3 = new DataView(ds.Tables[“Products”]);
view3.RowFilter = "ProductName LIKE 'P%'";
GridView3.DataSource = view3;
this.DataBind();

Sorting with a DataView

The next example uses a page with three GridView controls. When the page loads, it binds the same
DataTable to each of the grids. However, it uses three different views, each of which sorts the results
using a different field.

The code begins by retrieving the list of employees into a DataSet:

// Create the Connection, DataAdapter, and DataSet.
string connectionString =
WebConfigurationManager.ConnectionStrings["Northwind"].ConnectionString;
SqlConnection con = new SqlConnection(connectionString);
string sql =
"SELECT TOP 5 EmployeeID, TitleOfCourtesy, LastName, FirstName FROM Employees";
SqlDataAdapter da = new SqlDataAdapter(sql, con);
DataSet ds = new DataSet();
// Fill the DataSet.
da.Fill(ds, "Employees");

The next step is to fill the GridView controls through data binding. To bind the first grid, you
can simply use the DataTable directly, which uses the default DataView and displays all the data.
For the other two grids, you must create a new DataView object. You can then set its Sort property explicitly.

// Bind the original data to #1.
grid1.DataSource = ds.Tables["Employees"];
// Sort by last name and bind it to #2.
DataView view2 = new DataView(ds.Tables["Employees"]);
view2.Sort = "LastName";
grid2.DataSource = view2;


// Sort by first name and bind it to #3.
DataView view3 = new DataView(ds.Tables["Employees"]);
view3.Sort = "FirstName";
grid3.DataSource = view3;
Sorting a grid is simply a matter of setting the DataView.Sort property to a valid sorting expression.
This example sorts by each view using a single field, but you could also sort by multiple fields,
by specifying a comma-separated list. Here’s an example:
view2.Sort = "LastName, FirstName";

■Note The sort is according to the data type of the column. Numeric and date columns are ordered from
smallest to largest. String columns are sorted alphanumerically without regard to case, assuming the
DataTable.CaseSensitive property is false (the default). Columns that contain binary data cannot be sorted.
You can also use the ASC and DESC attributes to sort in ascending or descending order.

Once you’ve bound the grids, you still need to trigger the data binding process that copies the
values from the DataTable into the control. You can do this for each control separately or for the
entire page by calling Page.DataBind(), as in this example:

Page.DataBind();

Working with Multiple Tables and Relationships

The next example shows a more advanced use of the DataSet that, in addition to providing disconnected
data, uses table relationships. This example demonstrates how to retrieve some records from
the Categories and Products tables of the Northwind database and how to create a relationship
between them so that it’s easy to navigate from a category record to all of its child products and
create a simple report.
The first step is to initialize the ADO.NET objects and declare the two SQL queries (for retrieving
categories and products), as shown here:
string connectionString =
WebConfigurationManager.ConnectionStrings["Northwind"].ConnectionString;
SqlConnection con = new SqlConnection(connectionString);
string sqlCat = "SELECT CategoryID, CategoryName FROM Categories";
string sqlProd = "SELECT ProductName, CategoryID FROM Products";
SqlDataAdapter da = new SqlDataAdapter(sqlCat, con);
DataSet ds = new DataSet();

Next, the code executes both queries, adding two tables to the DataSet. Note that the connection
is explicitly opened at the beginning and closed after the two operations, ensuring the best
possible performance.

try
{
con.Open();
// Fill the DataSet with the Categories table.
da.Fill(ds, "Categories");
// Change the command text and retrieve the Products table.
// You could also use another DataAdapter object for this task.
da.SelectCommand.CommandText = sqlProd;
da.Fill(ds, "Products");
}
finally
{
con.Close();
}

In this example, the same DataAdapter is used to fill both tables. This technique is perfectly
legitimate, and it makes sense in this scenario because you don’t need to reuse the DataAdapter
to update the data source. However, if you were using the DataAdapter both to query data and to
commit changes, you probably wouldn’t use this approach. Instead, you would use a separate
DataAdapter for each table so that you could make sure each DataAdapter has the appropriate
insert, update, and delete commands for the corresponding table.
At this point you have a DataSet with two tables. These two tables are linked in the Northwind
database by a relationship against the CategoryID field. This field is the primary key for the Categories
table and the foreign key in the Products table. Unfortunately, ADO.NET does not provide
any way to read a relationship from the data source and apply it to your DataSet automatically.
Instead, you need to manually create a DataRelation that represents the relationship.
A relationship is created by defining a DataRelation object and adding it to the DataSet.
Relations collection. When you create the DataRelation, you specify three constructor arguments:
the name of the relationship, the DataColumn for the primary key in the parent table, and the
DataColumn for the foreign key in the child table.
Here’s the code you need for this example:

// Define the relationship between Categories and Products.

DataRelation relat = new DataRelation("CatProds",
ds.Tables["Categories"].Columns["CategoryID"],
ds.Tables["Products"].Columns["CategoryID"]);
// Add the relationship to the DataSet.
ds.Relations.Add(relat);

Once you’ve retrieved all the data, you can loop through the records of the Categories table and
add the name of each category to the HTML string:

StringBuilder htmlStr = new StringBuilder("");
// Loop through the category records and build the HTML string.
foreach (DataRow row in ds.Tables["Categories"].Rows)
{
htmlStr.Append("");
htmlStr.Append(row["CategoryName"].ToString());
htmlStr.Append("

    ");
    ...

    Here’s the interesting part. Inside this block, you can access the related product records for the
    current category by calling the DataRow.GetChildRows() method. Once you have this array of product
    records, you can loop through it using a nested foreach loop. This is far simpler than the code
    you’d need in order to look up this information in a separate object or to execute multiple queries
    with traditional connection-based access.
    The following piece of code demonstrates this approach, retrieving the child records and completing
    the outer foreach loop:
    ...
    // Get the children (products) for this parent (category).
    DataRow[] childRows = row.GetChildRows(relat);
    // Loop through all the products in this category.
    foreach (DataRow childRow in childRows)
    {
    htmlStr.Append("
  • ");

  • htmlStr.Append(childRow["ProductName"].ToString());
    htmlStr.Append("");
    }
    htmlStr.Append("
");
}

The last step is to display the HTML string on the page:
HtmlContent.Text = htmlStr.ToString();

Stored Procedure For Insert,Delete,Update,Select Commands

Before you can start coding the data access logic, you need to make sure you have the set of stored
procedures you need in order to retrieve, insert, and update information. The following code shows
the five stored procedures that are needed:


CREATE PROCEDURE InsertEmployee
@EmployeeID int OUTPUT
@FirstName varchar(10),
@LastName varchar(20),
@TitleOfCourtesy varchar(25),
AS
INSERT INTO Employees
(TitleOfCourtesy, LastName, FirstName, HireDate)
VALUES (@TitleOfCourtesy, @LastName, @FirstName, GETDATE());
SET @EmployeeID = @@IDENTITY
GO


CREATE PROCEDURE DeleteEmployee
@EmployeeID int
AS
DELETE FROM Employees WHERE EmployeeID = @EmployeeID
GO


CREATE PROCEDURE UpdateEmployee
@EmployeeID int,
@TitleOfCourtesy varchar(25),
@LastName varchar(20),
@FirstName varchar(10)
AS
UPDATE Employees
SET TitleOfCourtesy = @TitleOfCourtesy,
LastName = @LastName,
FirstName = @FirstName
WHERE EmployeeID = @EmployeeID
GO


CREATE PROCEDURE GetAllEmployees
AS
SELECT EmployeeID, FirstName, LastName, TitleOfCourtesy FROM Employees
GO


CREATE PROCEDURE CountEmployees
AS
SELECT COUNT(EmployeeID) FROM Employees
GO


CREATE PROCEDURE GetEmployee
@EmployeeID int
AS
SELECT FirstName, LastName, TitleOfCourtesy FROM Employees
WHERE EmployeeID = @EmployeeID
GO

Stored Procedures

Parameterized commands are just a short step from commands that call full-fledged stored
procedures.
A stored procedure, of course, is a batch of one or more SQL statements that are stored in the
database. Stored procedures are similar to functions in that they are well-encapsulated blocks of
logic that can accept data (through input parameter) and return data (through result sets and output
parameters).

Stored procedures have many benefits:
• They are easier to maintain. For example, you can optimize the commands in a stored procedure
without recompiling the application that uses it.
• They allow you to implement more secure database usage. For example, you can allow the
Windows account that runs your ASP.NET code to use certain stored procedures but restrict
access to the underlying tables.
• They can improve performance. Because a stored procedure batches together multiple
statements, you can get a lot of work done with just one trip to the database server. If your
database is on another computer, this reduces the total time to perform a complex task
dramatically.

Here’s the SQL code needed to create a stored procedure for inserting a single record into the
Employees table. This stored procedure isn’t in the Northwind database initially, so you’ll need to
add it to the database (using a tool such as Enterprise Manager or Query Analyzer) before you use it.
CREATE PROCEDURE InsertEmployee
@TitleOfCourtesy varchar(25),
@LastName varchar(20),
@FirstName varchar(10),
@EmployeeID int OUTPUT
AS
INSERT INTO Employees
(TitleOfCourtesy, LastName, FirstName, HireDate)
VALUES (@TitleOfCourtesy, @LastName, @FirstName, GETDATE());
SET @EmployeeID = @@IDENTITY
GO
This stored procedure takes three parameters for the employee’s title of courtesy, last name, and
first name. It returns the ID of the new record through the output parameter called @EmployeeID,
which is retrieved after the INSERT statement using the @@IDENTITY function. This is one example
of a simple task that a stored procedure can make much easier. Without using a stored procedure,
it’s quite awkward to try to determine the automatically generated identity value of a new record
you’ve just inserted.
Next, you can create a SqlCommand to wrap the call to the stored procedure. This command
takes the same three parameters as inputs and uses @@IDENTITY to get and then return the
ID of the new record. Here is the first step, which creates the required objects and sets the
InsertEmployee as the command text:
string connectionString =
WebConfigurationManager.ConnectionStrings["Northwind"].ConnectionString;
SqlConnection con = new SqlConnection(connectionString);
// Create the command for the InsertEmployee stored procedure.
SqlCommand cmd = new SqlCommand("InsertEmployee", con);
cmd.CommandType = CommandType.StoredProcedure;
Now you need to add the stored procedure’s parameters to the Command.Parameters collection.
When you do, you need to specify the exact data type and length of the parameter so that it
matches the details in the database.

Here’s how it works for a single parameter:

cmd.Parameters.Add(new SqlParameter("@TitleOfCourtesy", SqlDbType.NVarChar, 25));
cmd.Parameters["@TitleOfCourtesy"].Value = title;

The first line creates a new SqlParameter object; sets its name, type, and size in the constructor;
and adds it to the Parameters collection. The second line assigns the value for the parameter, which will be sent to the stored procedure when you execute the command.

Now you can add the next two parameters in a similar way:

cmd.Parameters.Add(new SqlParameter("@LastName", SqlDbType.NVarChar, 20));
cmd.Parameters["@LastName"].Value = lastName;
cmd.Parameters.Add(new SqlParameter("@FirstName", SqlDbType.NVarChar, 10));
cmd.Parameters["@FirstName"].Value = firstName;
The last parameter is an output parameter, which allows the stored procedure to return information
to your code. Although this Parameter object is created in the same way, you must make
sure you specify it is an output parameter by setting its Direction property to Output. You don’t
need to supply a value.

cmd.Parameters.Add(new SqlParameter("@EmployeeID", SqlDbType.Int, 4));
cmd.Parameters["@EmployeeID"].Direction = ParameterDirection.Output;

Finally, you can open the connection and execute the command with the ExecuteNonQuery()
method. When the command is completed, you can read the output value, as shown here:

con.Open();
try
{
int numAff = cmd.ExecuteNonQuery();
HtmlContent.Text += String.Format(
"Inserted {0} record(s)
", numAff);
// Get the newly generated ID.
empID = (int)cmd.Parameters["@EmployeeID"].Value;
HtmlContent.Text += "New ID: " + empID.ToString();
}
finally
{
con.Close();
}

In the next chapter, you’ll see a small but fully functional database component that does all its
work through stored procedures.

The ExecuteNonQuery() Method

The ExecuteNonQuery() method executes commands that don’t return a result set, such as INSERT,
DELETE, and UPDATE. The ExecuteNonQuery() method returns a single piece of information—the
number of affected records.
Here’s an example that uses a DELETE command by dynamically building a SQL string:
SqlConnection con = new SqlConnection(connectionString);
string sql = "DELETE FROM Employees WHERE EmployeeID = " + empID.ToString();
SqlCommand cmd = new SqlCommand(sql, con);
try
{
con.Open();
int numAff = cmd.ExecuteNonQuery();
HtmlContent.Text += string.Format("
Deleted {0} record(s)
",
numAff);
}
catch (SqlException exc)
{
HtmlContent.Text += string.Format("Error: {0}

", exc.Message);
}
finally
{
con.Close();
}

The ExecuteScalar() Method

The ExecuteScalar() method returns the value stored in the first field of the first row of a result set
generated by the command’s SELECT query. This method is usually used to execute a query that
retrieves only a single field, perhaps calculated by a SQL aggregate function such as COUNT() or
SUM().
The following procedure shows how you can get (and write on the page) the number of records
in the Employees table with this approach:
SqlConnection con = new SqlConnection(connectionString);
string sql = " SELECT COUNT(*) FROM Employees ";
SqlCommand cmd = new SqlCommand(sql, con);
// Open the Connection and get the COUNT(*) value.
con.Open();
int numEmployees = (int)cmd.ExecuteScalar();
con.Close();
// Display the information.
HtmlContent.Text += "
Total employees: " +
numEmployees.ToString() + "

";
The code is fairly straightforward, but it’s worth noting that you must cast the returned value to
the proper type because ExecuteScalar() returns an object.

The ExecuteReader() Method and the DataReader

string ConnectionString = ConfigurationManager.ConnectionStrings["Northwind"].ConnectionString;
protected void Page_Load(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection(ConnectionString);
try
{
con.Open();
string sqlcmd = "SELECT TOP 5 * FROM Employees;" +
"SELECT TOP 5 * FROM Customers;SELECT TOP 5 * FROM Suppliers";
SqlCommand cmd=new SqlCommand(sqlcmd,con);
SqlDataReader reader=cmd.ExecuteReader();
StringBuilder htmlString = new StringBuilder("");
int i = 0;
do
{
htmlString.Append("RowSet: ");
htmlString.Append(i.ToString());
htmlString.Append("");
while (reader.Read())
{
htmlString.Append("

  • ");
    // Get all the fields in this row.
    for (int field = 0; field <>");
    }
    htmlString.Append("

    ");
    i++;
    }
    while (reader.NextResult());
    Response.Write(htmlString.ToString());
    }
    catch (Exception ex)
    {
    Response.Write(ex.Message);
    }
    finally
    {
    con.Close();
    }
    }