Tuesday, 29 October 2013

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



4 comments:

  1. I like your blog.I have a lot of learned to see this blog.
    Thankyou so much for post this blog.....

    ReplyDelete
  2. this technological concepts are really well being and wonderful thus it is very much interesting and very well good too, really i got more information from your knowledge.


    Digital Marketing Company in Chennai

    ReplyDelete
  3. Very nice post here and thanks for it .I always like and such a super contents of these post.Excellent and very cool idea and great content of different kinds of the valuable information's.

    digital marketing company in chennai

    ReplyDelete
  4. Such a great articles in my carrier, It's wonderful commands like easiest understand words of knowledge in information's.

    digital marketing course in chennai
    software testing training in chennai

    ReplyDelete