LINQ for Stored procedure returning Multiple ResultSets

LINQ for Stored procedure returning Multiple ResultSets When you have SP which is returning multiple recordset and when you try to drag into dbml file then Visual Studio will not able to generate the code for this in the DataContext class. For this you have to manually write the code into Partial class (present with the dbml file), I usually drag my SP into dbml file and then copy past generated code into partial class and modify, just replace "ISingleResult" with "IMultipleResults". Let say you have one SP called "GerResources" which is returning two RecordSets Stored Procedure CREATE PROCEDURE [dbo].[GetResource] ( @ResourceID int ) AS Begin Select Id, Value from Employee where ResourceId = @ResourceID Select Name , Value from Content where ResourceId = @ResourceID End NameSpace using System.Data.Linq; using System.Data.Linq.Mapping; using System.Linq; using System.Linq.Expressions; DataContext Code public partial class YourDataContext : System.Data.Linq.DataContext { [FunctionAttribute(Name = "dbo.GetResource")] [ResultType(typeof(Employee))] [ResultType(typeof(Content))] public IMultipleResults GetResource(int resourceID) { IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), resourceID); return ((IMultipleResults)(result.ReturnValue)); } } Content and Employee is Entity class. public sealed class Employee { private int _id; private string _value; public int Id { get { return _id; } private set { _id = value; } } public string Value { get { return _value; } private set { _value = value; } } } public sealed class Content { private int _name; private string _value; public int Name { get { return _name; } private set { _name = value; } } public string Value { get { return _value; } private set { _value = value; } } } Call DataContext Method // Create and open the connection YourDataContext rdc = new YourDataContext(); //Get multiple records from resource (Employee) List<Employee> emp = result.GetResult<Employee>().ToList(); //Get multiple records from resource (content) List<Content> cont = result.GetResult<Content>().ToList(); //Read the Data from Employee Object foreach (Employee e in emp) { MessageBox.Show(e.Id + e.Value); } //Read the Data from Content Object foreach (Content c in cont) { MessageBox.Show(c.Name + c.Value); } To read only Single records from LINQ Note: If you have single Row returning from SP then use "Single()" method instead of "foreach" loop //Create and open the connection YourDataContext rdc = new YourDataContext(); //Get multiple records from resource (Employee) List<Employee> emp = result.GetResult<Employee>().Single(); //Display the value MessageBox.Show(e.Id + e.Value);

Fiddler is free web debugging tool

Fiddler is a Web Debugging Proxy which logs all HTTP(S) traffic between your computer and the Internet. Fiddler allows you to inspect all HTTP(S) traffic, set breakpoints, and "fiddle" with incoming or outgoing data. Fiddler includes a powerful event-based scripting subsystem, and can be extended using any .NET language. Fiddler is freeware and can debug traffic from virtually any application, including Internet Explorer, Mozilla Firefox, Opera, and thousands more. Download from here http://www.fiddler2.com/fiddler2/version.asp

Error: Unknown Return Type, The return types for the following stored procedures could not be detected….(LINQ).

Error: Unknown Return Type, The return types for the following stored procedures could not be detected….. Error: The return types for the following stored procedures could not be detected. Set the return type for each stored procedure in the Properties window. Situations This error usually comes when you are trying to add your Stored Procedure into DBML (LINQ) file. Cause This problem occurs whenever the designer cannot figure out the return type of the SP. This usually happens when the stored procedure has multiple results or uses a temp table. The SQL Server feature that attempts to derive the Meta data of the function reports no columns for the result shape, so the designer defaults to thinking it only has the single int return value. Solutions · The one way to get these types of stored procedures to work is to edit DBML by hand or write your own method signature for the procedure in a partial class To Handle multiple record set return from stored procedure by LINQ see the link here. · The second way is to avoid using #temp Table in your stored procedure, instead of you can use Table type variable like below (@TempTable) Ex: DECLARE @TempTable TABLE ( AttributeID INT, Value NVARCHAR(200) ) INSERT INTO @TempTable Select * from Attribute OR --Execute SP and insert results into @TempTable INSERT INTO @TempTable Exec GetAttribute @Id You can do all operation which you was doing with #Temp table like Join, Insert, Select etc.

