As previously posted I'm working on a number of blog posts to show you how to integrate Webservices with the Business Data Catalog. It just so happens that I've got to prepare some demos for the EVO User Group Launch so I thought I might as well incorporate it all around that. What we're looking to build is a solution that traveling sales people can use. Our fictional company Zebra Communications sell fax machines and photocopiers. They have an existing SQL Server database that they want to expose to MOSS 2007 via the BDC, and also allow new sales data to be entered via InfoPath forms that can be used on site, off site, and on PDA's. A great scenario for demonstrating the power of BDC and Info Path all together I think. So here's the database structure already in SQL Server that we are planning on using.
As you can see it's a pretty standard database structure, nothing earth shattering. The first thing we need to think about for this project is the web services that we want to create to expose this data in the database. For our InfoPath form we are going to want to capture sales orders, so we want to create a webservices that accepts a sales order object, with one of the properties of a sales order being an array of SaleProducts. Our InfoPath form will also have drop downs to select the sales persons name, the customer, and the product being sold. So we'll need to create a web service call each to return an array of sales people, customers and products. So that we can easily return the correct data we are going to create some classes that model the structure of database, so we'll have a sale class, as well as a salesProduct, customer, salesPerson and Product class.
With regards the Business Data Catalog we are going to import the products and customers table so we can display data from them in web parts and also allow this data to be searched. To allow this to happen, and so our BDC Meta Man tool can generate our application definition file nicely for us, the product and customer service are going to implement 3 methods each. The first method will act as a finder method and will just return all the products or customers, the second method is a specific finder method that returns a specific product or customer depending on the Id passed in as a parameter. The final method acts as an IdEnumerator method which returns all the Ids as a list of strings.
Here's a list of classes we are going to create to represent the data we bring back from the DB
Customer
Product
Sale
SaleProduct
SalesPerson
They are all simple classes containing properties for each column in the DB except for Sales while has a list of SaleProduct's as an extra field.
So here is a prototype of our web services and the methods for each one:
Sales.asmx
public void AddSale(Sale salesObject)
Customers.asmx
public List<Customer> GetCustomers()
public Customer GetCustomerById(int customerId)
public string[] GetCustomerIds()
Products.asmx
public List<Product> GetProducts()
public Product GetProductById(int productId)
public string[] GetProductIds()
SalesPeople.asmx
public List<SalesPerson> GetSalesPeople()
We're lucky that GetCustomers and GetProducts can be used by both our InfoPath forms and the Business Data Catalog for getting all the customers and products respectively.
Now I don't like doing work I don't have to, and we need to generate some code to access our database. I'm a fan of great tools that save me time, and so for generating our stored procs I'm using CodeSmith. The professional version is $499 but if you listen to an ephisode of HanselMinutes you can get $100 off straight away. Either way it's a great time saving tool. Out of the box you get a number of templates, the two I'm going to use are from the DatabaseSchema templates and are AllStoredProcedures.cst and AllCommandWrappers. The first template generates insert, update and delete stored procs for each table. It's also very nice in that it generates selects, inserts and updates based on relationships between the tables. The second template, AllCommendWrappers, generates a C# wrapper class for each stored proc, which makes calling them a breeze. Once we've executed our generated stored procs and added our generated classes to access the stored procs we are ready to add the code to return the objects we want in our webservices. I'm not going to go over the code in each webservice, but instead focus on the Sales and the Products webservice.
The Products webservice
Nothing better than showing you the code for it...
string connString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
[WebMethod]
public List<Product> GetProducts()
{
ZebraCommunications.usp_SelectProductsAll productHelper = new ZebraCommunications.usp_SelectProductsAll();
productHelper.ConnectionString = connString;
DataSet ds = productHelper.ExecuteDataSet();
List<Product> products = new List<Product>();
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
Product p = new Product();
p.ProductId = Convert.ToInt32(ds.Tables[0].Rows[i]["ProductId"]);
p.CategoryId = Convert.ToInt32(ds.Tables[0].Rows[i]["CategoryId"]);
p.Name = ds.Tables[0].Rows[i]["Name"].ToString();
p.CostPrice = Convert.ToDouble(ds.Tables[0].Rows[i]["CostPrice"]);
p.SalePrice = Convert.ToDouble(ds.Tables[0].Rows[i]["SalePrice"]);
products.Add(p);
}
return products;
}
[WebMethod]
public Product GetProductById(int productId)
{
ZebraCommunications.usp_SelectProduct productHelper = new ZebraCommunications.usp_SelectProduct();
productHelper.ConnectionString = connString;
productHelper.ProductId = productId;
DataSet ds = productHelper.ExecuteDataSet();
Product product = null;
if (ds.Tables[0].Rows.Count == 0)
throw new Exception("Incorrect product id.");
else
{
product = new Product();
product.ProductId = Convert.ToInt32(ds.Tables[0].Rows[0]["ProductId"]);
product.CategoryId = Convert.ToInt32(ds.Tables[0].Rows[0]["CategoryId"]);
product.Name = ds.Tables[0].Rows[0]["Name"].ToString();
product.CostPrice = Convert.ToDouble(ds.Tables[0].Rows[0]["CostPrice"]);
product.SalePrice = Convert.ToDouble(ds.Tables[0].Rows[0]["SalePrice"]);
product.ReOrderLevel = Convert.ToInt32(ds.Tables[0].Rows[0]["ReOrderLevel"]);
product.SupplierId = Convert.ToInt32(ds.Tables[0].Rows[0]["SupplierId"]);
}
return product;
}
[WebMethod]
public List<string> GetProductIds()
{
ZebraCommunications.usp_SelectProductsAll productHelper = new ZebraCommunications.usp_SelectProductsAll();
productHelper.ConnectionString = connString;
DataSet ds = productHelper.ExecuteDataSet();
List<string> strings = new List<string>();
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
strings.Add(ds.Tables[0].Rows[i]["ProductId"].ToString());
}
return strings;
}
Now this may not be the best code for doing data access and outputting to a webservice, but at the end of the day the webservices are only a small part of this project. The main part is the application defintion file we're going to generate using BDC Meta Man to integrate these webservices with MOSS, and of course the InfoPath forms. I'll make the database and webservices available to download so if anyone wants to improve them feel free. You can see how using the ZebraCommunication namespace classes which are the CommandWrapper ones I generated with CodeSmith makes executing the stored procs and getting data back really easy. Here's the second webservice I'm going to show you, the sales one...
The Sales Webservice
string connString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
[WebMethod]
public void AddSale(Sale sale)
{
usp_InsertSale insertSale = new usp_InsertSale();
insertSale.ConnectionString = connString;
insertSale.SalesPersonId = sale.SalesPersonId;
insertSale.CustomerId = sale.CustomerId;
insertSale.SaleDate = sale.SaleDate;
insertSale.Execute();
foreach(SaleProduct sp in sale.Products)
{
usp_InsertSaleProduct saleProduct = new usp_InsertSaleProduct();
saleProduct.ConnectionString = connString;
saleProduct.ProductId = sp.ProductId;
saleProduct.Quantity = sp.Quantity;
saleProduct.SalePrice = sp.SalePrice;
saleProduct.SaleId = insertSale.SaleId;
saleProduct.Execute();
}
}
And so that's the two webservices I wanted to show you. The Customer webservice is virtually identical to the products one except it returns Customes :-), and the SalesPeople webservice just has a method to return a list of SalesPeople.
That's it for today. Next post on Sunday evening we'll go though creating an InfoPath form to add new sales orders and how to publish it to MOSS 2007 so it can be filled out via the browser and mobile devices. On Sunday I'll make the webservice project and database available to download as well incase you don't want to code/create it :-) Later next week we'll go over the Business Data Catalog part.