In which layer should i create Sql Parameters for stored procedure with table value parameters using EF 5.0

Question

I have created a data access layer(DAL) in my project and i am using Entity framework5 for CRUD operations. Also i have implemented repository and unit of work patterns in my data access layer.

Now i have come up with a situation where i have to execute a stored procedure which expects a table value parameter as input. How to write a generic function and in which class in DAL that executes any stored procedure with any type of parameters? And i dont want to create hardcode SqlParameters in my business layer.

Currently i have hardcoded this function in my unit of work class.

public void ExecuteSqlCommand(string command, params object[] parameters)
        {
            DataTable dataTable = new DataTable();

            dataTable.Columns.Add(new DataColumn("ProductId"));
            dataTable.Columns.Add(new DataColumn("IsListed"));
            dataTable.Columns.Add(new DataColumn("WasEverListed"));
            dataTable.Columns.Add(new DataColumn("MarketplaceItemNumber"));

            SqlParameter param = new SqlParameter("@tvpProductIsListed", SqlDbType.Structured);
            param.SqlValue = dataTable;
            param.TypeName = "dbo.ProductIsListedTableType";

            int ret = dbContext.Database.ExecuteSqlCommand("EXEC " + command + " @tvpProductIsListed, @RetailerId, @BrandId, @MarketplaceId",
                param,
                new SqlParameter("@RetailerId", 1),
                new SqlParameter("@BrandId", 1),
                new SqlParameter("@MarketplaceId", 1));           
        }

Help will be highly appreciated in this problem!

_any stored procedure with any type of parameters_ But how do you know which parameters can be used with a sproc? — Gert Arnold, Jan 08 '14 at 21:14

score 1 · Answer 1 · edited Mar 31 '14 at 18:38

Take a step back. Decide whether you want to let EF do all the database CRUD for you - which for single entities it is reasonably good at, or you do it yourself? Also, trying to write something generic is hard. Somewhere along the line in being generic you need to do some kind of "IF " test.

Best way is to have one component deal with a particular thing and have many of them for specific things - easier to fix as well if you suddenly discover the need for a deviation in your generalisation. I wouldn't embed sql script in code either - let the database engine handle it.

So I would have a custom repository with Get, GetList, Insert, Update, Delete methods. The insert and update would take a an entity of a given type and then you need to have something that lets you do a to/from mapping of the entity on to the table type which can then be passed into the stored procedure. additionally all the methods return both a data and a status for error handling.

In which layer should i create Sql Parameters for stored procedure with table value parameters using EF 5.0

1 Answers1