How to use sql function with Linq to Sql……………………………………………
-- Sql Query...................
create table Employee (SrNo int primary key,Name varchar(50),Dob date,Salary int)
insert into Employee values(3,'Sonu Rai','09/23/1995',8900)
--create function getage for calculate age with
dateofbirth.............
create function getage(@dob date)returns varchar(50)
as
begin
declare @age varchar(50)=null
declare @month int
set @month=DATEDIFF("mm",@dob,GETDATE())
set @age=CONVERT(varchar(50),@month/12)+' '+'Years'+' '
+ CONVERT(varchar(50),@month%12)+' '+'Months'
return @age
end
--check it........................
select * from
Employee
select SrNo,Name,dbo.getage(dob) from Employee
Result:
Note :There are two parts in DataClasses.dbml one is for tables of database and
another in Stored Procedure and function
Source code Default2.aspx……………………………
<%@ Page Language="C#"
AutoEventWireup="true"
CodeFile="Default2.aspx.cs"
Inherits="Default2"
%>
<!DOCTYPE html PUBLIC
"-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<table width="100%">
<tr><td><b><center>How to use sql function with Linq to Sql</center> </b></td></tr>
<tr><td><center><asp:Button ID="btngetfunction"
runat="server"
Text="Execute Function in Linq To
Sql" onclick="btngetfunction_Click" /></center></td></tr>
<tr><td><center>
<asp:GridView ID="GridView1"
runat="server">
</asp:GridView>
</center></td></tr>
</table>
</div>
</form>
</body>
</html>
Source code Default2.aspx.cs……………………………
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
public partial class Default2 :
System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs
e)
{
}
//create object DataClassesDataContext......................
DataClassesDataContext dd = new DataClassesDataContext();
//code for button
protected void
btngetfunction_Click(object sender, EventArgs e)
{
var v = from m in dd.GetTable<Employee>()
select new {
m.SrNo, m.Name, m.Dob, m.Salary, Age = dd.getage(m.Dob) };
GridView1.DataSource
= v;
GridView1.DataBind();
}
}
Output:
Note: If User or Student don’t
know about PL/Sql then different way to apply
above concept with Linq to Sql……………………
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
public partial class Default2 :
System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs
e)
{
}
//create object DataClassesDataContext......................
DataClassesDataContext dd = new DataClassesDataContext();
protected void
btngetfunction_Click(object sender, EventArgs e)
{
var v = from m in dd.GetTable<Employee>()
select new {
m.SrNo, m.Name, m.Dob, m.Salary, Age=CalculateAge((DateTime)m.Dob)
};
GridView1.DataSource
= v;
GridView1.DataBind();
}
//create a Method for calculate Age...........................
public string
CalculateAge(DateTime d)
{
TimeSpan ts = DateTime.Now.Subtract(d);
int days = Convert.ToInt32(ts.TotalDays);
string msg = (days / 365).ToString() +" "+ "Years";
days =
days % 365;
msg +=" "+ (days / 30).ToString() +" "+ "Months";
return msg;
}
}
gud going on
ReplyDelete