Personal Web & Sample Site
Skip Navigation Links
HOME |
About Me |
Articles
| Contact Us
Basic Reporting - Programmatic Parameters
Rating: 6 user(s) have rated this article Average rating: 5.0
Posted by: joet, on 8/5/2011, in category "MS SQL"
Views: this article has been read 3955 times
Location: Houston, Tx, United States
Abstract: First, we need to add another query to the EmployeesDataTableAdapter that retrieves those employees whose HireDate occured in a specific month. I added the following query to the EmployeesDataTableAdapter and called it "GetEmployeeByHireDateMonth". SELECT EmployeeID, LastName, FirstName, Title, HireDate, ReportsTo, Country FROM Employees WHERE DATEPART(m, HireDate) = @Month

 

First, we need to add another query to the EmployeesDataTableAdapter that retrieves those employees whose HireDate occured in a specific month. I added the following query to the EmployeesDataTableAdapter and called it "GetEmployeeByHireDateMonth".

SELECT EmployeeID, LastName, FirstName, Title, HireDate, ReportsTo, Country
FROM Employees
WHERE DATEPART(m, HireDate) = @Month

As you see in the WHERE clause "DATEPART" is a T-SQL function that returns a particular date portion of a "datetime" data type; in this case we're using "DATEPART" to return the month of the "HireDate" column.

Remember that our web site is built using an n-tier multi-layer architecture, we need to add a method to our EmployeesBLL class file. Again, this method simply calls down into the DAL and returns the results. Add the following method:

 

    [System.ComponentModel.DataObjectMethodAttribute
    (System.ComponentModel.DataObjectMethodType.Select, false)]
public Northwind.EmployeesDataTable GetEmployeesByHiredDateMonth(int month)
{
    return Adapter.GetEmployeesByHiredDateMonth(month);
}

To show these results we drop a gridview onto the page and then add an ObjectDataSource as its data source. We use the EmployeeBLL object and then choose the method we created earlier called "GetEmployeeByHireDateMonth". "DO NOT PROVIDE A PARAMETER VALUE". We will provide this value programmatically through the "Selecting" event of the ObjectDataSource.

 

    protected void ObjectDataSource1_Selecting(object sender, ObjectDataSourceSelectingEventArgs e)
    {
        e.InputParameters["month"] = DateTime.Now.Month;
    }

 

Go here for example!


How would you rate this article?

User Feedback

Post your comment
Name:
E-mail:
Comment:
Insert Cancel

Copyright © 2013 Jose M. Tamez
Last Updated August 18th 2013