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



13 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
  5. Fertility is the natural capability to produce offspring. As a measure, fertility rate is the number of offspring born per mating pair, individual or population.Human fertility depends on factors of nutrition, sexual behavior, consanguinity, culture, instinct, endocrinology, timing, economics, way of life, and emotions.Greate thinks of a fertility center for humans.

    Fertility Center in OMR

    ReplyDelete
  6. great blog Here is STUCORNER the Best JAVA training institute in delhi you can visit their site:Stucorner

    ReplyDelete
  7. This is a smart blog. I mean it. You have so much knowledge about this issue, and so much passion.

    Best Web Designing Company Delhi
    T shirt printing near me
    Solan accident news

    ReplyDelete
  8. Your article was perfect had helped me alot in finding the right thing i had searching this stuff thank you so much for provding me this information.me been very sad after reading this announcement…


    jantar mantar in delhi


    insectsight


    womens cardigans

    tour to kodaikanal

    places to visit in naintal

    places to visit in delhi

    ReplyDelete
  9. Hello,

    Great Post. It's very Useful Information. In Future, Hope To See More Post. Thanks You For Sharing.
    CTET Coaching In Noida
    UPTET Coaching In Noida
    B.Ed Entrance Coaching In Noida
    Thanks
    Shweta Singh

    ReplyDelete