Concatenating Row Values into one column in Oracle, SQL Server Database and LINQ

Concatenating Row Values into one column in Oracle, SQL Server Database and LINQ Concatenating row values is very common requirements in different databases, to write this kind of query is very time consuming, I tried to combine Oracle,SQL Server database and LINQ simple solution into below example. Maybe this is not matched with your requirement but you will get an idea to proceed. Requirement Let say you have one Employee table having FK reference with department table like below Employee table EMP_ID DEPT_ID 1 101 2 101 3 102 4 102 5 102 How to write a select query so that it returns two columns DEPT_ID and Comma Separated employees under that department. Example DEPT_ID EMPLOYEES 101 1,2 102 3,4,5 Solution (ORACLE DATABASE) To achieve above requirement there are different solution available in oracle different versions Oracle 10g: Use COLLECT function like SELECT DEPT_ID, CAST (COLLECT (TO_CHAR(EMP_ID)) AS SYS.ODCIVARCHAR2LIST) AS EMPLOYEES FROM EMPLOYEE GROUP BY DEPT_ID Note: If SYS.ODCIVARCHAR2LIST does not work then you have to create one table object, see below example CREATE OR REPLACE TYPE Collect_Type AS TABLE OF VARCHAR2 (4000); SELECT DEPT_ID, CAST (COLLECT (TO_CHAR(EMP_ID)) AS Collect_Type) AS EMPLOYEES FROM EMPLOYEE GROUP BY DEPT_ID Oracle 11g: Use LISTAGG function like SELECT DEPT_ID, LISTAGG (TO_CHAR(EMP_ID), ', ') WITHIN GROUP (ORDER BY DEPT_ID)as "EMPLOYEES" FROM employees; Ref: http://download.oracle.com/docs/cd/E11882_01/server.112/e10592/functions087.htm Result: DEPT_ID EMPLOYEES 101 1,2 102 3,4,5 Solution (SQL SERVER DATABASE) SQL Server: SQL server database also you can achieve same result in multiple ways, like XML PATH or Custom Function etc. Using XML PATH Select Main.Dept_ID, Left(Main.Employee,Len(Main.Employee)-1) As "Employees" From(Select distinct ST2.Dept_ID, (Select cast(ST1.Emp_ID as varchar(50)) + ',' AS [text()] From dbo.Employee ST1 Where ST1.Dept_ID= ST2.Dept_ID ORDER BY ST1.Dept_ID For XML PATH ('')) [Employee] From dbo.Employee ST2) [Main] Using Custom Function CREATE FUNCTION ConcatEmp(@DeptId varchar(50)) RETURNS VARCHAR(1000) AS BEGIN DECLARE @temp VARCHAR(1000) SELECT @temp = COALESCE(@temp + ', ', '') + cast(Emp_ID as varchar(50)) FROM [Employee] WHERE Dept_ID = @DeptId return @temp END After creating above function you can simply execute in select statement like below SELECT Dept_ID , dbo.ConcatEmp(Dept_ID) AS Employees FROM Employee GROUP BY Dept_ID Result: DEPT_ID EMPLOYEES 101 1,2 102 3,4,5 Solution (LINQ QUERY) LINQ is one of the .NET (Framework 3.0 and above) features to write SQL query in code, if you are trying to achieve same result using LINQ, you can get idea from following example Here I have created one small console application and write following code to get same results as SQL Server and Oracle Database using System; using System.Collections.Generic; using System.Linq; using System.Text; namespace ConsoleApplication1 { public class Test { static void Main() { var Employee = new[] { new { Dept="101", EmpId="1" }, new { Dept="101", EmpId="2" }, new { Dept="102", EmpId="3" }, new { Dept="102", EmpId="4" }, new { Dept="102", EmpId="5" }, }; var groupedUsers = Employee.GroupBy(Dept => Dept.Dept); foreach (var group in groupedUsers) { Console.Write(" {0} :", group.Key); foreach (var entry in group) { Console.Write(" {0}", entry.EmpId); } Console.WriteLine(); } Console.Read(); } } } Result: ---------- 101: 1,2 102: 3,4,5

