0

I'm in the point to implement an C# application that needs to consume already existing stored procedure that receive IDs or values in params. My task in charge is in two steps:

1- migrate stored procedure in order to receive list of IDs(int) and list of current params, means like a table

2- implement the layer that cal this procedures and will receive List and KeyValuePair or KeyValuePair

What should be the best approach to do this ?

EntityFramework to wrap SPs or not ORM at alla? How to implement List and KeyValuePair params ob SP side ? with Table-Valued params ?

I'm with SQL 2012

thanks,

user810917
  • 241
  • 4
  • 12
  • 1
    Table valued params look good to me. [See second answer](http://stackoverflow.com/questions/1069311/passing-an-array-of-parameters-to-a-stored-procedure). Also, you shouldn't ask `What's the best approach` type questions. It's off-topic because you're asking for opinions. – Rick S May 28 '14 at 12:42
  • IMO, the best approach is the most voted answer of [this](http://stackoverflow.com/questions/7097079/c-sharp-sql-server-passing-a-list-to-a-stored-procedure). – Silvestre May 28 '14 at 12:47
  • sorry for ask for best approach. in my opinion XML params is to avoid (as mentioned in duplicate post). dont like an extra layer in c# to handle this. seems TVP is the way to go ?? – user810917 May 28 '14 at 12:47

2 Answers2

4

Try in sql side User defined table type functionality and pass table as parameter in stored procedure.

For example:

CREATE TABLE Test
(
Id int NOT NULL IDENTITY (1, 1),
TestName varchar(50) NOT NULL,
Value int NULL
) ON [PRIMARY]

-- Create a table data type

CREATE TYPE [dbo].[TestType] As Table
(
--This type has structure similar to the DB table 
TestName varchar(50) NOT NULL,
Value int NULL
)

--This is the Stored Procedure

CREATE PROCEDURE [dbo].[TestProcedure]
(
    @Test As [dbo].[TestType] Readonly
)
AS
Begin
Insert Into Test(TestName,Value)
Select TestName, Value From @Test
End

C# code passing the data as follows:

DataTable dataTable = new DataTable("SampleDataType");

// We create column names as per the type in DB 

dataTable.Columns.Add("TestName", typeof(string)); 
dataTable.Columns.Add("Value", typeof(Int32)); 

// And fill in some values 

dataTable.Rows.Add("Metal", 99); 
dataTable.Rows.Add("HG", null);
...
SqlParameter parameter = new SqlParameter(); 

// The parameter for the SP must be of SqlDbType.Structured 

parameter.ParameterName="@Test"; 
parameter.SqlDbType = System.Data.SqlDbType.Structured; 
parameter.Value = dataTable; 

command.Parameters.Add(parameter);
Hiral Nayak
  • 1,062
  • 8
  • 15
1

I dealt with this same issue just recently. The links in the comments above lay out how to do SPs with table valued parameters. I've used the TVP method and it was easy and clean.

When it comes to Entity Framework, you can make EF aware of the Stored Procedures and call them and get the results back into EF Objects. Here's a link:

http://msdn.microsoft.com/en-us/data/gg699321.aspx

It's quite a bit more work than just calling the SPs with ADO. A major consideration is whether the results returned by the SP map directly onto one of your objects. Suppose you're joining a couple of tables in a search. You'd have to make a new model for those results and map the SP to that model and for what? So everything will run slower.

If you're just reading data and the results don't map exactly to an existing model you should skip EF and use ADO directly. If, OTOH, you're doing reads and writes and you really want to keep everything in EF for consistency's sake, it is possible.

RyanB
  • 757
  • 4
  • 11
  • thanks, I ended up using ADO, I'm keeping my SPs with TVP only to do updates, counts or something that dont return data. – user810917 Jun 02 '14 at 09:35