I use business logic layer, TableAdapter(middle tier) and StoredProcedure(backend). In my stored procedure,
I set as follows:
set nocount off
What and where should I do in order to get the rows affected count in the business logic layer(C#/VB code)?
Thank you.
Hi
Here is snippet fromBuilding and using a 3-tiered data architecture with ASP.NET 2.0(Creating a Business Logic Layer) :
[System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Update,true)]public bool UpdateProduct(string productName,int? supplierID,int? categoryID,string quantityPerUnit,decimal? unitPrice,short? unitsInStock,short? unitsOnOrder,short? reorderLevel,bool discontinued,int productID) { Northwind.ProductsDataTable products = Adapter.GetProductByProductID(productID);if (products.Count == 0)// no matching record found, return falsereturn false; Northwind.ProductsRow product = products[0];// Business rule check - cannot discontinue a product that's supplied by only // one supplierif (discontinued) {// Get the products we buy from this supplier Northwind.ProductsDataTable productsBySupplier = Adapter.GetProductsBySupplierID(product.SupplierID);if (productsBySupplier.Count == 1)// this is the only product we buy from this supplierthrow new ApplicationException("You cannot mark a product as discontinued if its the only product purchased from a supplier"); } product.ProductName = productName;if (supplierID ==null) product.SetSupplierIDNull();else product.SupplierID = supplierID.Value;if (categoryID ==null) product.SetCategoryIDNull();else product.CategoryID = categoryID.Value;if (quantityPerUnit ==null) product.SetQuantityPerUnitNull();else product.QuantityPerUnit = quantityPerUnit;if (unitPrice ==null) product.SetUnitPriceNull();else product.UnitPrice = unitPrice.Value;if (unitsInStock ==null) product.SetUnitsInStockNull();else product.UnitsInStock = unitsInStock.Value;if (unitsOnOrder ==null) product.SetUnitsOnOrderNull();else product.UnitsOnOrder = unitsOnOrder.Value;if (reorderLevel ==null) product.SetReorderLevelNull();else product.ReorderLevel = reorderLevel.Value; product.Discontinued = discontinued;// Update the product recordint rowsAffected = Adapter.Update(product);// Return true if precisely one row was updated, otherwise falsereturn rowsAffected == 1; }|||@.@.ROWCOUNT will return you no.of records affected in database hence in sp. so you need to return it as output parameter from sp to upper layers.
thanks,
satish.
sql