below is my data access codes...
public List<CategoriesRollup> CategBranch()
{
var categBranch = new List<CategoriesRollup>();
const string spName = "categories_rollup_newbranchlist";
using (var con = _connectionString.GetConnection())
using (var cmd = new SqlCommand(spName, con))
{
try
{
cmd.Parameters.Add("@username", SqlDbType.VarChar).Value = UsersSession.Users.UserName;
cmd.Parameters.Add("@terminal", SqlDbType.VarChar).Value = IpAddress.GetMyIpAddress();
cmd.Parameters.Add("@type", SqlDbType.TinyInt).Value = 1;
cmd.CommandType = CommandType.StoredProcedure;
var rdr = cmd.ExecuteReader();
if (rdr.HasRows)
while (rdr.Read())
categBranch.Add(new CategoriesRollup
{
BranchLookupKey = Convert.ToInt32(rdr["BranchLookupKey"]),
Branch = rdr["Branch"].ToString(),
ComboPromoKey = Convert.ToInt32(rdr["ComboPromoKey"]),
Combo = rdr["Combo"].ToString(),
CategoryKey = Convert.ToInt32(rdr["CategoryKey"]),
Category = rdr["Category"].ToString(),
ItemCategoryKey = Convert.ToInt32(rdr["ItemCategoryKey"]),
Item = rdr["Item"].ToString()
});
then my sp in ssms
ALTER PROCEDURE [dbo].[categories_rollup_branchlist]
@range INT = 0,
@username VARCHAR(20),
@terminal VARCHAR(60),
@type TINYINT
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE
@Error INT,
@Rowcount INT,
--****** SYSTEM LOGS
@action VARCHAR(20),
@log_details VARCHAR(8000),
@record_key INT,
---temporary table
--@key INT,
--@result INT,
@categoryKeys VARCHAR(max),
@startPromos INT,
@countPromos INT,
@promoKey INT,
@parse VARCHAR(max),
@final VARCHAR(max),
@branch_code VARCHAR(max)
SET @Error = 0
SET @action = 'CATEGORIES ROLLUP LIST'
SET @record_key = 0
BEGIN TRANSACTION begin_transactionA
SAVE TRANSACTION save_transactionB
SET ROWCOUNT @range
-----
CREATE TABLE #promosPerBranch
( -- id INT ,
id INT IDENTITY (1,1),
PromoKey int
)
CREATE TABLE #wholebranch (
--Id INT IDENTITY (1,1),
--Id INT,
-- result INT,
BranchLookupKey INT,
Branch VARCHAR(50),
ComboPromoKey INT,
Combo VARCHAR(60),
CategoryKey INT,
Category VARCHAR(50),
ItemCategoryKey INT,
Item VARCHAR(60)
)
--SET IDENTITY_INSERT #promosPerBranch ON
INSERT INTO #promosPerBranch
SELECT promo_key AS 'PromoKey'
FROM dbo.branch_promos
WHERE branch_lookup_key = branch_lookup_key
SELECT @countPromos = COUNT(*) from #promosPerBranch
SET @startPromos = 1
WHILE @startPromos <= @countPromos
BEGIN
SELECT @branch_code = branch_code FROM dbo.branches WHERE lookup_key = @promoKey
SELECT @promoKey = promoKey FROM #promosPerBranch WHERE id = @startPromos
SELECT @categoryKeys = p.category_keys FROM dbo.promos p WHERE [key] = @promoKey
--SET IDENTITY_INSERT #wholebranch ON
INSERT INTO #wholebranch
SELECT
-- 0 AS 'result',
b.lookup_key AS 'BranchLookupKey',
b.branch_code AS 'Branch',
p.[key] AS 'ComboPromoKey',
p.name AS 'Combo',
c.[key] AS 'CategoryKey',
c.name AS 'Category',
i.category_key AS 'ItemCategoryKey',
i.name AS 'Item'
FROM items i
INNER JOIN dbo.branches b ON i.category_key = b.lookup_key
INNER JOIN dbo.categories c ON i.category_key = c.[key]
INNER JOIN dbo.promos p ON p.[key] = @promoKey
WHERE category_key IN (SELECT [key] FROM dbo.categories
WHERE [key] IN (SELECT * FROM dbo.parse_comma_delimited_string(@categoryKeys)))
SET @startPromos = @startPromos + 1
--SET IDENTITY_INSERT #wholebranch OFF
--SET IDENTITY_INSERT #promosPerBranch OFF
END
SELECT * FROM #promosPerBranch
SELECT * FROM #wholebranch
DROP TABLE #promosPerBranch
DROP TABLE #wholebranch