in my database I have a sp which requires 1 parameter, for fetching some data. but when I run, it shows error.
here my code as below
ALTER PROCEDURE dbo.Get_Leave
(
@Sno int
)
AS
begin
select * from mtbl_Leave_Data where Sno = @Sno
end
and code file is
LeaveDataContext DataCnx = new LeaveDataContext();
public DataTable Load(int Sno)
{
var ld= DataCnx.Get_Leave(Sno);
return (DataTable)ld;
}
let me know please
It is our choices that show what we truly are, far more than our abilities...
I am trying exactly the same as you suggested but a little bit confused how to do this see my code below
public DataTable Load(int Sno)
{
LeaveDataContext ldc = new LeaveDataContext();
var lv = ldc.Get_Leave(Sno);
foreach (Get_LeaveResult leave in lv)
{
DataTable dt = leave.;
}
}
actually I am doing this in my data access layer, later I'll use this method in my BAL layer and than in Presentation Layer, Could you please let me know OR modify this code accordingly so that I could get a return type as a DataTable
Thanks
Gaurav
It is our choices that show what we truly are, far more than our abilities...
see my code below for converting list to datatabel
public DataTable Load<T>(IList<T> data)
{
LeaveDataContext props = new LeaveDataContext();
var leave = props.Get_Leave(typeof(T));
DataTable table = new DataTable();
for (int i = 0; i < props.Count; i++)
{
PropertyDescriptor prop = props[i];
table.Columns.Add(prop.Name, prop.PropertyType);
}
object[] values = new object[props.Count];
foreach (T item in data)
{
for (int i = 0; i < values.Length; i++)
{
values[i] = props[i].GetValue(item);
}
table.Rows.Add(values);
}
return table;
}
here datatable named Load, here I need to put a parameter because the sp is used in this code named Get_Leave() need a int parameter Sno; How can I put there, please let me know
Thanks
It is our choices that show what we truly are, far more than our abilities...
"TBLEMPLOYEE" structure:
-------------------------------
EMP001 SANDEEP MITTAL 30000
EMP002 RITESH KUMAR 25000
EMP003 ABHAY KUMAR 25000
Stored Procedure:
-------------------------------
CREATE PROCEDURE dbo.Get_Emp
(
@EmpID CHAR(6)
)
AS
BEGIN
SELECT * FROM TBLEMPLOYEE WHERe EMPID = @EmpID
END
--------------------------------
I imported table, SP to edmx and registered SP using funtion import using "TBLEMPLOYEE" as return type..following the link as suggested in above post.
CS Code:
--------------------------------
using System.Reflection;
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
testEntities1 test = new testEntities1();
List<TBLEMPLOYEE> v = test.Get_Emp("EMP001").ToList();
DataTable l = Load<TBLEMPLOYEE>(v);
}
}
protected DataTable Load<T>(List<T> param)
{
DataTable table = new DataTable();
DataRow row = null;
PropertyInfo[] info = param[0].GetType().GetProperties(BindingFlags.DeclaredOnly | BindingFlags.Public | BindingFlags.Instance);
foreach (PropertyInfo i in info)
{
table.Columns.Add(i.Name);
}
object[] values = new object[info.Length];
foreach (T item in param)
{
for (int i = 0; i < values.Length; i++)
{
row = table.NewRow();
values[i] = info[i].GetValue(item,null);
}
table.Rows.Add(values);
}
return table;
}
Hope this helps...:-)
Even if you are using Linq to SQL , Load<T> function remains same.
Linq to sql returns System.Data.Linq.ISingleResult<T>, when working with SP.
So the only place you can convert it to datatable after getting result in System.Data.Linq.ISingleResult<T>
So the change in code would be:
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
DataClassesDataContext dc = new DataClassesDataContext();
List<Get_EmpResult> list = dc.Get_Emp("EMP001").ToList();
DataTable table = Load<Get_EmpResult>(list);
}
}
//Get_EmpResult is generated by Linq.
Please mark the post as "Answer" that helps you.
Marked as answer by demoninside9 on Feb 28, 2012 04:21 AM
demoninside9
Participant
1182 Points
1697 Posts
Unable to cast object of type 'SingleResult`1[Get_LeaveResult]' to type 'System.Data.DataTable'.
Feb 23, 2012 09:10 AM|LINK
hi all,
in my database I have a sp which requires 1 parameter, for fetching some data. but when I run, it shows error.
here my code as below
ALTER PROCEDURE dbo.Get_Leave ( @Sno int ) AS begin select * from mtbl_Leave_Data where Sno = @Sno end and code file is LeaveDataContext DataCnx = new LeaveDataContext(); public DataTable Load(int Sno) { var ld= DataCnx.Get_Leave(Sno); return (DataTable)ld; } let me know pleaseavinash_bhud...
Contributor
2881 Points
517 Posts
Re: Unable to cast object of type 'SingleResult`1[Get_LeaveResult]' to type 'System.Data.DataTabl...
Feb 23, 2012 09:23 AM|LINK
You can not directly cast store procedure return value into DataTable.
check this link.
http://weblogs.asp.net/scottgu/archive/2007/08/16/linq-to-sql-part-6-retrieving-data-using-stored-procedures.aspx
demoninside9
Participant
1182 Points
1697 Posts
Re: Unable to cast object of type 'SingleResult`1[Get_LeaveResult]' to type 'System.Data.DataTabl...
Feb 27, 2012 03:00 AM|LINK
Hi Avinash,
I am trying exactly the same as you suggested but a little bit confused how to do this see my code below
public DataTable Load(int Sno) { LeaveDataContext ldc = new LeaveDataContext(); var lv = ldc.Get_Leave(Sno); foreach (Get_LeaveResult leave in lv) { DataTable dt = leave.; } } actually I am doing this in my data access layer, later I'll use this method in my BAL layer and than in Presentation Layer, Could you please let me know OR modify this code accordingly so that I could get a return type as a DataTable Thanks Gauravme_ritz
Star
9337 Points
1447 Posts
Re: Unable to cast object of type 'SingleResult`1[Get_LeaveResult]' to type 'System.Data.DataTabl...
Feb 27, 2012 03:13 AM|LINK
When you are using SP with Entity framework or linq....these things return result in the form of some collection
e.g. List, IEnumerable etc. You need to define you own method to convert the collection into datatable.
I mean you can pass the list to a function and define a datatable, loop through collection, create datarows in the process and
adding it to the datatable..and finally return it.
More info:
http://beyondrelational.com/blogs/jalpesh/archive/2010/08/18/entity-framework-4-0-bind-stored-procedure-with-result-entity-class.aspx
demoninside9
Participant
1182 Points
1697 Posts
Re: Unable to cast object of type 'SingleResult`1[Get_LeaveResult]' to type 'System.Data.DataTabl...
Feb 27, 2012 03:57 AM|LINK
see my code below for converting list to datatabel
public DataTable Load<T>(IList<T> data) { LeaveDataContext props = new LeaveDataContext(); var leave = props.Get_Leave(typeof(T)); DataTable table = new DataTable(); for (int i = 0; i < props.Count; i++) { PropertyDescriptor prop = props[i]; table.Columns.Add(prop.Name, prop.PropertyType); } object[] values = new object[props.Count]; foreach (T item in data) { for (int i = 0; i < values.Length; i++) { values[i] = props[i].GetValue(item); } table.Rows.Add(values); } return table; }here datatable named Load, here I need to put a parameter because the sp is used in this code named Get_Leave() need a int parameter Sno; How can I put there, please let me know
Thanks
me_ritz
Star
9337 Points
1447 Posts
Re: Unable to cast object of type 'SingleResult`1[Get_LeaveResult]' to type 'System.Data.DataTabl...
Feb 27, 2012 05:41 AM|LINK
Some Sample Code that i tried at my end,
"TBLEMPLOYEE" structure: ------------------------------- EMP001 SANDEEP MITTAL 30000 EMP002 RITESH KUMAR 25000 EMP003 ABHAY KUMAR 25000 Stored Procedure: ------------------------------- CREATE PROCEDURE dbo.Get_Emp ( @EmpID CHAR(6) ) AS BEGIN SELECT * FROM TBLEMPLOYEE WHERe EMPID = @EmpID END -------------------------------- I imported table, SP to edmx and registered SP using funtion import using "TBLEMPLOYEE" as return type..following the link as suggested in above post. CS Code: -------------------------------- using System.Reflection; protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { testEntities1 test = new testEntities1(); List<TBLEMPLOYEE> v = test.Get_Emp("EMP001").ToList(); DataTable l = Load<TBLEMPLOYEE>(v); } } protected DataTable Load<T>(List<T> param) { DataTable table = new DataTable(); DataRow row = null; PropertyInfo[] info = param[0].GetType().GetProperties(BindingFlags.DeclaredOnly | BindingFlags.Public | BindingFlags.Instance); foreach (PropertyInfo i in info) { table.Columns.Add(i.Name); } object[] values = new object[info.Length]; foreach (T item in param) { for (int i = 0; i < values.Length; i++) { row = table.NewRow(); values[i] = info[i].GetValue(item,null); } table.Rows.Add(values); } return table; } Hope this helps...:-)demoninside9
Participant
1182 Points
1697 Posts
Re: Unable to cast object of type 'SingleResult`1[Get_LeaveResult]' to type 'System.Data.DataTabl...
Feb 28, 2012 03:07 AM|LINK
hi me_ritz I agree with your code. But you are using Linq to entity and I am using linq to sql.
so what Should I need to convert to datatable..?
Thanks
me_ritz
Star
9337 Points
1447 Posts
Re: Unable to cast object of type 'SingleResult`1[Get_LeaveResult]' to type 'System.Data.DataTabl...
Feb 28, 2012 03:51 AM|LINK
Hi demoninside9,
Even if you are using Linq to SQL , Load<T> function remains same.
Linq to sql returns System.Data.Linq.ISingleResult<T>, when working with SP.
So the only place you can convert it to datatable after getting result in System.Data.Linq.ISingleResult<T>
So the change in code would be:
protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { DataClassesDataContext dc = new DataClassesDataContext(); List<Get_EmpResult> list = dc.Get_Emp("EMP001").ToList(); DataTable table = Load<Get_EmpResult>(list); } } //Get_EmpResult is generated by Linq.demoninside9
Participant
1182 Points
1697 Posts
Re: Unable to cast object of type 'SingleResult`1[Get_LeaveResult]' to type 'System.Data.DataTabl...
Feb 28, 2012 04:22 AM|LINK
Thanks a lot me_ritz for hanging on this with me....