LINQ to Stored Procedure(without parameter)

For Advance Asp.net Training Fill Enquiry Form

LINQ to Stored Procedure

(Without Parameter)

LINQ also provide facility to connect with Stored Procedure. Following is the table which I am using to explain this topic.

Table Name:-Emp

Figure 1

To work with LINQ to SQL we have to add LINQ Classes. Using Add New Item add LINQ to SQL Classes.

Figure 2

Now create class to map with Table Emp

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Data.Linq.Mapping;

[Table(Name="Emp")]

public class Class1

{

    //IsDbGenerated represents that it is Identity column

    [Column(IsPrimaryKey=true, IsDbGenerated=true)]

    public int id;

    [Column]

    public string name;

    [Column]

    public int age;

}

Follow the following steps:-

Step 1:-

Create Stored Procedure

create  proc selectAllData

as

select * from emp

Step 2:-

To work with stored procedure in LINQ we have to use DataContext class. To run stored procedure we have to use ExecuteMethodCall which is protected type. So we have to first inherit the DataContext Class to call the method ExecuteMethodCall ().

I have created the class name DAO and inherit the DataContext class in it.

(Kindly add the namespace System.Data.LINQ)

Initialize the DataContext class constructor and pass connection string to it.

public class DAO:DataContext

{

    public DAO(string con_string)

        : base(con_string)

    { }

}

Step 3:-

Now create the method in the DAO class to work with Stored Procedure

[Function(Name = "selectAllData", IsComposable = false)]

    public ISingleResult<Class1> EmpData()

    {

        ISingleResult<Class1> res = (ISingleResult<Class1>)((ExecuteMethodCall(this, (MethodInfo)(MethodInfo.GetCurrentMethod()))).ReturnValue);

        return res;

   

    }

Assign the function attribute on the method which is in System.Data.Linq.Mapping namespace. Function attribute is used for Stored Procedure and User Defined Function.

Name property define the stored procedure name and IsComposable=false is used for stored procedure and IsComposable=true is used for user defined function.

The complete code is as follows:-

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Data.Linq;

using System.Data.Linq.Mapping;

using System.Reflection;

using System.Data;

public class DAO:DataContext

{

 

    public DAO(string con_string)

        : base(con_string)

    { }

    [Function(Name = "selectAllData", IsComposable = false)]

    public ISingleResult<Class1> EmpData()

    {

        ISingleResult<Class1> res = (ISingleResult<Class1>)((ExecuteMethodCall(this, (MethodInfo)(MethodInfo.GetCurrentMethod()))).ReturnValue);

        return res;

   

    }

  

}

Now create the object of DAO class and call the method EmpData()

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

public partial class LinqToSP : System.Web.UI.Page

{

    protected void Page_Load(object sender, EventArgs e)

    {

        DAO d = new DAO("Data Source=ISHA;Initial Catalog=isha;Persist Security Info=True;User ID=isha;Password=pass@123");

        foreach (var data in d.EmpData())

        {

            Response.Write(data.id + " " + data.name+"<br/>");

       

        }

       

    }

}

The output of this code is as follow:-

Figure 3