How to get the time difference between two date columns? Oracle & SQL Server

How to get the time difference between two date columns? Oracle & SQL Server Oracle allows two date values to be subtracted from each other returning a numeric value indicating the number of days between the two dates (may be a fraction). This example will show how to relate it back to a time value. Test Data: SQL> CREATE TABLE dates (date1 DATE, date2 DATE); Table created. SQL> INSERT INTO dates VALUES (SYSDATE, SYSDATE-1); 1 row created. SQL> INSERT INTO dates VALUES (SYSDATE, SYSDATE-1/24); 1 row created. SQL> INSERT INTO dates VALUES (SYSDATE, SYSDATE-1/60/24); 1 row created. SQL> SELECT (date1 - date2) FROM dates; DATE1-DATE2 ----------------------- 1 .041666667 .000694444 Solution 1 SQL> SELECT floor(((date1-date2)*24*60*60)/3600) 2 ' HOURS ' 3 floor((((date1-date2)*24*60*60) - 4 floor(((date1-date2)*24*60*60)/3600)*3600)/60) 5 ' MINUTES ' 6 round((((date1-date2)*24*60*60) - 7 floor(((date1-date2)*24*60*60)/3600)*3600 - 8 (floor((((date1-date2)*24*60*60) - 9 floor(((date1-date2)*24*60*60)/3600)*3600)/60)*60) )) 10 ' SECS ' time_difference 11 FROM dates; TIME_DIFFERENCE -------------------------------------------------------------------------------- 24 HOURS 0 MINUTES 0 SECS 1 HOURS 0 MINUTES 0 SECS 0 HOURS 1 MINUTES 0 SECS Solution 2 If you don't want to go through the floor and ceiling maths, try this method: SQL> SELECT to_number( to_char(to_date('1','J') + 2 (date1 - date2), 'J') - 1) days, 3 to_char(to_date('00:00:00','HH24:MI:SS') + 4 (date1 - date2), 'HH24:MI:SS') time 5 FROM dates; DAYS TIME ---------- -------- 1 00:00:00 0 01:00:00 0 00:01:00 Solution 3 Here is a simpler method: SQL> SELECT trunc(date1-date2) days, 2 to_char(trunc(sysdate) + (date1 - date2), 'HH24 "Hours" MI "Minutes" SS "Seconds"') time 3 FROM dates; DAYS TIME ---------- ------------------------------ 1 00 Hours 00 Minutes 00 Seconds 0 01 Hours 00 Minutes 00 Seconds 0 00 Hours 01 Minutes 00 Seconds How to add a day/hour/minute/second to a date value? Oracle The SYSDATE pseudo-column shows the current system date and time. Adding 1 to SYSDATE will advance the date by 1 day. Use fractions to add hours, minutes or seconds to the date. Look at these examples: SQL> select sysdate, sysdate+1/24, sysdate +1/1440, sysdate + 1/86400 from dual; SYSDATE SYSDATE+1/24 SYSDATE+1/1440 SYSDATE+1/86400 -------------------- -------------------- -------------------- -------------------- 03-Jul-2002 08:32:12 03-Jul-2002 09:32:12 03-Jul-2002 08:33:12 03-Jul-2002 08:32:13 SQL server: DATEDIFF: This Function has been used to get the difference between two dates Reference: Oracle: http://www.orafaq.com/faq/how_does_one_get_the_time_difference_between_two_date_columns SQL Server : http://www.java2s.com/Code/SQLServer/Date-Timezone/DATEDIFFreturnthedifferencebetweentwodates.htm