///***************************data service.cs************************************///
using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web;
using System.Collections;
using System.Collections.Generic;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.Web.Script.Services;
/// <summary>
/// Summary description for WebService
/// </summary>
public abstract class BaseEntity
{
}
public class Employee : BaseEntity
{
private int _id;
private string _lastName = string.Empty;
private string _firstName = string.Empty;
private DateTime _birthDate;
private DateTime _hireDate;
private string _address = string.Empty;
private string _city = string.Empty;
private string _postalCode = string.Empty;
private string _country = string.Empty;
private string _phone = string.Empty;
public int ID
{
get
{
return _id;
}
set
{
_id = value;
}
}
public string LastName
{
get
{
return _lastName;
}
set
{
_lastName = value;
}
}
public string FirstName
{
get
{
return _firstName;
}
set
{
_firstName = value;
}
}
public DateTime BirthDate
{
get
{
return _birthDate;
}
set
{
_birthDate = value;
}
}
public DateTime HireDate
{
get
{
return _hireDate;
}
set
{
_hireDate = value;
}
}
public string Address
{
get
{
return _address;
}
set
{
_address = value;
}
}
public string City
{
get
{
return _city;
}
set
{
_city = value;
}
}
public string PostalCode
{
get
{
return _postalCode;
}
set
{
_postalCode = value;
}
}
public string Country
{
get
{
return _country;
}
set
{
_country = value;
}
}
public string Phone
{
get
{
return _phone;
}
set
{
_phone = value;
}
}
}
public class Supplier : BaseEntity
{
private int _id;
private string _company = string.Empty;
private string _contactName = string.Empty;
private string _contactTitle = string.Empty;
private string _address = string.Empty;
private string _city = string.Empty;
private string _region = string.Empty;
private string _postalCode = string.Empty;
private string _country = string.Empty;
private string _phone = string.Empty;
private string _fax = string.Empty;
private bool _active;
public int ID
{
get
{
return _id;
}
set
{
_id = value;
}
}
public string Company
{
get
{
return _company;
}
set
{
_company = value;
}
}
public string ContactName
{
get
{
return _contactName;
}
set
{
_contactName = value;
}
}
public string ContactTitle
{
get
{
return _contactTitle;
}
set
{
_contactTitle = value;
}
}
public string Address
{
get
{
return _address;
}
set
{
_address = value;
}
}
public string City
{
get
{
return _city;
}
set
{
_city = value;
}
}
public string Region
{
get
{
return _region;
}
set
{
_region = value;
}
}
public string PostalCode
{
get
{
return _postalCode;
}
set
{
_postalCode = value;
}
}
public string Country
{
get
{
return _country;
}
set
{
_country = value;
}
}
public string Phone
{
get
{
return _phone;
}
set
{
_phone = value;
}
}
public string Fax
{
get
{
return _fax;
}
set
{
_fax = value;
}
}
public bool Active
{
get
{
return _active;
}
set
{
_active = value;
}
}
}
public class Category : BaseEntity
{
private int _id;
private string _name = string.Empty;
public int ID
{
get
{
return _id;
}
set
{
_id = value;
}
}
public string Name
{
get
{
return _name;
}
set
{
_name = value;
}
}
}
public class Customer : BaseEntity
{
private string _id;
private string _company = string.Empty;
private string _contactName = string.Empty;
private string _contactTitle = string.Empty;
private string _address = string.Empty;
private string _city = string.Empty;
private string _postalCode = string.Empty;
private string _country = string.Empty;
private string _phone = string.Empty;
public string ID
{
get
{
return _id;
}
set
{
_id = value;
}
}
public string Company
{
get
{
return _company;
}
set
{
_company = value;
}
}
public string ContactName
{
get
{
return _contactName;
}
set
{
_contactName = value;
}
}
public string ContactTitle
{
get
{
return _contactTitle;
}
set
{
_contactTitle = value;
}
}
public string Address
{
get
{
return _address;
}
set
{
_address = value;
}
}
public string City
{
get
{
return _city;
}
set
{
_city = value;
}
}
public string PostalCode
{
get
{
return _postalCode;
}
set
{
_postalCode = value;
}
}
public string Country
{
get
{
return _country;
}
set
{
_country = value;
}
}
public string Phone
{
get
{
return _phone;
}
set
{
_phone = value;
}
}
}
public class Product : BaseEntity
{
private int _id;
private string _name = string.Empty;
private int _categoryID;
private string _categoryName = string.Empty;
private int _supplierID;
private string _supplierName = string.Empty;
private string _quantityPerUnit = string.Empty;
private decimal _unitPrice;
private short _unitsInStock;
private short _unitsOnOrder;
private bool _discontinued;
public int ID
{
get
{
return _id;
}
set
{
_id = value;
}
}
public string Name
{
get
{
return _name;
}
set
{
_name = value;
}
}
public int CategoryID
{
get
{
return _categoryID;
}
set
{
_categoryID = value;
}
}
public string CategoryName
{
get
{
return _categoryName;
}
set
{
_categoryName = value;
}
}
public int SupplierID
{
get
{
return _supplierID;
}
set
{
_supplierID = value;
}
}
public string SupplierName
{
get
{
return _supplierName;
}
set
{
_supplierName = value;
}
}
public string QuantityPerUnit
{
get
{
return _quantityPerUnit;
}
set
{
_quantityPerUnit = value;
}
}
public decimal UnitPrice
{
get
{
return _unitPrice;
}
set
{
_unitPrice = value;
}
}
public short UnitsInStock
{
get
{
return _unitsInStock;
}
set
{
_unitsInStock = value;
}
}
public short UnitsOnOrder
{
get
{
return _unitsOnOrder;
}
set
{
_unitsOnOrder = value;
}
}
public bool Discontinued
{
get
{
return _discontinued;
}
set
{
_discontinued = value;
}
}
}
public class Orders : BaseEntity
{
private int _orderid;
private decimal _freight;
private string _shipname=string.Empty;
private string _shipaddress = string.Empty;
public int OrderID
{
get
{
return _orderid;
}
set
{
_orderid = value;
}
}
public decimal Freight
{
get
{
return _freight;
}
set
{
_freight = value;
}
}
public string ShipName
{
get
{
return _shipname;
}
set
{
_shipname = value;
}
}
public string ShipAddress
{
get
{
return _shipaddress;
}
set
{
_shipaddress = value;
}
}
}
public class PagedResult<T> where T : BaseEntity
{
private int _total;
private List<T> _rows;
public int Total
{
get
{
return _total;
}
set
{
_total = value;
}
}
public List<T> Rows
{
get
{
return _rows;
}
set
{
_rows = value;
}
}
}
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[ScriptService()]
public class DataService : System.Web.Services.WebService
{
private static readonly string ConnectionString = ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString;
public DataService()
{
}
[WebMethod]
public string HelloWorld() {
return "Hello World";
}
[WebMethod()]
public PagedResult<Product> GetProductList(int start, int max, string sortColumn, string sortOrder)
{
if (max == 0)
{
max = 10;
}
if (string.IsNullOrEmpty(sortColumn) || (string.Compare(sortColumn, "Name", true) == 0))
{
sortColumn = "ProductName";
}
else if (string.Compare(sortColumn, "SupplierName") == 0)
{
sortColumn = "CompanyName";
}
if (string.IsNullOrEmpty(sortOrder))
{
sortOrder = "ASC";
}
const string SQL = "SELECT [ProductID], " +
" [ProductName], " +
" [CategoryID], " +
" [CategoryName], " +
" [SupplierID], " +
" [SupplierName], " +
" [QuantityPerUnit], " +
" [UnitPrice], " +
" [UnitsInStock], " +
" [UnitsOnOrder], " +
" [Discontinued] " +
"FROM " +
" ( " +
" SELECT [P].[ProductID], " +
" [P].[ProductName], " +
" 
.[CategoryID], " +
" .[CategoryName] AS [CategoryName], " +
" 
.[SupplierID], " +
" .[CompanyName] AS [SupplierName], " +
" [P].[QuantityPerUnit], " +
" [P].[UnitPrice], " +
" [P].[UnitsInStock], " +
" [P].[UnitsOnOrder], " +
" [P].[Discontinued], " +
" ROW_NUMBER() OVER (ORDER BY [{0}] {1}) AS [RowIndex] " +
" FROM " +
" [Products] AS [P] " +
" LEFT OUTER JOIN " +
" [Categories] AS " +
" ON [P].[CategoryID] = .[CategoryID] " +
" LEFT OUTER JOIN " +
" [Suppliers] AS " +
" ON [P].[SupplierID] = .[SupplierID] " +
" ) AS [ProductWithRowIndex] " +
"WHERE " +
" ([RowIndex] > {2}) " +
"AND ([RowIndex] <= ({2} + {3})) " +
" " +
"SELECT COUNT(ProductID) " +
"FROM " +
" [Products] ";
int total = 0;
List<Product> list = new List<Product>();
using (IDbConnection cnn = CreateConnection())
{
using (IDbCommand cmd = cnn.CreateCommand())
{
cmd.CommandText = string.Format(SQL, sortColumn, sortOrder, start, max);
using (IDataReader rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
Product p = BuildProduct(rdr);
list.Add(p);
}
if ((rdr.NextResult()) && (rdr.Read()))
{
total = rdr.GetInt32(0);
}
}
}
}
if ((list.Count == 0) || (total == 0))
{
return null;
}
PagedResult<Product> result = new PagedResult<Product>();
result.Rows = list;
result.Total = total;
return result;
}
[WebMethod()]
public PagedResult<Customer> GetCustomerList(int start, int max, string sortColumn, string sortOrder)
{
if (max == 0)
{
max = 10;
}
if (string.IsNullOrEmpty(sortColumn) || (string.Compare(sortColumn, "Company", true) == 0))
{
sortColumn = "CompanyName";
}
if (string.IsNullOrEmpty(sortOrder))
{
sortOrder = "ASC";
}
const string SQL = "SELECT [CustomerID], " +
" [CompanyName], " +
" [ContactName], " +
" [ContactTitle], " +
" [Address], " +
" [City], " +
" [PostalCode], " +
" [Country], " +
" [Phone] " +
"FROM " +
" ( " +
" SELECT [CustomerID], " +
" [CompanyName], " +
" [ContactName], " +
" [ContactTitle], " +
" [Address], " +
" [City], " +
" [PostalCode], " +
" [Country], " +
" [Phone], " +
" ROW_NUMBER() OVER (ORDER BY [{0}] {1}) AS [RowIndex] " +
" FROM " +
" [Customers] " +
" ) AS [CustomerWithRowIndex] " +
"WHERE " +
" ([RowIndex] > {2}) " +
"AND ([RowIndex] <= ({2} + {3})) " +
" " +
"SELECT COUNT(CustomerID) " +
"FROM " +
" [Customers] ";
int total = 0;
List<Customer> list = new List<Customer>();
using (IDbConnection cnn = CreateConnection())
{
using (IDbCommand cmd = cnn.CreateCommand())
{
cmd.CommandText = string.Format(SQL, sortColumn, sortOrder, start, max);
using (IDataReader rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
Customer c = BuildCustomer(rdr);
list.Add(c);
}
if ((rdr.NextResult()) && (rdr.Read()))
{
total = rdr.GetInt32(0);
}
}
}
}
if ((list.Count == 0) || (total == 0))
{
return null;
}
PagedResult<Customer> result = new PagedResult<Customer>();
result.Rows = list;
result.Total = total;
return result;
}
[WebMethod()]
public Supplier[ GetAllSupplierById(string id)
{
List<Supplier> list = new List<Supplier>();
using (IDbConnection cnn = CreateConnection())
{
using (IDbCommand cmd = cnn.CreateCommand())
{
cmd.CommandText = "SELECT [SupplierID], " +
" [CompanyName], " +
" [ContactName], " +
" [ContactTitle], " +
" [Address], " +
" [City], " +
" [Region], " +
" [PostalCode], " +
" [Country], " +
" [Phone], " +
" [Fax] " +
"FROM " +
" [Suppliers] " +
" where [SupplierID] = " + id;
using (IDataReader rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
Supplier s = BuildSupplier(rdr);
list.Add(s);
}
}
}
}
if (list.Count == 0)
{
return null;
}
for (int i = 0; i < list.Count; i++)
{
if (i == 0)
{
//list. = true;
}
else
{
//list.Active = ((i % 2) == 0);
}
}
return list.ToArray();
}
[WebMethod()]
public Supplier[ GetAllSupplier()
{
List<Supplier> list = new List<Supplier>();
using (IDbConnection cnn = CreateConnection())
{
using (IDbCommand cmd = cnn.CreateCommand())
{
cmd.CommandText = "SELECT [SupplierID], " +
" [CompanyName], " +
" [ContactName], " +
" [ContactTitle], " +
" [Address], " +
" [City], " +
" [Region], " +
" [PostalCode], " +
" [Country], " +
" [Phone], " +
" [Fax] " +
"FROM " +
" [Suppliers] " +
"ORDER BY [CompanyName] ASC";
using (IDataReader rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
Supplier s = BuildSupplier(rdr);
list.Add(s);
}
}
}
}
if (list.Count == 0)
{
return null;
}
for (int i = 0; i < list.Count; i++)
{
if (i == 0)
{
list
.Active = true;
}
else
{
list.Active = ((i % 2) == 0);
}
}
return list.ToArray();
}
[WebMethod()]
public PagedResult<Orders> GetOrdersList(int start, int max, string sortColumn, string sortOrder,string CustID)
{
if (max == 0)
{
max = 10;
}
if (string.IsNullOrEmpty(sortColumn) || (string.Compare(sortColumn, "OrderID", true) == 0))
{
sortColumn = "OrderID";
}
if (string.IsNullOrEmpty(sortOrder))
{
sortOrder = "ASC";
}
const string SQL = "SELECT [OrderID], " +
" [Freight], " +
" [ShipName], " +
" [ShipAddress] " +
"FROM " +
" ( " +
" SELECT [OrderID], " +
" [Freight], " +
" [ShipName], " +
" [ShipAddress], " +
" ROW_NUMBER() OVER (ORDER BY [{0}] {1}) AS [RowIndex] " +
" FROM " +
" [Orders] WHERE [CustomerID]='{4}' " +
" ) AS [OrdersWithRowIndex] " +
"WHERE " +
" ([RowIndex] > {2}) " +
"AND ([RowIndex] <= ({2} + {3})) " +
" " +
"SELECT COUNT(OrderID) " +
"FROM " +
" [Orders] WHERE [CustomerID]='{4}' ";
int total = 0;
List<Orders> list = new List<Orders>();
using (IDbConnection cnn = CreateConnection())
{
using (IDbCommand cmd = cnn.CreateCommand())
{
cmd.CommandText = string.Format(SQL, sortColumn, sortOrder, start, max, CustID);
using (IDataReader rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
Orders c = BuildOrders(rdr);
list.Add(c);
}
if ((rdr.NextResult()) && (rdr.Read()))
{
total = rdr.GetInt32(0);
}
}
}
}
if ((list.Count == 0) || (total == 0))
{
return null;
}
PagedResult<Orders> result = new PagedResult<Orders>();
result.Rows = list;
result.Total = total;
return result;
}
[WebMethod()]
public Customer[ GetAllCustomers()
{
List<Customer> list = new List<Customer>();
using (IDbConnection cnn = CreateConnection())
{
using (IDbCommand cmd = cnn.CreateCommand())
{
cmd.CommandText = "SELECT [CustomerID], " +
" [CompanyName], " +
" [ContactName], " +
" [ContactTitle], " +
" [Address], " +
" [City], " +
" [PostalCode], " +
" [Country], " +
" [Phone] " +
" From "+
" [Customers] " +
" ORDER BY [CompanyName] ASC ";
using (IDataReader rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
Customer c = BuildCustomer(rdr);
list.Add(c);
}
}
}
}
if (list.Count == 0)
{
return null;
}
//for (int i = 0; i < list.Count; i++)
//{
// if (i == 0)
// {
// list.Active = true;
// }
// else
// {
// list.Active = ((i % 2) == 0);
// }
//}
return list.ToArray();
}
public Product[ GetAllProducts()
{
List<Product> list = new List<Product>();
using (IDbConnection cnn = CreateConnection())
{
using (IDbCommand cmd = cnn.CreateCommand())
{
cmd.CommandText = "SELECT [ProductID], " +
" [ProductName], " +
" [CategoryID], " +
" [SupplierID], " +
" [QuantityPerUnit], " +
" [UnitPrice], " +
" [UnitsInStock], " +
" [UnitsOnOrder], " +
" [Discontinued] " +
" FROM " +
" [Products] "+
" ORDER BY [ProductName] ASC ";
using (IDataReader rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
Product c = BuildProduct(rdr);
list.Add(c);
}
}
}
}
if (list.Count == 0)
{
return null;
}
//for (int i = 0; i < list.Count; i++)
//{
// if (i == 0)
// {
// list.Active = true;
// }
// else
// {
// list.Active = ((i % 2) == 0);
// }
//}
return list.ToArray();
}
public Orders[ GetAllOrders()
{
List<Orders> list = new List<Orders>();
using (IDbConnection cnn = CreateConnection())
{
using (IDbCommand cmd = cnn.CreateCommand())
{
cmd.CommandText = "SELECT [OrderID], " +
" [Freight], " +
" [ShipName], " +
" [ShipAddress] " +
" FROM "+
" [Orders] " +
" ORDER BY [OrderID] ASC ";
using (IDataReader rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
Orders ord = BuildOrders(rdr);
list.Add(ord);
}
}
}
}
if (list.Count == 0)
{
return null;
}
//for (int i = 0; i < list.Count; i++)
//{
// if (i == 0)
// {
// list.Active = true;
// }
// else
// {
// list.Active = ((i % 2) == 0);
// }
//}
return list.ToArray();
}
private static Supplier BuildSupplier(IDataReader reader)
{
Supplier supplier = new Supplier();
supplier.ID = reader.IsDBNull(0) ? 0 : reader.GetInt32(0);
supplier.Company = reader.IsDBNull(1) ? string.Empty : reader.GetString(1).Trim();
supplier.ContactName = reader.IsDBNull(2) ? string.Empty : reader.GetString(2).Trim();
supplier.ContactTitle = reader.IsDBNull(3) ? string.Empty : reader.GetString(3).Trim();
supplier.Address = reader.IsDBNull(4) ? string.Empty : reader.GetString(4).Trim();
supplier.City = reader.IsDBNull(5) ? string.Empty : reader.GetString(5).Trim();
supplier.Region = reader.IsDBNull(6) ? string.Empty : reader.GetString(6).Trim();
supplier.PostalCode = reader.IsDBNull(7) ? string.Empty : reader.GetString(7).Trim();
supplier.Country = reader.IsDBNull(8) ? string.Empty : reader.GetString(8).Trim();
supplier.Phone = reader.IsDBNull(9) ? string.Empty : reader.GetString(9).Trim();
supplier.Fax = reader.IsDBNull(10) ? string.Empty : reader.GetString(10).Trim();
return supplier;
}
private static Employee BuildEmployee(IDataReader reader)
{
Employee employee = new Employee();
employee.ID = reader.IsDBNull(0) ? 0 : reader.GetInt32(0);
employee.LastName = reader.IsDBNull(1) ? string.Empty : reader.GetString(1).Trim();
employee.FirstName = reader.IsDBNull(2) ? string.Empty : reader.GetString(2).Trim();
employee.BirthDate = reader.IsDBNull(3) ? DateTime.MinValue : reader.GetDateTime(3).ToLocalTime();
employee.HireDate = reader.IsDBNull(4) ? DateTime.MinValue : reader.GetDateTime(4).ToLocalTime();
employee.Address = reader.IsDBNull(5) ? string.Empty : reader.GetString(5).Trim();
employee.City = reader.IsDBNull(6) ? string.Empty : reader.GetString(6).Trim();
employee.PostalCode = reader.IsDBNull(7) ? string.Empty : reader.GetString(7).Trim();
employee.Country = reader.IsDBNull(8) ? string.Empty : reader.GetString(8).Trim();
employee.Phone = reader.IsDBNull(9) ? string.Empty : reader.GetString(9).Trim();
return employee;
}
private static Customer BuildCustomer(IDataReader reader)
{
Customer customer = new Customer();
customer.ID = reader.IsDBNull(0) ? string.Empty : reader.GetString(0).Trim();
customer.Company = reader.IsDBNull(1) ? string.Empty : reader.GetString(1).Trim();
customer.ContactName = reader.IsDBNull(2) ? string.Empty : reader.GetString(2).Trim();
customer.ContactTitle = reader.IsDBNull(3) ? string.Empty : reader.GetString(3).Trim();
customer.Address = reader.IsDBNull(4) ? string.Empty : reader.GetString(4).Trim();
customer.City = reader.IsDBNull(5) ? string.Empty : reader.GetString(5).Trim();
customer.PostalCode = reader.IsDBNull(6) ? string.Empty : reader.GetString(6).Trim();
customer.Country = reader.IsDBNull(7) ? string.Empty : reader.GetString(7).Trim();
customer.Phone = reader.IsDBNull(8) ? string.Empty : reader.GetString(8).Trim();
return customer;
}
private static Product BuildProduct(IDataReader reader)
{
Product product = new Product();
product.ID = reader.IsDBNull(0) ? int.MinValue : reader.GetInt32(0);
product.Name = reader.IsDBNull(1) ? string.Empty : reader.GetString(1).Trim();
product.CategoryID = reader.IsDBNull(2) ? int.MinValue : reader.GetInt32(2);
//product.CategoryName = reader.IsDBNull(3) ? string.Empty : reader.GetString(3);
product.SupplierID = reader.IsDBNull(3) ? int.MinValue : reader.GetInt32(3);
// product.SupplierName = reader.IsDBNull(5) ? string.Empty : reader.GetString(5);
product.QuantityPerUnit = reader.IsDBNull(4) ? string.Empty : reader.GetString(4).Trim();
product.UnitPrice = reader.IsDBNull(5) ? decimal.MinValue : reader.GetDecimal(5);
product.UnitsInStock = reader.IsDBNull(6) ? short.MinValue : reader.GetInt16(6);
product.UnitsOnOrder = reader.IsDBNull(7) ? short.MinValue : reader.GetInt16(7);
product.Discontinued = reader.IsDBNull(8) ? false : reader.GetBoolean(8);
return product;
}
private static Orders BuildOrders(IDataReader reader)
{
Orders order = new Orders();
order.OrderID = reader.IsDBNull(0) ? int.MinValue : reader.GetInt32(0);
order.Freight = reader.IsDBNull(1) ? decimal.MaxValue: reader.GetDecimal(1);
order.ShipName = reader.IsDBNull(2) ? string.Empty : reader.GetString(2).Trim();
order.ShipAddress = reader.IsDBNull(3) ? string.Empty : reader.GetString(3).Trim();
return order;
}
private static IDbConnection CreateConnection()
{
IDbConnection cnn = new System.Data.SqlClient.SqlConnection(ConnectionString);
cnn.Open();
return cnn;
}
}
///****************************DataService.cs***************************************///