1

Procedure (modified):

alter procedure searchProgramUnitResult(
    @id char(10)
)
as
begin
    select id from table1 where id = @id
end

Sam procedure in the DBML Designer (after importing the procedure to the MVC project):

[global::System.Data.Linq.Mapping.FunctionAttribute(Name="dbo.searchProgramUnit")]
public ISingleResult<searchProgramUnitResult> searchProgramUnit([global::System.Data.Linq.Mapping.ParameterAttribute(DbType="VarChar(10)")] ref string id){
    IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())),id);
id = ((string)(result.GetParameterValue(0)));
return ((ISingleResult<searchProgramUnitResult>)(result.ReturnValue));
}

Question is, how do I retrieve the result set in another C# class?

public ??Data-type search (string id){
    DataContextClass db = new DataContextClass();         
    ??Datatype results = db.searchProgramUnit(id);
    return results;
}
Dylan Czenski
  • 1,305
  • 4
  • 29
  • 49
  • 1
    Your stored procedure definition looks odd - you're specifying that `@id` is an _output_ but you're using it like an _input_ (and never setting it to anything). – D Stanley Jun 23 '16 at 16:59
  • just try my answer and give me a feedback, this link will help u 2 http://stackoverflow.com/questions/4873607/how-to-use-dbcontext-database-sqlquerytelementsql-params-with-stored-proced – Bassam Alugili Jun 23 '16 at 21:07

2 Answers2

0

Are you asking about the data type? 

public List<string> search (string id){
    DataContextClass db = new DataContextClass();         
    List<string> results = db.searchProgramUnit(id).ToList();
    return results;
}
Dennis
  • 346
  • 3
  • 14
  • Not all the coloumns in the table are `string` – Dylan Czenski Jun 23 '16 at 17:59
  • You're passing a string ID and returning it back. If you want to list all columns in your select statement, entity framework will create a result object. db.searchProgramUnit(id).ToList() will return the collection of said object with properties. Please post the stored procedure you're really working with because select id from table1 where id = @id is not really useful to understand the real problem. – Dennis Jun 23 '16 at 23:17
0

If you have mapped the stored procedure in your DbContext you can call it like that:

using (var context = new DataContextClass())
{
    var courses = context.searchProgramUnit("1");

    foreach (table1 cs in table1s)
        Console.WriteLine(cs.Name);
}

another approach which works also with Code First:

using (var ctx = new DataContextClass())
{
    var idParam = new SqlParameter
    {
        ParameterName = "id",
        Value = "1"
    };
    var table1List = ctx.Database.SqlQuery<table1>("exec searchProgramUnitResult @id ", idParam).ToList<table1>();

    foreach (table cs in table1List)
        Console.WriteLine("Name: {0}", cs.Name);
}

table1 is your entity/class name!

Bassam Alugili
  • 16,345
  • 7
  • 52
  • 70